[
https://issues.apache.org/jira/browse/CALCITE-5132?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen resolved CALCITE-5132.
--------------------------------
Fix Version/s: 1.42.0
Assignee: Zhen Chen
Resolution: Fixed
Fixed in
[{{b04f744}}|https://github.com/apache/calcite/commit/b04f744f7fbc6d5a4f12b2bb591be000265a88b2]
Thanks for the review [~mbudiu]
> Scalar IN subquery returns UNKNOWN instead of FALSE when key is partially NULL
> ------------------------------------------------------------------------------
>
> 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
> Assignee: Zhen Chen
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
> Scalar IN subquery returns UNKNOWN instead of FALSE when key is partially
> NULL. The SQL
> {code:java}
> select empno, deptno, (empno, deptno) in ((7521, null)) from
> "scott".emp;{code}
> returns UNKNOWN for every row:
> {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}
> But Postgres and MySQL return UNKNOWN for only one row, FALSE for the others:
> {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}
> The following queries are similar, and also return an incorrect result:
> {code:java}
> select empno, deptno, (empno, deptno)
> in (values (7521, null))
> from "scott".emp;
> select empno, deptno,
> (empno, deptno) in (select deptno, deptno from "scott".dept)
> from "scott".emp;
> {code}
> This is because Calcite evaluates "(7521, null) = (3456, null)" as UNKNOWN,
> but it should be FALSE.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
