Description:
root_test2_3306> show create table tt1\G
*************************** 1. row ***************************
Table: tt1
Create Table: CREATE TABLE `tt1` (
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
root_test2_3306> show create table tt2\G
*************************** 1. row ***************************
Table: tt2
Create Table: CREATE TABLE `tt2` (
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
root_test2_3306> select * from tt1;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
+------+------+
5 rows in set (0.00 sec)
root_test2_3306> select * from tt2;
Empty set (0.01 sec)
root_test2_3306>
------------------------------
-- The wrong result:
root_test2_3306> select 1 in (select a.c1 from (select c1,c2 from tt1 union all select c1,c2 from tt2) a where a.c2 > 1) r;
+------+
| r |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
------------------------------
-- The right result:
root_test2_3306> select 1 in (select a.c1 from (select c1,c2 from tt1 where c2>1 union all select c1,c2 from tt2 where c2>1) a ) r;
+------+
| r |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
------------------------------
How to repeat:
Repeat my steps above。
Description: root_test2_3306> show create table tt1\G *************************** 1. row *************************** Table: tt1 Create Table: CREATE TABLE `tt1` ( `c1` int DEFAULT NULL, `c2` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) root_test2_3306> show create table tt2\G *************************** 1. row *************************** Table: tt2 Create Table: CREATE TABLE `tt2` ( `c1` int DEFAULT NULL, `c2` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) root_test2_3306> select * from tt1; +------+------+ | c1 | c2 | +------+------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | | 5 | 1 | +------+------+ 5 rows in set (0.00 sec) root_test2_3306> select * from tt2; Empty set (0.01 sec) root_test2_3306> ------------------------------ -- The wrong result: root_test2_3306> select 1 in (select a.c1 from (select c1,c2 from tt1 union all select c1,c2 from tt2) a where a.c2 > 1) r; +------+ | r | +------+ | 1 | +------+ 1 row in set (0.01 sec) ------------------------------ -- The right result: root_test2_3306> select 1 in (select a.c1 from (select c1,c2 from tt1 where c2>1 union all select c1,c2 from tt2 where c2>1) a ) r; +------+ | r | +------+ | 0 | +------+ 1 row in set (0.00 sec) ------------------------------ How to repeat: Repeat my steps above。