[jira] [Commented] (CALCITE-7052) When conformance specifies isGroupbyAlias = true the validator rejects legal queries

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

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

Mihai Budiu commented on CALCITE-7052:
--------------------------------------

I think this bug was introduced in [CALCITE-4512]
[~yanjing.wang] you wrote this code.
I have reviewed it.

The fix in 4512 is incorrect because it will expand the following query:

{code:sql}
select floor(empno/2) as empno from emp group by empno
{code}

into

{code:sql}
select floor(empno/2) as empno from emp group by floor(empno/2)
{code}

which does something completely different.

The test in misc.iq doesn't catch this because it doesn't use the correct conformance.

> When conformance specifies isGroupbyAlias = true the validator rejects legal queries
> ------------------------------------------------------------------------------------
>
>                 Key: CALCITE-7052
>                 URL: https://issues.apache.org/jira/browse/CALCITE-7052
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.40.0
>            Reporter: Mihai Budiu
>            Priority: Minor
>
> The following test in SqlValidatorTest passes:
> {code:java}
>     sql("SELECT ALL - cor0.empno AS empno "
>         + "FROM emp AS cor0 GROUP BY empno HAVING NOT ( cor0.empno ) IS NULL")
>         .ok();
> {code}
> but the following test fails:
> {code:java]
>     sql("SELECT ALL - cor0.empno AS empno "
>         + "FROM emp AS cor0 GROUP BY empno HAVING NOT ( cor0.empno ) IS NULL")
>         .withConformance(SqlConformanceEnum.LENIENT)
>         .ok();
> {code}
> with the error:
> {code}
> Expression 'COR0.EMPNO' is not being grouped
> {code}
> The validator incorrectly rewrites the query as:
> SELECT ALL - `COR0`.`EMPNO` AS `EMPNO`
> FROM `EMP` AS `COR0`
> GROUP BY - `COR0`.`EMPNO`
> HAVING NOT `COR0`.`EMPNO` IS NULL
> because in this conformance isGroupbyAlias returns true.
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)