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 18:38:58
Message-ID: 27916.1159468738@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
I wrote:
> 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.

Now that I look more closely, makeRowNullTest() is actually WRONG even
for the cases it handles.  SQL99/SQL2003 define <null predicate> thus:

         1) Let R be the value of the <row value expression>.

         2) If every value in R is the null value, then "R IS NULL" is true;
            otherwise, it is false.

         3) If no value in R is the null value, then "R IS NOT NULL" is
            true; otherwise, it is false.

makeRowNullTest() is set up to return TRUE for an IS NOT NULL test if
*any* element of R is non null:

regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is not null;

                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on int8_tbl x  (cost=0.00..1.05 rows=5 width=16)
   Filter: ((q1 IS NOT NULL) OR (q2 IS NOT NULL))
(2 rows)

So this is clearly a bug and clearly one of long standing --- we've been
getting this wrong since PG 7.3 :-(

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Luke LonerganDate: 2006-09-28 18:39:31
Subject: Re: New version of money type
Previous:From: Joshua D. DrakeDate: 2006-09-28 18:37:57
Subject: Re: contrib uninstall scripts need some love

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