revision-id: 502bc77f23715f84a049fe6c28a861e9af271016 (mariadb-10.4.11-496-g502bc77f237) parent(s): 4addd31531f722438b8b702c9cd00c28b61efce3 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-12-11 22:44:13 +0300 message: 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. --- mysql-test/main/range.result | 30 ------------------------------ mysql-test/main/range.test | 24 ------------------------ mysql-test/main/range_mrr_icp.result | 30 ------------------------------ 3 files changed, 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=