MDEV-21958: Query having many NOT-IN clauses running forever

Basic variant of the fix: do not consider conditions in form

  unique_key NOT IN (c1,c2...)

to be sargable. If there are only a few constants, the condition
is not selective. If there are a lot constants, the overhead of
processing such a huge range list is not worth it.

(Backport to 10.2)

2 files changed, 36 insertions, 0 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 9ea0dc12a0b..49bce9a316d 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -3158,6 +3158,21 @@ pk	a	b
 1	5	50
 65	5	50
 drop table t1;
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (
+pk int primary key,
+key1 int,
+col1 varchar(255),
+key (key1, pk)
+);
+insert into t2 (pk, key1)
+select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C;
+# This must use ALL, not range:
+explain select * from t2 force index (primary) where pk not in (1,2,3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1000	Using where
+drop table t1,t2;
 #
 # End of 10.2 tests
 #
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 264f7c784ce..4088aa2990e 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -2183,6 +2183,27 @@ eval $q4;
 
 drop table t1;
 
+#
+# MDEV-21958: Query having many NOT-IN clauses running forever (testcase 2)
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t2 (
+  pk int primary key,
+  key1 int,
+  col1 varchar(255),
+  key (key1, pk)
+);
+
+insert into t2 (pk, key1)
+select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C;
+
+--echo # This must use ALL, not range:
+explain select * from t2 force index (primary) where pk not in (1,2,3);
+
+drop table t1,t2;
+
 --echo #
 --echo # End of 10.2 tests
 --echo #