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

Commitbc5a08a

Browse files
author
Richard Guo
committed
Avoid NullTest deduction for clone clauses
In commitb262ad4, we introduced an optimization that reduces an ISNOT NULL qual on a column defined as NOT NULL to constant true, and anIS NULL qual on a NOT NULL column to constant false, provided we canprove that the input expression of the NullTest is not nullable by anyouter join. This deduction happens after we have generated multipleclones of the same qual condition to cope with commuted-left-joincases.However, performing the NullTest deduction for clone clauses can beunsafe, because we don't have a reliable way to determine if the inputexpression of a NullTest is non-nullable: nullingrel bits in cloneclauses may not reflect reality, so we dare not draw conclusions fromclones about whether Vars are guaranteed not-null.To fix, we check whether the given RestrictInfo is a clone clause inrestriction_is_always_true and restriction_is_always_false, and avoidperforming any reduction if it is.There are several ensuing plan changes in predicate.out, and we haveto modify the tests to ensure that they continue to test what they areintended to. Additionally, this fix causes the test case added inf00ab1f to no longer trigger the bug that commit fixed, so we alsoremove that test case.Back-patch to v17 where this bug crept in.Reported-by: Ronald Cruz <cruz@rentec.com>Diagnosed-by: Tom Lane <tgl@sss.pgh.pa.us>Author: Richard Guo <guofenglinux@gmail.com>Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://postgr.es/m/f5320d3d-77af-4ce8-b9c3-4715ff33f213@rentec.comBackpatch-through: 17
1 parentd6dd2a0 commitbc5a08a

File tree

3 files changed

+187
-62
lines changed

3 files changed

+187
-62
lines changed

