[
https://issues.apache.org/jira/browse/CALCITE-4512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17325953#comment-17325953
]
Julian Hyde commented on CALCITE-4512:
--------------------------------------
I think it is correct to expand. But the expansion should use the qualified
name, so it is clearly referencing the column, not the alias. Like this:
{code}
select replace(name, 'a', 'b') as name
from users
group by replace(replace(users.name, 'a', 'b'), 'a', 'b')
{code}
Let's just confirm. Can you try {{SELECT x + 1 AS x FROM t GROUP BY x + 1}} on
a database that does group by alias (BigQuery, MySQL). I think you will find
that x is incremented twice, because the query will have been expanded to
{{SELECT x + 1 AS x FROM t GROUP BY (t.x + 1) + 1}}. Conversely, if you write
{{SELECT x + 1 AS x FROM t GROUP BY t.x + 1}} (using a qualified column name) x
will be incremented once.
To be clear. The "double expansion" is correct. What is incorrect is that the
validator throws an error.
[~yanjing.wang], Are you working on this? If not, please clear 'fix version'.
> group by expression has argument name same with select list item alias,
> causes validation error
> -----------------------------------------------------------------------------------------------
>
> Key: CALCITE-4512
> URL: https://issues.apache.org/jira/browse/CALCITE-4512
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.26.0
> Environment: jvm: open-jdk8
> Reporter: yanjing.wang
> Priority: Major
> Fix For: 1.27.0
>
>
> String sql = "select replace(name, 'a', 'b') as name from users group by
> replace(name, 'a', 'b')";
>
> when group by expression has argument name same with select list item alias,
> the group by argument will be expanded to select list item when sql
> conformance supports 'groupByAlias', so the above sql will be expanded to
> String sql = "select replace(name, 'a', 'b') as name from users group by
> replace(replace(name, 'a', 'b'), 'a', 'b')";
>
> this is unexpected.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
