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