Обсуждение: XML column causes record to be both NULL and NOT NULL

Поиск
Список
Период
Сортировка

XML column causes record to be both NULL and NOT NULL

От
Wilm Hoyer
Дата:

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

Re: XML column causes record to be both NULL and NOT NULL

От
Tom Lane
Дата:
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

AW: XML column causes record to be both NULL and NOT NULL

От
Wilm Hoyer
Дата:
> 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.