You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by dj...@apache.org on 2026/10/22 22:44:28 UTC
(cloudberry) 02/02: ORCA: project every computed USING join column
This is an automated email from the ASF dual-hosted git repository.
tuhaihe pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 5921f13526078c1126903aafc9632b03faeb262d
Author: Jianghua Yang <yj...@gmail.com>
AuthorDate: Thu Oct 22 04:29:22 2026 +0800
ORCA: project every computed USING join column
TranslateJoinExprInFromToDXL only added the CoalesceExpr flavour of a
merged USING column to the computed-columns project list, but
TranslateExprToDXLProject allocates a fresh colid for every join alias
var that is not a plain Var. The <query level, varno, varattno>
mapping loaded right below therefore pointed at a column no operator
produced for the coercion flavours (FuncExpr, RelabelType, ...).
Target list references were unaffected because
flatten_join_alias_var_optimizer rewrites them to the base relation
columns, but it deliberately leaves quals alone, so a predicate on the
merged column still goes through the mapping and ORCA bailed out with
DXL-to-Expr Translation: Attribute number 21 not found in project
list
This is a pre-existing bug for the FuncExpr case (int vs bigint USING
columns) and would newly be reachable for RelabelType. Add the project
element for any non-Var join alias instead of enumerating node tags.
Extend the join_gp cases with predicates on the merged column for both
the RelabelType (varchar/text, int/oid) and the FuncExpr (int/bigint)
shapes.
Reported-by: Alena Rybakina
---
.../gpopt/translate/CTranslatorQueryToDXL.cpp | 20 +++---
src/test/regress/expected/join_gp.out | 77 +++++++++++++++++++++
src/test/regress/expected/join_gp_optimizer.out | 78 ++++++++++++++++++++++
src/test/regress/sql/join_gp.sql | 25 +++++++
4 files changed, 192 insertions(+), 8 deletions(-)
diff --git a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp
index 04da2a8afb1..7acfa69054f 100644
--- a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp
+++ b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp
@@ -4143,12 +4143,11 @@ CTranslatorQueryToDXL::TranslateJoinExprInFromToDXL(JoinExpr *join_expr)
continue;
}
// A merged USING column is a plain Var when both inputs have the same
- // type, a CoalesceExpr for a FULL JOIN, or the left input coerced to
- // the common type otherwise (FuncExpr, RelabelType, ...). References
- // to join alias columns have already been flattened by
- // CQueryMutators::NormalizeQuery, so any expression the scalar
- // translator understands is acceptable here; unsupported nodes raise
- // a regular "unsupported feature" exception from TranslateExprToDXL.
+ // type, a CoalesceExpr for a FULL JOIN, and the outer input coerced to
+ // the common type otherwise (FuncExpr, RelabelType, ...). Any
+ // expression the scalar translator understands is acceptable here;
+ // unsupported nodes still raise the regular "unsupported feature"
+ // exception from TranslateExprToDXL.
CHAR *col_name_char_array = strVal(lfirst(lc_col_name));
// create the DXL node holding the target list entry and add it to proj list
@@ -4156,9 +4155,14 @@ CTranslatorQueryToDXL::TranslateJoinExprInFromToDXL(JoinExpr *join_expr)
(Expr *) join_alias_node, col_name_char_array);
project_list_dxlnode->AddChild(project_elem_dxlnode);
- if (IsA(join_alias_node, CoalesceExpr))
+ if (!IsA(join_alias_node, Var))
{
- // add coalesce expression to the computed columns
+ // TranslateExprToDXLProject reuses the input colid for a plain
+ // Var but allocates a new one for any other expression, so the
+ // column only exists if the join computes it. Quals are left
+ // alone by flatten_join_alias_var_optimizer and still reference
+ // the merged column through the mapping loaded below, hence the
+ // projection is required and not merely an optimization.
project_elem_dxlnode->AddRef();
project_list_computed_cols_dxlnode->AddChild(project_elem_dxlnode);
}
diff --git a/src/test/regress/expected/join_gp.out b/src/test/regress/expected/join_gp.out
index de867433210..82bb5a377cf 100644
--- a/src/test/regress/expected/join_gp.out
+++ b/src/test/regress/expected/join_gp.out
@@ -3723,5 +3723,82 @@ select f2, count(*) from using_coerce_t1 x(x0,x1) left join (using_coerce_t1 lef
| 1
(3 rows)
+-- Quals are deliberately not flattened by flatten_join_alias_var_optimizer, so
+-- a predicate on the merged column references the computed join output column
+-- directly. Without a projection producing it ORCA fell back with
+-- "Attribute number N not found in project list".
+explain (costs off) select k from using_coerce_t1 left join using_coerce_t2 using(k) where k = 'a';
+ QUERY PLAN
+---------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
+ -> Seq Scan on using_coerce_t1
+ Filter: ((k)::text = 'a'::text)
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on using_coerce_t2
+ Filter: (k = 'a'::text)
+ Optimizer: Postgres query optimizer
+(9 rows)
+
+select k from using_coerce_t1 left join using_coerce_t2 using(k) where k = 'a';
+ k
+---
+ a
+(1 row)
+
+select f2 from using_coerce_t1 join using_coerce_t2 using(f2) where f2 = 1;
+ f2
+----
+ 1
+(1 row)
+
+select k from using_coerce_t1 full join using_coerce_t2 using(k) where k = 'c';
+ k
+---
+ c
+(1 row)
+
drop table using_coerce_t1;
drop table using_coerce_t2;
+--
+-- Same, for a merged column that needs a real cast function (FuncExpr) rather
+-- than a binary-compatible relabel.
+--
+create table using_coerce_t3(k int, v int) distributed by (v);
+create table using_coerce_t4(k bigint, v int) distributed by (v);
+insert into using_coerce_t3 values (1, 1), (2, 2);
+insert into using_coerce_t4 values (1, 1), (3, 3);
+explain (costs off) select k from using_coerce_t3 left join using_coerce_t4 using(k) where k = 1;
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Right Join
+ Hash Cond: (using_coerce_t4.k = using_coerce_t3.k)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: using_coerce_t4.k
+ -> Seq Scan on using_coerce_t4
+ -> Hash
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Hash Key: using_coerce_t3.k
+ -> Seq Scan on using_coerce_t3
+ Filter: ((k)::bigint = 1)
+ Optimizer: Postgres query optimizer
+(12 rows)
+
+select k from using_coerce_t3 left join using_coerce_t4 using(k) where k = 1;
+ k
+---
+ 1
+(1 row)
+
+select k from using_coerce_t3 full join using_coerce_t4 using(k) order by 1;
+ k
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+drop table using_coerce_t3;
+drop table using_coerce_t4;
diff --git a/src/test/regress/expected/join_gp_optimizer.out b/src/test/regress/expected/join_gp_optimizer.out
index 4a3bb4c849b..fcde9757d3e 100644
--- a/src/test/regress/expected/join_gp_optimizer.out
+++ b/src/test/regress/expected/join_gp_optimizer.out
@@ -3682,5 +3682,83 @@ select f2, count(*) from using_coerce_t1 x(x0,x1) left join (using_coerce_t1 lef
| 1
(3 rows)
+-- Quals are deliberately not flattened by flatten_join_alias_var_optimizer, so
+-- a predicate on the merged column references the computed join output column
+-- directly. Without a projection producing it ORCA fell back with
+-- "Attribute number N not found in project list".
+explain (costs off) select k from using_coerce_t1 left join using_coerce_t2 using(k) where k = 'a';
+ QUERY PLAN
+--------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Left Join
+ Hash Cond: ((using_coerce_t1.k)::text = using_coerce_t2.k)
+ -> Seq Scan on using_coerce_t1
+ Filter: ((k)::text = 'a'::text)
+ -> Hash
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on using_coerce_t2
+ Optimizer: GPORCA
+(9 rows)
+
+select k from using_coerce_t1 left join using_coerce_t2 using(k) where k = 'a';
+ k
+---
+ a
+(1 row)
+
+select f2 from using_coerce_t1 join using_coerce_t2 using(f2) where f2 = 1;
+ f2
+----
+ 1
+(1 row)
+
+select k from using_coerce_t1 full join using_coerce_t2 using(k) where k = 'c';
+ k
+---
+ c
+(1 row)
+
drop table using_coerce_t1;
drop table using_coerce_t2;
+--
+-- Same, for a merged column that needs a real cast function (FuncExpr) rather
+-- than a binary-compatible relabel.
+--
+create table using_coerce_t3(k int, v int) distributed by (v);
+create table using_coerce_t4(k bigint, v int) distributed by (v);
+insert into using_coerce_t3 values (1, 1), (2, 2);
+insert into using_coerce_t4 values (1, 1), (3, 3);
+explain (costs off) select k from using_coerce_t3 left join using_coerce_t4 using(k) where k = 1;
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Result
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Left Join
+ Hash Cond: ((using_coerce_t3.k)::bigint = using_coerce_t4.k)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: (using_coerce_t3.k)::bigint
+ -> Seq Scan on using_coerce_t3
+ Filter: ((k)::bigint = 1)
+ -> Hash
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Hash Key: using_coerce_t4.k
+ -> Seq Scan on using_coerce_t4
+ Optimizer: GPORCA
+(13 rows)
+
+select k from using_coerce_t3 left join using_coerce_t4 using(k) where k = 1;
+ k
+---
+ 1
+(1 row)
+
+select k from using_coerce_t3 full join using_coerce_t4 using(k) order by 1;
+ k
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+drop table using_coerce_t3;
+drop table using_coerce_t4;
diff --git a/src/test/regress/sql/join_gp.sql b/src/test/regress/sql/join_gp.sql
index 0f318140a6f..23b5ad2b508 100644
--- a/src/test/regress/sql/join_gp.sql
+++ b/src/test/regress/sql/join_gp.sql
@@ -1349,5 +1349,30 @@ select k from using_coerce_t1 left join using_coerce_t2 using(k) order by 1;
select f2, count(*) from using_coerce_t1 left join using_coerce_t2 using(f2) group by f2 order by 1;
select f2, count(*) from using_coerce_t1 x(x0,x1) left join (using_coerce_t1 left join using_coerce_t2 using(f2)) on (x0 = 'a') group by f2 order by 1;
+-- Quals are deliberately not flattened by flatten_join_alias_var_optimizer, so
+-- a predicate on the merged column references the computed join output column
+-- directly. Without a projection producing it ORCA fell back with
+-- "Attribute number N not found in project list".
+explain (costs off) select k from using_coerce_t1 left join using_coerce_t2 using(k) where k = 'a';
+select k from using_coerce_t1 left join using_coerce_t2 using(k) where k = 'a';
+select f2 from using_coerce_t1 join using_coerce_t2 using(f2) where f2 = 1;
+select k from using_coerce_t1 full join using_coerce_t2 using(k) where k = 'c';
+
drop table using_coerce_t1;
drop table using_coerce_t2;
+
+--
+-- Same, for a merged column that needs a real cast function (FuncExpr) rather
+-- than a binary-compatible relabel.
+--
+create table using_coerce_t3(k int, v int) distributed by (v);
+create table using_coerce_t4(k bigint, v int) distributed by (v);
+insert into using_coerce_t3 values (1, 1), (2, 2);
+insert into using_coerce_t4 values (1, 1), (3, 3);
+
+explain (costs off) select k from using_coerce_t3 left join using_coerce_t4 using(k) where k = 1;
+select k from using_coerce_t3 left join using_coerce_t4 using(k) where k = 1;
+select k from using_coerce_t3 full join using_coerce_t4 using(k) order by 1;
+
+drop table using_coerce_t3;
+drop table using_coerce_t4;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org