Thread: BUG #4076: "IS NOT NULL (IS NULL)" return wrong answer even where all fields are NULL(or NOT NULL).
BUG #4076: "IS NOT NULL (IS NULL)" return wrong answer even where all fields are NULL(or NOT NULL).
From
"Tatsuhito Kasahara"
Date:
The following bug has been logged online: Bug reference: 4076 Logged by: Tatsuhito Kasahara Email address: kasahara.tatsuhito@oss.ntt.co.jp PostgreSQL version: 8.1.11 Operating system: Red Hat Enterprise Linux 5.1 Description: "IS NOT NULL (IS NULL)" return wrong answer even where all fields are NULL(or NOT NULL). Details: I noticed that "IS NOT NULL" and "IS NULL" return wrong answer in following case. ===================================================== CREATE TABLE tbl (i int, j int); INSERT INTO tbl VALUES (1, 2); INSERT INTO tbl VALUES (1, NULL); INSERT INTO tbl VALUES (NULL, 2); INSERT INTO tbl VALUES (NULL, NULL); SELECT * FROM (SELECT * FROM tbl) AS row WHERE row IS NOT NULL; i | j ---+--- 1 | 2 1 | | 2 | (4 rows) SELECT * FROM (SELECT * FROM tbl) AS row WHERE row IS NULL; i | j ---+--- (0 rows) ===================================================== Then PostgreSQL used "Filter: (ROW(i, j) IS NOT NULL)" and "Filter: (ROW(i, j) IS NULL)". "SELECT * FROM tbl WHERE ROW(i,j) IS NOT NULL;" and "SELECT * FROM tbl WHERE ROW(i,j) IS NULL" seemed right action. ===================================================== SELECT * FROM tbl WHERE ROW(i,j) IS NOT NULL; i | j ---+--- 1 | 2 1 | | 2 (3 rows) SELECT * FROM tbl WHERE ROW(i,j) IS NULL; i | j ---+--- | (1 row) ===================================================== Is this a bug? # And 8.2.x and 8.3.x seemed to be all right in the case of action both "(x)AS row WHERE row IS NOT NULL" and "(x) AS row WHERE row IS NULL". I think following fix is related.. http://archives.postgresql.org/pgsql-committers/2006-09/msg00439.php
Re: BUG #4076: "IS NOT NULL (IS NULL)" return wrong answer even where all fields are NULL(or NOT NULL).
From
Tom Lane
Date:
"Tatsuhito Kasahara" <kasahara.tatsuhito@oss.ntt.co.jp> writes: > PostgreSQL version: 8.1.11 > Description: "IS NOT NULL (IS NULL)" return wrong answer even where > all fields are NULL(or NOT NULL). > I think following fix is related.. > http://archives.postgresql.org/pgsql-committers/2006-09/msg00439.php Yup, it is. Why are you reporting this? regards, tom lane
Re: BUG #4076: "IS NOT NULL (IS NULL)" return wrong answer even where all fields are NULL(or NOT NULL).
From
Tatsuhito Kasahara
Date:
Hi. Tom Lane wrote: >> I think following fix is related.. >> http://archives.postgresql.org/pgsql-committers/2006-09/msg00439.php > > Yup, it is. Why are you reporting this? Document(for 8.1.11) said "A row value is considered not null if it has at least one field that is not null. ". http://www.postgresql.org/docs/8.1/static/functions-comparisons.html#AEN13425 But "IS NOT NULL" action seemed that it also evaluate the row is not null if the row has no field that is not null in such case. I didn't know the action is valid or invalid .. So I reported this. Best regards. -- NTT OSS Center Tatsuhito Kasahara kasahara.tatsuhito@oss.ntt.co.jp