Обсуждение: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results

The following bug has been logged on the website:

Bug reference:      18999
Logged by:          Jinhui Lai
Email address:      jinhui-lai@foxmail.com
PostgreSQL version: 17.5
Operating system:   ubuntu 22.04
Description:

Dear PG developers,

Thanks for reading my report.

I think "SELECT COUNT(c0) FROM t0" = "SELECT COUNT(c0) FROM t0 WHERE c0 IS
NOT NULL" + "SELECT COUNT(c0) FROM t0 WHERE c0 IS NULL". However, the
following case violates this equivalence relationship.

Please you can reproduce it as follows:

CREATE TYPE composite AS (a TEXT, b TEXT);
CREATE TABLE t0 (c0 composite UNIQUE NOT NULL);
INSERT INTO t0 VALUES ('(,)');
INSERT INTO t0 VALUES ('(NULL,)');
INSERT INTO t0 VALUES ('(,NULL)');

SELECT COUNT(c0) FROM t0 ;
 count
-------
     3

SELECT COUNT(c0) FROM t0 WHERE c0 IS NOT NULL;
 count
-------
     0

SELECT COUNT(c0) FROM t0 WHERE c0 IS NULL;
 count
-------
     1

SELECT COUNT(c0) FROM t0 WHERE c0 IS NULL OR c0 IS NOT NULL;
 count
-------
     1


SELECT VERSION()
version
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.5 (Debian 17.5-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

Thanks for you time.

Best regards,
Jinhui

PG Bug reporting form <noreply@postgresql.org> writes:
> I think "SELECT COUNT(c0) FROM t0" = "SELECT COUNT(c0) FROM t0 WHERE c0 IS
> NOT NULL" + "SELECT COUNT(c0) FROM t0 WHERE c0 IS NULL".

This is not so when c0 is composite.  Per [1]:

    If the expression is row-valued, then IS NULL is true when the row
    expression itself is null or when all the row's fields are null,
    while IS NOT NULL is true when the row expression itself is
    non-null and all the row's fields are non-null. Because of this
    behavior, IS NULL and IS NOT NULL do not always return inverse
    results for row-valued expressions; in particular, a row-valued
    expression that contains both null and non-null fields will return
    false for both tests.

Pretty weird, I agree, but that's what the SQL standard
says to do.

            regards, tom lane

[1] https://www.postgresql.org/docs/current/functions-comparison.html

"=?utf-8?B?SmluaHVpIExhaQ==?=" <jinhui-lai@foxmail.com> writes:
> Thanks for your reply, I get your detailed explanation. I also agree that is weird. Especially, the the c0's
constraintis NOT NULL. 

Yeah.  We have adopted the policy that the spec's definition of this
applies only to the specific SQL expression constructs "c IS NULL"
and "c IS NOT NULL".  Elsewhere we generally take the position that
nullness is a simple boolean property: there is a value there, or
there isn't.  Whether a row value contains some null columns doesn't
change that: if the container is there then it's not null.

Legalistic reading of the spec text offers some support for this
position, but I don't know whether the committee members actually
think that way or just have failed to clarify the text enough.
In any case, that's our position and we're quite unlikely to change
it, for reasons of backwards compatibility and performance.

            regards, tom lane