XML column causes record to be both NULL and NOT NULL

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

Responses

Browse pgsql-bugs by date

  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.