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

Add the new file

1 files changed, 33 insertions, 0 deletions

diff --git a/mysql-test/main/range_notembedded.test b/mysql-test/main/range_notembedded.test
new file mode 100644
index 00000000000..4dc49429ff1
--- /dev/null
+++ b/mysql-test/main/range_notembedded.test
@@ -0,0 +1,33 @@
+#
+# Range tests without embedded server. 
+#   The first reason to have them is that embedded server doesn't have
+#   optimizer trace.
+#
+--source include/not_embedded.inc
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+--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;