[ https://issues.apache.org/jira/browse/CALCITE-6373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17840011#comment-17840011 ]
Ulrich Kramer commented on CALCITE-6373: ---------------------------------------- We fixed the issue using this commit: https://github.com/sap-contributions/calcite/commit/624a57e61f08fe8a171f1866c330381c86f56239 > 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)