From c945233a192d559695b83a252b61168e7611ea03 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Tue, 2 Sep 2014 18:54:29 +0400 Subject: MDEV-6657: Poor plan choice for ORDER BY key DESC optimization... The problem was caused by the following scenario: - range optimizer picks an index IDX1 which doesn't match the ORDER BY ... LIMIT clause. - test_if_skip_sort_order() decides to switch to index IDX2 which matches the ORDER BY ... LIMIT. - it runs SQL_SELECT::test_quick_select() for the second time to produce an quick select for IDX2. - However, test_quick_select() would figure that full index scan on IDX1 is still cheaper (its calculations ignore the LIMIT n). Fixed this by - passing force_quick_range=true to test_quick_select() - in test_quick_select, don't consider full index scans if the mentioned parameter is true. Numerous changes in .result files are caused by test_quick_select() being run after "early/late NULLs filtering" feature has injected NOT NULL condition. --- mysql-test/r/myisam_explain_non_select_all.result | 4 +- mysql-test/r/order_by.result | 6 +-- mysql-test/r/show_explain.result | 4 +- mysql-test/r/subselect.result | 4 +- mysql-test/r/subselect_mat.result | 2 +- mysql-test/r/subselect_no_exists_to_in.result | 4 +- mysql-test/r/subselect_no_mat.result | 4 +- mysql-test/r/subselect_no_opts.result | 4 +- mysql-test/r/subselect_no_scache.result | 4 +- mysql-test/r/subselect_no_semijoin.result | 4 +- mysql-test/r/subselect_sj_mat.result | 2 +- sql/opt_range.cc | 2 +- sql/sql_select.cc | 8 +++- .../mysql-test/tokudb_mariadb/r/mdev6657.result | 47 +++++++++++++++++++ .../mysql-test/tokudb_mariadb/t/mdev6657.test | 53 ++++++++++++++++++++++ 15 files changed, 129 insertions(+), 23 deletions(-) create mode 100644 storage/tokudb/mysql-test/tokudb_mariadb/r/mdev6657.result create mode 100644 storage/tokudb/mysql-test/tokudb_mariadb/t/mdev6657.test diff --git a/mysql-test/r/myisam_explain_non_select_all.result b/mysql-test/r/myisam_explain_non_select_all.result index 285a1ca6786..a9eeee8548c 100644 --- a/mysql-test/r/myisam_explain_non_select_all.result +++ b/mysql-test/r/myisam_explain_non_select_all.result @@ -674,14 +674,14 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 0) order by `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value # Status of "equivalent" SELECT query execution: Variable_name Value -Handler_read_first 1 +Handler_read_key 1 Handler_read_next 3 # Status of testing query execution: Variable_name Value diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 294142737d9..aba8d6c201c 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -297,7 +297,7 @@ create table t1 (a int not null, b int, c varchar(10), key (a, b, c)); insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b'); explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 22 NULL 11 Using where; Using index +1 SIMPLE t1 range a a 22 NULL 2 Using where; Using index select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc; a b c 1 NULL b @@ -2569,7 +2569,7 @@ SELECT * FROM t1 r JOIN t1 s ON r.a = s.a WHERE s.a IN (2,9) OR s.a < 100 AND s.a != 0 ORDER BY 1 LIMIT 10; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE r index PRIMARY PRIMARY 4 NULL 10 100.00 Using where; Using index +1 SIMPLE r range PRIMARY PRIMARY 4 NULL 12 100.00 Using where; Using index 1 SIMPLE s eq_ref PRIMARY PRIMARY 4 test.r.a 1 100.00 Using index Warnings: Note 1003 select `test`.`r`.`a` AS `a`,`test`.`s`.`a` AS `a` from `test`.`t1` `r` join `test`.`t1` `s` where ((`test`.`s`.`a` = `test`.`r`.`a`) and ((`test`.`r`.`a` in (2,9)) or ((`test`.`r`.`a` < 100) and (`test`.`r`.`a` <> 0)))) order by 1 limit 10 @@ -2600,7 +2600,7 @@ CREATE TABLE t1 (a INT,KEY (a)); INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 5 NULL 10 Using where; Using index; Using filesort +1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; a 1 10 1 diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result index 3695384bac4..8aefb552d57 100644 --- a/mysql-test/r/show_explain.result +++ b/mysql-test/r/show_explain.result @@ -1036,7 +1036,7 @@ explain SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system NULL NULL NULL NULL 1 -1 SIMPLE t1 index b b 6 NULL 107 Using where; Using index +1 SIMPLE t1 range b b 6 NULL 107 Using where; Using index 1 SIMPLE t3 ref PRIMARY PRIMARY 5 test.t1.b 1 Using index set @show_explain_probe_select_id=1; set debug_dbug='+d,show_explain_probe_do_select'; @@ -1044,7 +1044,7 @@ SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, fie show explain for $thr2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system NULL NULL NULL NULL 1 Using filesort -1 SIMPLE t1 index b b 6 NULL 107 Using where; Using index +1 SIMPLE t1 range b b 6 NULL 107 Using where; Using index 1 SIMPLE t3 ref PRIMARY PRIMARY 5 test.t1.b 1 Using index Warnings: Note 1003 SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 5678a455234..55184f42c97 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6974,7 +6974,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # @@ -7008,7 +7008,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 6cc627ad16c..30b020cbcf6 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -103,7 +103,7 @@ explain extended select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index NULL # 18 # 3 100.00 # -2 MATERIALIZED t2i index it2i1,it2i3 # 9 # 5 100.00 # +2 MATERIALIZED t2i range it2i1,it2i3 # 9 # 5 100.00 # Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <`test`.`t1i`.`a1`>((`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = ``.`max(b1)`)))))) select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 5434bb920e3..58ee06d754d 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -6974,7 +6974,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # @@ -7008,7 +7008,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 3325889e1e0..53307efdb89 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6968,7 +6968,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # @@ -7001,7 +7001,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 01af3ea4b16..c228ae46347 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6965,7 +6965,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # @@ -6999,7 +6999,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 16ebf7a4199..7d1dde6498e 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6980,7 +6980,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # @@ -7014,7 +7014,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index d98f7ad67de..c850644fbaa 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6965,7 +6965,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # @@ -6999,7 +6999,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index +2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 95dfc34777b..7417ab56ff8 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -107,7 +107,7 @@ select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index it1i1,it1i3 # 18 # 3 100.00 # 1 PRIMARY eq_ref distinct_key # 8 # 1 100.00 # -2 MATERIALIZED t2i index it2i1,it2i3 # 9 # 5 100.00 # +2 MATERIALIZED t2i range it2i1,it2i3 # 9 # 5 100.00 # Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (``.`max(b1)` = `test`.`t1i`.`a1`) select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 2616e044025..7a584eed742 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3056,7 +3056,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, param.alloced_sel_args= 0; /* Calculate cost of full index read for the shortest covering index */ - if (!head->covering_keys.is_clear_all()) + if (!force_quick_range && !head->covering_keys.is_clear_all()) { int key_for_use= find_shortest_key(head, &head->covering_keys); double key_read_time= head->file->keyread_time(key_for_use, 1, records) + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 05c88a5f534..860f0cd346c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -20168,7 +20168,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, (tab->join->select_options & OPTION_FOUND_ROWS) ? HA_POS_ERROR : - tab->join->unit->select_limit_cnt,0, + tab->join->unit->select_limit_cnt,TRUE, TRUE) <= 0; if (res) { @@ -20286,6 +20286,12 @@ check_reverse_order: */ if (!table->covering_keys.is_set(best_key)) table->disable_keyread(); + else + { + if (!table->key_read) + table->enable_keyread(); + } + if (!quick_created) { if (select) // Throw any existing quick select diff --git a/storage/tokudb/mysql-test/tokudb_mariadb/r/mdev6657.result b/storage/tokudb/mysql-test/tokudb_mariadb/r/mdev6657.result new file mode 100644 index 00000000000..a09e2f2010e --- /dev/null +++ b/storage/tokudb/mysql-test/tokudb_mariadb/r/mdev6657.result @@ -0,0 +1,47 @@ +drop table if exists t1,t2,t3; +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +CREATE TABLE t3 ( +col1 int(10) unsigned NOT NULL DEFAULT '0', +col2 mediumint(8) unsigned NOT NULL DEFAULT '0', +col3 smallint(5) NOT NULL DEFAULT '1', +filler varchar(255) DEFAULT NULL, +PRIMARY KEY (col1,col2,col3), +KEY key1 (col1,col2) USING BTREE +) ENGINE=TokuDB DEFAULT CHARSET=latin1 PACK_KEYS=1 COMPRESSION=TOKUDB_LZMA; +insert into t3 select 1300000000+a, 12345, 7890, 'data' from t2; +insert into t3 select 1400000000+a, 12345, 7890, 'data' from t2; +insert into t3 select 1410799999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1410899999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1410999999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411099999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411199999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411299999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411399999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411499999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411599999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411699999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411899999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411999999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1412099999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1412199999+a, 12345, 7890, 'data' from t2; +# The following must use range(PRIMARY): +explain +select col1,col2,col3 +from t3 +where col1 <= 1410799999 +order by col1 desc,col2 desc,col3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range PRIMARY,key1 PRIMARY 4 NULL 2001 Using where; Using index +# The same query but the constant is bigger. +# The query should use range(PRIMARY), not full index scan: +explain +select col1,col2,col3 +from t3 +where col1 <= 1412199999 +order by col1 desc, col2 desc, col3 desc limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range PRIMARY,key1 PRIMARY 4 NULL 15001 Using where; Using index +drop table t1,t2,t3; diff --git a/storage/tokudb/mysql-test/tokudb_mariadb/t/mdev6657.test b/storage/tokudb/mysql-test/tokudb_mariadb/t/mdev6657.test new file mode 100644 index 00000000000..636dee1cde8 --- /dev/null +++ b/storage/tokudb/mysql-test/tokudb_mariadb/t/mdev6657.test @@ -0,0 +1,53 @@ +--disable_warnings +drop table if exists t1,t2,t3; +--enable_warnings + +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; + +CREATE TABLE t3 ( + col1 int(10) unsigned NOT NULL DEFAULT '0', + col2 mediumint(8) unsigned NOT NULL DEFAULT '0', + col3 smallint(5) NOT NULL DEFAULT '1', + filler varchar(255) DEFAULT NULL, + PRIMARY KEY (col1,col2,col3), + KEY key1 (col1,col2) USING BTREE +) ENGINE=TokuDB DEFAULT CHARSET=latin1 PACK_KEYS=1 COMPRESSION=TOKUDB_LZMA; + +insert into t3 select 1300000000+a, 12345, 7890, 'data' from t2; +insert into t3 select 1400000000+a, 12345, 7890, 'data' from t2; +insert into t3 select 1410799999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1410899999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1410999999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411099999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411199999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411299999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411399999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411499999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411599999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411699999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411899999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1411999999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1412099999+a, 12345, 7890, 'data' from t2; +insert into t3 select 1412199999+a, 12345, 7890, 'data' from t2; + +--echo # The following must use range(PRIMARY): +explain +select col1,col2,col3 +from t3 +where col1 <= 1410799999 +order by col1 desc,col2 desc,col3 desc limit 1; + +--echo # The same query but the constant is bigger. +--echo # The query should use range(PRIMARY), not full index scan: +explain +select col1,col2,col3 +from t3 +where col1 <= 1412199999 +order by col1 desc, col2 desc, col3 desc limit 1; + +drop table t1,t2,t3; + -- cgit v1.2.1