Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd2ef775

Browse files
committed
Fix constant-folding of ROW(...) IS [NOT] NULL with composite fields.
The SQL standard appears to specify that IS [NOT] NULL's tests of fieldnullness are non-recursive, ie, we shouldn't consider that a compositefield 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 expressionwas such that the planner could apply constant folding.Also, adjust the docs to mention that IS [NOT] DISTINCT FROM NULL can beused as a substitute test if a simple null check is wanted for a rowtypeargument. That motivated reordering things so that IS [NOT] DISTINCT FROMis described before IS [NOT] NULL. In HEAD, I went a bit further and addeda table showing all the comparison-related predicates.Per bug #14235. Back-patch to all supported branches, since it's certainlyundesirable that constant-folding should change the semantics.Report and patch by Andrew Gierth; assorted wordsmithing and revisedregression test cases by me.Report: <20160708024746.1410.57282@wrigleys.postgresql.org>
1 parentcf35406 commitd2ef775

File tree

5 files changed

+136
-40
lines changed

5 files changed

+136
-40
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 34 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -289,6 +289,32 @@
289289
a nonempty range is always implied.
290290
</para>
291291

292+
<para>
293+
<indexterm>
294+
<primary>IS DISTINCT FROM</primary>
295+
</indexterm>
296+
<indexterm>
297+
<primary>IS NOT DISTINCT FROM</primary>
298+
</indexterm>
299+
Ordinary comparison operators yield null (signifying <quote>unknown</>),
300+
not true or false, when either input is null. For example,
301+
<literal>7 = NULL</> yields null, as does <literal>7 &lt;&gt; NULL</>. When
302+
this behavior is not suitable, use the
303+
<literal>IS <optional> NOT </> DISTINCT FROM</literal> constructs:
304+
<synopsis>
305+
<replaceable>a</replaceable> IS DISTINCT FROM <replaceable>b</replaceable>
306+
<replaceable>a</replaceable> IS NOT DISTINCT FROM <replaceable>b</replaceable>
307+
</synopsis>
308+
For non-null inputs, <literal>IS DISTINCT FROM</literal> is
309+
the same as the <literal>&lt;&gt;</> operator. However, if both
310+
inputs are null it returns false, and if only one input is
311+
null it returns true. Similarly, <literal>IS NOT DISTINCT
312+
FROM</literal> is identical to <literal>=</literal> for non-null
313+
inputs, but it returns true when both inputs are null, and false when only
314+
one input is null. Thus, these constructs effectively act as though null
315+
were a normal data value, rather than <quote>unknown</>.
316+
</para>
317+
292318
<para>
293319
<indexterm>
294320
<primary>IS NULL</primary>
@@ -320,8 +346,7 @@
320346
<literal><replaceable>expression</replaceable> = NULL</literal>
321347
because <literal>NULL</> is not <quote>equal to</quote>
322348
<literal>NULL</>. (The null value represents an unknown value,
323-
and it is not known whether two unknown values are equal.) This
324-
behavior conforms to the SQL standard.
349+
and it is not known whether two unknown values are equal.)
325350
</para>
326351

327352
<tip>
@@ -338,47 +363,20 @@
338363
</para>
339364
</tip>
340365

