[
https://issues.apache.org/jira/browse/CALCITE-7279?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18047057#comment-18047057
]
krooswu commented on CALCITE-7279:
----------------------------------
*Environment:* ClickHouse Server v25.12.1
*Reproduction SQL:*
SQL
{{SELECT e.empno, j.dname, j.locFROM emp AS eLEFT JOIN ( SELECT d1.deptno,
d1.dname, d2.loc FROM dept AS d1 INNER JOIN dept AS d2 ON d1.deptno =
d2.deptno
) AS j ON e.deptno = j.deptno;}}
*Observed Exception:*
Plaintext
{{Code: 47. DB::Exception: Identifier 'j.loc' cannot be resolved from subquery
with name j.
In scope SELECT ... Maybe you meant: ['d2.loc']. (UNKNOWN_IDENTIFIER)}}
*Analysis:* The error confirms that ClickHouse cannot resolve {{j.loc}} because
it still associates the column with the internal qualifier {{{}d2{}}}. This
confirms the need for *explicit aliasing* ({{{}d2.loc AS loc{}}}) within the
subquery to flatten the scope and make the identifier visible to the outer
query block.
> [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
> Affects Versions: 1.40.0
> Environment: *ClickHouse version:*
> 25.10.1.3832
> Reporter: Nikita Ilin
> Assignee: krooswu
> Priority: Major
> Labels: pull-request-available
>
> *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)
