The following bug has been logged online: Bug reference: 4076 Logged by: Tatsuhito Kasahara Email address: [EMAIL PROTECTED] 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
