diff options
| -rw-r--r-- | doc/src/sgml/func.sgml | 70 | ||||
| -rw-r--r-- | src/backend/executor/execQual.c | 15 | ||||
| -rw-r--r-- | src/backend/optimizer/util/clauses.c | 13 | ||||
| -rw-r--r-- | src/test/regress/expected/rowtypes.out | 54 | ||||
| -rw-r--r-- | src/test/regress/sql/rowtypes.sql | 24 |
5 files changed, 136 insertions, 40 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index a15cf2d1791..d6ed0ce7a68 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -291,6 +291,32 @@ <para> <indexterm> + <primary>IS DISTINCT FROM</primary> + </indexterm> + <indexterm> + <primary>IS NOT DISTINCT FROM</primary> + </indexterm> + Ordinary comparison operators yield null (signifying <quote>unknown</>), + not true or false, when either input is null. For example, + <literal>7 = NULL</> yields null, as does <literal>7 <> NULL</>. When + this behavior is not suitable, use the + <literal>IS <optional> NOT </> DISTINCT FROM</literal> constructs: +<synopsis> +<replaceable>a</replaceable> IS DISTINCT FROM <replaceable>b</replaceable> +<replaceable>a</replaceable> IS NOT DISTINCT FROM <replaceable>b</replaceable> +</synopsis> + For non-null inputs, <literal>IS DISTINCT FROM</literal> is + the same as the <literal><></> operator. However, if both + inputs are null it returns false, and if only one input is + null it returns true. Similarly, <literal>IS NOT DISTINCT + FROM</literal> is identical to <literal>=</literal> for non-null + inputs, but it returns true when both inputs are null, and false when only + one input is null. Thus, these constructs effectively act as though null + were a normal data value, rather than <quote>unknown</>. + </para> + + <para> + <indexterm> <primary>IS NULL</primary> </indexterm> <indexterm> @@ -320,8 +346,7 @@ <literal><replaceable>expression</replaceable> = NULL</literal> because <literal>NULL</> is not <quote>equal to</quote> <literal>NULL</>. (The null value represents an unknown value, - and it is not known whether two unknown values are equal.) This - behavior conforms to the SQL standard. + and it is not known whether two unknown values are equal.) </para> <tip> @@ -338,7 +363,6 @@ </para> </tip> - <note> <para> If the <replaceable>expression</replaceable> is row-valued, then <literal>IS NULL</> is true when the row expression itself is null @@ -346,39 +370,13 @@ <literal>IS NOT NULL</> is true when the row expression itself is non-null and all the row's fields are non-null. Because of this behavior, <literal>IS NULL</> and <literal>IS NOT NULL</> do not always return - inverse results for row-valued expressions, i.e., a row-valued - expression that contains both NULL and non-null values will return false - 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> - - <para> - <indexterm> - <primary>IS DISTINCT FROM</primary> - </indexterm> - <indexterm> - <primary>IS NOT DISTINCT FROM</primary> - </indexterm> - Ordinary comparison operators yield null (signifying <quote>unknown</>), - not true or false, when either input is null. For example, - <literal>7 = NULL</> yields null, as does <literal>7 <> NULL</>. When - this behavior is not suitable, use the - <literal>IS <optional> NOT </> DISTINCT FROM</literal> constructs: -<synopsis> -<replaceable>expression</replaceable> IS DISTINCT FROM <replaceable>expression</replaceable> -<replaceable>expression</replaceable> IS NOT DISTINCT FROM <replaceable>expression</replaceable> -</synopsis> - For non-null inputs, <literal>IS DISTINCT FROM</literal> is - the same as the <literal><></> operator. However, if both - inputs are null it returns false, and if only one input is - null it returns true. Similarly, <literal>IS NOT DISTINCT - FROM</literal> is identical to <literal>=</literal> for non-null - inputs, but it returns true when both inputs are null, and false when only - one input is null. Thus, these constructs effectively act as though null - were a normal data value, rather than <quote>unknown</>. + inverse results for row-valued expressions; in particular, a row-valued + expression that contains both null and non-null fields will return false + for both tests. In some cases, it may be preferable to + write <replaceable>row</replaceable> <literal>IS DISTINCT FROM NULL</> + or <replaceable>row</replaceable> <literal>IS NOT DISTINCT FROM NULL</>, + which will simply check whether the overall row value is null without any + additional tests on the row fields. </para> <para> diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c index f2e8ee2f77b..c56a509fc97 100644 --- a/src/backend/executor/execQual.c +++ b/src/backend/executor/execQual.c @@ -3793,6 +3793,21 @@ ExecEvalNullTest(NullTestState *nstate, if (ntest->argisrow && !(*isNull)) { + /* + * The SQL standard defines IS [NOT] NULL for a non-null rowtype + * argument as: + * + * "R IS NULL" is true if every field is the null value. + * + * "R IS NOT NULL" is true if no field is the null value. + * + * This definition is (apparently intentionally) not recursive; so our + * tests on the fields are primitive attisnull tests, not recursive + * checks to see if they are all-nulls or no-nulls rowtypes. + * + * The standard does not consider the possibility of zero-field rows, + * but here we consider them to vacuously satisfy both predicates. + */ HeapTupleHeader tuple; Oid tupType; int32 tupTypmod; diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index e6d83d4fd93..6093c5419d7 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -3330,7 +3330,7 @@ eval_const_expressions_mutator(Node *node, arg = eval_const_expressions_mutator((Node *) ntest->arg, context); - if (arg && IsA(arg, RowExpr)) + if (ntest->argisrow && arg && IsA(arg, RowExpr)) { /* * We break ROW(...) IS [NOT] NULL into separate tests on @@ -3342,8 +3342,6 @@ eval_const_expressions_mutator(Node *node, List *newargs = NIL; ListCell *l; - Assert(ntest->argisrow); - foreach(l, rarg->args) { Node *relem = (Node *) lfirst(l); @@ -3362,10 +3360,17 @@ eval_const_expressions_mutator(Node *node, return makeBoolConst(false, false); continue; } + + /* + * Else, make a scalar (argisrow == false) NullTest + * for this field. Scalar semantics are required + * because IS [NOT] NULL doesn't recurse; see comments + * in ExecEvalNullTest(). + */ newntest = makeNode(NullTest); newntest->arg = (Expr *) relem; newntest->nulltesttype = ntest->nulltesttype; - newntest->argisrow = type_is_rowtype(exprType(relem)); + newntest->argisrow = false; newntest->location = ntest->location; newargs = lappend(newargs, newntest); } diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out index 3630ef49438..2971640b4bd 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -657,3 +657,57 @@ select row_to_json(r) from (select q2,q1 from tt1 offset 0) r; {"q2":0,"q1":0} (3 rows) +-- +-- IS [NOT] NULL should not recurse into nested composites (bug #14235) +-- +explain (verbose, costs off) +select r, r is null as isnull, r is not null as isnotnull +from (values (1,row(1,2)), (1,row(null,null)), (1,null), + (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Values Scan on "*VALUES*" + Output: ROW("*VALUES*".column1, "*VALUES*".column2), (("*VALUES*".column1 IS NULL) AND ("*VALUES*".column2 IS NULL)), (("*VALUES*".column1 IS NOT NULL) AND ("*VALUES*".column2 IS NOT NULL)) +(2 rows) + +select r, r is null as isnull, r is not null as isnotnull +from (values (1,row(1,2)), (1,row(null,null)), (1,null), + (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); + r | isnull | isnotnull +-------------+--------+----------- + (1,"(1,2)") | f | t + (1,"(,)") | f | t + (1,) | f | f + (,"(1,2)") | f | f + (,"(,)") | f | f + (,) | t | f +(6 rows) + +explain (verbose, costs off) +with r(a,b) as + (values (1,row(1,2)), (1,row(null,null)), (1,null), + (null,row(1,2)), (null,row(null,null)), (null,null) ) +select r, r is null as isnull, r is not null as isnotnull from r; + QUERY PLAN +---------------------------------------------------------- + CTE Scan on r + Output: r.*, (r.* IS NULL), (r.* IS NOT NULL) + CTE r + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1, "*VALUES*".column2 +(5 rows) + +with r(a,b) as + (values (1,row(1,2)), (1,row(null,null)), (1,null), + (null,row(1,2)), (null,row(null,null)), (null,null) ) +select r, r is null as isnull, r is not null as isnotnull from r; + r | isnull | isnotnull +-------------+--------+----------- + (1,"(1,2)") | f | t + (1,"(,)") | f | t + (1,) | f | f + (,"(1,2)") | f | f + (,"(,)") | f | f + (,) | t | f +(6 rows) + diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql index 677d34a62c8..a62dee2ef84 100644 --- a/src/test/regress/sql/rowtypes.sql +++ b/src/test/regress/sql/rowtypes.sql @@ -286,3 +286,27 @@ create temp table tt1 as select * from int8_tbl limit 2; create temp table tt2 () inherits(tt1); insert into tt2 values(0,0); select row_to_json(r) from (select q2,q1 from tt1 offset 0) r; + +-- +-- IS [NOT] NULL should not recurse into nested composites (bug #14235) +-- + +explain (verbose, costs off) +select r, r is null as isnull, r is not null as isnotnull +from (values (1,row(1,2)), (1,row(null,null)), (1,null), + (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); + +select r, r is null as isnull, r is not null as isnotnull +from (values (1,row(1,2)), (1,row(null,null)), (1,null), + (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); + +explain (verbose, costs off) +with r(a,b) as + (values (1,row(1,2)), (1,row(null,null)), (1,null), + (null,row(1,2)), (null,row(null,null)), (null,null) ) +select r, r is null as isnull, r is not null as isnotnull from r; + +with r(a,b) as + (values (1,row(1,2)), (1,row(null,null)), (1,null), + (null,row(1,2)), (null,row(null,null)), (null,null) ) +select r, r is null as isnull, r is not null as isnotnull from r; |