Skip site navigation (1) Skip section navigation (2)

Re: Row IS NULL question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Teodor Sigaev <teodor(at)sigaev(dot)ru>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Row IS NULL question
Date: 2006-09-28 15:45:32
Message-ID: 26107.1159458332@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Teodor Sigaev <teodor(at)sigaev(dot)ru> writes:
> % echo 'SELECT count(*) FROM tst WHERE ROW(tst.*) IS NULL;' | psql wow
> SET
>   count
> -------
>       0
> (1 row)

Hm, it turns out that this works:
	select * from int8_tbl x where row(x.q1,x.q2) is null;
but not this:
	select * from int8_tbl x where row(x.*) is null;

EXPLAIN tells the tale:

regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is null;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on int8_tbl x  (cost=0.00..1.05 rows=1 width=16)
   Filter: ((q1 IS NULL) AND (q2 IS NULL))
(2 rows)

regression=# explain select * from int8_tbl x where row(x.*) is null;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on int8_tbl x  (cost=0.00..1.05 rows=1 width=16)
   Filter: (x.* IS NULL)
(2 rows)

Apparently what's happening is that gram.y's makeRowNullTest() bursts
the RowExpr apart into individual isnull tests.  Now that RowExpr
expansion can change the number of items in the row, it's clearly
premature to do that processing in gram.y --- we should move it to
parse analysis.

Part of the issue is that ExecEvalNullTest simply tests for whether the
presented Datum is null, which I think is impossible for a whole-row Var
coming from a table (but it could happen for a row-returning function's
result, for example).  I think that according to the letter of the spec,
an IS [NOT] NULL test should "drill down" into rowtype datums and check
nullness of the individual row fields.  Probably the same is true for
array datums.

Moving makeRowNullTest() doesn't seem like a big deal, but changing
ExecEvalNullTest would take some added code.  Do we want to tackle that
during beta, or hold off till 8.3?  An argument for doing it now is that
we just added nulls-in-arrays in 8.2, and it'd be good if the semantics
of that were right the first time rather than changing later.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-09-28 15:56:04
Subject: Re: Faster StrNCpy
Previous:From: luis garciaDate: 2006-09-28 15:43:09
Subject: Re: Constant changes (Re-Build)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group