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)