diff options
Diffstat (limited to 'mysql-test/r/group_by.result')
| -rw-r--r-- | mysql-test/r/group_by.result | 244 |
1 files changed, 244 insertions, 0 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 820a828d604..ba3fa7bb083 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1940,4 +1940,248 @@ 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 |