possible mis-handling of nulls in views in 7.0.2
Hello,
I'm running Postgres 7.0.2 and have run into a curious situation. I
got a back a null value in a select on VIEW that is defined as not
allowing that column to be null. Here's a screenshot:
marvel=> \d shipments;
View = shipments
Query = SELECT "web_data"."shipment_id", "web_data"."order_id",
"web_data"."customer_id", "web_data"."purchase_order_num", "web_data"."actual_ship
_date", "web_data"."pro_num", "sum"("web_data"."qt_ordered") AS
"qt_ordered", "sum"("web_data"."qt_shipped") AS "qt_shipped" FROM
"web_data" WHERE (
"web_data"."shipment_id" NOTNULL) GROUP BY "web_data"."shipment_id",
"web_data"."order_id", "web_data"."customer_id", "web_data"."actual_ship_date",
"web_data"."pro_num", "web_data"."purchase_order_num";
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| shipment_id | varchar() |
32 |
| order_id | varchar() |
100 |
| customer_id | varchar() |
10 |
| purchase_order_num | varchar() |
100 |
| actual_ship_date | date |
4 |
| pro_num | varchar() |
100 |
| qt_ordered | float8 |
8 |
| qt_shipped | float8 |
8 |
+----------------------------------+----------------------------------+-------+
marvel=>### Notice that the shipment_id is NOTNULL
### now watch:
marvel=> select * from shipments where shipment_id is null;
shipment_id|order_id|customer_id|purchase_order_num|actual_ship_date|pro_num|qt_ordered|qt_shipped
-----------+--------+-----------+------------------+----------------+-------+----------+----------
| | | | |
| |
(1 row)
#############
It returns a row with a null shipment id!
I'm not sure what's happening here-- I tried to simplify this to a
simple case, but I couldn't reproduce the bug. Oddly, this null row
doesn't seem to appear in the table web_data that the view references. I
think it's easy enough to work around, but I'm curious what might be
happening here.
Thanks,
-mark
personal website } Summersault Website Development
http://mark.stosberg.com/ { http://www.summersault.com/
Mark Stosberg <mark@summersault.com> writes:
I'm running Postgres 7.0.2 and have run into a curious situation. I
got a back a null value in a select on VIEW that is defined as not
allowing that column to be null.
I think this is an artifact of the curious (not to say broken)
implementation of views pre-7.1. However, it's hard to tell for sure
because I can't reproduce your problem. Are you sure you are running
7.0.2 and not something older? Can you provide a self-contained
example? My test went like this:
play=> select version();
version
------------------------------------------------------------------
PostgreSQL 7.0.2 on hppa2.0-hp-hpux10.20, compiled by gcc 2.95.2
(1 row)
play=> create table foo (f1 int, f2 int);
CREATE
play=> insert into foo values(1,2);
INSERT 873546 1
play=> insert into foo values(1,3);
INSERT 873547 1
play=> insert into foo values(2,4);
INSERT 873548 1
play=> insert into foo values(2,5);
INSERT 873549 1
play=> create view v2 as select f1,sum(f2) from foo where f1 notnull group by f1;
CREATE 873571 1
play=> select * from v2 ;
f1 | sum
----+-----
1 | 5
2 | 9
(2 rows)
play=> select * from v2 where f1 isnull;
f1 | sum
----+-----
(0 rows)
regards, tom lane
Tom,
I tried to build a simple test case and I couldn't reproduce either.
I'm still actively working with that database, though-- If I run into it
again, and can reproduce a condensed case, I'll definitely submit it. It
sounds like views are being improved a good deal in 7.1, so perhaps my
oddity would be repaired anyway. Thanks for checking it out.
-mark
Tom Lane wrote:
Mark Stosberg <mark@summersault.com> writes:
I'm running Postgres 7.0.2 and have run into a curious situation. I
got a back a null value in a select on VIEW that is defined as not
allowing that column to be null.I think this is an artifact of the curious (not to say broken)
implementation of views pre-7.1. However, it's hard to tell for sure
because I can't reproduce your problem. Are you sure you are running
7.0.2 and not something older? Can you provide a self-contained
example? My test went like this:play=> select version();
version
------------------------------------------------------------------
PostgreSQL 7.0.2 on hppa2.0-hp-hpux10.20, compiled by gcc 2.95.2
(1 row)play=> create table foo (f1 int, f2 int);
CREATE
play=> insert into foo values(1,2);
INSERT 873546 1
play=> insert into foo values(1,3);
INSERT 873547 1
play=> insert into foo values(2,4);
INSERT 873548 1
play=> insert into foo values(2,5);
INSERT 873549 1
play=> create view v2 as select f1,sum(f2) from foo where f1 notnull group by f1;
CREATE 873571 1
play=> select * from v2 ;
f1 | sum
----+-----
1 | 5
2 | 9
(2 rows)play=> select * from v2 where f1 isnull;
f1 | sum
----+-----
(0 rows)regards, tom lane
--