[
https://issues.apache.org/jira/browse/CALCITE-4512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17326811#comment-17326811
]
Julian Hyde commented on CALCITE-4512:
--------------------------------------
I agree that " SELECT x + 1 AS x FROM t GROUP BY (t.x + 1) + 1" doesn't make
sense. Your example with "FLOOR(id / 2)" makes more sense, and allows to
execute the query and deduce what expression has been computed.
But now we need to figure out which queries are valid. When we implemented
group-by-alias, I was under the impression that the alias would be substituted
wherever it occurred in a GROUP BY expression, but I think I was wrong; now, my
hypothesis is that the alias will be substituted only if the GROUP BY
expression is an identifier. Do you agree with that hypothesis?
The following might be a useful experiment on MySQL and BigQuery:
{code}
SELECT FLOOR(e.deptno / 2) AS deptno
FROM Emp AS e JOIN Dept AS d ON e.deptno = d.deptno
GROUP BY deptno
{code}
If the {{deptno}} obscures the {{e.deptno}} and {{d.deptno}} columns, there
will not be an error about ambiguous column references.
Also:
{code}
SELECT FLOOR(e.deptno / 2) AS deptno,
FLOOR(e.deptno / 2) + 1
FROM Emp AS e JOIN Dept AS d ON e.deptno = d.deptno
GROUP BY deptno + 1
{code}
Because {{deptno + 1}} is an expression, is the {{deptno}} alias no longer
visible, and does it now throw an error about ambiguous columns?
> 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)
