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:27 UTC
(cloudberry) 01/02: ORCA: accept coerced join alias vars in USING joins
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 fa4fddedba195a207edc014b121a38e0953d4ca5
Author: Jianghua Yang <yj...@gmail.com>
AuthorDate: Thu Oct 15 01:36:12 2026 +0800
ORCA: accept coerced join alias vars in USING joins
The merged column of a USING join is the left input coerced to the
common type when the two input types differ. For binary-compatible
types such as varchar vs text or int vs oid the parser emits a
RelabelType, which CTranslatorQueryToDXL::TranslateJoinExprInFromToDXL
rejected with an assertion that only allowed Var, FuncExpr and
CoalesceExpr. On assert-enabled builds every such query fell back to
the Postgres planner:
CTranslatorQueryToDXL.cpp:4145: Failed assertion:
IsA(join_alias_node, Var) || IsA(join_alias_node, FuncExpr) ||
IsA(join_alias_node, CoalesceExpr)
References to join alias columns are flattened by
CQueryMutators::NormalizeQuery before the join itself is translated,
which is why the FuncExpr case already worked without any special
handling. The projection element only has to be an expression the
scalar translator understands, so drop the enumeration. Nodes the
scalar translator does not support still raise the regular
unsupported-feature exception and fall back cleanly.
Add a regression case to join_gp covering varchar/text and int/oid
USING columns, including the nested left join shape from the
aggregates test.
---
.../gpopt/translate/CTranslatorQueryToDXL.cpp | 10 ++--
src/test/regress/expected/join_gp.out | 53 ++++++++++++++++++++++
src/test/regress/expected/join_gp_optimizer.out | 50 ++++++++++++++++++++
src/test/regress/sql/join_gp.sql | 19 ++++++++
4 files changed, 129 insertions(+), 3 deletions(-)
diff --git a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp
index df662af05f5..04da2a8afb1 100644
--- a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp
+++ b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp
@@ -4142,9 +4142,13 @@ CTranslatorQueryToDXL::TranslateJoinExprInFromToDXL(JoinExpr *join_expr)
{
continue;
}
- GPOS_ASSERT(IsA(join_alias_node, Var) ||
- IsA(join_alias_node, FuncExpr) ||
- IsA(join_alias_node, CoalesceExpr));
+ // 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.
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
diff --git a/src/test/regress/expected/join_gp.out b/src/test/regress/expected/join_gp.out
index a8f99c64e64..de867433210 100644
--- a/src/test/regress/expected/join_gp.out
+++ b/src/test/regress/expected/join_gp.out
@@ -3672,3 +3672,56 @@ select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2 jo
drop table lat_oq_t2;
drop table lat_oq_t3;
+--
+-- Test that ORCA can plan a USING join whose merged column is the left input
+-- coerced with a binary-compatible cast (RelabelType), e.g. varchar vs text or
+-- int vs oid. The query-to-DXL translator used to assert on such join alias
+-- vars and fall back to the Postgres planner.
+--
+create table using_coerce_t1(k varchar(10), f2 int) distributed by (f2);
+create table using_coerce_t2(k text, f2 oid) distributed by (f2);
+insert into using_coerce_t1 values ('a', 1), ('b', 2);
+insert into using_coerce_t2 values ('a', 1), ('c', 3);
+explain (costs off) select k from using_coerce_t1 left join using_coerce_t2 using(k) order by 1;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: ((using_coerce_t1.k)::character varying)
+ -> Sort
+ Sort Key: ((using_coerce_t1.k)::character varying)
+ -> Hash Right Join
+ Hash Cond: (using_coerce_t2.k = (using_coerce_t1.k)::text)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: using_coerce_t2.k
+ -> Seq Scan on using_coerce_t2
+ -> Hash
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Hash Key: using_coerce_t1.k
+ -> Seq Scan on using_coerce_t1
+ Optimizer: Postgres query optimizer
+(14 rows)
+
+select k from using_coerce_t1 left join using_coerce_t2 using(k) order by 1;
+ k
+---
+ a
+ b
+(2 rows)
+
+select f2, count(*) from using_coerce_t1 left join using_coerce_t2 using(f2) group by f2 order by 1;
+ f2 | count
+----+-------
+ 1 | 1
+ 2 | 1
+(2 rows)
+
+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;
+ f2 | count
+----+-------
+ 1 | 1
+ 2 | 1
+ | 1
+(3 rows)
+
+drop table using_coerce_t1;
+drop table using_coerce_t2;
diff --git a/src/test/regress/expected/join_gp_optimizer.out b/src/test/regress/expected/join_gp_optimizer.out
index ead29d2dd3b..4a3bb4c849b 100644
--- a/src/test/regress/expected/join_gp_optimizer.out
+++ b/src/test/regress/expected/join_gp_optimizer.out
@@ -3634,3 +3634,53 @@ select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2 jo
drop table lat_oq_t2;
drop table lat_oq_t3;
+--
+-- Test that ORCA can plan a USING join whose merged column is the left input
+-- coerced with a binary-compatible cast (RelabelType), e.g. varchar vs text or
+-- int vs oid. The query-to-DXL translator used to assert on such join alias
+-- vars and fall back to the Postgres planner.
+--
+create table using_coerce_t1(k varchar(10), f2 int) distributed by (f2);
+create table using_coerce_t2(k text, f2 oid) distributed by (f2);
+insert into using_coerce_t1 values ('a', 1), ('b', 2);
+insert into using_coerce_t2 values ('a', 1), ('c', 3);
+explain (costs off) select k from using_coerce_t1 left join using_coerce_t2 using(k) order by 1;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: ((using_coerce_t1.k)::character varying)
+ -> Sort
+ Sort Key: ((using_coerce_t1.k)::character varying)
+ -> Hash Left Join
+ Hash Cond: ((using_coerce_t1.k)::text = using_coerce_t2.k)
+ -> Seq Scan on using_coerce_t1
+ -> Hash
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on using_coerce_t2
+ Optimizer: GPORCA
+(11 rows)
+
+select k from using_coerce_t1 left join using_coerce_t2 using(k) order by 1;
+ k
+---
+ a
+ b
+(2 rows)
+
+select f2, count(*) from using_coerce_t1 left join using_coerce_t2 using(f2) group by f2 order by 1;
+ f2 | count
+----+-------
+ 1 | 1
+ 2 | 1
+(2 rows)
+
+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;
+ f2 | count
+----+-------
+ 1 | 1
+ 2 | 1
+ | 1
+(3 rows)
+
+drop table using_coerce_t1;
+drop table using_coerce_t2;
diff --git a/src/test/regress/sql/join_gp.sql b/src/test/regress/sql/join_gp.sql
index f632cc5e0ac..0f318140a6f 100644
--- a/src/test/regress/sql/join_gp.sql
+++ b/src/test/regress/sql/join_gp.sql
@@ -1332,3 +1332,22 @@ select * from generate_series(1,2) t1, lateral (select t3.i from lat_oq_t2 t2 jo
drop table lat_oq_t2;
drop table lat_oq_t3;
+
+--
+-- Test that ORCA can plan a USING join whose merged column is the left input
+-- coerced with a binary-compatible cast (RelabelType), e.g. varchar vs text or
+-- int vs oid. The query-to-DXL translator used to assert on such join alias
+-- vars and fall back to the Postgres planner.
+--
+create table using_coerce_t1(k varchar(10), f2 int) distributed by (f2);
+create table using_coerce_t2(k text, f2 oid) distributed by (f2);
+insert into using_coerce_t1 values ('a', 1), ('b', 2);
+insert into using_coerce_t2 values ('a', 1), ('c', 3);
+
+explain (costs off) select k from using_coerce_t1 left join using_coerce_t2 using(k) order by 1;
+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;
+
+drop table using_coerce_t1;
+drop table using_coerce_t2;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org