From: | Wilm Hoyer <W(dot)Hoyer(at)dental-vision(dot)de> |
---|---|
To: | "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | XML column causes record to be both NULL and NOT NULL |
Date: | 2023-07-04 18:46:23 |
Message-ID: | 3a116b87d6eb4a6a8040749c991fe961@dental-vision.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-07-04 18:59:24 | Re: XML column causes record to be both NULL and NOT NULL |
Previous Message | Daniel Gustafsson | 2023-07-04 15:43:17 | Re: BUG #17695: Failed Assert in logical replication snapbuild. |