Corvin Kuebler created CALCITE-6373:
---------------------------------------
Summary: 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
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)
