Thread: XML column causes record to be both NULL and NOT NULL
Hi,
i found a strange behavior and hope it’s not a pilot error.
PostgreSQL Version:
PostgreSQL 14.8, compiled by Visual C++ build 1914, 64-bit
Reproducer:
CREATE TABLE IF NOT EXISTS public.test
(
num integer NOT NULL,
prev integer
);
INSERT INTO public.test
VALUES (1, null),
(2, 1),
(3, 2);
CREATE OR REPLACE FUNCTION public.steps()
RETURNS SETOF test
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
nextVal public.test;
BEGIN
SELECT * INTO nextVal FROM public.test
WHERE prev = 2;
RAISE NOTICE 'nextVal IS NULL? % or nextVal IS NOT NULL? %', nextVal IS NULL, nextVal IS NOT NULL;
IF nextVal IS NOT NULL THEN
RETURN NEXT nextVal;
END IF;
END;
$BODY$;
SELECT * FROM steps();
-- returns: 3, 2
-- NOTICE: nextVal IS NULL? f or nextVal IS NOT NULL? t
ALTER TABLE public.test ADD COLUMN x xml;
SELECT * FROM steps();
-- returns: nothing ; expected 3, 2
-- NOTICE: nextVal IS NULL? f or nextVal IS NOT NULL? f
I reduced this from a rather complicate function until I stumbled upon the freshly added xml column causing the wrong results.
Sure it could be further condensed, but I hope it’s small enough for a report now.
Best wishes and keep up the great work
Wilm Hoyer
Wilm Hoyer <W.Hoyer@dental-vision.de> writes:
> i found a strange behavior and hope it's not a pilot error.
Well, it is strange, but it's acting per SQL spec.
As stated at [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.
The fact that the added column is of XML type isn't relevant here;
what's relevant is that it has a null value.
regards, tom lane
[1] https://www.postgresql.org/docs/current/functions-comparison.html
> Well, it is strange, but it's acting per SQL spec. > As stated at [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. > The fact that the added column is of XML type isn't relevant here; what's relevant is that it has a null value. > regards, tom lane > [1] https://www.postgresql.org/docs/current/functions-comparison.html Thanks for the Explanation. The solution is clear then - just test part of the record or "flatten" the record with a castto text. Best regards, Wilm.