[
https://issues.apache.org/jira/browse/CALCITE-7279?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nikita Ilin updated CALCITE-7279:
---------------------------------
Environment:
*ClickHouse version:*
25.10.1.3832
> [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
> Environment: *ClickHouse version:*
> 25.10.1.3832
> Reporter: Nikita Ilin
> Priority: Major
>
> *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)
