Merge vvagin@work.mysql.com:/home/bk/mysql-4.1

into eagle.mysql.r18.ru:/home/vva/work/VARIANCE/4.1.clone


BitKeeper/etc/logging_ok:
  auto-union
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/item_sum.cc:
  Auto merged
sql/lex.h:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged

6 files changed, 40 insertions, 35 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index c1f36283ab0..409fd110ab9 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -42,21 +42,21 @@ 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(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1;
-sum(a)	count(a)	avg(a)	std(a)	bit_or(a)	bit_and(a)	min(a)	max(a)	min(c)	max(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	0	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(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;
+sum(a)	count(a)	avg(a)	std(a)	variance(a)	bit_or(a)	bit_and(a)	min(a)	max(a)	min(c)	max(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		
+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
+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) as sum from t1 group by grp;
 grp	sum
 NULL	NULL
 1	7
-2	20
-3	44.816496580928
+2	20.25
+3	45.483163247594
 create table t2 (grp int, a bigint unsigned, c char(10));
 insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp;
 replace into t2 select grp, a, c from t1 limit 2,1;
@@ -72,14 +72,14 @@ CREATE TABLE t1 (id int(11),value1 float(10,2));
 INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00);
 CREATE TABLE t2 (id int(11),name char(20));
 INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two');
-select id, avg(value1), std(value1) from t1 group by id;
-id	avg(value1)	std(value1)
-1	1.000000	0.816497
-2	11.000000	0.816497
-select name, avg(value1), std(value1) from t1, t2 where t1.id = t2.id group by t1.id;
-name	avg(value1)	std(value1)
-Set One	1.000000	0.816497
-Set Two	11.000000	0.816497
+select id, avg(value1), std(value1), variance(value1) from t1 group by id;
+id	avg(value1)	std(value1)	variance(value1)
+1	1.000000	0.816497	0.666667
+2	11.000000	0.816497	0.666667
+select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id;
+name	avg(value1)	std(value1)	variance(value1)
+Set One	1.000000	0.816497	0.666667
+Set Two	11.000000	0.816497	0.666667
 drop table t1,t2;
 create table t1 (id int not null);
 create table t2 (id int not null,rating int null);
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index a33ce457176..bd1bd523964 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -62,4 +62,8 @@ select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null;
 Fld1	max(Fld2)
 1	20
 3	50
+select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
+Fld1	max(Fld2)
+1	20
+3	50
 drop table t1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 46343eb8248..37943ec1189 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2656,14 +2656,14 @@ companynr	count(*)
 58	23
 53	4
 50	11
-select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1) from t2 where companynr = 34 and fld4<>"";
-count(*)	min(fld4)	max(fld4)	sum(fld1)	avg(fld1)	std(fld1)
-70	absentee	vest	17788966	254128.0857	3272.5940
-select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1) from t2 group by companynr limit 3;
-companynr	count(*)	min(fld4)	max(fld4)	sum(fld1)	avg(fld1)	std(fld1)
-00	82	Anthony	windmills	10355753	126289.6707	115550.9757
-29	95	abut	wetness	14473298	152350.5053	8368.5480
-34	70	absentee	vest	17788966	254128.0857	3272.5940
+select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
+count(*)	min(fld4)	max(fld4)	sum(fld1)	avg(fld1)	std(fld1)	variance(fld1)
+70	absentee	vest	17788966	254128.0857	3272.5940	10709871.3069
+select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
+companynr	count(*)	min(fld4)	max(fld4)	sum(fld1)	avg(fld1)	std(fld1)	variance(fld1)
+00	82	Anthony	windmills	10355753	126289.6707	115550.9757	13352027981.7087
+29	95	abut	wetness	14473298	152350.5053	8368.5480	70032594.9026
+34	70	absentee	vest	17788966	254128.0857	3272.5940	10709871.3069
 select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
 companynr	t2nr	count(price)	sum(price)	min(price)	max(price)	avg(price)
 37	1	1	5987435	5987435	5987435	5987435.0000
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 57e9ae24e08..8a9a5655e1b 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -21,9 +21,9 @@ select count(distinct a),count(distinct grp) from t1;
 insert into t1 values (null,null,'');
 select count(distinct a),count(distinct grp) from t1;
 
-select sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1;
-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;
-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(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;
+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;
+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) as sum from t1 group by grp;
 
 create table t2 (grp int, a bigint unsigned, c char(10));
 insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp;
@@ -40,8 +40,8 @@ CREATE TABLE t1 (id int(11),value1 float(10,2));
 INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00); 
 CREATE TABLE t2 (id int(11),name char(20)); 
 INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two'); 
-select id, avg(value1), std(value1) from t1 group by id;
-select name, avg(value1), std(value1) from t1, t2 where t1.id = t2.id group by t1.id;
+select id, avg(value1), std(value1), variance(value1) from t1 group by id;
+select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id;
 drop table t1,t2;
 
 #
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index 8dd7606d82b..7f0a1225bda 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -59,4 +59,5 @@ select Fld1, max(Fld2) as q from t1 group by Fld1 having q is not null;
 select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null;
 select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
 select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null;
+select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
 drop table t1;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 64287dc4170..7ea86845cb0 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1577,8 +1577,8 @@ select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld
 select count(*) from t1;
 select companynr,count(*),sum(fld1) from t2 group by companynr;
 select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
-select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1) from t2 where companynr = 34 and fld4<>"";
-select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1) from t2 group by companynr limit 3;
+select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
+select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
 select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
 select /*! SQL_SMALL_RESULT */ companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
 select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;