[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=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=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=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=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=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)
