[
https://issues.apache.org/jira/browse/CALCITE-5282?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17603425#comment-17603425
]
Stamatis Zampetakis commented on CALCITE-5282:
----------------------------------------------
In a similar situation in HIVE-26524, when there is a left/right join with
empty values it seems possible to remove the join and values branch altogether.
I am guessing that if the empty values disappears from the plan maybe the
problem reported here (for Postgres and other DBMS) may also go away.
[~kramerul] please have a look in the PR for HIVE-26524 and share your
thoughts; at some point there is also some code to introduce typed nulls as you
propose here.
> 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)
