[
https://issues.apache.org/jira/browse/CALCITE-6368?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17846076#comment-17846076
]
James Duong commented on CALCITE-6368:
--------------------------------------
[~cbrisson] , I tried the query above in SqlValidatorTest and it looks to have
expanded the COALESCE:
{code:java}
WITH `A` AS (SELECT 1 AS `X`) SELECT CASE WHEN `A0`.`X` IS NOT NULL THEN
`A0`.`X` ELSE `A1`.`X` END, *
FROM `A` AS `A0`
NATURAL INNER JOIN `A` AS `A1 {code}
(ie, the coalesce is expanded).
This is at commit c228804e258f72cd5df0f1f263dcc4f86410d3ff
I'll check with 1.37 and 1.36 as well.
> Inconsistent handling of COALESCE during validation
> ---------------------------------------------------
>
> Key: CALCITE-6368
> URL: https://issues.apache.org/jira/browse/CALCITE-6368
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.36.0
> Reporter: Claude Brisson
> Assignee: James Duong
> Priority: Major
>
> {{SqlCoalesceFunction}} has a {{rewriteCall()}} method which will replace the
> call with an {{SqlCase}} call during the unconditional rewrites stage.
> But expanding natural joins and {{USING}} conditions is done using
> {{COALESCE}} calls in a later validation stage.
> As a result, the following query:
> {code}
> WITH A AS (SELECT 1 AS X)
> SELECT
> COALESCE(A0.X, A1.X),
> *
> FROM A AS A0 NATURAL JOIN A AS A1
> {code}
> is transformed by the validation step into:
> {code}
> WITH `A` AS (SELECT 1 AS `X`)
> SELECT
> CASE WHEN `A0`.`X` IS NOT NULL THEN `A0`.`X` ELSE `A1`.`X` END,
> COALESCE(`A0`.`X`, `A1`.`X` AS `X0`) AS `X`
> FROM `A` AS `A0` NATURAL INNER JOIN `A` AS `A1`
> {code}
> Maybe, the expanded results of {{*}} with natural joins and of {{USING}}
> should pass through unconditional rewrites at creation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
