[
https://issues.apache.org/jira/browse/CALCITE-5132?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-5132:
---------------------------------
Description:
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}
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 Calcite evaluates "(7521, null) = (3456, null)" as UNKNOWN, but
it should be FALSE.
was:
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;
> 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
> Priority: Major
>
> 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}
> 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 Calcite evaluates "(7521, null) = (3456, null)" as UNKNOWN,
> but it should be FALSE.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
