xiong duan created CALCITE-7076:
-----------------------------------
Summary: 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
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)
