This is an automated email from the ASF dual-hosted git repository. reshke pushed a commit to branch jdejii in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 83bb342681efcf1d2abc1b8360b8a981aaac0e5f 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 | 42 +++++++++++------- src/test/regress/expected/join_hash_optimizer.out | 41 +++++++++++------- src/test/regress/sql/join_gp.sql | 16 +++++++ src/test/regress/sql/join_hash.sql | 2 + 8 files changed, 184 insertions(+), 35 deletions(-) diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index b65897043ca..ce05a4301a4 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -2970,9 +2970,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 45552d53aaf..c1a9404e91d 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" @@ -263,6 +264,14 @@ make_restrictinfo_internal(PlannerInfo *root, restrictinfo->left_hasheqoperator = InvalidOid; restrictinfo->right_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 e459d425fff..a8f99c64e64 100644 --- a/src/test/regress/expected/join_gp.out +++ b/src/test/regress/expected/join_gp.out @@ -3619,3 +3619,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 2c80ee0add7..ead29d2dd3b 100644 --- a/src/test/regress/expected/join_gp_optimizer.out +++ b/src/test/regress/expected/join_gp_optimizer.out @@ -3581,3 +3581,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 681084c5318..f446822e861 100644 --- a/src/test/regress/expected/join_hash.out +++ b/src/test/regress/expected/join_hash.out @@ -1282,6 +1282,8 @@ 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. +-- CBDB_FIXME: our planner implemention forces not-very-effitient +-- plan here, we can enhace by pushin join below motion. begin; set local enable_hashjoin = on; explain (costs off) @@ -1289,28 +1291,36 @@ 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 --------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) - -> Nested Loop - -> Broadcast Motion 3:3 (slice2; 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)) - -> Seq Scan on tenk1 t1 - -> Hash - -> Broadcast Motion 3:3 (slice3; segments: 3) + 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 -(14 rows) +(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; -ERROR: illegal rescan of motion node: invalid plan (nodeMotion.c:XXX) -HINT: Likely caused by bad NL-join, try setting enable_nestloop to off + q2 | fivethous | f1 +-----+-----------+---- + 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 cb7511c6ebc..ced4544a233 100644 --- a/src/test/regress/expected/join_hash_optimizer.out +++ b/src/test/regress/expected/join_hash_optimizer.out @@ -1398,26 +1398,35 @@ 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 --------------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) - -> Nested Loop - -> Broadcast Motion 3:3 (slice2; 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)) - -> Seq Scan on tenk1 t1 - -> Hash - -> Broadcast Motion 3:3 (slice3; segments: 3) +--------------------------------------------------------------------------------- + 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 -(14 rows) + 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; -ERROR: illegal rescan of motion node: invalid plan (nodeMotion.c:XXX) -HINT: Likely caused by bad NL-join, try setting enable_nestloop to off + 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 6a96d9b98e1..f632cc5e0ac 100644 --- a/src/test/regress/sql/join_gp.sql +++ b/src/test/regress/sql/join_gp.sql @@ -1316,3 +1316,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; diff --git a/src/test/regress/sql/join_hash.sql b/src/test/regress/sql/join_hash.sql index 6607047360e..85559b7d3d0 100644 --- a/src/test/regress/sql/join_hash.sql +++ b/src/test/regress/sql/join_hash.sql @@ -668,6 +668,8 @@ 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. +-- CBDB_FIXME: our planner implemention forces not-very-effitient +-- plan here, we can enhace by pushin join below motion. begin; set local enable_hashjoin = on; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
