Re: strange IS NULL behaviour

On Thu, Jul  4, 2013 at 04:29:20PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > I developed the attached patch which properly recurses into ROW()
> > records checking for NULLs;  you can see it returns the right answer in
> > all cases (and constant folds too):
> 
> My recollection of the previous discussion is that we didn't have
> consensus on what the "right" behavior is, so I'm not sure you can just
> assert that this patch is right.  In any case this is only touching the
> tip of the iceberg.  If we intend that rows of nulls should be null,
> then we have got issues with, for example, NOT NULL column constraint
> checks, which don't have any such recursion built into them.  I think
> the same is true for plpgsql variable NOT NULL restrictions, and there
> are probably some other places.

Well we have three cases:
1  SELECT ROW(NULL) IS NULL;2  SELECT ROW(ROW(NULL)) IS NULL;3  SELECT ROW(ROW(ROW(NULL))) IS NULL;

I think we could have them all return false, or all true, or the first
one true, and the rest false.  What I don't think we can justify is 1
and 2 as true, and 3 false.

Can someone show how those others behave?  I don't know enough to test
it.

> > The optimizer seems like the right place to fix this, per my patch.
> 
> No, it isn't, or at least it's far from the only place.  If we're going
> to change this, we would also want to change the behavior of tests on
> RECORD values, which is something that would have to happen at runtime.

I checked RECORD and that behaves with recursion:
SELECT RECORD(NULL) IS NULL; ?column?---------- tSELECT RECORD(RECORD(NULL)) IS NULL; ?column?---------- tSELECT
RECORD(RECORD(RECORD(NULL)))IS NULL; ?column?---------- t
 

Am I missing something?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +