You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "yanjing.wang (Jira)" <ji...@apache.org> on 2022/03/15 11:45:00 UTC
[jira] [Updated] (CALCITE-5043) GROUP BY alias or SELECT field should be determined by using sql dialect when alias and field are same
[ https://issues.apache.org/jira/browse/CALCITE-5043?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
yanjing.wang updated CALCITE-5043:
----------------------------------
Description:
{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.
was:
{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}
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.
> 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 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)