[
https://issues.apache.org/jira/browse/CALCITE-4512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17326234#comment-17326234
]
yanjing.wang edited comment on CALCITE-4512 at 4/21/21, 7:54 AM:
-----------------------------------------------------------------
{{I have no idea to verify}} {{that}} *{{SELECT x + 1 AS x FROM t GROUP BY x +
1}}* will be expanded to *SELECT x + 1 AS x FROM t GROUP BY (t.x + 1) + 1* in
MySQL.
but I use MySQL 5.7.32 to verify that *select floor(id / 2) as id from users t
group by floor(id / 2)* has been expanded to *select floor(id / 2) as id from
users t group by floor(t.id / 2)*. given that the users table has id values 1,
2, 3, 4. the sql query exact result is 0, 1, 2.
if the sql has been expanded to *select floor(id / 2) as id from users t group
by floor(floor(t.id/2) / 2)*, the sql result would be 0, 2 due to
*floor(floor(t.id/2) / 2)* resulting 0, 0, 0, 1. and further, the MySQL raises
error when executing *select floor(id / 2) as id from users t group by
floor(floor(t.id/2) / 2)* because select list not in group by clause, so I
think MySQL doesn't support the 'double expansion'.
[~julianhyde] why do you think that MySQL will expanded *{{GROUP BY x + 1 to
GROUP BY (t.x + 1) + 1?}}*
was (Author: yanjing.wang):
{{I have no idea to verify}} {{that}} *{{SELECT x + 1 AS x FROM t GROUP BY x +
1}}* will be expanded to *SELECT x + 1 AS x FROM t GROUP BY (t.x + 1) + 1* in
MySQL.
but I use MySQL 5.7.32 to verify that *select floor(id / 2) as id from users t
group by floor(id / 2)* has been expanded to *select floor(id / 2) as id from
users t group by floor(t.id / 2)*. given that the users table has id values 1,
2, 3, 4. the sql query exact result is 0, 1, 2.
if the sql has been expanded to *select floor(id / 2) as id from users t group
by floor(floor(t.id/2) / 2)*, the sql result would be 0, 2 due to
*floor(floor(t.id/2) / 2)* resulting 0, 0, 0, 1. and further, the MySQL raises
error when executing *select floor(id / 2) as id from users t group by
floor(floor(t.id/2) / 2)* because select list not in group by clause.
[~julianhyde] why do you think that MySQL will expanded *{{GROUP BY x + 1 to
GROUP BY (t.x + 1) + 1?}}*
> 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)
