From bcdd8a19490974f73a2473d3a3cece8d3558d676 Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Tue, 26 Jul 2016 15:25:02 -0400
Subject: Fix constant-folding of ROW(...) IS [NOT] NULL with composite fields.
The SQL standard appears to specify that IS [NOT] NULL's tests of field
nullness are non-recursive, ie, we shouldn't consider that a composite
field with value ROW(NULL,NULL) is null for this purpose.
ExecEvalNullTest got this right, but eval_const_expressions did not,
leading to weird inconsistencies depending on whether the expression
was such that the planner could apply constant folding.
Also, adjust the docs to mention that IS [NOT] DISTINCT FROM NULL can be
used as a substitute test if a simple null check is wanted for a rowtype
argument. That motivated reordering things so that IS [NOT] DISTINCT FROM
is described before IS [NOT] NULL. In HEAD, I went a bit further and added
a table showing all the comparison-related predicates.
Per bug #14235. Back-patch to all supported branches, since it's certainly
undesirable that constant-folding should change the semantics.
Report and patch by Andrew Gierth; assorted wordsmithing and revised
regression test cases by me.
Report: <20160708024746.1410.57282@wrigleys.postgresql.org>
---
doc/src/sgml/func.sgml | 70 ++++++++++++++++++++++++--------------------------
1 file changed, 34 insertions(+), 36 deletions(-)
(limited to 'doc/src/sgml')
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 17def7ba2da..b380b5a6723 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -289,6 +289,32 @@
a nonempty range is always implied.
+
+
+ IS DISTINCT FROM
+
+
+ IS NOT DISTINCT FROM
+
+ Ordinary comparison operators yield null (signifying unknown),
+ not true or false, when either input is null. For example,
+ 7 = NULL yields null, as does 7 <> NULL. When
+ this behavior is not suitable, use the
+ IS NOT DISTINCT FROM constructs:
+
+a IS DISTINCT FROM b
+a IS NOT DISTINCT FROM b
+
+ For non-null inputs, IS DISTINCT FROM is
+ the same as the <> operator. However, if both
+ inputs are null it returns false, and if only one input is
+ null it returns true. Similarly, IS NOT DISTINCT
+ FROM is identical to = 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 unknown.
+
+
IS NULL
@@ -320,8 +346,7 @@
expression = NULL
because NULL is not equal to
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.)
@@ -338,7 +363,6 @@
-
If the expression is row-valued, then
IS NULL is true when the row expression itself is null
@@ -346,39 +370,13 @@
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,
IS NULL and 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 PostgreSQL
- versions prior to 8.2.
-
-
-
-
-
- IS DISTINCT FROM
-
-
- IS NOT DISTINCT FROM
-
- Ordinary comparison operators yield null (signifying unknown),
- not true or false, when either input is null. For example,
- 7 = NULL yields null, as does 7 <> NULL. When
- this behavior is not suitable, use the
- IS NOT DISTINCT FROM constructs:
-
-expression IS DISTINCT FROM expression
-expression IS NOT DISTINCT FROM expression
-
- For non-null inputs, IS DISTINCT FROM is
- the same as the <> operator. However, if both
- inputs are null it returns false, and if only one input is
- null it returns true. Similarly, IS NOT DISTINCT
- FROM is identical to = 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 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 row IS DISTINCT FROM NULL
+ or row IS NOT DISTINCT FROM NULL,
+ which will simply check whether the overall row value is null without any
+ additional tests on the row fields.
--
cgit v1.2.3