pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG: IS NOT NULL on RECORD variable fails in 17.5-dev
4+ messages / 3 participants
[nested] [flat]
* BUG: IS NOT NULL on RECORD variable fails in 17.5-dev
@ 2025-08-07 19:43 Peter Praxmarer <ppraxmarer@gmail.com>
2025-08-07 19:53 ` Re: BUG: IS NOT NULL on RECORD variable fails in 17.5-dev Tom Lane <tgl@sss.pgh.pa.us>
2025-08-07 19:54 ` Re: BUG: IS NOT NULL on RECORD variable fails in 17.5-dev David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Peter Praxmarer @ 2025-08-07 19:43 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG: IS NOT NULL on RECORD variable fails in 17.5-dev
2025-08-07 19:43 BUG: IS NOT NULL on RECORD variable fails in 17.5-dev Peter Praxmarer <ppraxmarer@gmail.com>
@ 2025-08-07 19:53 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2025-08-07 19:53 UTC (permalink / raw)
To: Peter Praxmarer <ppraxmarer@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG: IS NOT NULL on RECORD variable fails in 17.5-dev
2025-08-07 19:43 BUG: IS NOT NULL on RECORD variable fails in 17.5-dev Peter Praxmarer <ppraxmarer@gmail.com>
@ 2025-08-07 19:54 ` David G. Johnston <david.g.johnston@gmail.com>
2025-08-07 20:15 ` Re: BUG: IS NOT NULL on RECORD variable fails in 17.5-dev Peter Praxmarer <ppraxmarer@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2025-08-07 19:54 UTC (permalink / raw)
To: Peter Praxmarer <ppraxmarer@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG: IS NOT NULL on RECORD variable fails in 17.5-dev
2025-08-07 19:43 BUG: IS NOT NULL on RECORD variable fails in 17.5-dev Peter Praxmarer <ppraxmarer@gmail.com>
2025-08-07 19:54 ` Re: BUG: IS NOT NULL on RECORD variable fails in 17.5-dev David G. Johnston <david.g.johnston@gmail.com>
@ 2025-08-07 20:15 ` Peter Praxmarer <ppraxmarer@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Praxmarer @ 2025-08-07 20:15 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-08-07 20:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-07 19:43 BUG: IS NOT NULL on RECORD variable fails in 17.5-dev Peter Praxmarer <ppraxmarer@gmail.com>
2025-08-07 19:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-08-07 19:54 ` David G. Johnston <david.g.johnston@gmail.com>
2025-08-07 20:15 ` Peter Praxmarer <ppraxmarer@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox