This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new aa90b89709 [CALCITE-2659] Inefficient plan In natural left/right join
aa90b89709 is described below
commit aa90b897092e0d9ce4cb9cb5ed387b159dfa3355
Author: Mihai Budiu <[email protected]>
AuthorDate: Tue Jun 16 15:34:57 2026 -0700
[CALCITE-2659] Inefficient plan In natural left/right join
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/sql/validate/SqlValidatorImpl.java | 26 ++++++++++++++++------
.../apache/calcite/test/SqlToRelConverterTest.xml | 21 +++++++++--------
2 files changed, 29 insertions(+), 18 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 7989b7e7d8..1e2b298808 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -7843,17 +7843,29 @@ private SqlNode expandExprFromJoin(SqlJoin join,
SqlIdentifier identifier, Selec
assert qualifiedNode.size() == 2;
- final SqlCall coalesceCall =
- SqlStdOperatorTable.COALESCE.createCall(SqlParserPos.ZERO,
qualifiedNode.get(0),
- qualifiedNode.get(1));
+ // COALESCE is only needed for FULL JOIN; for RIGHT JOIN use the
right
+ // column (always non-null), and for INNER/LEFT JOIN use the left
column.
+ final JoinType joinType = join.getJoinType();
+ final SqlNode colRef;
+ if (joinType.generatesNullsOnLeft() &&
joinType.generatesNullsOnRight()) {
+ colRef =
+ SqlStdOperatorTable.COALESCE.createCall(
+ qualifiedNode.get(0).getParserPosition()
+ .plus(qualifiedNode.get(1).getParserPosition()),
+ qualifiedNode.get(0), qualifiedNode.get(1));
+ } else if (joinType.generatesNullsOnLeft()) {
+ colRef = qualifiedNode.get(1);
+ } else {
+ colRef = qualifiedNode.get(0);
+ }
- // If there is an alias for the column, no need to wrap the coalesce
with an AS operator
+ // If there is an alias for the column, no need to wrap with an AS
operator
boolean haveAlias = fieldAliases.contains(name);
if (haveAlias) {
- return coalesceCall;
+ return colRef;
} else {
- return SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO,
coalesceCall,
- new SqlIdentifier(identifier.getSimple(), SqlParserPos.ZERO));
+ return SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO, colRef,
+ new SqlIdentifier(identifier.getSimple(),
identifier.getParserPosition()));
}
}
}
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 192a0140da..0bf02c9a73 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -4042,12 +4042,11 @@ group by deptno]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(DEPTNO=[$0], EXPR$1=[$1])
- LogicalAggregate(group=[{0}], EXPR$1=[COUNT()])
- LogicalProject($f0=[$7])
- LogicalJoin(condition=[=($7, $9)], joinType=[inner])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+LogicalAggregate(group=[{0}], EXPR$1=[COUNT()])
+ LogicalProject(DEPTNO=[$7])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -4063,7 +4062,7 @@ group by grouping sets ((deptno), (deptno, job))]]>
<![CDATA[
LogicalProject(DEPTNO=[$0], EXPR$1=[$2], EXPR$2=[$3], EXPR$3=[$4])
LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
EXPR$1=[GROUPING($0)], EXPR$2=[GROUPING($0, $1)], EXPR$3=[COUNT()])
- LogicalProject($f0=[$7], JOB=[$2])
+ LogicalProject(DEPTNO=[$7], JOB=[$2])
LogicalJoin(condition=[=($7, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -4112,7 +4111,7 @@ natural join customer.contact_peek t2]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(X=[COALESCE(ROW($4, $5, $6), ROW($14, $15, $16)).X])
+LogicalProject(X=[$4])
LogicalJoin(condition=[AND(=($0, $10), =($1, $11), =($2, $12), =($3, $13),
AND(AND(=($4, $14), =($5, $15)), =($6, $16)), AND(AND(=($7, $17), =($8, $18)),
=($9, $19)))], joinType=[inner])
LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
@@ -4484,7 +4483,7 @@ group by grouping sets ((deptno), (deptno, job))]]>
<![CDATA[
LogicalProject(DEPTNO=[$0], EXPR$1=[$2], EXPR$2=[$3], EXPR$3=[$4])
LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
EXPR$1=[GROUPING($0)], EXPR$2=[GROUPING($0, $1)], EXPR$3=[COUNT()])
- LogicalProject($f0=[$7], JOB=[$2])
+ LogicalProject(DEPTNO=[$7], JOB=[$2])
LogicalJoin(condition=[=($7, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -4532,7 +4531,7 @@ using (coord)]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(X=[COALESCE(ROW($4, $5, $6), ROW($14, $15, $16)).X])
+LogicalProject(X=[$4])
LogicalJoin(condition=[AND(AND(=($4, $14), =($5, $15)), =($6, $16))],
joinType=[inner])
LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
@@ -7521,7 +7520,7 @@ FROM emp RIGHT OUTER JOIN dept using (deptno)]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(DEPTNO=[COALESCE($7, $9)], NAME=[$10])
+LogicalProject(DEPTNO=[$9], NAME=[$10])
LogicalJoin(condition=[=($7, $9)], joinType=[right])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
