diff options
Diffstat (limited to 'mysql-test/t/group_by.test')
| -rw-r--r-- | mysql-test/t/group_by.test | 36 |
1 files changed, 36 insertions, 0 deletions
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index d4214442709..ec20cd8aa76 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1472,4 +1472,40 @@ WHERE a = ( GROUP BY 1; DROP TABLE t1, t2; +--echo # +--echo # Bug #1002146: Unneeded filesort if usage of join buffer is not allowed +--echo # (bug mdev-645) +--echo # + +CREATE TABLE t1 (pk int PRIMARY KEY, a int, INDEX idx(a)); +INSERT INTO t1 VALUES (3,2), (2,3), (5,3), (6,4); + +CREATE TABLE t2 (pk int PRIMARY KEY, a int, INDEX idx(a)); +INSERT INTO t2 VALUES (9,0), (10,3), (6,4), (1,6), (3,100), (5,200); + +set join_cache_level=0; + +EXPLAIN +SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 + GROUP BY t2.a; +SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 + GROUP BY t2.a; + +set join_cache_level=default; + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='outer_join_with_cache=off'; + +EXPLAIN +SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 + GROUP BY t2.a; +SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6 + GROUP BY t2.a; + +set optimizer_switch=@save_optimizer_switch; + + +DROP TABLE t1,t2; + + --echo # End of 5.3 tests |