[
https://issues.apache.org/jira/browse/CALCITE-4512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17330036#comment-17330036
]
yanjing.wang commented on CALCITE-4512:
---------------------------------------
ok, I have experimented some tests on MySQL, and concluded that the alias will
be substituted only if the GROUP BY expression is an identifier and the
identifier doesn't collide with *any real column* in current select scope. see
the following emp table.
empno deptno
1 1
1 2
1 1
{code:java}
select empno + deptno as empno from emp group by empno;{code}
MySQL results 2. so *empno hasn't been expanded to empno + deptno*. how should
calcite behave? I think we should conform with MySQL, because most engines
execute GROUP BY *first*, and doesn't recognize alias. Meanwhile, I think
calcite should support that deptno doesn't occur within GROUP BY clause.
> 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
>
> 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)
