summaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/optimizer/plan/initsplan.c18
-rw-r--r--src/test/regress/expected/predicate.out175
-rw-r--r--src/test/regress/sql/predicate.sql56
3 files changed, 187 insertions, 62 deletions
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index cd0ce25bd86..3b21f8ae596 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2740,6 +2740,15 @@ bool
restriction_is_always_true(PlannerInfo *root,
RestrictInfo *restrictinfo)
{
+ /*
+ * For a clone clause, we don't have a reliable way to determine if the
+ * input expression of a NullTest is non-nullable: nullingrel bits in
+ * clone clauses may not reflect reality, so we dare not draw conclusions
+ * from clones about whether Vars are guaranteed not-null.
+ */
+ if (restrictinfo->has_clone || restrictinfo->is_clone)
+ return false;
+
/* Check for NullTest qual */
if (IsA(restrictinfo->clause, NullTest))
{
@@ -2789,6 +2798,15 @@ bool
restriction_is_always_false(PlannerInfo *root,
RestrictInfo *restrictinfo)
{
+ /*
+ * For a clone clause, we don't have a reliable way to determine if the
+ * input expression of a NullTest is non-nullable: nullingrel bits in
+ * clone clauses may not reflect reality, so we dare not draw conclusions
+ * from clones about whether Vars are guaranteed not-null.
+ */
+ if (restrictinfo->has_clone || restrictinfo->is_clone)
+ return false;
+
/* Check for NullTest qual */
if (IsA(restrictinfo->clause, NullTest))
{
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 6f1cc0d54cd..b79037748b7 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -97,55 +97,50 @@ SELECT * FROM pred_tab t WHERE t.b IS NULL OR t.c IS NULL;
-- and b) its Var is not nullable by any outer joins
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON TRUE
- LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
- QUERY PLAN
--------------------------------------------------
+ LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL;
+ QUERY PLAN
+-------------------------------------
Nested Loop Left Join
-> Seq Scan on pred_tab t1
-> Materialize
- -> Nested Loop Left Join
- -> Seq Scan on pred_tab t2
- -> Materialize
- -> Seq Scan on pred_tab t3
-(7 rows)
+ -> Seq Scan on pred_tab t2
+(4 rows)
-- Ensure the IS_NOT_NULL qual is not ignored when columns are made nullable
-- by an outer join
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON t1.a = 1
+ FULL JOIN pred_tab t2 ON t1.a = t2.a
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
QUERY PLAN
-------------------------------------------
Nested Loop Left Join
Join Filter: (t2.a IS NOT NULL)
- -> Nested Loop Left Join
- Join Filter: (t1.a = 1)
- -> Seq Scan on pred_tab t1
- -> Materialize
+ -> Merge Full Join
+ Merge Cond: (t1.a = t2.a)
+ -> Sort
+ Sort Key: t1.a
+ -> Seq Scan on pred_tab t1
+ -> Sort
+ Sort Key: t2.a
-> Seq Scan on pred_tab t2
-> Materialize
-> Seq Scan on pred_tab t3
-(9 rows)
+(12 rows)
-- Ensure the IS_NULL qual is reduced to constant-FALSE, since a) it's on a NOT
-- NULL column, and b) its Var is not nullable by any outer joins
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON TRUE
- LEFT JOIN pred_tab t3 ON t2.a IS NULL AND t2.b = 1;
- QUERY PLAN
----------------------------------------------------
+ LEFT JOIN pred_tab t2 ON t1.a IS NULL;
+ QUERY PLAN
+--------------------------------
Nested Loop Left Join
+ Join Filter: false
-> Seq Scan on pred_tab t1
- -> Materialize
- -> Nested Loop Left Join
- Join Filter: (false AND (t2.b = 1))
- -> Seq Scan on pred_tab t2
- -> Result
- One-Time Filter: false
-(8 rows)
+ -> Result
+ One-Time Filter: false
+(5 rows)
-- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is
-- nullable by an outer join
@@ -172,55 +167,50 @@ SELECT * FROM pred_tab t1
-- Ensure the OR clause is ignored when an OR branch is provably always true
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON TRUE
- LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
- QUERY PLAN
--------------------------------------------------
+ LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL OR t2.b = 1;
+ QUERY PLAN
+-------------------------------------
Nested Loop Left Join
-> Seq Scan on pred_tab t1
-> Materialize
- -> Nested Loop Left Join
- -> Seq Scan on pred_tab t2
- -> Materialize
- -> Seq Scan on pred_tab t3
-(7 rows)
+ -> Seq Scan on pred_tab t2
+(4 rows)
-- Ensure the NullTest is not ignored when the column is nullable by an outer
-- join
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON t1.a = 1
+ FULL JOIN pred_tab t2 ON t1.a = t2.a
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
QUERY PLAN
---------------------------------------------------
Nested Loop Left Join
Join Filter: ((t2.a IS NOT NULL) OR (t2.b = 1))
- -> Nested Loop Left Join
- Join Filter: (t1.a = 1)
- -> Seq Scan on pred_tab t1
- -> Materialize
+ -> Merge Full Join
+ Merge Cond: (t1.a = t2.a)
+ -> Sort
+ Sort Key: t1.a
+ -> Seq Scan on pred_tab t1
+ -> Sort
+ Sort Key: t2.a
-> Seq Scan on pred_tab t2
-> Materialize
-> Seq Scan on pred_tab t3
-(9 rows)
+(12 rows)
-- Ensure the OR clause is reduced to constant-FALSE when all OR branches are
-- provably false
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON TRUE
- LEFT JOIN pred_tab t3 ON (t2.a IS NULL OR t2.c IS NULL) AND t2.b = 1;
- QUERY PLAN
----------------------------------------------------
+ LEFT JOIN pred_tab t2 ON (t1.a IS NULL OR t1.c IS NULL);
+ QUERY PLAN
+--------------------------------
Nested Loop Left Join
+ Join Filter: false
-> Seq Scan on pred_tab t1
- -> Materialize
- -> Nested Loop Left Join
- Join Filter: (false AND (t2.b = 1))
- -> Seq Scan on pred_tab t2
- -> Result
- One-Time Filter: false
-(8 rows)
+ -> Result
+ One-Time Filter: false
+(5 rows)
-- Ensure the OR clause is not reduced to constant-FALSE when a column is
-- made nullable from an outer join
@@ -290,3 +280,84 @@ SELECT * FROM pred_parent WHERE a IS NULL;
(2 rows)
DROP TABLE pred_parent, pred_child;
+-- Validate we do not reduce a clone clause to a constant true or false
+CREATE TABLE pred_tab (a int, b int);
+CREATE TABLE pred_tab_notnull (a int, b int NOT NULL);
+INSERT INTO pred_tab VALUES (1, 1);
+INSERT INTO pred_tab VALUES (2, 2);
+INSERT INTO pred_tab_notnull VALUES (2, 2);
+INSERT INTO pred_tab_notnull VALUES (3, 3);
+ANALYZE pred_tab;
+ANALYZE pred_tab_notnull;
+-- Ensure the IS_NOT_NULL qual is not reduced to constant true and removed
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+ LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
+ QUERY PLAN
+---------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: (t3.b IS NOT NULL)
+ -> Nested Loop Left Join
+ Join Filter: (t2.a = t3.a)
+ -> Seq Scan on pred_tab t2
+ -> Materialize
+ -> Seq Scan on pred_tab_notnull t3
+ -> Materialize
+ -> Seq Scan on pred_tab t4
+(12 rows)
+
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+ LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
+ a | b | a | b | a | b | a | b
+---+---+---+---+---+---+---+---
+ 1 | 1 | 1 | 1 | | | |
+ 1 | 1 | 2 | 2 | 2 | 2 | 1 | 1
+ 1 | 1 | 2 | 2 | 2 | 2 | 2 | 2
+ 2 | 2 | 1 | 1 | | | |
+ 2 | 2 | 2 | 2 | 2 | 2 | 1 | 1
+ 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
+(6 rows)
+
+-- Ensure the IS_NULL qual is not reduced to constant false
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+ LEFT JOIN pred_tab t4 ON t3.b IS NULL AND t3.a IS NOT NULL;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: ((t3.b IS NULL) AND (t3.a IS NOT NULL))
+ -> Nested Loop Left Join
+ Join Filter: (t2.a = t3.a)
+ -> Seq Scan on pred_tab t2
+ -> Materialize
+ -> Seq Scan on pred_tab_notnull t3
+ -> Materialize
+ -> Seq Scan on pred_tab t4
+(12 rows)
+
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+ LEFT JOIN pred_tab t4 ON t3.b IS NULL AND t3.a IS NOT NULL;
+ a | b | a | b | a | b | a | b
+---+---+---+---+---+---+---+---
+ 1 | 1 | 1 | 1 | | | |
+ 1 | 1 | 2 | 2 | 2 | 2 | |
+ 2 | 2 | 1 | 1 | | | |
+ 2 | 2 | 2 | 2 | 2 | 2 | |
+(4 rows)
+
+DROP TABLE pred_tab;
+DROP TABLE pred_tab_notnull;
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index 63f6a7786f3..9dcb81b1bc5 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -64,22 +64,20 @@ SELECT * FROM pred_tab t WHERE t.b IS NULL OR t.c IS NULL;
-- and b) its Var is not nullable by any outer joins
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON TRUE
- LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
+ LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL;
-- Ensure the IS_NOT_NULL qual is not ignored when columns are made nullable
-- by an outer join
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON t1.a = 1
+ FULL JOIN pred_tab t2 ON t1.a = t2.a
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
-- Ensure the IS_NULL qual is reduced to constant-FALSE, since a) it's on a NOT
-- NULL column, and b) its Var is not nullable by any outer joins
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON TRUE
- LEFT JOIN pred_tab t3 ON t2.a IS NULL AND t2.b = 1;
+ LEFT JOIN pred_tab t2 ON t1.a IS NULL;
-- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is
-- nullable by an outer join
@@ -95,22 +93,20 @@ SELECT * FROM pred_tab t1
-- Ensure the OR clause is ignored when an OR branch is provably always true
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON TRUE
- LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
+ LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL OR t2.b = 1;
-- Ensure the NullTest is not ignored when the column is nullable by an outer
-- join
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON t1.a = 1
+ FULL JOIN pred_tab t2 ON t1.a = t2.a
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
-- Ensure the OR clause is reduced to constant-FALSE when all OR branches are
-- provably false
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
- LEFT JOIN pred_tab t2 ON TRUE
- LEFT JOIN pred_tab t3 ON (t2.a IS NULL OR t2.c IS NULL) AND t2.b = 1;
+ LEFT JOIN pred_tab t2 ON (t1.a IS NULL OR t1.c IS NULL);
-- Ensure the OR clause is not reduced to constant-FALSE when a column is
-- made nullable from an outer join
@@ -147,3 +143,43 @@ EXPLAIN (COSTS OFF)
SELECT * FROM pred_parent WHERE a IS NULL;
DROP TABLE pred_parent, pred_child;
+
+-- Validate we do not reduce a clone clause to a constant true or false
+CREATE TABLE pred_tab (a int, b int);
+CREATE TABLE pred_tab_notnull (a int, b int NOT NULL);
+
+INSERT INTO pred_tab VALUES (1, 1);
+INSERT INTO pred_tab VALUES (2, 2);
+
+INSERT INTO pred_tab_notnull VALUES (2, 2);
+INSERT INTO pred_tab_notnull VALUES (3, 3);
+
+ANALYZE pred_tab;
+ANALYZE pred_tab_notnull;
+
+-- Ensure the IS_NOT_NULL qual is not reduced to constant true and removed
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+ LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
+
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+ LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
+
+-- Ensure the IS_NULL qual is not reduced to constant false
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+ LEFT JOIN pred_tab t4 ON t3.b IS NULL AND t3.a IS NOT NULL;
+
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+ LEFT JOIN pred_tab t4 ON t3.b IS NULL AND t3.a IS NOT NULL;
+
+DROP TABLE pred_tab;
+DROP TABLE pred_tab_notnull;
generated by cgit v1.2.3 (git 2.46.0) at 2025-09-21 02:19:18 +0000