You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by re...@apache.org on 2026/08/17 18:56:35 UTC
(cloudberry) 02/02: Fix JOIN motion type selection for join quals containing outer refs
This is an automated email from the ASF dual-hosted git repository.
reshke pushed a commit to branch fix_laterla
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 188319be500fda15a5bdd205f679b1b2ffcb4a62
Author: reshke <re...@double.cloud>
AuthorDate: Mon Aug 17 08:39:37 2026 +0000
Fix JOIN motion type selection for join quals containing outer refs
f
---
src/backend/optimizer/plan/initsplan.c | 3 --
src/backend/optimizer/util/restrictinfo.c | 9 ++++
src/test/regress/expected/join_gp.out | 53 +++++++++++++++++++++++
src/test/regress/expected/join_gp_optimizer.out | 53 +++++++++++++++++++++++
src/test/regress/expected/join_hash.out | 33 ++++++++------
src/test/regress/expected/join_hash_optimizer.out | 43 ++++++++++++++++++
src/test/regress/sql/join_gp.sql | 16 +++++++
7 files changed, 194 insertions(+), 16 deletions(-)
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index b8cd960a528..bb6bb4d7123 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2532,9 +2532,6 @@ distribute_restrictinfo_to_rels(PlannerInfo *root,
Relids relids = restrictinfo->required_relids;
RelOptInfo *rel;
- if (contains_outer_params((Node *) restrictinfo->clause, root))
- restrictinfo->contain_outer_query_references = true;
-
switch (bms_membership(relids))
{
case BMS_SINGLETON:
diff --git a/src/backend/optimizer/util/restrictinfo.c b/src/backend/optimizer/util/restrictinfo.c
index 52404497174..7e98d52381c 100644
--- a/src/backend/optimizer/util/restrictinfo.c
+++ b/src/backend/optimizer/util/restrictinfo.c
@@ -14,6 +14,7 @@
*/
#include "postgres.h"
+#include "cdb/cdbmutate.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
@@ -235,6 +236,14 @@ make_restrictinfo_internal(PlannerInfo *root,
restrictinfo->hasheqoperator = InvalidOid;
+ /*
+ * Determine whether this clause references any var in outer query levels.
+ * Such clauses must be evaluated in the same slice as the parent query,
+ * so we set this planner hint to later use in join motion planning.
+ */
+ restrictinfo->contain_outer_query_references =
+ contains_outer_params((Node *) clause, root);
+
return restrictinfo;
}
diff --git a/src/test/regress/expected/join_gp.out b/src/test/regress/expected/join_gp.out
index 6e2a3af3955..5f39abb23c8 100644
--- a/src/test/regress/expected/join_gp.out
+++ b/src/test/regress/expected/join_gp.out
@@ -3613,3 +3613,56 @@ drop table if exists repli_t1_pk;
drop table if exists repli_t2_pk;
drop table if exists repli_t3_pk;
drop table if exists repli_t4_pk;
+--
+-- Test that a join qual containing an outer-level reference
+-- is correctly identified as referring to the outer
+-- query, so that the join motion is planned in the parent slice.
+--
+create table lat_oq_t2(i int) distributed by (i);
+create table lat_oq_t3(i int) distributed by (i);
+insert into lat_oq_t2 select generate_series(1,10);
+insert into lat_oq_t3 select generate_series(1,10);
+explain (costs off) select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2 join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Nested Loop
+ -> Function Scan on generate_series t1
+ -> Materialize
+ -> Sort
+ Sort Key: t3.i
+ -> Hash Join
+ Hash Cond: (t2.i = (t3.i + t1.t1))
+ -> Materialize
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on lat_oq_t2 t2
+ -> Hash
+ -> Materialize
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on lat_oq_t3 t3
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2 join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+ t1 | i
+----+---
+ 1 | 1
+ 1 | 2
+ 1 | 3
+ 1 | 4
+ 1 | 5
+ 1 | 6
+ 1 | 7
+ 1 | 8
+ 1 | 9
+ 2 | 1
+ 2 | 2
+ 2 | 3
+ 2 | 4
+ 2 | 5
+ 2 | 6
+ 2 | 7
+ 2 | 8
+(17 rows)
+
+drop table lat_oq_t2;
+drop table lat_oq_t3;
diff --git a/src/test/regress/expected/join_gp_optimizer.out b/src/test/regress/expected/join_gp_optimizer.out
index 4138130b0a5..75d2dfbcb21 100644
--- a/src/test/regress/expected/join_gp_optimizer.out
+++ b/src/test/regress/expected/join_gp_optimizer.out
@@ -3575,3 +3575,56 @@ drop table if exists repli_t1_pk;
drop table if exists repli_t2_pk;
drop table if exists repli_t3_pk;
drop table if exists repli_t4_pk;
+--
+-- Test that a join qual containing an outer-level reference
+-- is correctly identified as referring to the outer
+-- query, so that the join motion is planned in the parent slice.
+--
+create table lat_oq_t2(i int) distributed by (i);
+create table lat_oq_t3(i int) distributed by (i);
+insert into lat_oq_t2 select generate_series(1,10);
+insert into lat_oq_t3 select generate_series(1,10);
+explain (costs off) select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2 join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Nested Loop
+ -> Function Scan on generate_series t1
+ -> Materialize
+ -> Sort
+ Sort Key: t3.i
+ -> Hash Join
+ Hash Cond: (t2.i = (t3.i + t1.t1))
+ -> Materialize
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on lat_oq_t2 t2
+ -> Hash
+ -> Materialize
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on lat_oq_t3 t3
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2 join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+ t1 | i
+----+---
+ 1 | 1
+ 1 | 2
+ 1 | 3
+ 1 | 4
+ 1 | 5
+ 1 | 6
+ 1 | 7
+ 1 | 8
+ 1 | 9
+ 2 | 1
+ 2 | 2
+ 2 | 3
+ 2 | 4
+ 2 | 5
+ 2 | 6
+ 2 | 7
+ 2 | 8
+(17 rows)
+
+drop table lat_oq_t2;
+drop table lat_oq_t3;
diff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/expected/join_hash.out
index fba2aaca178..2426e0af5aa 100644
--- a/src/test/regress/expected/join_hash.out
+++ b/src/test/regress/expected/join_hash.out
@@ -1267,18 +1267,25 @@ select i8.q2, ss.* from
int8_tbl i8,
lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4
on t1.fivethous = i4.f1+i8.q2 order by 1,2) ss;
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
Nested Loop
- -> Seq Scan on int8_tbl i8
- -> Sort
- Sort Key: t1.fivethous, i4.f1
- -> Hash Join
- Hash Cond: (t1.fivethous = (i4.f1 + i8.q2))
- -> Seq Scan on tenk1 t1
- -> Hash
- -> Seq Scan on int4_tbl i4
-(9 rows)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on int8_tbl i8
+ -> Materialize
+ -> Sort
+ Sort Key: t1.fivethous, i4.f1
+ -> Hash Join
+ Hash Cond: (t1.fivethous = (i4.f1 + i8.q2))
+ -> Materialize
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on tenk1 t1
+ -> Hash
+ -> Materialize
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Seq Scan on int4_tbl i4
+ Optimizer: Postgres query optimizer
+(16 rows)
select i8.q2, ss.* from
int8_tbl i8,
@@ -1286,10 +1293,10 @@ lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4
on t1.fivethous = i4.f1+i8.q2 order by 1,2) ss;
q2 | fivethous | f1
-----+-----------+----
- 456 | 456 | 0
- 456 | 456 | 0
123 | 123 | 0
123 | 123 | 0
+ 456 | 456 | 0
+ 456 | 456 | 0
(4 rows)
rollback;
diff --git a/src/test/regress/expected/join_hash_optimizer.out b/src/test/regress/expected/join_hash_optimizer.out
index 1835bfa4f31..f5425eb7828 100644
--- a/src/test/regress/expected/join_hash_optimizer.out
+++ b/src/test/regress/expected/join_hash_optimizer.out
@@ -1365,3 +1365,46 @@ WHERE
(1 row)
ROLLBACK;
+-- Verify that we behave sanely when the inner hash keys contain parameters
+-- (that is, outer or lateral references). This situation has to defeat
+-- re-use of the inner hash table across rescans.
+begin;
+set local enable_hashjoin = on;
+explain (costs off)
+select i8.q2, ss.* from
+int8_tbl i8,
+lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4
+ on t1.fivethous = i4.f1+i8.q2 order by 1,2) ss;
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Nested Loop
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on int8_tbl i8
+ -> Materialize
+ -> Sort
+ Sort Key: t1.fivethous, i4.f1
+ -> Hash Join
+ Hash Cond: (t1.fivethous = (i4.f1 + i8.q2))
+ -> Materialize
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Seq Scan on tenk1 t1
+ -> Hash
+ -> Materialize
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Seq Scan on int4_tbl i4
+ Optimizer: Postgres query optimizer
+(16 rows)
+
+select i8.q2, ss.* from
+int8_tbl i8,
+lateral (select t1.fivethous, i4.f1 from tenk1 t1 join int4_tbl i4
+ on t1.fivethous = i4.f1+i8.q2 order by 1,2) ss;
+ q2 | fivethous | f1
+-----+-----------+----
+ 123 | 123 | 0
+ 123 | 123 | 0
+ 456 | 456 | 0
+ 456 | 456 | 0
+(4 rows)
+
+rollback;
diff --git a/src/test/regress/sql/join_gp.sql b/src/test/regress/sql/join_gp.sql
index fae1fdc7c5d..4abcbe9f2f4 100644
--- a/src/test/regress/sql/join_gp.sql
+++ b/src/test/regress/sql/join_gp.sql
@@ -1310,3 +1310,19 @@ drop table if exists repli_t1_pk;
drop table if exists repli_t2_pk;
drop table if exists repli_t3_pk;
drop table if exists repli_t4_pk;
+
+--
+-- Test that a join qual containing an outer-level reference
+-- is correctly identified as referring to the outer
+-- query, so that the join motion is planned in the parent slice.
+--
+create table lat_oq_t2(i int) distributed by (i);
+create table lat_oq_t3(i int) distributed by (i);
+insert into lat_oq_t2 select generate_series(1,10);
+insert into lat_oq_t3 select generate_series(1,10);
+
+explain (costs off) select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2 join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2 join lat_oq_t3 t3 on t2.i = t3.i + t1 order by 1) z;
+
+drop table lat_oq_t2;
+drop table lat_oq_t3;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org