xiong duan created CALCITE-5132:
-----------------------------------
Summary: When the IN-list that include NUll value is converted to
Values as a SCALA subquery will return wrong result
Key: CALCITE-5132
URL: https://issues.apache.org/jira/browse/CALCITE-5132
Project: Calcite
Issue Type: Bug
Affects Versions: 1.30.0
Reporter: xiong duan
The SQL：
{code:java}
select empno, deptno, (empno, deptno) in ((7521, null)) from "scott".emp;{code}
{noformat}
+-------+--------+--------+
| EMPNO | DEPTNO | EXPR$2 |
+-------+--------+--------+
| 7369 | 20 | |
| 7499 | 30 | |
| 7521 | 30 | |
| 7566 | 20 | |
| 7654 | 30 | |
| 7698 | 30 | |
| 7782 | 10 | |
| 7788 | 20 | |
| 7839 | 10 | |
| 7844 | 30 | |
| 7876 | 20 | |
| 7900 | 30 | |
| 7902 | 20 | |
| 7934 | 10 | |
+-------+--------+--------+
(14 rows)
{noformat}
In PG、MySQL, will return :
{noformat}
+-----+------+--------+
|empno|deptno|?column?|
+-----+------+--------+
|7369 |20 |false |
|7499 |30 |false |
|7521 |30 |NULL |
|7566 |20 |false |
|7654 |30 |false |
|7698 |30 |false |
|7782 |10 |false |
|7788 |20 |false |
|7839 |10 |false |
|7844 |30 |false |
|7876 |20 |false |
|7900 |30 |false |
|7902 |20 |false |
|7934 |10 |false |
+-----+------+--------+{noformat}
Same SQL include:
{code:java}
select empno, deptno, (empno, deptno) in (values(7521, null)) from "scott".emp;
{code}
{code:java}
select empno, deptno, (empno, deptno) in (select deptno,deptno from
"scott".dept) from "scott".emp;{code}
This is because The Calcite evaluates the (7521, null) eq (3456, null) is
UNKNOW, But it should be FALSE;
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
