Merge bk-internal.mysql.com:/home/bk/mysql-4.0

into mashka.mysql.fi:/home/my/mysql-4.0

2 files changed, 63 insertions, 1 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index b129beaac81..80ccb6c7bb8 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -47,7 +47,7 @@ sum(all a)	count(all a)	avg(all a)	std(all a)	bit_or(all a)	bit_and(all a)	min(a
 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	0	0	NULL	NULL	0	0	NULL	NULL		
+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
@@ -204,3 +204,44 @@ select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1=
 max(t2.a1)
 NULL
 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;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 1915c2172ad..40d829d3e70 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -126,3 +126,24 @@ select max(t1.a2) from t1 left outer join t2 on t1.a1=10 where t1.a1=20;
 select max(t1.a2) from t1 left outer join t2 on t1.a1=10 where t1.a1=10;
 select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA';
 drop table t1,t2;
+
+#
+# Test of group function and NULL values
+#
+
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+drop table t1;