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.

1 files changed, 30 insertions, 0 deletions

diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index 826ac621064..ebefc21a58d 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -3238,6 +3238,36 @@ analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	SIMPLE	t1	index	a	a	5	NULL	2000	2000.00	10.05	60.05	Using where; Using index
 drop table t1,ten,t2;
+#
+# MDEV-21958: Query having many NOT-IN clauses running forever
+#
+create table t2 (
+pk int primary key,
+key1 int,
+col1 int,
+key (key1, pk)
+);
+insert into t2 (pk, key1) values (1,1),(2,2),(3,3),(4,4),(5,5);
+set @tmp_21958=@@optimizer_trace;
+set optimizer_trace=1;
+explain select * from t2 where key1 in (1,2,3) and pk not in (1,2,3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	PRIMARY,key1	NULL	NULL	NULL	5	Using where
+# This should show only ranges in form "(1) <= (key1) <= (1)"
+#  ranges over "pk" should not be constructed.
+select json_detailed(JSON_EXTRACT(trace, '$**.ranges'))
+from information_schema.optimizer_trace;
+json_detailed(JSON_EXTRACT(trace, '$**.ranges'))
+[
+    
+    [
+        "(1) <= (key1) <= (1)",
+        "(2) <= (key1) <= (2)",
+        "(3) <= (key1) <= (3)"
+    ]
+]
+set optimizer_trace=@tmp_21958;
+drop table t2;
 # End of 10.4 tests
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
 set global innodb_stats_persistent_sample_pages=