Re: strange IS NULL behaviour

On Mon, Nov 26, 2012 at 09:29:19PM +0100, Hannu Krosing wrote:
> On 11/26/2012 09:05 PM, Tom Lane wrote:
> >Hannu Krosing <hannu(at)2ndQuadrant(dot)com> writes:
> >>In some previous mail Tom Lane claimed that by SQL standard
> >>either an array of all NULLs or a record with all fields NULLs (I
> >>don't remember which) is also considered NULL. If this is true,
> >>then an empty array - which can be said to consist of nothing
> >>but NULLs - should itself be NULL.
> >What I think you're referring to is that the spec says that "foo IS
> >NULL" should return true if foo is a record containing only null fields.
> Is this requirement recursive ?
>
> That is , should
>
> ROW(NULL, NULL, ROW(NULL, ROW(NULL, NULL))) IS NULL
> also be true ?
>
> Currently PostgreSQL does this kind of IS NULL for "simple" rows
>
> hannu=# SELECT ROW(NULL, NULL) IS NULL;
> ?column?
> ----------
> t
> (1 row)
>
> and also for first level row types
>
> hannu=# SELECT ROW(NULL, ROW(NULL, NULL)) IS NULL;
> ?column?
> ----------
> t
> (1 row)
>
> but then mysteriously stops working at third level
>
> hannu=# SELECT ROW(NULL, NULL, ROW(NULL, ROW(NULL, NULL))) IS NULL;
> ?column?
> ----------
> f
> (1 row)

I finally had time to look at this, and it is surprising. I used
EXPLAIN VERBOSE to see what the optimizer was outputting:

EXPLAIN VERBOSE SELECT ROW(null) IS NULL;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
--> Output: true

EXPLAIN VERBOSE SELECT ROW(ROW(null)) IS NULL;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
--> Output: (ROW(NULL::unknown) IS NULL)

EXPLAIN VERBOSE SELECT ROW(ROW(ROW(null))) IS NULL;
QUERY PLAN
---------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
--> Output: (ROW(ROW(NULL::unknown)) IS NULL)

The first test outputs a constant, 'true'. The second test is
ROW(NULL::unknown) because the inner ROW(NULL) was converted to
NULL:unknown. The third one, which returns false (wrong), happens
because you have ROW embedded in ROW, which the optimizer can't process,
and the executor can't either.

I developed the attached patch which properly recurses into ROW()
records checking for NULLs; you can see it returns the right answer in
all cases (and constant folds too):

test=> EXPLAIN VERBOSE SELECT ROW(null) IS NULL;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
--> Output: true
(2 rows)

test=> EXPLAIN VERBOSE SELECT ROW(ROW(null)) IS NULL;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
--> Output: true
(2 rows)

test=> EXPLAIN VERBOSE SELECT ROW(ROW(ROW(null))) IS NULL;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
--> Output: true
(2 rows)

You might think the problem is only with constants, but it extends to
column values too (non-patched server):

CREATE TABLE test (x INT);
CREATE TABLE

INSERT INTO test VALUES (1), (NULL);
INSERT 0 2

SELECT ROW(x) IS NULL FROM test;
?column?
----------
f
t

SELECT ROW(ROW(x)) IS NULL FROM test;
?column?
----------
f
t

SELECT ROW(ROW(ROW(x))) IS NULL FROM test;
?column?
----------
--> f
--> f

With the patch, that works too:

SELECT ROW(ROW(ROW(x))) IS NULL FROM test;
?column?
----------
f
t

The optimizer seems like the right place to fix this, per my patch. It
already flattens IS NULL tests into a series of AND clauses, and now by
recursing, it handles nested ROW values properly too.

This fix would be for head only.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +