Case when statement dealing with null logic return a wrong result.

[yanghy233]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. schema.

create table `t2`
(
    `vkey` integer,
    `c16`  double,
    primary key (`vkey`)
);
insert into `t2` (`vkey`, `c16`)
values (4, 1.73);

2. sql statement.

select case when
(
    select (
               ((null) in
                (select ((case when ((`t2`.`c16`) <> (-3.12)) then (`t2`.`vkey`) else (`t2`.`vkey`) end
                            ) is null))
                   ) is not null)
    from `t2`
)
then 11
else 22
end as `result`;

2. What did you expect to see? (Required)

MySQL and TIDB have different execution results in the above case.

The correct result in MySQL 8:

mysql> select case when
    -> (
    ->     select (
    ->                ((null) in
    ->                 (select ((case when ((`t2`.`c16`) <> (-3.12)) then (`t2`.`vkey`) else (`t2`.`vkey`) end
    ->                             ) is null))
    ->                    ) is not null)
    ->     from `t2`
    -> )
    -> then 11
    -> else 22
    -> end as `result`;
+--------+
| result |
+--------+
|     22 |
+--------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

In TiDB v8.1.1:

MySQL [test]> select case when
    -> (
    ->     select (
    ->                ((null) in
    ->                 (select ((case when ((`t2`.`c16`) <> (-3.12)) then (`t2`.`vkey`) else (`t2`.`vkey`) end
    ->                             ) is null))
    ->                    ) is not null)
    ->     from `t2`
    -> )
    -> then 11
    -> else 22
    -> end as `result`;
+--------+
| result |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

tidb v8.1.1

[yanghy233]

/label affects-8.1

[yanghy233]

Also affects TiDB v8.4
/label affects-8.4