[jira] [Commented] (CALCITE-6373) Distinct optimization produces broken sql query
[ 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)
[jira] [Commented] (CALCITE-6373) Distinct optimization produces broken sql query
[ https://issues.apache.org/jira/browse/CALCITE-6373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17840282#comment-17840282 ] Ulrich Kramer commented on CALCITE-6373: 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)
[jira] [Commented] (CALCITE-6373) Distinct optimization produces broken sql query
[ https://issues.apache.org/jira/browse/CALCITE-6373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17840146#comment-17840146 ] Mihai Budiu commented on CALCITE-6373: -- Do you plan to contribute the code to Calcite? I think that the Calcite style guides do not like * imports. > 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)
[jira] [Commented] (CALCITE-6373) Distinct optimization produces broken sql query
[ 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)
[jira] [Commented] (CALCITE-6373) Distinct optimization produces broken sql query
[ 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)