diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2019-05-22 08:42:31 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2019-05-22 08:42:31 +0300 |
commit | cf77951fb6717f5eb87a2b4baad12a33cd155f27 (patch) | |
tree | 487ef13a47e10adb759fe789b21f7900ad9f3bd8 /mysql-test/main/order_by.result | |
parent | 1921df669712135b2b6f78c96c2aa6ae9a7399f2 (diff) | |
parent | 592dc59d7a5f9bd80bffdd9d0f003243ff639767 (diff) | |
download | mariadb-git-cf77951fb6717f5eb87a2b4baad12a33cd155f27.tar.gz |
Merge 10.3 into 10.4
Diffstat (limited to 'mysql-test/main/order_by.result')
-rw-r--r-- | mysql-test/main/order_by.result | 57 |
1 files changed, 57 insertions, 0 deletions
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index a053b6060c4..9532c2995ce 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -3343,6 +3343,63 @@ 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; +# # MDEV-17761: Odd optimizer choice with ORDER BY LIMIT and condition selectivity # create table t1(a int); |