|
If the system variable @@in_subquery_conversion_threshold is set to a small enough number then queries with NOT IN predicate in most cases return wrong result sets.
The following test case demonstrate this:
create table t1 (a int, b int);
|
insert into t1 values
|
(1,2), (4,6), (9,7),(1,1), (2,5), (7,8);
|
|
select * from t1
|
where (a,b) not in ((1,2),(8,9), (5,1));
|
|
set @@in_subquery_conversion_threshold= 2;
|
select * from t1
|
where (a,b) not in ((1,2),(8,9), (5,1));
|
When running this test we have:
MariaDB [test]>
|
MariaDB [test]> select * from t1
|
-> where (a,b) not in ((1,2),(8,9), (5,1));
|
+------+------+
|
| a | b |
|
+------+------+
|
| 4 | 6 |
|
| 9 | 7 |
|
| 1 | 1 |
|
| 2 | 5 |
|
| 7 | 8 |
|
+------+------+
|
|
MariaDB [test]> set @@in_subquery_conversion_threshold= 2;
|
|
MariaDB [test]> select * from t1
|
-> where (a,b) not in ((1,2),(8,9), (5,1));
|
+------+------+
|
| a | b |
|
+------+------+
|
| 1 | 2 |
|
+------+------+
|
|