This is an automated email from the ASF dual-hosted git repository.
leborchuk pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new 94041052ad0 Fix join condition lost after pull up sublink to join.
(#1960)
94041052ad0 is described below
commit 94041052ad0f4b927771f194f36a7e6510f517cb
Author: Alena Rybakina <[email protected]>
AuthorDate: Tue Sep 8 17:57:40 2026 +0300
Fix join condition lost after pull up sublink to join. (#1960)
After pulling up the sublink to join, the raw join condition may get
lost in the rewritten query, potentially leading to incorrect results.
Within the SubqueryToJoinWalker() function, we address this issue
by adding an 'else' branch to prevent the loss of join clauses and
keep them in their original positions.
Cherry-picked from open-gpdb; the expected files were regenerated on
Cloudberry, whose plans for the new test differ from GPDB's.
(cherry picked from commit c06d16b86316e1d66182f5c75a6c3abe6c30acd4)
Co-authored-by: chaotian <[email protected]>
---
src/backend/cdb/cdbsubselect.c | 7 ++
src/test/regress/expected/subselect.out | 82 ++++++++++++++++++++++
src/test/regress/expected/subselect_optimizer.out | 84 +++++++++++++++++++++++
src/test/regress/sql/subselect.sql | 43 ++++++++++++
4 files changed, 216 insertions(+)
diff --git a/src/backend/cdb/cdbsubselect.c b/src/backend/cdb/cdbsubselect.c
index 4813439de0f..83b908c9ce5 100644
--- a/src/backend/cdb/cdbsubselect.c
+++ b/src/backend/cdb/cdbsubselect.c
@@ -451,6 +451,13 @@ SubqueryToJoinWalker(Node *node,
ConvertSubqueryToJoinContext *context)
*/
context->safeToConvert = false;
}
+ else
+ {
+ /*
+ * For other expressions, we should keep them in original place.
+ */
+ context->innerQual = make_and_qual(context->innerQual, node);
+ }
return;
}
diff --git a/src/test/regress/expected/subselect.out
b/src/test/regress/expected/subselect.out
index 7b827ca937d..575a0cee5d6 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -2324,3 +2324,85 @@ select (select max((select t.i))) from t;
(1 row)
drop table t;
+-- Fix join condition expression lost as pull up sublink to join.
+create table tl1(a int, b int, c int, d int) distributed by (a);
+create table tl2(a int, b int, c int, d int) distributed by (a);
+create table tl3(a int, b int, c int, d int) distributed by (a);
+create table tl4(a int, b int, c int, d int) distributed by (a);
+insert into tl1 values (-1, 3, 1, 0);
+insert into tl2 values (2, 1, 1, 0);
+insert into tl2 values (3, 1, 1, 0);
+insert into tl2 values (1, 1, 1, 0);
+insert into tl3 values (9, 9, 1, 9);
+insert into tl4 values (-1, -1, -1, -1);
+explain(costs off, verbose on)
+select * from tl1
+where
+ tl1.b = (
+ select
+ max(tl2.a)
+ from
+ tl2 join tl4
+ on tl4.d = tl2.d
+ where
+ tl2.b = tl1.c
+ );
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: tl1.a, tl1.b, tl1.c, tl1.d
+ -> Hash Join
+ Output: tl1.a, tl1.b, tl1.c, tl1.d
+ Inner Unique: true
+ Hash Cond: ((tl1.b = "Expr_SUBQUERY".csq_c1) AND (tl1.c =
"Expr_SUBQUERY".csq_c0))
+ -> Seq Scan on public.tl1
+ Output: tl1.a, tl1.b, tl1.c, tl1.d
+ -> Hash
+ Output: "Expr_SUBQUERY".csq_c1, "Expr_SUBQUERY".csq_c0
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ Output: "Expr_SUBQUERY".csq_c1, "Expr_SUBQUERY".csq_c0
+ -> Subquery Scan on "Expr_SUBQUERY"
+ Output: "Expr_SUBQUERY".csq_c1,
"Expr_SUBQUERY".csq_c0
+ -> Finalize HashAggregate
+ Output: tl2.b, max(tl2.a)
+ Group Key: tl2.b
+ -> Redistribute Motion 3:3 (slice3;
segments: 3)
+ Output: tl2.b, (PARTIAL max(tl2.a))
+ Hash Key: tl2.b
+ -> Streaming Partial HashAggregate
+ Output: tl2.b, PARTIAL max(tl2.a)
+ Group Key: tl2.b
+ -> Hash Join
+ Output: tl2.b, tl2.a
+ Hash Cond: (tl4.d = tl2.d)
+ -> Broadcast Motion 3:3
(slice4; segments: 3)
+ Output: tl4.d
+ -> Seq Scan on
public.tl4
+ Output: tl4.d
+ -> Hash
+ Output: tl2.b, tl2.a,
tl2.d
+ -> Seq Scan on
public.tl2
+ Output: tl2.b,
tl2.a, tl2.d
+ Settings: optimizer = 'off', gp_cte_sharing = 'off'
+ Optimizer: Postgres query optimizer
+(36 rows)
+
+select * from tl1
+where
+ tl1.b = (
+ select
+ max(tl2.a)
+ from
+ tl2 join tl4
+ on tl4.d = tl2.d
+ where
+ tl2.b = tl1.c
+ );
+ a | b | c | d
+---+---+---+---
+(0 rows)
+
+drop table tl1;
+drop table tl2;
+drop table tl3;
+drop table tl4;
diff --git a/src/test/regress/expected/subselect_optimizer.out
b/src/test/regress/expected/subselect_optimizer.out
index 53ac286306f..2003e8d1f46 100644
--- a/src/test/regress/expected/subselect_optimizer.out
+++ b/src/test/regress/expected/subselect_optimizer.out
@@ -2407,3 +2407,87 @@ select (select max((select t.i))) from t;
(1 row)
drop table t;
+-- Fix join condition expression lost as pull up sublink to join.
+create table tl1(a int, b int, c int, d int) distributed by (a);
+create table tl2(a int, b int, c int, d int) distributed by (a);
+create table tl3(a int, b int, c int, d int) distributed by (a);
+create table tl4(a int, b int, c int, d int) distributed by (a);
+insert into tl1 values (-1, 3, 1, 0);
+insert into tl2 values (2, 1, 1, 0);
+insert into tl2 values (3, 1, 1, 0);
+insert into tl2 values (1, 1, 1, 0);
+insert into tl3 values (9, 9, 1, 9);
+insert into tl4 values (-1, -1, -1, -1);
+explain(costs off, verbose on)
+select * from tl1
+where
+ tl1.b = (
+ select
+ max(tl2.a)
+ from
+ tl2 join tl4
+ on tl4.d = tl2.d
+ where
+ tl2.b = tl1.c
+ );
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: tl1.a, tl1.b, tl1.c, tl1.d
+ -> Hash Join
+ Output: tl1.a, tl1.b, tl1.c, tl1.d
+ Hash Cond: ((tl1.b = (max(tl2.a))) AND (tl1.c = tl2.b))
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: tl1.a, tl1.b, tl1.c, tl1.d
+ Hash Key: tl1.c
+ -> Seq Scan on public.tl1
+ Output: tl1.a, tl1.b, tl1.c, tl1.d
+ -> Hash
+ Output: (max(tl2.a)), tl2.b
+ -> GroupAggregate
+ Output: max(tl2.a), tl2.b
+ Group Key: tl2.b
+ -> Sort
+ Output: tl2.a, tl2.b
+ Sort Key: tl2.b
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: tl2.a, tl2.b
+ Hash Key: tl2.b
+ -> Hash Join
+ Output: tl2.a, tl2.b
+ Hash Cond: (tl2.d = tl4.d)
+ -> Redistribute Motion 3:3 (slice4;
segments: 3)
+ Output: tl2.a, tl2.b, tl2.d
+ Hash Key: tl2.d
+ -> Seq Scan on public.tl2
+ Output: tl2.a, tl2.b, tl2.d
+ -> Hash
+ Output: tl4.d
+ -> Redistribute Motion 3:3
(slice5; segments: 3)
+ Output: tl4.d
+ Hash Key: tl4.d
+ -> Seq Scan on public.tl4
+ Output: tl4.d
+ Settings: gp_cte_sharing = 'off'
+ Optimizer: GPORCA
+(38 rows)
+
+select * from tl1
+where
+ tl1.b = (
+ select
+ max(tl2.a)
+ from
+ tl2 join tl4
+ on tl4.d = tl2.d
+ where
+ tl2.b = tl1.c
+ );
+ a | b | c | d
+---+---+---+---
+(0 rows)
+
+drop table tl1;
+drop table tl2;
+drop table tl3;
+drop table tl4;
diff --git a/src/test/regress/sql/subselect.sql
b/src/test/regress/sql/subselect.sql
index d55930d329d..d665e1f6271 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -1115,3 +1115,46 @@ select (select max((select t.i))) from t;
select (select max((select t.i))) from t;
drop table t;
+
+-- Fix join condition expression lost as pull up sublink to join.
+create table tl1(a int, b int, c int, d int) distributed by (a);
+create table tl2(a int, b int, c int, d int) distributed by (a);
+create table tl3(a int, b int, c int, d int) distributed by (a);
+create table tl4(a int, b int, c int, d int) distributed by (a);
+
+insert into tl1 values (-1, 3, 1, 0);
+insert into tl2 values (2, 1, 1, 0);
+insert into tl2 values (3, 1, 1, 0);
+insert into tl2 values (1, 1, 1, 0);
+insert into tl3 values (9, 9, 1, 9);
+insert into tl4 values (-1, -1, -1, -1);
+
+explain(costs off, verbose on)
+select * from tl1
+where
+ tl1.b = (
+ select
+ max(tl2.a)
+ from
+ tl2 join tl4
+ on tl4.d = tl2.d
+ where
+ tl2.b = tl1.c
+ );
+
+select * from tl1
+where
+ tl1.b = (
+ select
+ max(tl2.a)
+ from
+ tl2 join tl4
+ on tl4.d = tl2.d
+ where
+ tl2.b = tl1.c
+ );
+
+drop table tl1;
+drop table tl2;
+drop table tl3;
+drop table tl4;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
