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

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

    [ 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)