This is an automated email from the ASF dual-hosted git repository. reshke pushed a commit to branch REL_2_STABLE in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit acb2b7302d9e3b35b47ab3b3c44839440ce8f45c Author: reshke <[email protected]> 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: [email protected] For additional commands, e-mail: [email protected]
