[CALCITE-5282] JdbcValues should add CAST on NULL values

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved

Environment

Calcite 1.13.1 on Mac

• Assignee: Unassigned
• Reporter: Ulrich Kramer
• Created: 12/Sep/22 09:07
• Updated: 21/Sep/22 14:46

Description

The following unit test in JdbcAdapterTest.java is working fine

  @Test void testNullValuesPlan() {
    final String sql = "select empno, ename, e.deptno, dname\n"
        + "from scott.emp e left outer join (select * from scott.dept where 0 = 1) d\n"
        + "on e.deptno = d.deptno";
    final String explain = "PLAN=JdbcToEnumerableConverter\n" +
        "  JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], DNAME=[$4])\n" +
        "    JdbcJoin(condition=[=($2, $3)], joinType=[left])\n" +
        "      JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n" +
        "        JdbcTableScan(table=[[SCOTT, EMP]])\n" +
        "      JdbcValues(tuples=[[]])\n\n";
    final String jdbcSql = "SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\", \"t\".\"DEPTNO\", \"t0\".\"DNAME\"\n" +
        "FROM (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n" +
        "FROM \"SCOTT\".\"EMP\") AS \"t\"\n" +
        "LEFT JOIN (SELECT *\n" +
        "FROM (VALUES (NULL, NULL)) AS \"t\" (\"DEPTNO\", \"DNAME\")\n" +
        "WHERE 1 = 0) AS \"t0\" ON \"t\".\"DEPTNO\" = \"t0\".\"DEPTNO\"";
    CalciteAssert.model(JdbcTest.SCOTT_MODEL)
        .query(sql)
        .explainContains(explain)
        .runs();
  }

The problem is that JdbcValues is loosing the type information for each NULL column
and postgres complains about that. Inside the join condition t.DEPTNO = to.DEPTNO. postgres doesn't know the type of t.DEPTNO, assumes it's of type TEXT and raises an error like ERROR: operator does not exist: text = integer, Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Would it be possible to add a CAST in case of NULL values in JdbcValues.
Changing VALUES (NULL, NULL) to VALUES (CAST(NULL AS ...), CAST(NULL AS ...)) in the resulting SQL statement.

If it is appreciated, we could provide a PR.

If you are asking yourself, why we are doing something strange like WHERE 1 = 0: We are applying row level access policies as WHERE condition. In this case the user has no access to the table at all.

Issue Links

• is related to: CALCITE-5294 Prune the null-generating side of an outer join if it is empty
• relates to: HIVE-26524 Use Calcite to remove sections of a query plan known never produces rows