5.5 merge

1 files changed, 86 insertions, 0 deletions

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index ea4af4590e0..1f2bbc470dc 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -2130,6 +2130,47 @@ the value below *must* be 1
 show status like 'Created_tmp_disk_tables';
 Variable_name	Value
 Created_tmp_disk_tables	1
+#
+#  Bug #1002146: Unneeded filesort if usage of join buffer is not allowed
+#  (bug mdev-645)
+#
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	5	Using where; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
+SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6
+GROUP BY t2.a;
+a
+3
+4
+100
+200
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	5	Using where; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where; Using index
+SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6
+GROUP BY t2.a;
+a
+0
+3
+4
+100
+200
+set optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1,t2;
 # End of 5.3 tests
 #
 # Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00
@@ -2161,6 +2202,51 @@ f1	MIN(f2)	MAX(f2)
 DROP TABLE t1;
 #End of test#49771
 #
+# Test of bug in GROUP_CONCAT with ROLLUP
+#
+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;
+b	GROUP_CONCAT( a, b ORDER BY a, b )
+c	1c
+v	2v,2v
+SELECT b, GROUP_CONCAT( a, b ORDER BY a, b )
+FROM t1 JOIN t2 ON c = b GROUP BY b WITH ROLLUP;
+b	GROUP_CONCAT( a, b ORDER BY a, b )
+c	1c
+v	2v,2v
+NULL	1c,2v,2v
+DROP TABLE t1,t2;
+#
+# Test of MDEV-4002
+#
+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;
+i	GROUP_CONCAT(  d1, d2 ORDER BY d1, d2 )
+1	11.1
+2	22.2
+NULL	11.1,22.2
+EXECUTE stmt;
+i	GROUP_CONCAT(  d1, d2 ORDER BY d1, d2 )
+1	11.1
+2	22.2
+NULL	11.1,22.2
+DROP TABLE t1;
+#
 # Bug #58782
 # Missing rows with SELECT .. WHERE .. IN subquery 
 # with full GROUP BY and no aggr