Nikita Ilin created CALCITE-7279:
------------------------------------
Summary: [ClickHouse] Dialect does not generate required subquery
wrappers for nested JOINs for ClickHouse
Key: CALCITE-7279
URL: https://issues.apache.org/jira/browse/CALCITE-7279
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.40.0
Reporter: Nikita Ilin
*Problem Description*
When generating SQL for ClickHouse using {{{}ClickHouseSqlDialect{}}}, the
{{RelToSqlConverter}} produces syntactically invalid SQL if the underlying
{{RelNode}} plan contains nested JOINs.
ClickHouse mandates that if an operand of a JOIN (especially the RHS) is itself
a JOIN operation (including CROSS JOINs), it must be wrapped in an aliased
subquery (e.g., {{{}SELECT * FROM (...) AS alias{}}}). Calcite currently does
not generate this wrapper for the ClickHouse dialect.
*Conceptual RelNode Structure:*
{code:java}
LogicalJoin(condition=[...], joinType=[left])
LogicalTableScan(table=[FactTable])
LogicalJoin(condition=[true], joinType=[inner]) <-- Nested JOIN on RHS
LogicalAggregate(...)
LogicalAggregate(...){code}
*Actual Result (Invalid ClickHouse SQL):*
{code:sql}
SELECT ...
FROM `FactTable`
LEFT JOIN (
(SELECT DISTINCT `x` FROM `Dim1`) AS `t0`
CROSS JOIN
(SELECT DISTINCT `y` FROM `Dim2`) AS `t1`
) ON ...{code}
*Expected Result (Valid ClickHouse SQL):*
{code:sql}
SELECT ...
FROM `FactTable`
LEFT JOIN (
-- Wrapper needed for ClickHouse compatibility
SELECT * FROM (
(SELECT DISTINCT `x` FROM `Dim1`) AS `t0`
CROSS JOIN
(SELECT DISTINCT `y` FROM `Dim2`) AS `t1`
) AS nested_alias
) AS `t_wrapped` ON ...{code}
*Impact and Workarounds*
This issue forces users to implement complex workarounds, such as:
# Fragile regex post-processing of the SQL string.
# Complex {{RelNode}} rewriting (e.g., injecting opaque UDFs via
{{RelShuttle}} or identity aggregates) to force the generation of a subquery
boundary.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
