[jira] [Comment Edited] (CALCITE-6373) Distinct optimization produces broken sql query

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

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

Ulrich Kramer edited comment on CALCITE-6373 at 4/24/24 4:32 AM:
-----------------------------------------------------------------

I'm aware of the fact that the formatting doesn't fit Calcite's requirements.
I just wanted to present the solution here. I think the fix needs more changes regarding {{SqlConformance.isGroupByAlias}}.
If {{SqlConformance.isGroupByAlias}} is supported, aliases could be used to fix this issue. In this case no sub-select is required.


was (Author: kramerul):
I'm aware of the fact that the formatting doesn't fit Calcite's requirements.
I just wanted to present the solution here. I think the fix needs more changes regarding {{SqlConformance.isGroupByAlias}}.
If {{SqlConformance.isGroupByAlias}} is supported, aliases could be used to fix this issue. In this case not sub-select is required.

> Distinct optimization produces broken sql query
> -----------------------------------------------
>
>                 Key: CALCITE-6373
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6373
>             Project: Calcite
>          Issue Type: Bug
>          Components: core, jdbc-adapter
>    Affects Versions: 1.36.0
>            Reporter: Corvin Kuebler
>            Priority: Major
>
> Can be reproduced with the following test in JDBCTests:
> {code:java}
>     String statement = "SELECT\n" +
>                        "  DISTINCT \"case-column\"\n" +
>                        "FROM (\n" +
>                        "   SELECT \n" +
>                        "   CASE\n" +
>                        "      WHEN CAST(? AS VARCHAR) = \"ENAME\" THEN CAST(? AS INTEGER)\n" +
>                        "      ELSE CAST(? AS INTEGER)\n" +
>                        "      END AS \"case-column\"\n" +
>                        "   FROM \"EMP\")";
>     CalciteAssert.model(JdbcTest.SCOTT_MODEL)
>         .query(statement)
>         .consumesPreparedStatement(p -> {
>           p.setString(1, "name");
>           p.setInt(2, 2);
>           p.setInt(3, 1);
>         })
>         .planHasSql("");
> {code}
> Lets assume the following statement is passed through calcite:
> Before:
> {code:java}
> SELECT
>   DISTINCT "case-column"
> FROM
>    SELECT 
>    CASE
>       WHEN CAST(? AS VARCHAR) = "store_name" THEN CAST(? AS INTEGER)
>       ELSE CAST(? AS INTEGER)
>       END AS "case-column"
>    FROM "foodmart"."store"
> {code}
> After:
> {code:java}
> SELECT
>    CASE
>       WHEN ? = "ENAME" THEN ?
>       ELSE ?
>    END AS "case-column"
> FROM
>    "SCOTT"."EMP"
> GROUP BY
>    CASE
>       WHEN ? = "ENAME" THEN ?
>       ELSE ?
>    END
> {code}
> The produced statement hast two issues:
> 1. The missing casts (see also https://issues.apache.org/jira/browse/CALCITE-6346)
> 2. Instead of pushing the DISTINCT it is replaced with a GROUP BY. This is usually fine *but* since the field is a case statement containing dynamic parameters it is not.
> During sql syntax evaluation the database will give an error (the field in the select is not contained in group by). This is because the dynamic parameters are not evaluated during sql syntax evaluation.
> I think this could be fixed by adding an alias to the field in the select clause and referencing it in the group by clause instead of duplicating the case statement and the dynamic parameters.



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