[
https://issues.apache.org/jira/browse/CALCITE-6373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17841285#comment-17841285
]
Julian Hyde commented on CALCITE-6373:
--------------------------------------
I suspect that the fix only fixes the special case of one variable. If there
are two or more variables they might be re-ordered in the generated SQL. And
even with one variable, the variable might be duplicated in the generated SQL.
I guess it's OK to fix a special case of the bug. But the commit message should
perhaps note that it is a limited fix.
> 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)
