[
https://issues.apache.org/jira/browse/CALCITE-6583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17882128#comment-17882128
]
Julian Hyde commented on CALCITE-6583:
--------------------------------------
Does the reformulated version return the same results? I suspect that it does
(inside a WHERE, FALSE and NULL aka UNKNOWN have the same effect, and knowing
that, Calcite seems to have aggressively pushed down the implicit IS TRUE
condition into the CASE).
If the results are the same, it's only a minor bug - namely that the generated
SQL is unpleasant to look at.
> Case Statement reformulation in JdbcAdapter
> -------------------------------------------
>
> Key: CALCITE-6583
> URL: https://issues.apache.org/jira/browse/CALCITE-6583
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
> Affects Versions: 1.37.0
> Reporter: Corvin Kuebler
> Priority: Major
>
> Hey @all!
> We encountered some strange behavior when working with the JdbcAdapter.
> The following test in JdbcAdapterTests shows the issue:
> {code:java}
> @Test void testReformulatesCaseStatements() throws Exception {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("SELECT\n" +
> " \"EMPNO\"\n" +
> "FROM\n" +
> " (\n" +
> " SELECT\n" +
> " \"EMPNO\",\n" +
> " (\n" +
> " CASE\n" +
> " WHEN \"EMPNO\" = CAST(? AS INT)\n" +
> " THEN CAST(? AS INT)\n" +
> " ELSE NULL\n" +
> " END\n" +
> " ) AS \"test-case\"\n" +
> " FROM\n" +
> " \"EMP\"\n" +
> " WHERE\n" +
> " \"DEPTNO\" IN (CAST(? AS INT))\n" +
> " )\n" +
> "WHERE\n" +
> " \"test-case\" IN (?)")
> .planContains("ELSE NULL");
> }
> {code}
> PlanSql:
> {code:java}
> SELECT "EMPNO"
> FROM "SCOTT"."EMP"
> WHERE CAST("DEPTNO" AS INTEGER) = CAST(? AS INTEGER) AND CASE WHEN
> CAST("EMPNO" AS INTEGER) = CAST(? AS INTEGER) THEN CAST(? AS INTEGER) = ?
> ELSE FALSE END
> {code}
> The "incoming" case statement contains an else branch that sets the output of
> the branch to `null`.
> However we encountered the issue that the `ELSE null` is rewritten to `ELSE
> FALSE` by calcite.
> This only happens for this specific query "configuration" (same filters/etc.)
> Could some1 please take a look and tell me if the reformulation is a bug
> (which it is imho).
> BR,
> Corvin
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