341-
<note>
342366
<para>
343367
If the <replaceable>expression</replaceable> is row-valued, then
344368
<literal>IS NULL</> is true when the row expression itself is null
345369
or when all the row's fields are null, while
346370
<literal>IS NOT NULL</> is true when the row expression itself is non-null
347371
and all the row's fields are non-null. Because of this behavior,
348372
<literal>IS NULL</> and <literal>IS NOT NULL</> do not always return
349-
inverse results for row-valued expressions, i.e., a row-valued
350-
expression that contains both NULL and non-null values will return false
351-
for both tests.
352-
This definition conforms to the SQL standard, and is a change from the
353-
inconsistent behavior exhibited by <productname>PostgreSQL</productname>
354-
versions prior to 8.2.
355-
</para>
356-
</note>
357-
358-
<para>
359-
<indexterm>
360-
<primary>IS DISTINCT FROM</primary>
361-
</indexterm>
362-
<indexterm>
363-
<primary>IS NOT DISTINCT FROM</primary>
364-
</indexterm>
365-
Ordinary comparison operators yield null (signifying <quote>unknown</>),
366-
not true or false, when either input is null. For example,
367-
<literal>7 = NULL</> yields null, as does <literal>7 &lt;&gt; NULL</>. When
368-
this behavior is not suitable, use the
369-
<literal>IS <optional> NOT </> DISTINCT FROM</literal> constructs:
370-
<synopsis>
371-
<replaceable>expression</replaceable> IS DISTINCT FROM <replaceable>expression</replaceable>
372-
<replaceable>expression</replaceable> IS NOT DISTINCT FROM <replaceable>expression</replaceable>
373-
</synopsis>
374-
For non-null inputs, <literal>IS DISTINCT FROM</literal> is
375-
the same as the <literal>&lt;&gt;</> operator. However, if both
376-
inputs are null it returns false, and if only one input is
377-
null it returns true. Similarly, <literal>IS NOT DISTINCT
378-
FROM</literal> is identical to <literal>=</literal> for non-null
379-
inputs, but it returns true when both inputs are null, and false when only
380-
one input is null. Thus, these constructs effectively act as though null
381-
were a normal data value, rather than <quote>unknown</>.
373+
inverse results for row-valued expressions; in particular, a row-valued
374+
expression that contains both null and non-null fields will return false
375+
for both tests. In some cases, it may be preferable to
376+
write <replaceable>row</replaceable> <literal>IS DISTINCT FROM NULL</>
377+
or <replaceable>row</replaceable> <literal>IS NOT DISTINCT FROM NULL</>,
378+
which will simply check whether the overall row value is null without any
379+
additional tests on the row fields.
382380
</para>
383381

384382
<para>

