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 <br...@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 <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 00f8ffb..51bfe31
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 351,357 ****
for both tests.
This definition conforms to the SQL standard, and is a change from the
inconsistent behavior exhibited by <productname>PostgreSQL</productname>
! versions prior to 8.2.
</para>
</note>
--- 351,359 ----
for both tests.
This definition conforms to the SQL standard, and is a change from the
inconsistent behavior exhibited by <productname>PostgreSQL</productname>
! versions prior to 8.2. Row expressions inside row
! expressions are processed as non-null, even if the sub-row contains only
! nulls.
</para>
</note>
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
new file mode 100644
index 76c032c..ce7047f
*** a/src/backend/optimizer/util/clauses.c
--- b/src/backend/optimizer/util/clauses.c
*************** eval_const_expressions_mutator(Node *nod
*** 3100,3109 ****
return makeBoolConst(false, false);
continue;
}
newntest = makeNode(NullTest);
newntest->arg = (Expr *) relem;
newntest->nulltesttype = ntest->nulltesttype;
! newntest->argisrow = type_is_rowtype(exprType(relem));
newargs = lappend(newargs, newntest);
}
/* If all the inputs were constants, result is TRUE */
--- 3100,3124 ----
return makeBoolConst(false, false);
continue;
}
+ /* Do we have a ROW() expression in the row? */
+ if (type_is_rowtype(exprType(relem)))
+ {
+ /*
+ * ROW values in ROW values are not null, even if
+ * those sub-ROW values contain only nulls. This
+ * makes the code match PL/pgSQL and constraint
+ * IS NULL checks which don't probe into sub-ROWs
+ * for NULL values.
+ */
+ if (ntest->nulltesttype == IS_NULL)
+ return makeBoolConst(false, false);
+ continue;
+ }
+
newntest = makeNode(NullTest);
newntest->arg = (Expr *) relem;
newntest->nulltesttype = ntest->nulltesttype;
! newntest->argisrow = false;
newargs = lappend(newargs, newntest);
}
/* If all the inputs were constants, result is TRUE */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
