[
https://issues.apache.org/jira/browse/CALCITE-4512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17504736#comment-17504736
]
Xurenhe commented on CALCITE-4512:
----------------------------------
Hi all, I met the similar problem, and do some investigations about this issue.
{code:java}
-- ddl
CREATE TABLE test_tbl (
id integer,
col1 varchar
);
-- table's data
id | col1
----+------
1 | abc
2 | abb
3 | abcd
4 | a
5 | abc
{code}
Exec query as below:
{code:java}
// query sql
SELECT LENGTH(col1) AS col1, COUNT(*)
FROM test_tbl
GROUP BY col1 {code}
MySQL-Result(MySQL5.7 or MySQL8.0):
{code:java}
+------+----------+
| col1 | COUNT(*) |
+------+----------+
| 1 | 1 |
| 3 | 1 |
| 3 | 2 |
| 4 | 1 |
+------+----------+ {code}
PostgreSQL-Result(PostgreSQL 13):
{code:java}
col1 | count
------+-------
1 | 1
3 | 1
4 | 1
3 | 2 {code}
SQLite-Result:(SQLite 3)
{code:java}
col1 | count
------+-------
1 | 1
3 | 1
4 | 1
3 | 2 {code}
> 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
> Labels: pull-request-available
> Time Spent: 2h 20m
> Remaining Estimate: 0h
>
> 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.20.1#820001)
