[CALCITE-6583] Case Statement reformulation in JdbcAdapter

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 1.37.0
• Component/s: core, jdbc-adapter
• Assignee: Unassigned
• Reporter: Corvin Kuebler
• Created: 16/Sep/24 11:22
• Updated: 19/Sep/24 01:11
• Resolved: 18/Sep/24 08:13

Description

Hey @all!

We encountered some strange behavior when working with the JdbcAdapter.
The following test in JdbcAdapterTests shows the issue:

@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");
  }

PlanSql:

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

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

Issue Links

• is related to: CALCITE-6480 OracleDialect does not support CASE WHEN returning boolean