[
https://issues.apache.org/jira/browse/CALCITE-6583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Corvin Kuebler updated CALCITE-6583:
------------------------------------
Description:
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
was:
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}
The 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
> 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)
