diff options
Diffstat (limited to 'mysql-test/t/order_by.test')
-rw-r--r-- | mysql-test/t/order_by.test | 65 |
1 files changed, 65 insertions, 0 deletions
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index adb7e361a4f..d573c3b5b8a 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1851,3 +1851,68 @@ DROP TABLE t1; --echo End of 5.3 tests +--echo # +--echo # Bug 54599: discarded fast range scan for query with +--echo # GROUP BY + ORDER BY + LIMIT +--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, b int, index idx1(a,b), index idx2(b,a)); +insert into t1 + select 1000*s4.a+100*s3.a+10*s2.a + s1.a, 1000*s4.a+100*s3.a+10*s2.a+s1.a + from t0 s1, t0 s2, t0 s3, t0 s4; +--disable_result_log +analyze table t1; +--enable_result_log + +explain +select b, count(*) num_cnt from t1 + where a > 9750 group by b order by num_cnt; +flush status; +--disable_result_log +select b, count(*) num_cnt from t1 + where a > 9750 group by b order by num_cnt; +--enable_result_log +show status like '%Handler_read%'; + +explain +select b, count(*) num_cnt from t1 + where a > 9750 group by b order by num_cnt limit 1; +flush status; +--disable_result_log +select b, count(*) num_cnt from t1 + where a > 9750 group by b order by num_cnt limit 1; +--enable_result_log +show status like '%Handler_read%'; + +drop table t0, t1; + +--echo # +--echo # LP bug #1002508 : the number of expected rows to be examined is off +--echo # (bug #13528826) +--echo # + +CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam; +INSERT INTO t1 VALUES + (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); +CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam; +INSERT INTO t2 VALUES + (103, 7), (109, 3), (102, 3), (108, 1), (106, 3), + (107, 7), (105, 1), (101, 3), (100, 7), (110, 1); + +EXPLAIN +SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a; + +EXPLAIN +SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8; + +EXPLAIN +SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 100; + +DROP TABLE t1,t2; + +--echo End of 5.5 tests + + |