Обсуждение: 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 http://mark.stosberg.com/ 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 -- http://mark.stosberg.com/