[ https://issues.apache.org/jira/browse/CALCITE-6373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17838248#comment-17838248 ]
Julian Hyde commented on CALCITE-6373: -------------------------------------- I agree that any SQL-to-SQL transformation that alters the number or order of parameters (question marks in the text) is invalid. However, I don’t think the “distinct optimization” is to blame. The rel-to-SQL process should ensure that parameters are output in sequential order. I don’t know how to do that in general; is it possible to output PL/SQL or pgsql that assigns the parameters to variables, then uses those variables multiple times? > 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)