Thread: [BUGS] BUG #14665: Wrong IS NULL result for composite type that hascomposite element

The following bug has been logged on the website:

Bug reference:      14665
Logged by:          Julius Tuskenis
Email address:      julius.tuskenis@gmail.com
PostgreSQL version: 9.6.3
Operating system:   Windows 10
Description:


According to the documentation
(https://www.postgresql.org/docs/9.4/static/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"  

The problem is with the composite types that have composite types in them.
In cases then a subtype has all NULL fields the type is still evaluated NOT
NULL.

The code to reproduce the problem:

CREATE TYPE public.my_point AS  (x integer,   y integer);
ALTER TYPE public.my_point OWNER TO postgres;

/*A pixel has a colored point */
CREATE TYPE public.my_pixel AS  (p public.my_point,   color integer);
ALTER TYPE public.my_pixel OWNER TO postgres; 

SELECT  (a).p IS NULL AS point_is_null , (a).color IS NULL AS color_is_null , (a) IS NULL as my_pixel_is_null
FROM ( VALUES   ((ROW(1,2), 1)::my_pixel)   , ((ROW(1,2), NULL)::my_pixel)   , ((ROW(NULL,NULL), NULL)::my_pixel)   ,
((NULL,NULL)::my_pixel) 
 
) AS tbl(a);

RESULT:
f;f;f
f;t;f
t;t;f  <-- components are NULL, but the result is not...
t;t;t





--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

julius.tuskenis@gmail.com writes:
> The problem is with the composite types that have composite types in them.
> In cases then a subtype has all NULL fields the type is still evaluated NOT
> NULL.

Yeah, it's not recursive.  We concluded that that's correct per the
text of the SQL standard.  See discussion here:

https://www.postgresql.org/message-id/flat/20160708024746.1410.57282%40wrigleys.postgresql.org
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs