diff options
Diffstat (limited to 'mysql-test/r/order_by.result')
| -rw-r--r-- | mysql-test/r/order_by.result | 31 |
1 files changed, 31 insertions, 0 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 306fce1f3c2..0c72f816c21 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1557,3 +1557,34 @@ a 2001 1991 DROP TABLE t1; +# +# Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering +# is used +# +CREATE TABLE t1 (a INT, b INT, KEY (a)); +INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL); +INSERT INTO t1 SELECT a+4, b FROM t1; +INSERT INTO t1 SELECT a+8, b FROM t1; +CREATE TABLE t2 (a INT, b INT); +INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL); +INSERT INTO t2 SELECT a+4, b FROM t2; +# shouldn't have "using filesort" +EXPLAIN +SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 2 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 +# should have "using filesort" +EXPLAIN +SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer +# should have "using filesort" +EXPLAIN +SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer +DROP TABLE t1, t2; +End of 5.1 tests |