[jira] [Created] (CALCITE-5282) JdbcValues should add CAST on NULL values

["Ulrich Kramer (Jira)" <ji...@apache.org>]

Ulrich Kramer created CALCITE-5282:
--------------------------------------

             Summary: JdbcValues should add CAST on NULL values
                 Key: CALCITE-5282
                 URL: https://issues.apache.org/jira/browse/CALCITE-5282
             Project: Calcite
          Issue Type: Bug
         Environment: Calcite 1.13.1 on Mac
            Reporter: Ulrich Kramer


The following unit test in {{JdbcAdapterTest.java}} is working fine


{code:java}
  @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();
  }
{code}

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.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)