‎src/backend/optimizer/plan/initsplan.c

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2740,6 +2740,15 @@ bool
27402740
restriction_is_always_true(PlannerInfo*root,
27412741
RestrictInfo*restrictinfo)
27422742
{
2743+
/*
2744+
* For a clone clause, we don't have a reliable way to determine if the
2745+
* input expression of a NullTest is non-nullable: nullingrel bits in
2746+
* clone clauses may not reflect reality, so we dare not draw conclusions
2747+
* from clones about whether Vars are guaranteed not-null.
2748+
*/
2749+
if (restrictinfo->has_clone||restrictinfo->is_clone)
2750+
return false;
2751+
27432752
/* Check for NullTest qual */
27442753
if (IsA(restrictinfo->clause,NullTest))
27452754
{
@@ -2789,6 +2798,15 @@ bool
27892798
restriction_is_always_false(PlannerInfo*root,
27902799
RestrictInfo*restrictinfo)
27912800
{
2801+
/*
2802+
* For a clone clause, we don't have a reliable way to determine if the
2803+
* input expression of a NullTest is non-nullable: nullingrel bits in
2804+
* clone clauses may not reflect reality, so we dare not draw conclusions
2805+
* from clones about whether Vars are guaranteed not-null.
2806+
*/
2807+
if (restrictinfo->has_clone||restrictinfo->is_clone)
2808+
return false;
2809+
27922810
/* Check for NullTest qual */
27932811
if (IsA(restrictinfo->clause,NullTest))
27942812
{

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

Lines changed: 123 additions & 52 deletions
Original file line numberDiff line numberDiff line change
@@ -97,55 +97,50 @@ SELECT * FROM pred_tab t WHERE t.b IS NULL OR t.c IS NULL;
9797
-- and b) its Var is not nullable by any outer joins
9898
EXPLAIN (COSTS OFF)
9999
SELECT * FROM pred_tab t1
100-
LEFT JOIN pred_tab t2 ON TRUE
101-
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
102-
QUERY PLAN
103-
-------------------------------------------------
100+
LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL;
101+
QUERY PLAN
102+
-------------------------------------
104103
Nested Loop Left Join
105104
-> Seq Scan on pred_tab t1
106105
-> Materialize
107-
-> Nested Loop Left Join
108-
-> Seq Scan on pred_tab t2
109-
-> Materialize
110-
-> Seq Scan on pred_tab t3
111-
(7 rows)
106+
-> Seq Scan on pred_tab t2
107+
(4 rows)
112108

113109
-- Ensure the IS_NOT_NULL qual is not ignored when columns are made nullable
114110
-- by an outer join
115111
EXPLAIN (COSTS OFF)
116112
SELECT * FROM pred_tab t1
117-
LEFT JOIN pred_tab t2 ON t1.a =1
113+
FULL JOIN pred_tab t2 ON t1.a =t2.a
118114
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
119115
QUERY PLAN
120116
-------------------------------------------
121117
Nested Loop Left Join
122118
Join Filter: (t2.a IS NOT NULL)
123-
-> Nested Loop Left Join
124-
Join Filter: (t1.a = 1)
125-
-> Seq Scan on pred_tab t1
126-
-> Materialize
119+
-> Merge Full Join
120+
Merge Cond: (t1.a = t2.a)
121+
-> Sort
122+
Sort Key: t1.a
123+
-> Seq Scan on pred_tab t1
124+
-> Sort
125+
Sort Key: t2.a
127126
-> Seq Scan on pred_tab t2
128127
-> Materialize
129128
-> Seq Scan on pred_tab t3
130-
(9 rows)
129+
(12 rows)
131130

132131
-- Ensure the IS_NULL qual is reduced to constant-FALSE, since a) it's on a NOT
133132
-- NULL column, and b) its Var is not nullable by any outer joins
134133
EXPLAIN (COSTS OFF)
135134
SELECT * FROM pred_tab t1
136-
LEFT JOIN pred_tab t2 ON TRUE
137-
LEFT JOIN pred_tab t3 ON t2.a IS NULL AND t2.b = 1;
138-
QUERY PLAN
139-
---------------------------------------------------
135+
LEFT JOIN pred_tab t2 ON t1.a IS NULL;
136+
QUERY PLAN
137+
--------------------------------
140138
Nested Loop Left Join
139+
Join Filter: false
141140
-> Seq Scan on pred_tab t1
142-
-> Materialize
143-
-> Nested Loop Left Join
144-
Join Filter: (false AND (t2.b = 1))
145-
-> Seq Scan on pred_tab t2
146-
-> Result
147-
One-Time Filter: false
148-
(8 rows)
141+
-> Result
142+
One-Time Filter: false
143+
(5 rows)
149144

150145
-- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is
151146
-- nullable by an outer join
@@ -172,55 +167,50 @@ SELECT * FROM pred_tab t1
172167
-- Ensure the OR clause is ignored when an OR branch is provably always true
173168
EXPLAIN (COSTS OFF)
174169
SELECT * FROM pred_tab t1
175-
LEFT JOIN pred_tab t2 ON TRUE
176-
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
177-
QUERY PLAN
178-
-------------------------------------------------
170+
LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL OR t2.b = 1;
171+
QUERY PLAN
172+
-------------------------------------
179173
Nested Loop Left Join
180174
-> Seq Scan on pred_tab t1
181175
-> Materialize
182-
-> Nested Loop Left Join
183-
-> Seq Scan on pred_tab t2
184-
-> Materialize
185-
-> Seq Scan on pred_tab t3
186-
(7 rows)
176+
-> Seq Scan on pred_tab t2
177+
(4 rows)
187178

188179
-- Ensure the NullTest is not ignored when the column is nullable by an outer
189180
-- join
190181
EXPLAIN (COSTS OFF)
191182
SELECT * FROM pred_tab t1
192-
LEFT JOIN pred_tab t2 ON t1.a =1
183+
FULL JOIN pred_tab t2 ON t1.a =t2.a
193184
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
194185
QUERY PLAN
195186
---------------------------------------------------
196187
Nested Loop Left Join
197188
Join Filter: ((t2.a IS NOT NULL) OR (t2.b = 1))
198-
-> Nested Loop Left Join
199-
Join Filter: (t1.a = 1)
200-
-> Seq Scan on pred_tab t1
201-
-> Materialize
189+
-> Merge Full Join
190+
Merge Cond: (t1.a = t2.a)
191+
-> Sort
192+
Sort Key: t1.a
193+
-> Seq Scan on pred_tab t1
194+
-> Sort
195+
Sort Key: t2.a
202196
-> Seq Scan on pred_tab t2
203197
-> Materialize
204198
-> Seq Scan on pred_tab t3
205-
(9 rows)
199+
(12 rows)
206200

207201
-- Ensure the OR clause is reduced to constant-FALSE when all OR branches are
208202
-- provably false
209203
EXPLAIN (COSTS OFF)
210204
SELECT * FROM pred_tab t1
211-
LEFT JOIN pred_tab t2 ON TRUE
212-
LEFT JOIN pred_tab t3 ON (t2.a IS NULL OR t2.c IS NULL) AND t2.b = 1;
213-
QUERY PLAN
214-
---------------------------------------------------
205+
LEFT JOIN pred_tab t2 ON (t1.a IS NULL OR t1.c IS NULL);
206+
QUERY PLAN
207+
--------------------------------
215208
Nested Loop Left Join
209+
Join Filter: false
216210
-> Seq Scan on pred_tab t1
217-
-> Materialize
218-
-> Nested Loop Left Join
219-
Join Filter: (false AND (t2.b = 1))
220-
-> Seq Scan on pred_tab t2
221-
-> Result
222-
One-Time Filter: false
223-
(8 rows)
211+
-> Result
212+
One-Time Filter: false
213+
(5 rows)
224214

225215
-- Ensure the OR clause is not reduced to constant-FALSE when a column is
226216
-- made nullable from an outer join
@@ -290,3 +280,84 @@ SELECT * FROM pred_parent WHERE a IS NULL;
290280
(2 rows)
291281

292282
DROP TABLE pred_parent, pred_child;
283+
-- Validate we do not reduce a clone clause to a constant true or false
284+
CREATE TABLE pred_tab (a int, b int);
285+
CREATE TABLE pred_tab_notnull (a int, b int NOT NULL);
286+
INSERT INTO pred_tab VALUES (1, 1);
287+
INSERT INTO pred_tab VALUES (2, 2);
288+
INSERT INTO pred_tab_notnull VALUES (2, 2);
289+
INSERT INTO pred_tab_notnull VALUES (3, 3);
290+
ANALYZE pred_tab;
291+
ANALYZE pred_tab_notnull;
292+
-- Ensure the IS_NOT_NULL qual is not reduced to constant true and removed
293+
EXPLAIN (COSTS OFF)
294+
SELECT * FROM pred_tab t1
295+
LEFT JOIN pred_tab t2 ON TRUE
296+
LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
297+
LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
298+
QUERY PLAN
299+
---------------------------------------------------------------
300+
Nested Loop Left Join
301+
-> Seq Scan on pred_tab t1
302+
-> Materialize
303+
-> Nested Loop Left Join
304+
Join Filter: (t3.b IS NOT NULL)
305+
-> Nested Loop Left Join
306+
Join Filter: (t2.a = t3.a)
307+
-> Seq Scan on pred_tab t2
308+
-> Materialize
309+
-> Seq Scan on pred_tab_notnull t3
310+
-> Materialize
311+
-> Seq Scan on pred_tab t4
312+
(12 rows)
313+
314+
SELECT * FROM pred_tab t1
315+
LEFT JOIN pred_tab t2 ON TRUE
316+
LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
317+
LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
318+
a | b | a | b | a | b | a | b
319+
---+---+---+---+---+---+---+---
320+
1 | 1 | 1 | 1 | | | |
321+
1 | 1 | 2 | 2 | 2 | 2 | 1 | 1
322+
1 | 1 | 2 | 2 | 2 | 2 | 2 | 2
323+
2 | 2 | 1 | 1 | | | |
324+
2 | 2 | 2 | 2 | 2 | 2 | 1 | 1
325+
2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
326+
(6 rows)
327+
328+
-- Ensure the IS_NULL qual is not reduced to constant false
329+
EXPLAIN (COSTS OFF)
330+
SELECT * FROM pred_tab t1
331+
LEFT JOIN pred_tab t2 ON TRUE
332+
LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
333+
LEFT JOIN pred_tab t4 ON t3.b IS NULL AND t3.a IS NOT NULL;
334+
QUERY PLAN
335+
--------------------------------------------------------------------
336+
Nested Loop Left Join
337+
-> Seq Scan on pred_tab t1
338+
-> Materialize
339+
-> Nested Loop Left Join
340+
Join Filter: ((t3.b IS NULL) AND (t3.a IS NOT NULL))
341+
-> Nested Loop Left Join
342+
Join Filter: (t2.a = t3.a)
343+
-> Seq Scan on pred_tab t2
344+
-> Materialize
345+
-> Seq Scan on pred_tab_notnull t3
346+
-> Materialize
347+
-> Seq Scan on pred_tab t4
348+
(12 rows)
349+
350+
SELECT * FROM pred_tab t1
351+
LEFT JOIN pred_tab t2 ON TRUE
352+
LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
353+
LEFT JOIN pred_tab t4 ON t3.b IS NULL AND t3.a IS NOT NULL;
354+
a | b | a | b | a | b | a | b
355+
---+---+---+---+---+---+---+---
356+
1 | 1 | 1 | 1 | | | |
357+
1 | 1 | 2 | 2 | 2 | 2 | |
358+
2 | 2 | 1 | 1 | | | |
359+
2 | 2 | 2 | 2 | 2 | 2 | |
360+
(4 rows)
361+
362+
DROP TABLE pred_tab;
363+
DROP TABLE pred_tab_notnull;

‎src/test/regress/sql/predicate.sql

Lines changed: 46 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -64,22 +64,20 @@ SELECT * FROM pred_tab t WHERE t.b IS NULL OR t.c IS NULL;
6464
-- and b) its Var is not nullable by any outer joins
6565
EXPLAIN (COSTS OFF)
6666
SELECT*FROM pred_tab t1
67-
LEFT JOIN pred_tab t2ON TRUE
68-
LEFT JOIN pred_tab t3ONt2.aIS NOT NULL;
67+
LEFT JOIN pred_tab t2ONt1.aIS NOT NULL;
6968

