Fixed LP bug #1002146 (bug mdev-645).

If the setting of system variables does not allow to use join buffer
for a join query with GROUP BY <f1,...> / ORDER BY <f1,...> then
filesort is not needed if the first joined table is scanned in
the order compatible with order specified by the list <f1,...>.

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