[
https://issues.apache.org/jira/browse/CALCITE-5282?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17603541#comment-17603541
]
Stamatis Zampetakis commented on CALCITE-5282:
----------------------------------------------
As soon as the PR gets merged in Hive we are planning to create respective
CALCITE jiras to port the issues in calcite. Hopefully they should be present
in the next Calcite release.
> 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
> Priority: Major
>
> 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)
