Thread

BUG: IS NOT NULL on RECORD variable fails in 17.5-dev

Peter Praxmarer <ppraxmarer@gmail.com> — 2025-08-07T19:43:54Z

Hello,
I have discovered what appears to be a severe bug in a development version
of PostgreSQL 17 where a populated RECORD variable incorrectly fails an IS
NOT NULL check.

Version Information:

PostgreSQL 17.5 (Debian 17.5-1) on x86_64-pc-linux-gnu, compiled by gcc
(Debian 14.2.0-19) 14.2.0, 64-bit


*Minimal Reproducible Example:*

DO $$
DECLARE
    r RECORD;
BEGIN
    RAISE NOTICE '--- Assigning a row with a NULL column into "r" ---';
    SELECT 42 AS id, NULL::text AS name INTO r;
    RAISE NOTICE 'The value of r is: %', r;
    ASSERT r IS NOT NULL, 'ASSERTION FAILED: r IS NULL!';
    RAISE NOTICE 'Assertion Passed.';
END $$;


*Actual Output:*
NOTICE:  --- Assigning a row with a NULL column into "r" ---
NOTICE:  The value of r is: (42,)
ERROR:  ASSERTION FAILED: r IS NULL!
CONTEXT:  PL/pgSQL function inline_code_block line 8 at ASSERT

*Expected Output:* The ASSERT should pass without error. The RAISE NOTICE
proves the record is populated immediately before the ASSERT fails. Thank
you.

Re: BUG: IS NOT NULL on RECORD variable fails in 17.5-dev

Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-07T19:53:55Z

Peter Praxmarer <ppraxmarer@gmail.com> writes:
> I have discovered what appears to be a severe bug in a development version
> of PostgreSQL 17 where a populated RECORD variable incorrectly fails an IS
> NOT NULL check.

No, that's behaving per SQL standard, and the same as many PG versions
before it.  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.

IMHO this wasn't one of the SQL committee's better ideas, but we're
stuck with it.

			regards, tom lane

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

Re: BUG: IS NOT NULL on RECORD variable fails in 17.5-dev

David G. Johnston <david.g.johnston@gmail.com> — 2025-08-07T19:54:07Z

On Thu, Aug 7, 2025 at 12:44 PM Peter Praxmarer <ppraxmarer@gmail.com>
wrote:

>
> I have discovered what appears to be a severe bug in a development version
> of PostgreSQL 17 where a populated RECORD variable incorrectly fails an IS
> NOT NULL check.
>
>
Working as documented.  You probably want to test:  NOT (r IS NULL)

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

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.

David J.

Re: BUG: IS NOT NULL on RECORD variable fails in 17.5-dev

Peter Praxmarer <ppraxmarer@gmail.com> — 2025-08-07T20:15:06Z

Thank you!

The behavior I was looking for is:

r IS DISTINCT FROM NULL

Best,
Peter P.


Am Do., 7. Aug. 2025 um 12:54 Uhr schrieb David G. Johnston <
david.g.johnston@gmail.com>:

> On Thu, Aug 7, 2025 at 12:44 PM Peter Praxmarer <ppraxmarer@gmail.com>
> wrote:
>
>>
>> I have discovered what appears to be a severe bug in a development
>> version of PostgreSQL 17 where a populated RECORD variable incorrectly
>> fails an IS NOT NULL check.
>>
>>
> Working as documented.  You probably want to test:  NOT (r IS NULL)
>
> https://www.postgresql.org/docs/current/functions-comparison.html
>
> 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.
>
> David J.
>
>