Dwrite commented on code in PR #4692:
URL: https://github.com/apache/calcite/pull/4692#discussion_r2679398457
##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -11550,4 +11551,118 @@ 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 sql = sql(query)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withClickHouse()
+ .exec();
+
+ // 1. Verify that the inner join is wrapped in a subquery
+ assertThat(sql, containsString("LEFT JOIN (SELECT"));
+
+ // 2. Verify explicit aliasing which is crucial for ClickHouse scoping.
+ // We must see "source AS alias" to ensure the identifier is resolvable
outer scope.
+ assertThat(sql, containsString("`DEPTNO` AS `DEPTNO`"));
+ assertThat(sql, containsString("`DNAME` AS `DNAME`"));
+ assertThat(sql, containsString("`LOC` AS `LOC`"));
+
+ // 3. Ensure we don't have redundant double-wrapping like (SELECT * FROM
(SELECT...))
+ // The structure should be: LEFT JOIN (SELECT expr AS col FROM ...) AS j
+ assertFalse(sql.contains("SELECT *"),
+ "ClickHouse dialect should avoid SELECT * in nested joins");
+ }
+
+ /** 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.cnt\n"
+ + "FROM emp e\n"
+ + "LEFT JOIN (\n"
+ + " SELECT d1.deptno, COUNT(*) as cnt\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 sql = sql(query)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withClickHouse()
+ .exec();
+
+ // Aggregation results must be explicitly aliased for ClickHouse visibility
+ assertThat(sql, containsString("COUNT(*) AS `CNT`"));
Review Comment:
Updated. I removed the explicit AS cnt from the source query to confirm that
the converter automatically generates a system alias (like AS EXPR$1). This
proves the logic works even when the user doesn't provide an alias. Thanks for
the catch!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
