[jira] [Commented] (CALCITE-4512) group by expression has argument name same with select list item alias, causes validation error

["Xurenhe (Jira)" <ji...@apache.org>]

    [ 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)