Horton
Menu

Thread

  1. BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results

    PG Bug reporting form <noreply@postgresql.org> — 2025-07-25T04:43:03Z

    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

  2. Re: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-25T13:28:19Z

    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

  3. Re: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results

    Jinhui Lai <jinhui-lai@foxmail.com> — 2025-07-25T14:40:29Z

    &gt; [1] https://www.postgresql.org/docs/current/functions-comparison.html
&gt; Pretty weird, I agree, but that's what the SQL standard says to do.


Hi, Tom,


Thanks for your reply, I get your detailed explanation. I also agree that is weird. Especially, the the c0's constraint is NOT NULL.


Moreover, this case produce different results among different PG versions.


#PG 17.5, 16, 15, 14, 13, 12, 11, 10...#
CREATE TYPE composite AS (a TEXT, b TEXT);
CREATE TABLE t0 (c0 composite NOT NULL);
INSERT INTO t0 VALUES ('(,)');
INSERT INTO t0 VALUES ('(a,)');
INSERT INTO t0 VALUES ('(,b)');
SELECT COUNT(c0) FROM t0 WHERE c0 IS NOT NULL;
&nbsp;count
-------
&nbsp; &nbsp; &nbsp;0
SELECT COUNT(c0) FROM t0 WHERE c0 IS &nbsp;NULL;
&nbsp;count
-------
&nbsp; &nbsp; &nbsp;1


#PG 17.0-17.4#
CREATE TYPE composite AS (a TEXT, b TEXT);
CREATE TABLE t0 (c0 composite NOT NULL);
INSERT INTO t0 VALUES ('(,)');
INSERT INTO t0 VALUES ('(a,)');
INSERT INTO t0 VALUES ('(,b)');
SELECT COUNT(c0) FROM t0 WHERE c0 IS NOT NULL;
&nbsp;count
-------
&nbsp; &nbsp; &nbsp;3
SELECT COUNT(c0) FROM t0 WHERE c0 IS &nbsp;NULL;
&nbsp;count
-------
&nbsp; &nbsp; &nbsp;0


In my opinion, I think the result of PG 17.0-17.4 is more reasonable, because this result does not conflict with the NOT NULL constraint, nor does it conflict with the equivalence relationship of “3+0=3” (while 1+0 != 3). As you said, this is in line with the SQL standard. I wonder if the change in PG 17.5 to handle NULL and NON-NULL is to comply with the SQL standard.

Thanks for your time.


Best regard,
Jinhui

  4. Re: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-25T15:20:47Z

    "=?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 constraint is 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

  5. Re: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results

    David G. Johnston <david.g.johnston@gmail.com> — 2025-07-25T16:22:12Z

    On Fri, Jul 25, 2025, 07:40 Jinhui Lai <jinhui-lai@foxmail.com> wrote:

>
> #PG 17.5, 16, 15, 14, 13, 12, 11, 10...#
>
> #PG 17.0-17.4#
>

This would be an unintended change/regression when 17.0 came out that was
discovered and fixed in 17.5

Reverting back to long-established behavior should not be a surprising
action to see.

David J.