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

Re: Row IS NULL question

From: Gevik Babakhani <pgdev(at)xs4all(dot)nl>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Teodor Sigaev <teodor(at)sigaev(dot)ru>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Row IS NULL question
Date: 2006-09-28 16:00:19
Message-ID: 1159459219.12974.4.camel@voyager.truesoftware.net (view raw or flat)
Thread:
Lists: pgsql-hackers
Please excuse me for jumping in like this... but just for my
understanding...

Does this have anything to do with ExecEvalWholeRowVar?



On Thu, 2006-09-28 at 11:45 -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.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 
-- 
Regards,
Gevik Babakhani
http://www.postgresql.nl
http://www.truesoftware.nl







In response to

Responses

pgsql-hackers by date

Next:From: Luke LonerganDate: 2006-09-28 16:00:30
Subject: Re: New version of money type
Previous:From: Tom LaneDate: 2006-09-28 15:56:04
Subject: Re: Faster StrNCpy

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