[
https://issues.apache.org/jira/browse/CALCITE-7076?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17986375#comment-17986375
]
xiong duan commented on CALCITE-7076:
-------------------------------------
When we execute `a = b`, where a is not nullable but b is nullable, during the
verification stage, do we need to cast a to be nullable?
Or when we obtain the comparator, we should take into account the
rightKeyPhysType. Then, we can use the expression like this:
{code:java}
generateMergeJoinComparator(rightKeyPhysType, collation);{code}
> IN-list that includes NULL converted to Values throws exception when When
> there is a non-null column being compared with a NULL value
> -------------------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7076
> URL: https://issues.apache.org/jira/browse/CALCITE-7076
> Project: Calcite
> Issue Type: Bug
> Reporter: xiong duan
> Assignee: xiong duan
> Priority: Major
>
> In dummy.iq:
> {code:java}
> !use scott
> !set insubquerythreshold 0
> select * from "scott".emp where (empno, deptno) not in ((1, 2), (3, null),
> (cast(null as integer), cast(null as integer))); {code}
> Throws exception:
> {code:java}
> java.sql.SQLException: Error while executing SQL "select * from "scott".emp
> where (empno, deptno) not in ((1, 2), (3, null), (cast(null as integer),
> cast(null as integer)))": Cannot convert null to int {code}
> This is because empno is not nullable.
> In EnumerableMergeJoin, When generate the comparator :
> {code:java}
> final RelCollation collation = RelCollations.of(fieldCollations);
> final Expression comparator =
> leftKeyPhysType.generateMergeJoinComparator(collation); {code}
> Whether a comparison involving NULL values is included entirely depends on
> the Left Key. If the Left Key cannot be NULL, then the compare used by the
> generated codegen will be applied. Assuming that the left key is not null,
> but the Right Key may be NULL, an exception will be thrown.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
