On Fri, Jul 5, 2013 at 10:21:19AM -0400, Bruce Momjian wrote:
> 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.
I have done some more research in this, and was able to verify Tom's
concern that PL/pgSQL's IS NULL doesn't recurse into ROW expressions:
DO LANGUAGE plpgsql $$
DECLARE
r RECORD;
BEGIN
SELECT NULL INTO r;
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;
END;
$$;
NOTICE: true
DO
In this test, SELECT NULL (which internally would produce SELECT
ROW(NULL)), returns TRUE, while SELECT ROW(NULL) and further nesting
returns false.
This has made me adjust my goal and change it so SELECT ROW(NULL) IS
NULL returns true, and any further nesting returns false.
Attached is a patch which accomplishes this, and a documentation update.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +