[
https://issues.apache.org/jira/browse/CALCITE-7279?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-7279.
----------------------------------
Fix Version/s: 1.42.0
Resolution: Fixed
Fixed in
[https://github.com/apache/calcite/commit/7c9a58c3a71e3c44814540fafcc1f4fc60833b8d]
Thank you for the fix [~krooswu]
Many thanks to all the 5 reviewers who commented on this PR.
> Resolve ClickHouse identifier resolution error by aliasing nested JOIN
> projections
> ----------------------------------------------------------------------------------
>
> Key: CALCITE-7279
> URL: https://issues.apache.org/jira/browse/CALCITE-7279
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.40.0
> Environment: *ClickHouse version:*
> 25.10.1.3832
> Reporter: Nikita Ilin
> Assignee: krooswu
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
> *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)
