TiDB and MySQL behave differently when using the `IN` operator with `UNION` results of mixed data types

[r33s3n6]

1. Minimal reproduce step (Required)

select 'abc' in ( select 0  ) as c1;
select 'abc' in ( select 0 union all ( select cast(null as char)) ) as c2;
select 'abc' in ( select 0 union all ( select 1 where false) ) as c3;
select 'abc' in ( select 0 union all ( select '1' where false) ) as c4;

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

mysql> select 'abc' in ( select 0  ) as c1;
+----+
| c1 |
+----+
|  1 |
+----+
1 row in set, 1 warning (0.00 sec)

mysql> select 'abc' in ( select 0 union all ( select cast(null as char)) ) as c2;
+------+
| c2   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select 'abc' in ( select 0 union all ( select 1 where false) ) as c3;
+------+
| c3   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select 'abc' in ( select 0 union all ( select '1' where false) ) as c4;
+------+
| c4   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> select 'abc' in ( select 0  ) as c1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set, 2 warnings (0.00 sec)

mysql> select 'abc' in ( select 0 union all ( select cast(null as char)) ) as c2;
+------+
| c2   |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

mysql> select 'abc' in ( select 0 union all ( select 1 where false) ) as c3;
+------+
| c3   |
+------+
|    1 |
+------+
1 row in set, 2 warnings (0.00 sec)

mysql> select 'abc' in ( select 0 union all ( select '1' where false) ) as c4;
+------+
| c4   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v8.4.0-alpha-370-gf773b6eeb4
Edition: Community
Git Commit Hash: f773b6eeb4593a3e2c998c265f491a016570a426
Git Branch: HEAD
UTC Build Time: 2024-10-11 02:08:09
GoVersion: go1.23.2
Race Enabled: false
Check Table Before Drop: false
Store: tikv

about us

We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the above-mentioned vulnerabilities in TiDB that may lead to database logic error.

[windtalker]

The root cause is

1. The return type of ( select 0 ) is integer, so select 'abc' in ( select 0 ) as c1; actually means select 'abc' in (0), it will convert abc and 0 to double before compare, so the query returns 1(abc is convert to 0)
2. The return type of ( select 0 union all ( select cast(null as char)) ) is varchar, so the query actually means select 'abc' in ('0', null), so it returns NULL
3. the return type of ( select 0 union all ( select 1 where false) ) is integer, so it is the same as query 1
4. the return type of ( select 0 union all ( select '1' where false) ) is varchar, so it is the same as query 2, but no null literal, so it returns 0.

This issue is actually the same as https://github.com/pingcap/tidb/issues/56642. The TiDB's result is as expected, but unfortunately, different from MySQL's result.