10.0-base merge

1 files changed, 78 insertions, 0 deletions

diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 854023052cf..b2dd84f63c7 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -1,3 +1,4 @@
+--source include/have_innodb.inc
 
 # Initialise
 --disable_warnings
@@ -1483,6 +1484,41 @@ DROP TABLE t1;
 --echo the value below *must* be 1
 show status like 'Created_tmp_disk_tables';
 
+--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
 
 --echo #
@@ -1509,6 +1545,48 @@ DROP TABLE t1;
 --echo #End of test#49771
 
 --echo #
+--echo # Test of bug in GROUP_CONCAT with ROLLUP
+--echo #
+
+CREATE TABLE t1 ( b VARCHAR(8) NOT NULL, a INT NOT NULL ) ENGINE=MyISAM;
+INSERT INTO t1 (a,b) VALUES (1,'c'),(2,'v');
+
+CREATE TABLE t2 ( c VARCHAR(8), d INT, KEY (c, d) ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('v',6),('c',4),('v',3);
+
+SELECT b, GROUP_CONCAT( a, b ORDER BY a, b )
+FROM t1 JOIN t2 ON c = b GROUP BY b;
+
+SELECT b, GROUP_CONCAT( a, b ORDER BY a, b )
+FROM t1 JOIN t2 ON c = b GROUP BY b WITH ROLLUP;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Test of MDEV-4002
+--echo #
+
+CREATE TABLE t1 ( 
+  pk INT NOT NULL PRIMARY KEY, 
+  d1 DOUBLE, 
+  d2 DOUBLE, 
+  i INT NOT NULL DEFAULT '0',
+  KEY (i)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1,1.0,1.1,1),(2,2.0,2.2,2);
+
+PREPARE stmt FROM " 
+SELECT DISTINCT i, GROUP_CONCAT(  d1, d2 ORDER BY d1, d2 ) 
+FROM t1 a1 NATURAL JOIN t1 a2 GROUP BY i WITH ROLLUP 
+";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+DROP TABLE t1;
+
+--echo #
 --echo # Bug #58782
 --echo # Missing rows with SELECT .. WHERE .. IN subquery 
 --echo # with full GROUP BY and no aggr