[
https://issues.apache.org/jira/browse/CALCITE-5132?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17532654#comment-17532654
]
Julian Hyde commented on CALCITE-5132:
--------------------------------------
I wrote a test case and it works for me:
{noformat}
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index 6800f6ea06..45daefeb2f 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -1065,6 +1065,32 @@ Expression 'DEPTNO' is not being grouped
!use scott
+select empno, deptno, (empno, deptno) in ((7521, null)) from "scott".emp;
++-------+--------+--------+
+| EMPNO | DEPTNO | EXPR$2 |
++-------+--------+--------+
+| 7369 | 20 | false |
+| 7499 | 30 | false |
+| 7521 | 30 | |
+| 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 |
++-------+--------+--------+
+(14 rows)
+
+!ok
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[7521:SMALLINT], expr#9=[=($t0,
$t8)], expr#10=[null:BOOLEAN], expr#11=[AND($t9, $t10)], EMPNO=[$t0],
DEPTNO=[$t7], EXPR$2=[$t11])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
# ORDER BY expression with SELECT DISTINCT
select distinct deptno, job
from "scott".emp
{noformat}
> 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)
