Обсуждение: BUG #4076: "IS NOT NULL (IS NULL)" return wrong answer even where all fields are NULL(or NOT NULL).

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

"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

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