diff options
Diffstat (limited to 'mysql-test/r/func_group.result')
| -rw-r--r-- | mysql-test/r/func_group.result | 94 |
1 files changed, 42 insertions, 52 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index e9fb56a77d6..99d7bd2d087 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -42,22 +42,12 @@ insert into t1 values (null,null,''); select count(distinct a),count(distinct grp) from t1; count(distinct a) count(distinct grp) 6 3 -select sum(all a),count(all a),avg(all a),std(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1; -sum(all a) count(all a) avg(all a) std(all a) bit_or(all a) bit_and(all a) min(all a) max(all a) min(all c) max(all c) -21 6 3.5000 1.7078 7 0 1 6 E -select grp, sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp; -grp sum(a) count(a) avg(a) std(a) bit_or(a) bit_and(a) min(a) max(a) min(c) max(c) -NULL NULL 0 NULL NULL 0 0 NULL NULL -1 1 1 1.0000 0.0000 1 1 1 1 a a -2 5 2 2.5000 0.5000 3 2 2 3 b c -3 15 3 5.0000 0.8165 7 4 4 6 C E -select grp, sum(a)+count(a)+avg(a)+std(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp; select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1; sum(all a) count(all a) avg(all a) std(all a) variance(all a) bit_or(all a) bit_and(all a) min(all a) max(all a) min(all c) max(all c) 21 6 3.5000 1.7078 2.9167 7 0 1 6 E select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp; grp sum(a) count(a) avg(a) std(a) variance(a) bit_or(a) bit_and(a) min(a) max(a) min(c) max(c) -NULL 0 0 NULL NULL NULL 0 0 NULL NULL +NULL NULL 0 NULL NULL NULL 0 0 NULL NULL 1 1 1 1.0000 0.0000 0.0000 1 1 1 1 a a 2 5 2 2.5000 0.5000 0.2500 3 2 2 3 b c 3 15 3 5.0000 0.8165 0.6667 7 4 4 6 C E @@ -231,47 +221,6 @@ select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10; max(t1.a2) max(t2.a1) zzz BBB drop table t1,t2; -CREATE TABLE t1 (a int, b int); -select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; -count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -0 NULL NULL NULL NULL NULL -1 0 -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -insert into t1 values (1,null); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL -1 0 -insert into t1 values (1,null); -insert into t1 values (2,null); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 0 0 -2 0 NULL NULL NULL NULL NULL 0 0 -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL -1 0 -2 0 NULL NULL NULL NULL NULL -1 0 -insert into t1 values (2,1); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 0 0 -2 1 1 1.0000 0.0000 1 1 0 1 -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL -1 0 -2 1 1 1.0000 0.0000 1 1 1 1 -insert into t1 values (3,1); -select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL 0 0 -2 1 1 1.0000 0.0000 1 1 0 1 -3 1 1 1.0000 0.0000 1 1 1 1 -select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; -a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) -1 0 NULL NULL NULL NULL NULL -1 0 -2 1 1 1.0000 0.0000 1 1 1 1 -3 1 1 1.0000 0.0000 1 1 1 1 -drop table t1; create table t1( a1 char(3) primary key, a2 smallint, @@ -613,3 +562,44 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 3 NULL 14 Using index 1 SIMPLE t2 index NULL k2 4 NULL 6 Using where; Using index drop table t1, t2; +CREATE TABLE t1 (a int, b int); +select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; +count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +0 NULL NULL NULL NULL NULL 18446744073709551615 0 +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +insert into t1 values (1,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +insert into t1 values (1,null); +insert into t1 values (2,null); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 0 0 +2 0 NULL NULL NULL NULL NULL 0 0 +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +insert into t1 values (2,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 0 0 +2 1 1 1.0000 0.0000 1 1 0 1 +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 1 1 1.0000 0.0000 1 1 1 1 +insert into t1 values (3,1); +select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 0 0 +2 1 1 1.0000 0.0000 1 1 0 1 +3 1 1 1.0000 0.0000 1 1 1 1 +select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; +a count(b) sum(b) avg(b) std(b) min(b) max(b) bit_and(b) bit_or(b) +1 0 NULL NULL NULL NULL NULL 18446744073709551615 0 +2 1 1 1.0000 0.0000 1 1 1 1 +3 1 1 1.0000 0.0000 1 1 1 1 +drop table t1; |