Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.3.3
-
None
Description
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 |
|
+------+------+
|
Attachments
Issue Links
- is caused by
-
MDEV-12176 Transform [NOT] IN predicate with long list of values INTO [NOT] IN subquery.
- Closed
- relates to
-
MDEV-14835 Server crashes in strlen / Field_iterator_table::create_item when IN list reaches in_predicate_conversion_threshold
- Closed