7069
-- Ensure the IS_NOT_NULL qual is not ignored when columns are made nullable
7170
-- by an outer join
7271
EXPLAIN (COSTS OFF)
7372
SELECT*FROM pred_tab t1
74-
LEFT JOIN pred_tab t2ONt1.a=1
73+
FULLJOIN pred_tab t2ONt1.a=t2.a
7574
LEFT JOIN pred_tab t3ONt2.aIS NOT NULL;
7675

7776
-- Ensure the IS_NULL qual is reduced to constant-FALSE, since a) it's on a NOT
7877
-- NULL column, and b) its Var is not nullable by any outer joins
7978
EXPLAIN (COSTS OFF)
8079
SELECT*FROM pred_tab t1
81-
LEFT JOIN pred_tab t2ON TRUE
82-
LEFT JOIN pred_tab t3ONt2.a ISNULLANDt2.b=1;
80+
LEFT JOIN pred_tab t2ONt1.a ISNULL;
8381

8482
-- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is
8583
-- nullable by an outer join
@@ -95,22 +93,20 @@ SELECT * FROM pred_tab t1
9593
-- Ensure the OR clause is ignored when an OR branch is provably always true
9694
EXPLAIN (COSTS OFF)
9795
SELECT*FROM pred_tab t1
98-
LEFT JOIN pred_tab t2ON TRUE
99-
LEFT JOIN pred_tab t3ONt2.aIS NOT NULLORt2.b=1;
96+
LEFT JOIN pred_tab t2ONt1.aIS NOT NULLORt2.b=1;
10097

