Dwrite commented on code in PR #4692:
URL: https://github.com/apache/calcite/pull/4692#discussion_r2637132380
##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -11491,4 +11463,196 @@ 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</a>.
+ *
+ * <p>ClickHouse does not support nested JOIN syntax directly.
+ * When a JOIN's right side is another JOIN, it must be wrapped
+ * in a SELECT * FROM (...) subquery. */
+ @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();
+
+ assertThat(sql, containsString("LEFT JOIN (SELECT *"));
Review Comment:
Thanks for the suggestion. I agree that using `ok(expected)` is generally
preferred
and is what we should use whenever possible.
In this case, however, the generated SQL contains auto-generated subquery
aliases
(e.g. t220414 / t220415) coming from Calcite’s alias generation, and these
IDs are
not stable across different runs (for example, `gradlew test` vs IDE runs).
This makes a strict `ok(expected)` assertion flaky.
To avoid introducing non-deterministic tests, this test verifies the
structural
properties of the generated SQL instead (i.e. whether the required wrapper
`SELECT * FROM (...)` is present or absent), rather than asserting exact
alias
names.
I’ve added a comment in the test to document this reasoning. If alias
generation
becomes stable in the future, we can switch this back to `ok(expected)`.
--
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]
