diff options
author | Sergei Petrunia <psergey@askmonty.org> | 2014-10-06 12:21:53 +0400 |
---|---|---|
committer | Sergei Petrunia <psergey@askmonty.org> | 2014-10-06 12:21:53 +0400 |
commit | 1a7d17311c8325a072e5c912a2eb3fffb95aa97d (patch) | |
tree | 62e8b89bc4b4e772ec90e546cbe9e52d3eddf6ef | |
parent | 61d8b4a29bd6295b9db153a6ebb451346cd5bc64 (diff) | |
parent | 605b48d3e311e783ff60644dd468bbabb9a4a15c (diff) | |
download | mariadb-git-1a7d17311c8325a072e5c912a2eb3fffb95aa97d.tar.gz |
Merge ../10.1-orderby-fixes into 10.1
-rw-r--r-- | mysql-test/r/myisam_explain_non_select_all.result | 4 | ||||
-rw-r--r-- | mysql-test/r/order_by.result | 6 | ||||
-rw-r--r-- | mysql-test/r/order_by_optimizer_innodb.result | 98 | ||||
-rw-r--r-- | mysql-test/r/show_explain.result | 4 | ||||
-rw-r--r-- | mysql-test/r/subselect.result | 4 | ||||
-rw-r--r-- | mysql-test/r/subselect_mat.result | 2 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_exists_to_in.result | 4 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_mat.result | 4 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_opts.result | 4 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_scache.result | 4 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_semijoin.result | 4 | ||||
-rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 2 | ||||
-rw-r--r-- | mysql-test/t/order_by_optimizer_innodb.test | 98 | ||||
-rw-r--r-- | sql/field.h | 6 | ||||
-rw-r--r-- | sql/opt_range.cc | 2 | ||||
-rw-r--r-- | sql/sql_select.cc | 217 | ||||
-rw-r--r-- | storage/tokudb/mysql-test/tokudb_mariadb/r/mdev6657.result | 47 | ||||
-rw-r--r-- | storage/tokudb/mysql-test/tokudb_mariadb/t/mdev6657.test | 53 |
18 files changed, 444 insertions, 119 deletions
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 28a276d16c1..d5b25534de0 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/order_by_optimizer_innodb.result b/mysql-test/r/order_by_optimizer_innodb.result new file mode 100644 index 00000000000..f3167db4b9a --- /dev/null +++ b/mysql-test/r/order_by_optimizer_innodb.result @@ -0,0 +1,98 @@ +drop table if exists t0,t1,t2,t3; +# +# MDEV-6402: Optimizer doesn't choose best execution plan when composite key is used +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +CREATE TABLE t2 ( +pk1 int(11) NOT NULL, +pk2 int(11) NOT NULL, +fd5 bigint(20) DEFAULT NULL, +filler1 char(200), +filler2 char(200), +PRIMARY KEY (pk1,pk2), +UNIQUE KEY ux_pk1_fd5 (pk1,fd5) +) ENGINE=InnoDB; +insert into t2 +select +round(log(2,t1.a+1)), +t1.a, +t1.a, +REPEAT('filler-data-', 10), +REPEAT('filler-data-', 10) +from +t1; +select pk1, count(*) from t2 group by pk1; +pk1 count(*) +0 1 +1 1 +2 3 +3 6 +4 11 +5 23 +6 45 +7 91 +8 181 +9 362 +10 276 +# The following should use range(ux_pk1_fd5), two key parts (key_len=5+8=13) +EXPLAIN SELECT * FROM t2 USE INDEX(ux_pk1_fd5) WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range ux_pk1_fd5 ux_pk1_fd5 13 NULL 137 Using where +# This also must use range, not ref. key_len must be 13 +EXPLAIN SELECT * FROM t2 WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY,ux_pk1_fd5 ux_pk1_fd5 13 NULL 137 Using where +drop table t0,t1, t2; +# +# MDEV-6814: Server crashes in calculate_key_len on query with ORDER BY +# +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f2),KEY(f2,f1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,5,0),(2,6,0); +SELECT * FROM t1 WHERE f1 < 3 AND f2 IS NULL ORDER BY f1; +f1 f2 f3 +DROP TABLE t1; +# +# MDEV-6796: Unable to skip filesort when using implicit extended key +# +CREATE TABLE t1 ( +pk1 int(11) NOT NULL, +pk2 varchar(64) NOT NULL, +col1 varchar(16) DEFAULT NULL, +PRIMARY KEY (pk1,pk2), +KEY key1 (pk1,col1) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE t2 ( +pk1 int(11) NOT NULL, +pk2 varchar(64) NOT NULL, +col1 varchar(16) DEFAULT NULL, +PRIMARY KEY (pk1,pk2), +KEY key1 (pk1,col1,pk2) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +INSERT INTO `t1` VALUES +(12321321,'a8f5f167f44f4964e6c998dee827110c','video'), +(12321321,'d77a17a3659ffa60c54e0ea17b6c6d16','video'), +(12321321,'wwafdsafdsafads','video'), +(12321321,'696aa249f0738e8181957dd57c2d7d0b','video-2014-09-23'), +(12321321,'802f9f29584b486f356693e3aa4ef0af','video=sdsd'), +(12321321,'2f94543ff74aab82e9a058b4e8316d75','video=sdsdsds'), +(12321321,'c1316b9df0d203fd1b9035308de52a0a','video=sdsdsdsdsd'); +insert into t2 select * from t1; +# this must not use filesort: +explain SELECT pk2 +FROM t1 USE INDEX(key1) +WHERE pk1 = 123 +AND col1 = 'video' +ORDER BY pk2 DESC LIMIT 21; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref key1 key1 55 const,const 1 Using where; Using index +# this must not use filesort, either: +explain SELECT pk2 +FROM t2 USE INDEX(key1) +WHERE pk1 = 123 AND col1 = 'video' +ORDER BY pk2 DESC LIMIT 21; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref key1 key1 55 const,const 1 Using where; Using index +drop table t1, t2; 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 <expr_cache><`test`.`t1i`.`a1`>(<in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery2>`.`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 <subquery2> 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 <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`<subquery2>`.`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/mysql-test/t/order_by_optimizer_innodb.test b/mysql-test/t/order_by_optimizer_innodb.test new file mode 100644 index 00000000000..90430d11549 --- /dev/null +++ b/mysql-test/t/order_by_optimizer_innodb.test @@ -0,0 +1,98 @@ +--source include/have_innodb.inc + +--disable_warnings +drop table if exists t0,t1,t2,t3; +--enable_warnings + +--echo # +--echo # MDEV-6402: Optimizer doesn't choose best execution plan when composite key is used +--echo # +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; + +CREATE TABLE t2 ( + pk1 int(11) NOT NULL, + pk2 int(11) NOT NULL, + fd5 bigint(20) DEFAULT NULL, + filler1 char(200), + filler2 char(200), + PRIMARY KEY (pk1,pk2), + UNIQUE KEY ux_pk1_fd5 (pk1,fd5) + ) ENGINE=InnoDB; + +insert into t2 +select + round(log(2,t1.a+1)), + t1.a, + t1.a, + REPEAT('filler-data-', 10), + REPEAT('filler-data-', 10) +from + t1; + +select pk1, count(*) from t2 group by pk1; + +--echo # The following should use range(ux_pk1_fd5), two key parts (key_len=5+8=13) +EXPLAIN SELECT * FROM t2 USE INDEX(ux_pk1_fd5) WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10; +--echo # This also must use range, not ref. key_len must be 13 +EXPLAIN SELECT * FROM t2 WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10; + +drop table t0,t1, t2; + +--echo # +--echo # MDEV-6814: Server crashes in calculate_key_len on query with ORDER BY +--echo # +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f2),KEY(f2,f1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,5,0),(2,6,0); +SELECT * FROM t1 WHERE f1 < 3 AND f2 IS NULL ORDER BY f1; +DROP TABLE t1; + +--echo # +--echo # MDEV-6796: Unable to skip filesort when using implicit extended key +--echo # + +CREATE TABLE t1 ( + pk1 int(11) NOT NULL, + pk2 varchar(64) NOT NULL, + col1 varchar(16) DEFAULT NULL, + PRIMARY KEY (pk1,pk2), + KEY key1 (pk1,col1) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE t2 ( + pk1 int(11) NOT NULL, + pk2 varchar(64) NOT NULL, + col1 varchar(16) DEFAULT NULL, + PRIMARY KEY (pk1,pk2), + KEY key1 (pk1,col1,pk2) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO `t1` VALUES +(12321321,'a8f5f167f44f4964e6c998dee827110c','video'), +(12321321,'d77a17a3659ffa60c54e0ea17b6c6d16','video'), +(12321321,'wwafdsafdsafads','video'), +(12321321,'696aa249f0738e8181957dd57c2d7d0b','video-2014-09-23'), +(12321321,'802f9f29584b486f356693e3aa4ef0af','video=sdsd'), +(12321321,'2f94543ff74aab82e9a058b4e8316d75','video=sdsdsds'), +(12321321,'c1316b9df0d203fd1b9035308de52a0a','video=sdsdsdsdsd'); + +insert into t2 select * from t1; + +--echo # this must not use filesort: +explain SELECT pk2 +FROM t1 USE INDEX(key1) +WHERE pk1 = 123 +AND col1 = 'video' +ORDER BY pk2 DESC LIMIT 21; + +--echo # this must not use filesort, either: +explain SELECT pk2 +FROM t2 USE INDEX(key1) +WHERE pk1 = 123 AND col1 = 'video' +ORDER BY pk2 DESC LIMIT 21; + +drop table t1, t2; + diff --git a/sql/field.h b/sql/field.h index b5f332f5edc..fed6084fda2 100644 --- a/sql/field.h +++ b/sql/field.h @@ -281,6 +281,12 @@ public: LEX_STRING comment; /* Field is part of the following keys */ key_map key_start, part_of_key, part_of_key_not_clustered; + + /* + Bitmap of indexes that have records ordered by col1, ... this_field, ... + + For example, INDEX (col(prefix_n)) is not present in col.part_of_sortkey. + */ key_map part_of_sortkey; /* We use three additional unireg types for TIMESTAMP to overcome limitation diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 99c731d1541..13e82851841 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3064,7 +3064,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 e46498c0dc9..008311fb03f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -15044,6 +15044,11 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) @retval true can be used @retval false cannot be used */ + +/* + psergey-todo: this returns false for int_column='1234' (here '1234' is a + constant. Need to discuss this with Bar). +*/ static bool test_if_equality_guarantees_uniqueness(Item *l, Item *r) { @@ -19676,12 +19681,21 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, { KEY_PART_INFO *key_part,*key_part_end; key_part=table->key_info[idx].key_part; - key_part_end=key_part+table->key_info[idx].user_defined_key_parts; + key_part_end=key_part + table->key_info[idx].ext_key_parts; key_part_map const_key_parts=table->const_key_parts[idx]; + uint user_defined_kp= table->key_info[idx].user_defined_key_parts; int reverse=0; uint key_parts; - my_bool on_pk_suffix= FALSE; + bool have_pk_suffix= false; + uint pk= table->s->primary_key; DBUG_ENTER("test_if_order_by_key"); + + if ((table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) && + table->key_info[idx].ext_key_part_map && + pk != MAX_KEY && pk != idx) + { + have_pk_suffix= true; + } for (; order ; order=order->next, const_key_parts>>=1) { @@ -19694,58 +19708,37 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, */ for (; const_key_parts & 1 ; const_key_parts>>= 1) key_part++; + + /* + This check was in this function historically (although I think it's + better to check it outside of this function): - if (key_part >= key_part_end) - { - /* - We are at the end of the key. Check if the engine has the primary - key as a suffix to the secondary keys. If it has continue to check - the primary key as a suffix. - */ - if (!on_pk_suffix && (table->key_info[idx].ext_key_part_map & 1) && - (table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) && - table->s->primary_key != MAX_KEY && - table->s->primary_key != idx) - { - KEY_PART_INFO *start,*end; - uint pk_part_idx= 0; - on_pk_suffix= TRUE; - start= key_part= table->key_info[table->s->primary_key].key_part; - const_key_parts=table->const_key_parts[table->s->primary_key]; - - /* - Calculate true key_part_end and const_key_parts - (we have to stop as first not continous primary key part) - */ - for (key_part_end= key_part, - end= key_part+table->key_info[table->s->primary_key].user_defined_key_parts; - key_part_end < end; key_part_end++, pk_part_idx++) - { - /* Found hole in the pk_parts; Abort */ - if (!(table->key_info[idx].ext_key_part_map & - (((key_part_map) 1) << pk_part_idx))) - break; - } + "Test if the primary key parts were all const (i.e. there's one row). + The sorting doesn't matter" - /* Adjust const_key_parts */ - const_key_parts&= (((key_part_map) 1) << pk_part_idx) -1; + So, we're checking that + (1) this is an extended key + (2) we've reached its end + */ + key_parts= (key_part - table->key_info[idx].key_part); + if (have_pk_suffix && + reverse == 0 && // all were =const so far + key_parts == table->key_info[idx].ext_key_parts && + table->const_key_parts[pk] == PREV_BITS(uint, + table->key_info[pk]. + user_defined_key_parts)) + { + key_parts= 0; + reverse= 1; // Key is ok to use + goto ok; + } - for (; const_key_parts & 1 ; const_key_parts>>= 1) - key_part++; - /* - Test if the primary key parts were all const (i.e. there's one row). - The sorting doesn't matter. - */ - if (key_part == start+table->key_info[table->s->primary_key].user_defined_key_parts && - reverse == 0) - { - key_parts= 0; - reverse= 1; // Key is ok to use - goto ok; - } - } - else - DBUG_RETURN(0); + if (key_part == key_part_end) + { + /* + There are some items left in ORDER BY that we don't + */ + DBUG_RETURN(0); } if (key_part->field != field || !field->part_of_sortkey.is_set(idx)) @@ -19760,27 +19753,20 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, if (key_part < key_part_end) key_part++; } - if (on_pk_suffix) - { - uint used_key_parts_secondary= table->key_info[idx].user_defined_key_parts; - uint used_key_parts_pk= - (uint) (key_part - table->key_info[table->s->primary_key].key_part); - key_parts= used_key_parts_pk + used_key_parts_secondary; - if (reverse == -1 && - (!(table->file->index_flags(idx, used_key_parts_secondary - 1, 1) & - HA_READ_PREV) || - !(table->file->index_flags(table->s->primary_key, - used_key_parts_pk - 1, 1) & HA_READ_PREV))) - reverse= 0; // Index can't be used - } - else + key_parts= (uint) (key_part - table->key_info[idx].key_part); + + if (reverse == -1 && + !(table->file->index_flags(idx, user_defined_kp, 1) & HA_READ_PREV)) + reverse= 0; // Index can't be used + + if (have_pk_suffix && reverse == -1) { - key_parts= (uint) (key_part - table->key_info[idx].key_part); - if (reverse == -1 && - !(table->file->index_flags(idx, key_parts-1, 1) & HA_READ_PREV)) + uint pk_parts= table->key_info[pk].user_defined_key_parts; + if (!table->file->index_flags(pk, pk_parts, 1) & HA_READ_PREV) reverse= 0; // Index can't be used } + ok: if (used_key_parts != NULL) *used_key_parts= key_parts; @@ -19868,7 +19854,12 @@ test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts, uint best= MAX_KEY; KEY_PART_INFO *ref_key_part= table->key_info[ref].key_part; KEY_PART_INFO *ref_key_part_end= ref_key_part + ref_key_parts; - + + /* + Find the shortest key that + - produces the required ordering + - has key #ref (up to ref_key_parts) as its subkey. + */ for (nr= 0 ; nr < table->s->keys ; nr++) { if (usable_keys->is_set(nr) && @@ -20061,7 +20052,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, been taken into account. */ usable_keys= *map; - + + /* Find indexes that cover all ORDER/GROUP BY fields */ for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next) { Item *item= (*tmp_order->item)->real_item(); @@ -20081,6 +20073,10 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, { ref_key= tab->ref.key; ref_key_parts= tab->ref.key_parts; + /* + todo: why does JT_REF_OR_NULL mean filesort? We could find another index + that satisfies the ordering. I would just set ref_key=MAX_KEY here... + */ if (tab->type == JT_REF_OR_NULL || tab->type == JT_FT) goto use_filesort; } @@ -20107,15 +20103,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, if (ref_key >= 0 && ref_key != MAX_KEY) { - /* - We come here when there is a REF key. - */ + /* Current access method uses index ref_key with ref_key_parts parts */ if (!usable_keys.is_set(ref_key)) { - /* - We come here when ref_key is not among usable_keys - */ + /* However, ref_key doesn't match the needed ordering */ uint new_ref_key; + /* If using index only read, only consider other possible index only keys @@ -20131,27 +20124,23 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts, &usable_keys)) < MAX_KEY) { - if (tab->ref.key >= 0) - { - /* - We'll use ref access method on key new_ref_key. In general case - the index search tuple for new_ref_key will be different (e.g. - when one index is defined as (part1, part2, ...) and another as - (part1, part2(N), ...) and the WHERE clause contains - "part1 = const1 AND part2=const2". - So we build tab->ref from scratch here. - */ - KEYUSE *keyuse= tab->keyuse; - while (keyuse->key != new_ref_key && keyuse->table == tab->table) - keyuse++; - if (create_ref_for_key(tab->join, tab, keyuse, FALSE, - (tab->join->const_table_map | - OUTER_REF_TABLE_BIT))) - goto use_filesort; + /* + Index new_ref_key + - produces the required ordering, + - also has the same columns as ref_key for #ref_key_parts (this + means we will read the same number of rows as with ref_key). + */ - pick_table_access_method(tab); - } - else + /* + If new_ref_key allows to construct a quick select which uses more key + parts than ref(new_ref_key) would, do that. + + Otherwise, construct a ref access (todo: it's not clear what is the + win in using ref access when we could use quick select also?) + */ + if ((table->quick_keys.is_set(new_ref_key) && + table->quick_key_parts[new_ref_key] > ref_key_parts) || + !(tab->ref.key >= 0)) { /* The range optimizer constructed QUICK_RANGE for ref_key, and @@ -20176,19 +20165,47 @@ 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, FALSE) <= 0; if (res) { select->cond= save_cond; goto use_filesort; } + DBUG_ASSERT(tab->select->quick); + tab->type= JT_ALL; + tab->ref.key= -1; + tab->ref.key_parts= 0; + tab->use_quick= 1; + best_key= new_ref_key; /* We don't restore select->cond as we want to use the original condition as index condition pushdown is not active for the new index. + todo: why not perform index condition pushdown for the new index? */ } + else + { + /* + We'll use ref access method on key new_ref_key. In general case + the index search tuple for new_ref_key will be different (e.g. + when one index is defined as (part1, part2, ...) and another as + (part1, part2(N), ...) and the WHERE clause contains + "part1 = const1 AND part2=const2". + So we build tab->ref from scratch here. + */ + KEYUSE *keyuse= tab->keyuse; + while (keyuse->key != new_ref_key && keyuse->table == tab->table) + keyuse++; + if (create_ref_for_key(tab->join, tab, keyuse, FALSE, + (tab->join->const_table_map | + OUTER_REF_TABLE_BIT))) + goto use_filesort; + + pick_table_access_method(tab); + } + ref_key= new_ref_key; changed_key= true; } @@ -20294,6 +20311,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; + |