From: | Mark Stosberg <mark(at)summersault(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | possible mis-handling of nulls in views in 7.0.2 |
Date: | 2001-03-07 20:36:09 |
Message-ID: | 3AA69B8F.793EF834@summersault.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2001-03-07 20:51:00 | Re: Pbm with aggregates on empty output |
Previous Message | Peter Eisentraut | 2001-03-07 19:37:07 | Re: Using libpq to access a repote database from a c trigger function breaks. |