BUG: IS NOT NULL on RECORD variable fails in 17.5-dev
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.
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]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. 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
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.
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>:
Show quoted text
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.