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

Move the testcase into a separate file: embedded server
doesn't have optimizer trace.

3 files changed, 0 insertions, 84 deletions

diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 132ca019a61..c10ddf9d9fd 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -3241,36 +3241,6 @@ 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=
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index fab6c8530c2..65f580698c5 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2207,30 +2207,6 @@ let $a= `select group_concat(a) from t2`;
 eval analyze SELECT * FROM t1 where a in ($a);
 drop table t1,ten,t2;
 
---echo #
---echo # MDEV-21958: Query having many NOT-IN clauses running forever
---echo #
-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);
-
---echo # This should show only ranges in form "(1) <= (key1) <= (1)"
---echo #  ranges over "pk" should not be constructed.
-select json_detailed(JSON_EXTRACT(trace, '$**.ranges'))
-from information_schema.optimizer_trace;
-set optimizer_trace=@tmp_21958;
-
-drop table t2;
-
 --echo # End of 10.4 tests
 
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index ebefc21a58d..826ac621064 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -3238,36 +3238,6 @@ 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=