‎src/backend/executor/execQual.c

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3793,6 +3793,21 @@ ExecEvalNullTest(NullTestState *nstate,
37933793

37943794
if (ntest->argisrow&& !(*isNull))
37953795
{
3796+
/*
3797+
* The SQL standard defines IS [NOT] NULL for a non-null rowtype
3798+
* argument as:
3799+
*
3800+
* "R IS NULL" is true if every field is the null value.
3801+
*
3802+
* "R IS NOT NULL" is true if no field is the null value.
3803+
*
3804+
* This definition is (apparently intentionally) not recursive; so our
3805+
* tests on the fields are primitive attisnull tests, not recursive
3806+
* checks to see if they are all-nulls or no-nulls rowtypes.
3807+
*
3808+
* The standard does not consider the possibility of zero-field rows,
3809+
* but here we consider them to vacuously satisfy both predicates.
3810+
*/
37963811
HeapTupleHeadertuple;
37973812
OidtupType;
37983813
int32tupTypmod;

‎src/backend/optimizer/util/clauses.c

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3330,7 +3330,7 @@ eval_const_expressions_mutator(Node *node,
33303330

33313331
arg=eval_const_expressions_mutator((Node*)ntest->arg,
33323332
context);
3333-
if (arg&&IsA(arg,RowExpr))
3333+
if (ntest->argisrow&&arg&&IsA(arg,RowExpr))
33343334
{
33353335
/*
33363336
* We break ROW(...) IS [NOT] NULL into separate tests on
@@ -3342,8 +3342,6 @@ eval_const_expressions_mutator(Node *node,
33423342
List*newargs=NIL;
33433343
ListCell*l;
33443344

3345-
Assert(ntest->argisrow);
3346-
33473345
foreach(l,rarg->args)
33483346
{
33493347
Node*relem= (Node*)lfirst(l);
@@ -3362,10 +3360,17 @@ eval_const_expressions_mutator(Node *node,
33623360
returnmakeBoolConst(false, false);
33633361
continue;
33643362
}
3363+
3364+
/*
3365+
* Else, make a scalar (argisrow == false) NullTest
3366+
* for this field. Scalar semantics are required
3367+
* because IS [NOT] NULL doesn't recurse; see comments
3368+
* in ExecEvalNullTest().
3369+
*/
33653370
newntest=makeNode(NullTest);
33663371
newntest->arg= (Expr*)relem;
33673372
newntest->nulltesttype=ntest->nulltesttype;
3368-
newntest->argisrow=type_is_rowtype(exprType(relem));
3373+
newntest->argisrow=false;
33693374
newntest->location=ntest->location;
33703375
newargs=lappend(newargs,newntest);
33713376
}

‎src/test/regress/expected/rowtypes.out

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -657,3 +657,57 @@ select row_to_json(r) from (select q2,q1 from tt1 offset 0) r;
657657
{"q2":0,"q1":0}
658658
(3 rows)
659659

660+
--
661+
-- IS [NOT] NULL should not recurse into nested composites (bug #14235)
662+
--
663+
explain (verbose, costs off)
664+
select r, r is null as isnull, r is not null as isnotnull
665+
from (values (1,row(1,2)), (1,row(null,null)), (1,null),
666+
(null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
667+
QUERY PLAN
668+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
669+
Values Scan on "*VALUES*"
670+
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))
671+
(2 rows)
672+
673+
select r, r is null as isnull, r is not null as isnotnull
674+
from (values (1,row(1,2)), (1,row(null,null)), (1,null),
675+
(null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
676+
r | isnull | isnotnull
677+
-------------+--------+-----------
678+
(1,"(1,2)") | f | t
679+
(1,"(,)") | f | t
680+
(1,) | f | f
681+
(,"(1,2)") | f | f
682+
(,"(,)") | f | f
683+
(,) | t | f
684+
(6 rows)
685+
686+
explain (verbose, costs off)
687+
with r(a,b) as
688+
(values (1,row(1,2)), (1,row(null,null)), (1,null),
689+
(null,row(1,2)), (null,row(null,null)), (null,null) )
690+
select r, r is null as isnull, r is not null as isnotnull from r;
691+
QUERY PLAN
692+
----------------------------------------------------------
693+
CTE Scan on r
694+
Output: r.*, (r.* IS NULL), (r.* IS NOT NULL)
695+
CTE r
696+
-> Values Scan on "*VALUES*"
697+
Output: "*VALUES*".column1, "*VALUES*".column2
698+
(5 rows)
699+
700+
with r(a,b) as
701+
(values (1,row(1,2)), (1,row(null,null)), (1,null),
702+
(null,row(1,2)), (null,row(null,null)), (null,null) )
703+
select r, r is null as isnull, r is not null as isnotnull from r;
704+
r | isnull | isnotnull
705+
-------------+--------+-----------
706+
(1,"(1,2)") | f | t
707+
(1,"(,)") | f | t
708+
(1,) | f | f
709+
(,"(1,2)") | f | f
710+
(,"(,)") | f | f
711+
(,) | t | f
712+
(6 rows)
713+

‎src/test/regress/sql/rowtypes.sql

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -286,3 +286,27 @@ create temp table tt1 as select * from int8_tbl limit 2;
286286
create temp table tt2 () inherits(tt1);
287287
insert into tt2values(0,0);
288288
select row_to_json(r)from (select q2,q1from tt1 offset0) r;
289+
290+
--
291+
-- IS [NOT] NULL should not recurse into nested composites (bug #14235)
292+
--
293+
294+
explain (verbose, costs off)
295+
select r, r isnullas isnull, ris not nullas isnotnull
296+
from (values (1,row(1,2)), (1,row(null,null)), (1,null),
297+
(null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
298+
299+
select r, r isnullas isnull, ris not nullas isnotnull
300+
from (values (1,row(1,2)), (1,row(null,null)), (1,null),
301+
(null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
302+
303+
explain (verbose, costs off)
304+
with r(a,b)as
305+
(values (1,row(1,2)), (1,row(null,null)), (1,null),
306+
(null,row(1,2)), (null,row(null,null)), (null,null) )
307+
select r, r isnullas isnull, ris not nullas isnotnullfrom r;
308+
309+
with r(a,b)as
310+
(values (1,row(1,2)), (1,row(null,null)), (1,null),
311+
(null,row(1,2)), (null,row(null,null)), (null,null) )
312+
select r, r isnullas isnull, ris not nullas isnotnullfrom r;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp