[jira] [Comment Edited] (CALCITE-5043) GROUP BY alias or SELECT field should be determined by using sql dialect when alias and field are same

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

    [ https://issues.apache.org/jira/browse/CALCITE-5043?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17512582#comment-17512582 ] 

Julian Hyde edited comment on CALCITE-5043 at 3/25/22, 8:51 PM:
----------------------------------------------------------------

Does this bug relate to the JDBC adapter (i.e. when Calcite is generating SQL in other dialects)? Or something else (e.g. how Calcite handles SQL sent to it)?

If it is a bug in the JDBC adapter, then the remedy is to generate different SQL; let's start discussing what SQL the JDBC adapter should generate.

Are there any bugs related to this one? If so please link them.


was (Author: julianhyde):
Does this bug relate to the JDBC adapter (i.e. when Calcite is generating SQL in other dialects)? Or something else (e.g. how Calcite handles SQL sent to it)?

If it is a bug in the JDBC adapter, then the remedy is to generate different SQL; let's start discussing what SQL the JDBC adapter should generate.

> GROUP BY alias or SELECT field should be determined by using sql dialect when alias and field are same
> ------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-5043
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5043
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.29.0
>         Environment: jdk8
>            Reporter: yanjing.wang
>            Assignee: yanjing.wang
>            Priority: Major
>             Fix For: 1.31.0
>
>
> {noformat}
> select length(ename) as ename from emp group by ename{noformat}
> PostgreSQL: group by empno will be expanded to group by emp.empno.
> BigQuery: group by empno will be expanded to group by length(ename).
> MySQL ONLY_FULL_GROUP_BY mode(default mode): as PostgreSQL.
> MySQL extension:  as PostgreSQL.
> {noformat}
> select empno + deptno as empno from emp group by empno{noformat}
> PostgreSQL: throws column "emp.empno" must appear in the GROUP BY clause or be used in an aggregate function.
> BigQuery: group by empno will be expanded to group by empno + deptno.
> MySQL ONLY_FULL_GROUP_BY mode(default mode): throws as PostgreSQL.
> MySQL extension:  group by empno will be expanded to group by emp.empno.
> {noformat}
> select a.deptno + b.deptno as deptno from emp a , dept b group by deptno
> {noformat}
> PostgreSQL: throws column reference "deptno" is ambiguous.
> BigQuery: group by deptno will be expanded to group by a.deptno + b.deptno.
> MySQL ONLY_FULL_GROUP_BY mode(default mode): throws as PostgreSQL.
> MySQL extension:  as BigQuery.
> We need support expanding GROUP BY alias or SELECT field according to the sql dialect.



--
This message was sent by Atlassian Jira
(v8.20.1#820001)