revision-id: dcc7f93965f7fb534f29c5c682b2abf22e808fe2 (mariadb-10.4.11-497-gdcc7f93965f) parent(s): 502bc77f23715f84a049fe6c28a861e9af271016 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-12-11 22:45:54 +0300 message: MDEV-21958: Query having many NOT-IN clauses running forever, part 3 Add the new file --- mysql-test/main/range_notembedded.result | 31 ++++++++++++++++++++++++++++++ mysql-test/main/range_notembedded.test | 33 ++++++++++++++++++++++++++++++++ 2 files changed, 64 insertions(+) diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result new file mode 100644 index 00000000000..7084e0ca7a0 --- /dev/null +++ b/mysql-test/main/range_notembedded.result @@ -0,0 +1,31 @@ +drop table if exists t1,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; 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;