[
https://issues.apache.org/jira/browse/CALCITE-5132?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17533067#comment-17533067
]
Julian Hyde commented on CALCITE-5132:
--------------------------------------
[~nobigo], Yes, when I add that command to {{misc.iq}} I can reproduce the
problem.
> When the IN-list including a NULL value is converted to Values as a SCALAR
> 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
> Priority: Major
>
> 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)
