5.2 -> 5.3

1 files changed, 244 insertions, 0 deletions

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 4673f08db4f..07cccf149ee 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1950,6 +1950,250 @@ Warning	1292	Truncated incorrect INTEGER value: 'K'
 Warning	1292	Truncated incorrect INTEGER value: 'jxW<'
 DROP TABLE t1;
 SET SQL_BIG_TABLES=0;
+#
+# MDEV-641 LP:1002108 - Wrong result (or crash) from a query with duplicated field in the group list and a limit clause
+# Bug#11761078: 53534: INCORRECT 'SELECT SQL_BIG_RESULT...' 
+#               WITH GROUP BY ON DUPLICATED FIELDS
+#
+CREATE TABLE t1(
+col1 int, 
+UNIQUE INDEX idx (col1));
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
+(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
+EXPLAIN SELECT col1 AS field1, col1 AS field2
+FROM t1 GROUP BY field1, field2;;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	idx	5	NULL	20	Using index; Using temporary; Using filesort
+FLUSH STATUS;
+SELECT col1 AS field1, col1 AS field2
+FROM t1 GROUP BY field1, field2;;
+field1	field2
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+7	7
+8	8
+9	9
+10	10
+11	11
+12	12
+13	13
+14	14
+15	15
+16	16
+17	17
+18	18
+19	19
+20	20
+SHOW SESSION STATUS LIKE 'Sort_scan%';
+Variable_name	Value
+Sort_scan	1
+EXPLAIN SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
+FROM t1 GROUP BY field1, field2;;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	idx	5	NULL	20	Using index; Using filesort
+FLUSH STATUS;
+SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
+FROM t1 GROUP BY field1, field2;;
+field1	field2
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+7	7
+8	8
+9	9
+10	10
+11	11
+12	12
+13	13
+14	14
+15	15
+16	16
+17	17
+18	18
+19	19
+20	20
+SHOW SESSION STATUS LIKE 'Sort_scan%';
+Variable_name	Value
+Sort_scan	1
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
+FROM v1 
+GROUP BY field1, field2;
+field1	field2
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+7	7
+8	8
+9	9
+10	10
+11	11
+12	12
+13	13
+14	14
+15	15
+16	16
+17	17
+18	18
+19	19
+20	20
+SELECT SQL_BIG_RESULT tbl1.col1 AS field1, tbl2.col1 AS field2
+FROM t1 as tbl1, t1 as tbl2 
+GROUP BY field1, field2 
+LIMIT 3;
+field1	field2
+1	1
+1	2
+1	3
+explain
+select col1 f1, col1 f2 from t1 order by f2, f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	idx	5	NULL	20	Using index; Using filesort
+select col1 f1, col1 f2 from t1 order by f2, f1;
+f1	f2
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+7	7
+8	8
+9	9
+10	10
+11	11
+12	12
+13	13
+14	14
+15	15
+16	16
+17	17
+18	18
+19	19
+20	20
+explain
+select col1 f1, col1 f2 from t1 group by f2 order by f2, f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	idx	5	NULL	7	Using index for group-by; Using temporary; Using filesort
+select col1 f1, col1 f2 from t1 group by f2 order by f2, f1;
+f1	f2
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+7	7
+8	8
+9	9
+10	10
+11	11
+12	12
+13	13
+14	14
+15	15
+16	16
+17	17
+18	18
+19	19
+20	20
+explain
+select col1 f1, col1 f2 from t1 group by f1, f2 order by f2, f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	idx	5	NULL	20	Using index; Using temporary; Using filesort
+select col1 f1, col1 f2 from t1 group by f1, f2 order by f2, f1;
+f1	f2
+1	1
+2	2
+3	3
+4	4
+5	5
+6	6
+7	7
+8	8
+9	9
+10	10
+11	11
+12	12
+13	13
+14	14
+15	15
+16	16
+17	17
+18	18
+19	19
+20	20
+CREATE TABLE t2(
+col1 int, 
+col2 int, 
+UNIQUE INDEX idx (col1, col2));
+INSERT INTO t2(col1, col2) VALUES
+(1,20),(2,19),(3,18),(4,17),(5,16),(6,15),(7,14),(8,13),(9,12),(10,11),
+(11,10),(12,9),(13,8),(14,7),(15,6),(16,5),(17,4),(18,3),(19,2),(20,1);
+explain
+select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	idx	10	NULL	20	Using index; Using temporary; Using filesort
+select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3;
+f1	f2	f3
+1	20	1
+2	19	2
+3	18	3
+4	17	4
+5	16	5
+6	15	6
+7	14	7
+8	13	8
+9	12	9
+10	11	10
+11	10	11
+12	9	12
+13	8	13
+14	7	14
+15	6	15
+16	5	16
+17	4	17
+18	3	18
+19	2	19
+20	1	20
+explain
+select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	idx	10	NULL	20	Using index; Using filesort
+select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3;
+f1	f2	f3
+1	20	1
+2	19	2
+3	18	3
+4	17	4
+5	16	5
+6	15	6
+7	14	7
+8	13	8
+9	12	9
+10	11	10
+11	10	11
+12	9	12
+13	8	13
+14	7	14
+15	6	15
+16	5	16
+17	4	17
+18	3	18
+19	2	19
+20	1	20
+DROP VIEW v1;
+DROP TABLE t1, t2;
 # End of 5.1 tests
 #
 # LP bug#694450 Wrong result with non-standard GROUP BY + ORDER BY