diff options
author | Varun Gupta <varun.gupta@mariadb.com> | 2019-05-20 00:35:30 +0530 |
---|---|---|
committer | Varun Gupta <varun.gupta@mariadb.com> | 2019-05-20 01:42:38 +0530 |
commit | 7056812ed15abb398089b9c6aa6d7bf5c3cb8c0e (patch) | |
tree | 47e406d5f37978fb8dce5a6142c26992142bf77c /mysql-test/main | |
parent | 2ae83affef5a4d89f38272db31a400f968279a7a (diff) | |
download | mariadb-git-7056812ed15abb398089b9c6aa6d7bf5c3cb8c0e.tar.gz |
MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY
The issue in this case is that we take in account the estimates from quick keys instead of rec_per_key.
The estimates for quick keys are better than rec_per_key only if we have ref(const), so we need to check
that all keyparts in the ref key are of the type ref(const).
Diffstat (limited to 'mysql-test/main')
-rw-r--r-- | mysql-test/main/order_by.result | 57 | ||||
-rw-r--r-- | mysql-test/main/order_by.test | 37 |
2 files changed, 94 insertions, 0 deletions
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index db096acb162..8d1e471f618 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -3266,3 +3266,60 @@ NULLIF(GROUP_CONCAT(v1), null) C B DROP TABLE t1; +# +# MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2( +id int primary key, +key1 int,key2 int, +col1 int, +key(key1), key(key2) +); +insert into t2 +select +A.a + B.a*10 + C.a*100, +A.a + 10*B.a, A.a + 10*B.a, +123456 +from t1 A, t1 B, t1 C; +# here type should show ref not index +explain select +(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE +t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY +t2.key2 ASC +LIMIT 1) +from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 +2 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a 10 Using index condition; Using where; Using filesort +select +(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE +t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY +t2.key2 ASC +LIMIT 1) +from t1; +(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE +t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY +t2.key2 ASC +LIMIT 1) +900-0-123456 +901-1-123456 +902-2-123456 +903-3-123456 +904-4-123456 +905-5-123456 +906-6-123456 +907-7-123456 +908-8-123456 +909-9-123456 +drop table t1,t2; diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index d67c67de89c..58b91fbda91 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2201,3 +2201,40 @@ GROUP BY id ORDER BY id+1 DESC; DROP TABLE t1; + +--echo # +--echo # MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY +--echo # + +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2( + id int primary key, + key1 int,key2 int, + col1 int, + key(key1), key(key2) +); + +insert into t2 + select + A.a + B.a*10 + C.a*100, + A.a + 10*B.a, A.a + 10*B.a, + 123456 +from t1 A, t1 B, t1 C; + +let $query= select + (SELECT concat(id, '-', key1, '-', col1) + FROM t2 + WHERE + t2.key1 = t1.a and t2.key1 IS NOT NULL + ORDER BY + t2.key2 ASC + LIMIT 1) + from t1; + +--echo # here type should show ref not index +eval explain $query; +eval $query; + +drop table t1,t2; |