diff options
Diffstat (limited to 'mysql-test/r/group_by.result')
| -rw-r--r-- | mysql-test/r/group_by.result | 94 |
1 files changed, 86 insertions, 8 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 9f5eb053cb7..7f32643b727 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -522,6 +522,7 @@ NULL 9 3 b 1 drop table t1; +set big_tables=0; create table t1 (a int not null, b int not null); insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1); create table t2 (a int not null, b int not null, key(a)); @@ -659,7 +660,10 @@ insert into t1 (a,b) values (1,2),(1,3),(2,5); select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1; a r2 r1 1 1.0 2 -select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2; +select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2; +a r2 r1 +1 2 2 +select a, round(rand(100)*10) r2, sum(1) r1 from t1 group by a having r1>1 and r2<=2; a r2 r1 1 2 2 select a,sum(b) from t1 where a=1 group by c; @@ -668,6 +672,12 @@ a sum(b) select a*sum(b) from t1 where a=1 group by c; a*sum(b) 5 +select a*sum(b) as f1 from t1 where a=1 group by c having f1 <= 10; +f1 +5 +select a,a*sum(b) as f1 from t1 where a=1 group by c having a*sum(b)+0 <= 10; +a f1 +1 5 select sum(a)*sum(b) from t1 where a=1 group by c; sum(a)*sum(b) 10 @@ -1988,12 +1998,12 @@ SHOW SESSION STATUS LIKE 'Sort_scan%'; Variable_name Value Sort_scan 0 EXPLAIN SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2 -FROM t1 GROUP BY field1, 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;; +FROM t1 GROUP BY field1, field2; field1 field2 1 1 2 2 @@ -2082,6 +2092,58 @@ f1 f2 19 19 20 20 explain +select col1 f1, col1 f2 from t1 group by 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 +select col1 f1, col1 f2 from t1 group by 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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx 5 NULL 20 Using index +select col1 f1, col1 f2 from t1 group by f1, f2; +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 index NULL idx 5 NULL 20 Using index @@ -2141,6 +2203,22 @@ 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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range NULL idx 5 NULL 7 Using index for group-by +explain +select SQL_BIG_RESULT col1 f1, col2 f2, col1 f3 from t2 group by f1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range NULL idx 5 NULL 7 Using index for group-by +explain +select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL idx 10 NULL 20 Using index +explain +select col1 f1, col1 f2 from t2 group by f1, 1+1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range NULL idx 5 NULL 7 Using index for group-by +explain select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0; 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 @@ -2167,6 +2245,10 @@ f1 f2 f3 19 2 19 20 1 20 explain +select col1 f1, col2 f2, col1 f3 from t2 order by f1,f2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL idx 10 NULL 20 Using index +explain select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0; 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 @@ -2207,20 +2289,16 @@ field1 field2 2004-10-11 18:13:00 1 2009-02-19 02:05:00 5 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'g' Warning 1292 Truncated incorrect DOUBLE value: 'o' -Warning 1292 Truncated incorrect DOUBLE value: 'g' Warning 1292 Truncated incorrect DOUBLE value: 'v' SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ; field1 field2 2004-10-11 18:13:00 1 2009-02-19 02:05:00 5 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' Warning 1292 Truncated incorrect DOUBLE value: 'g' Warning 1292 Truncated incorrect DOUBLE value: 'o' -Warning 1292 Truncated incorrect DOUBLE value: 'g' Warning 1292 Truncated incorrect DOUBLE value: 'v' SET SESSION SQL_MODE=default; drop table t1; @@ -2342,7 +2420,7 @@ DROP TABLE t1; create table t1 (a int, b int); insert into t1 values (1,11), (1,12), (2,22),(2,23), (4,44),(4,45); create table t2 (c int, d int); -insert into t2 values (1,11), (1,12), (2,22),(2,23), (4,44),(4,45); +insert into t2 values (1,11), (2,22), (4,44); select distinct a,sum(b), (select d from t2 where c=a order by max(b) limit 1) from t1 group by a order by max(b); a sum(b) (select d from t2 where c=a order by max(b) limit 1) 1 23 11 |