Re: Row IS NULL question

On Thu, Sep 28, 2006 at 11:45:32AM -0400, Tom Lane wrote:
> 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.

I think this qualifies as a bug fix and should go in 8.2 :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!