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 22:28:55
Message-ID: 1159482535.22539.3.camel@voyager.truesoftware.net (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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 

Would it be correct to do the null test in ExecEvalNullTest 

(I would like to learn what happens but somehow I just cannot get the
debugger on makeRowNullTest in gram.c)

On Thu, 2006-09-28 at 14:38 -0400, Tom Lane wrote:

-- 
Regards,
Gevik Babakhani
http://www.postgresql.nl
http://www.truesoftware.nl







In response to

pgsql-hackers by date

Next:From: Henry B. HotzDate: 2006-09-28 22:46:57
Subject: Re: JAVA Support
Previous:From: Paul B. AndersonDate: 2006-09-28 22:11:55
Subject: Stored procedure array limits

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