This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 7c9a58c3a7 [CALCITE-7279] ClickHouse dialect should wrap nested JOINs
with explicit column aliases
7c9a58c3a7 is described below
commit 7c9a58c3a71e3c44814540fafcc1f4fc60833b8d
Author: krooswu <[email protected]>
AuthorDate: Wed Dec 17 22:55:10 2025 +0800
[CALCITE-7279] ClickHouse dialect should wrap nested JOINs with explicit
column aliases
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 88 +++++++++++++++-
.../java/org/apache/calcite/sql/SqlDialect.java | 18 ++++
.../calcite/sql/dialect/ClickHouseSqlDialect.java | 53 ++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 111 +++++++++++++++++++++
4 files changed, 269 insertions(+), 1 deletion(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index 47834ea9d0..592df51a0b 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -223,6 +223,74 @@ private static class AliasReplacementShuttle extends
SqlShuttle {
}
}
+ /**
+ * Wraps a nested join into a subquery with explicit column aliases.
+ *
+ * <p>This is specifically required for dialects like ClickHouse where the
+ * identifier resolver cannot resolve columns with internal table qualifiers
+ * (e.g., 'd1.loc') when they are wrapped in a subquery alias. By forcing
+ * an explicit 'AS' projection for every column, we ensure the identifiers
+ * are flattened and visible to the outer query block.
+ *
+ * @param input The Result of the nested join branch
+ * @param inputRel The RelNode representing the join branch to extract row
types
+ * @param outerAlias The alias to be assigned to the wrapped subquery
+ * @return A new Result containing the wrapped SQL with explicit aliases
+ */
+ protected Result wrapNestedJoin(Result input, RelNode inputRel, String
outerAlias) {
+ final SqlParserPos pos = SqlParserPos.ZERO;
+
+ // Obtain a SqlSelect representation of the input.
+ // We manually rewrite the SelectList to enforce explicit column aliases,
+ // preventing ClickHouse scoping issues while avoiding redundant sub-query
nesting.
+ final SqlSelect innerSelect = input.asSelect();
+ final SqlNodeList originalSelectList = innerSelect.getSelectList();
+ final List<String> fieldNames = inputRel.getRowType().getFieldNames();
+
+ final List<SqlNode> newSelectList = new ArrayList<>();
+
+ // Iterate through the fields to build explicit projections.
+ // Example: transforms 'd1.deptno' into 'd1.deptno AS deptno'.
+ for (int i = 0; i < fieldNames.size(); i++) {
+ SqlNode expr = originalSelectList.get(i);
+ String targetName = fieldNames.get(i);
+
+ // If the expression is already aliased, strip the AS to get the raw
expression.
+ if (expr.getKind() == SqlKind.AS) {
+ expr = ((SqlCall) expr).operand(0);
+ }
+
+ // Force an explicit alias to mask internal table qualifiers.
+ // This ensures the outer JOIN can resolve the column name
+ // without being confused by nested scope identifiers.
+ newSelectList.add(
+ SqlStdOperatorTable.AS.createCall(
+ pos,
+ expr,
+ new SqlIdentifier(targetName, pos)));
+ }
+
+ // Update the select list of the inner query with flattened aliases.
+ innerSelect.setSelectList(new SqlNodeList(newSelectList, pos));
+
+ // Wrap the modified Select node with the outer alias (e.g., AS j).
+ SqlNode wrappedNode =
+ SqlStdOperatorTable.AS.createCall(pos,
+ innerSelect,
+ new SqlIdentifier(outerAlias, pos));
+
+ // Return a new Result with empty clause lists. This "finalizes" the
+ // current sub-query and ensures the Implementor won't add
+ // redundant SELECT wrappers in subsequent steps.
+ return new Result(
+ wrappedNode,
+ Collections.emptyList(),
+ outerAlias,
+ inputRel.getRowType(),
+ ImmutableMap.of(outerAlias, inputRel.getRowType()));
+ }
+
+
/** Visits a Join; called by {@link #dispatch} via reflection. */
public Result visit(Join e) {
switch (e.getJoinType()) {
@@ -233,7 +301,25 @@ public Result visit(Join e) {
break;
}
final Result leftResult = visitInput(e, 0).resetAlias();
- final Result rightResult = visitInput(e, 1).resetAlias();
+ Result rightResult = visitInput(e, 1).resetAlias();
+
+ if (dialect.shouldWrapNestedJoin(e)) {
+
+ Set<String> usedNames = new HashSet<>(e.getRowType().getFieldNames());
+ // Add both left and right aliases
+ if (leftResult.neededAlias != null) {
+ usedNames.add(leftResult.neededAlias);
+ }
+ if (rightResult.neededAlias != null) {
+ usedNames.add(rightResult.neededAlias);
+ }
+
+ String safeAlias =
+ SqlValidatorUtil.uniquify("t", usedNames,
SqlValidatorUtil.EXPR_SUGGESTER);
+
+ rightResult = wrapNestedJoin(rightResult, e.getRight(), safeAlias);
+ }
+
final Context leftContext = leftResult.qualifiedContext();
final Context rightContext = rightResult.qualifiedContext();
parseCorrelTable(e, leftContext.implementor().joinContext(leftContext,
rightContext));
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
index a148e6ee6c..869976f0d4 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -1825,4 +1825,22 @@ private ContextImpl(DatabaseProduct databaseProduct,
conformance, nullCollation, dataTypeSystem, jethroInfo);
}
}
+
+ /**
+ * Returns whether this dialect requires wrapping a nested JOIN in a
subquery with <b>mandatory
+ * aliases</b> for both the table and its projected columns.
+ *
+ * <p>Example for ClickHouse (returns true):
+ * <pre>{@code
+ * // Before: ... LEFT JOIN (dept d1 INNER JOIN dept d2 ON ...) AS j ON ...
+ * // After: ... LEFT JOIN (
+ * // SELECT d1.deptno AS deptno, d2.loc AS loc ...
+ * // FROM dept d1 INNER JOIN dept d2 ON ...
+ * // ) AS t0 ON ...
+ * }</pre>
+ */
+ public boolean shouldWrapNestedJoin(RelNode relNode) {
+ return false;
+ }
+
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
index f33e49bb51..cd9e736f24 100644
---
a/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
+++
b/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
@@ -18,6 +18,12 @@
import org.apache.calcite.avatica.util.TimeUnitRange;
import org.apache.calcite.config.NullCollation;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Correlate;
+import org.apache.calcite.rel.core.Filter;
+import org.apache.calcite.rel.core.Join;
+import org.apache.calcite.rel.core.Project;
+import org.apache.calcite.rel.core.Sort;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
@@ -400,4 +406,51 @@ private static void unparseFloor(SqlWriter writer, SqlCall
call) {
call.operand(0).unparse(writer, 0, 0);
writer.endList(frame);
}
+
+ @Override public boolean shouldWrapNestedJoin(RelNode rel) {
+ if (!(rel instanceof Join)) {
+ return false;
+ }
+ Join join = (Join) rel;
+
+ // ClickHouse requires wrapping the right-side input if it's a JOIN
+ // to ensure that internal table qualifiers are flattened into explicit
aliases.
+ // This solves the Code 47 UNKNOWN_IDENTIFIER error.
+ RelNode right = join.getRight();
+
+ // If the right side is a Join or a Project containing a Join, it needs
aliasing protection
+ return right instanceof Join || containsJoinRecursive(right);
+ }
+
+ /**
+ * Checks whether the given RelNode contains a JOIN that is directly exposed
+ * to the outer scope, which could lead to "Unknown Identifier" errors in
ClickHouse.
+ *
+ * <p>ClickHouse (v25.x+) has strict scoping rules: when a JOIN appears on
the
+ * right side of another JOIN, internal table qualifiers (e.g., 'd2.loc') are
+ * stripped and become invisible to the outer query unless they are
explicitly
+ * aliased within a subquery.
+ *
+ * <p>We only check for JOINs wrapped by transparent single-input operators
+ * (Project, Filter, Sort) because these operators are typically collapsed
+ * into the same SELECT block, exposing the problematic JOIN structure to
+ * the outer boundary.
+ */
+ private static boolean containsJoinRecursive(RelNode rel) {
+ if (rel instanceof Join || rel instanceof Correlate) {
+ return true;
+ }
+
+ // Look through transparent single-input operators.
+ // We exclude Aggregate here because it naturally triggers a subquery
+ // boundary in RelToSqlConverter, which already provides the necessary
isolation.
+ if (rel instanceof Project
+ || rel instanceof Filter
+ || rel instanceof Sort) {
+ return rel.getInputs().size() == 1 &&
containsJoinRecursive(rel.getInput(0));
+ }
+
+ return false;
+ }
+
}
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index aba4ee7eaa..0c8e009c6e 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -11597,4 +11597,115 @@ public Sql schema(CalciteAssert.SchemaSpec
schemaSpec) {
relFn, transforms);
}
}
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7279";>[CALCITE-7279]
+ * ClickHouse dialect should wrap nested JOINs with explicit aliasing</a>. */
+ @Test void testClickHouseNestedJoin() {
+ final String query = "SELECT e.empno, j.dname, j.loc\n"
+ + "FROM emp e\n"
+ + "LEFT JOIN (\n"
+ + " SELECT d1.deptno, d1.dname, d2.loc\n"
+ + " FROM dept d1\n"
+ + " INNER JOIN dept d2 ON d1.deptno = d2.deptno\n"
+ + ") AS j ON e.deptno = j.deptno";
+
+ final String expected = "SELECT `EMP`.`EMPNO`, `t0`.`DNAME`, `t0`.`LOC`\n"
+ + "FROM `SCOTT`.`EMP`\n"
+ + "LEFT JOIN (SELECT `DEPT`.`DEPTNO` AS `DEPTNO`, `DEPT`.`DNAME` AS
`DNAME`,"
+ + " `DEPT0`.`LOC` AS `LOC`\n"
+ + "FROM `SCOTT`.`DEPT`\n"
+ + "INNER JOIN `SCOTT`.`DEPT` AS `DEPT0` ON `DEPT`.`DEPTNO` =
`DEPT0`.`DEPTNO`) "
+ + "AS `t0` ON `EMP`.`DEPTNO` = `t0`.`DEPTNO`";
+
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withClickHouse()
+ .ok(expected);
+ }
+
+ /** Test that simple JOINs without nesting are not wrapped unnecessarily. */
+ @Test void testClickHouseSimpleJoinNotWrapped() {
+ final String query = "SELECT e.empno, d.dname\n"
+ + "FROM emp e\n"
+ + "LEFT JOIN dept d ON e.deptno = d.deptno";
+
+ // Simple joins should remain flat as standard SQL
+ final String expected = "SELECT `EMP`.`EMPNO`, `DEPT`.`DNAME`\n"
+ + "FROM `SCOTT`.`EMP`\n"
+ + "LEFT JOIN `SCOTT`.`DEPT` ON `EMP`.`DEPTNO` = `DEPT`.`DEPTNO`";
+
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withClickHouse()
+ .ok(expected);
+ }
+
+ /** Test nested JOIN with aggregation to ensure expressions like COUNT(*)
are aliased. */
+ @Test void testClickHouseNestedJoinWithAggregation() {
+ final String query = "SELECT e.empno, j.\"EXPR$1\"\n"
+ + "FROM emp e\n"
+ + "LEFT JOIN (\n"
+ + " SELECT d1.deptno, COUNT(*)\n"
+ + " FROM dept d1\n"
+ + " INNER JOIN dept d2 ON d1.deptno = d2.deptno\n"
+ + " GROUP BY d1.deptno\n"
+ + ") AS j ON e.deptno = j.deptno";
+
+ final String expected = "SELECT `EMP`.`EMPNO`, `t0`.`EXPR$1`\n"
+ + "FROM `SCOTT`.`EMP`\n"
+ + "LEFT JOIN (SELECT `DEPT`.`DEPTNO`, COUNT(*) AS `EXPR$1`\n"
+ + "FROM `SCOTT`.`DEPT`\n"
+ + "INNER JOIN `SCOTT`.`DEPT` AS `DEPT0` ON `DEPT`.`DEPTNO` =
`DEPT0`.`DEPTNO`\n"
+ + "GROUP BY `DEPT`.`DEPTNO`) AS `t0` ON `EMP`.`DEPTNO` =
`t0`.`DEPTNO`";
+
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withClickHouse()
+ .ok(expected);
+ }
+
+ /** Test three-way JOIN to ensure the converter handles linear joins
normally. */
+ @Test void testClickHouseThreeWayJoin() {
+ final String query = "SELECT e.empno, d1.dname, d2.loc\n"
+ + "FROM emp e\n"
+ + "INNER JOIN dept d1 ON e.deptno = d1.deptno\n"
+ + "INNER JOIN dept d2 ON d1.deptno = d2.deptno";
+
+ // Standard multi-way joins shouldn't be forced into subqueries unless
nested on the right
+ final String expected = "SELECT `EMP`.`EMPNO`, `DEPT`.`DNAME`,
`DEPT0`.`LOC`\n"
+ + "FROM `SCOTT`.`EMP`\n"
+ + "INNER JOIN `SCOTT`.`DEPT` ON `EMP`.`DEPTNO` = `DEPT`.`DEPTNO`\n"
+ + "INNER JOIN `SCOTT`.`DEPT` AS `DEPT0` ON `DEPT`.`DEPTNO` =
`DEPT0`.`DEPTNO`";
+
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withClickHouse()
+ .ok(expected);
+ }
+
+ /** Regression test: Ensure MySQL dialect remains unaffected by
ClickHouse-specific fix. */
+ @Test void testMysqlNestedJoinNotWrapped() {
+ final String query = "SELECT e.empno, j.dname\n"
+ + "FROM emp e\n"
+ + "LEFT JOIN (\n"
+ + " SELECT d1.deptno, d1.dname\n"
+ + " FROM dept d1\n"
+ + " INNER JOIN dept d2 ON d1.deptno = d2.deptno\n"
+ + ") AS j ON e.deptno = j.deptno";
+
+ // MySQL supports nested join syntax; no additional wrapping select should
be added by our fix.
+ final String expected = "SELECT `EMP`.`EMPNO`, `t`.`DNAME`\n"
+ + "FROM `SCOTT`.`EMP`\n"
+ + "LEFT JOIN (SELECT `DEPT`.`DEPTNO`, `DEPT`.`DNAME`\n"
+ + "FROM `SCOTT`.`DEPT`\n"
+ + "INNER JOIN `SCOTT`.`DEPT` AS `DEPT0` ON `DEPT`.`DEPTNO` =
`DEPT0`.`DEPTNO`) AS `t` "
+ + "ON `EMP`.`DEPTNO` = `t`.`DEPTNO`";
+
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withMysql()
+ .ok(expected);
+ }
+
}