10198
-- Ensure the NullTest is not ignored when the column is nullable by an outer
10299
-- join
103100
EXPLAIN (COSTS OFF)
104101
SELECT*FROM pred_tab t1
105-
LEFT JOIN pred_tab t2ONt1.a=1
102+
FULLJOIN pred_tab t2ONt1.a=t2.a
106103
LEFT JOIN pred_tab t3ONt2.aIS NOT NULLORt2.b=1;
107104

108105
-- Ensure the OR clause is reduced to constant-FALSE when all OR branches are
109106
-- provably false
110107
EXPLAIN (COSTS OFF)
111108
SELECT*FROM pred_tab t1
112-
LEFT JOIN pred_tab t2ON TRUE
113-
LEFT JOIN pred_tab t3ON (t2.a ISNULLORt2.c ISNULL)ANDt2.b=1;
109+
LEFT JOIN pred_tab t2ON (t1.a ISNULLORt1.c ISNULL);
114110

115111
-- Ensure the OR clause is not reduced to constant-FALSE when a column is
116112
-- made nullable from an outer join
@@ -147,3 +143,43 @@ EXPLAIN (COSTS OFF)
147143
SELECT*FROM pred_parentWHERE a ISNULL;
148144

149145
DROPTABLE pred_parent, pred_child;
146+
147+
-- Validate we do not reduce a clone clause to a constant true or false
148+
CREATETABLEpred_tab (aint, bint);
149+
CREATETABLEpred_tab_notnull (aint, bintNOT NULL);
150+
151+
INSERT INTO pred_tabVALUES (1,1);
152+
INSERT INTO pred_tabVALUES (2,2);
153+
154+
INSERT INTO pred_tab_notnullVALUES (2,2);
155+
INSERT INTO pred_tab_notnullVALUES (3,3);
156+
157+
ANALYZE pred_tab;
158+
ANALYZE pred_tab_notnull;
159+
160+
-- Ensure the IS_NOT_NULL qual is not reduced to constant true and removed
161+
EXPLAIN (COSTS OFF)
162+
SELECT*FROM pred_tab t1
163+
LEFT JOIN pred_tab t2ON TRUE
164+
LEFT JOIN pred_tab_notnull t3ONt2.a=t3.a
165+
LEFT JOIN pred_tab t4ONt3.bIS NOT NULL;
166+
167+
SELECT*FROM pred_tab t1
168+
LEFT JOIN pred_tab t2ON TRUE
169+
LEFT JOIN pred_tab_notnull t3ONt2.a=t3.a
170+
LEFT JOIN pred_tab t4ONt3.bIS NOT NULL;
171+
172+
-- Ensure the IS_NULL qual is not reduced to constant false
173+
EXPLAIN (COSTS OFF)
174+
SELECT*FROM pred_tab t1
175+
LEFT JOIN pred_tab t2ON TRUE
176+
LEFT JOIN pred_tab_notnull t3ONt2.a=t3.a
177+
LEFT JOIN pred_tab t4ONt3.b ISNULLANDt3.aIS NOT NULL;
178+
179+
SELECT*FROM pred_tab t1
180+
LEFT JOIN pred_tab t2ON TRUE
181+
LEFT JOIN pred_tab_notnull t3ONt2.a=t3.a
182+
LEFT JOIN pred_tab t4ONt3.b ISNULLANDt3.aIS NOT NULL;
183+
184+
DROPTABLE pred_tab;
185+
DROPTABLE pred_tab_notnull;

0 commit comments

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