Merge mysql.com:/home/hf/work/mrg/mysql-5.0-opt

into  mysql.com:/home/hf/work/mrg/mysql-5.1-opt


mysql-test/r/insert_select.result:
  Auto merged
mysql-test/t/insert_select.test:
  Auto merged
sql/field.h:
  Auto merged
sql/item.cc:
  Auto merged
sql/item_sum.cc:
  Auto merged
sql/sql_select.h:
  Auto merged
mysql-test/r/create.result:
  merging
mysql-test/r/temp_table.result:
  SCCS merged
mysql-test/r/type_float.result:
  merging
mysql-test/r/union.result:
  merging
mysql-test/t/temp_table.test:
  merging

5 files changed, 74 insertions, 24 deletions

diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index ab9e6762d21..77eb2bb025f 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -455,21 +455,21 @@ create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnul
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `ifnull(a,a)` tinyint(4) DEFAULT NULL,
-  `ifnull(b,b)` smallint(6) DEFAULT NULL,
-  `ifnull(c,c)` mediumint(8) DEFAULT NULL,
-  `ifnull(d,d)` int(11) DEFAULT NULL,
-  `ifnull(e,e)` bigint(20) DEFAULT NULL,
-  `ifnull(f,f)` float(24,2) DEFAULT NULL,
-  `ifnull(g,g)` double(53,3) DEFAULT NULL,
-  `ifnull(h,h)` decimal(5,4) DEFAULT NULL,
-  `ifnull(i,i)` year(4) DEFAULT NULL,
-  `ifnull(j,j)` date DEFAULT NULL,
-  `ifnull(k,k)` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `ifnull(l,l)` datetime DEFAULT NULL,
-  `ifnull(m,m)` varchar(1) DEFAULT NULL,
-  `ifnull(n,n)` varchar(3) DEFAULT NULL,
-  `ifnull(o,o)` varchar(10) DEFAULT NULL
+  `ifnull(a,a)` tinyint(4) default NULL,
+  `ifnull(b,b)` smallint(6) default NULL,
+  `ifnull(c,c)` mediumint(8) default NULL,
+  `ifnull(d,d)` int(11) default NULL,
+  `ifnull(e,e)` bigint(20) default NULL,
+  `ifnull(f,f)` float(3,2) default NULL,
+  `ifnull(g,g)` double(4,3) default NULL,
+  `ifnull(h,h)` decimal(5,4) default NULL,
+  `ifnull(i,i)` year(4) default NULL,
+  `ifnull(j,j)` date default NULL,
+  `ifnull(k,k)` timestamp NOT NULL default '0000-00-00 00:00:00',
+  `ifnull(l,l)` datetime default NULL,
+  `ifnull(m,m)` varchar(1) default NULL,
+  `ifnull(n,n)` varchar(3) default NULL,
+  `ifnull(o,o)` varchar(10) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1,t2;
 create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 18a0ed1a1cb..57339b2e29f 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -730,3 +730,32 @@ f1	f2
 2	2
 10	10
 DROP TABLE t1, t2;
+SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
+CREATE TABLE t1 (c VARCHAR(30), INDEX ix_c (c(10)));
+CREATE TABLE t2 (d VARCHAR(10));
+INSERT INTO t1 (c) VALUES ('7_chars'), ('13_characters');
+EXPLAIN
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t1	ref	ix_c	ix_c	13	const	1	Using where
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
+(SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters')
+13
+13
+INSERT INTO t2 (d) 
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='13_characters') FROM t1;
+INSERT INTO t2 (d) 
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c='7_chars') FROM t1;
+INSERT INTO t2 (d)
+SELECT (SELECT SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)) 
+FROM t1;
+SELECT * FROM t2;
+d
+13
+13
+7
+7
+20
+20
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result
index 6c9a389c1f4..9cb4a6b2427 100644
--- a/mysql-test/r/temp_table.result
+++ b/mysql-test/r/temp_table.result
@@ -152,6 +152,27 @@ SELECT * FROM t1;
 i
 DROP TABLE t1;
 End of 4.1 tests.
+CREATE TABLE t1 ( c FLOAT( 20, 14 ) );
+INSERT INTO t1 VALUES( 12139 );
+CREATE TABLE t2 ( c FLOAT(30,18) );
+INSERT INTO t2 VALUES( 123456 );
+SELECT AVG( c ) FROM t1 UNION SELECT 1;
+AVG( c )
+12139
+1
+SELECT 1 UNION SELECT AVG( c ) FROM t1;
+1
+1
+12139
+SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1;
+1
+1
+123456
+SELECT c/1 FROM t1 UNION SELECT 1;
+c/1
+12139
+1
+DROP TABLE t1, t2;
 create temporary table t1 (a int);
 insert into t1 values (4711);
 select * from t1;
diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result
index dbe60aff3d9..de782f9a5e4 100644
--- a/mysql-test/r/type_float.result
+++ b/mysql-test/r/type_float.result
@@ -91,10 +91,10 @@ col1	col2	col3	col4
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `col1` double DEFAULT NULL,
-  `col2` double(53,5) DEFAULT NULL,
-  `col3` double DEFAULT NULL,
-  `col4` double DEFAULT NULL
+  `col1` double default NULL,
+  `col2` double(22,5) default NULL,
+  `col3` double default NULL,
+  `col4` double default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1,t2;
 create table t1 (a float);
@@ -232,12 +232,12 @@ insert into t2 values ("1.23456780");
 create table t3 select * from t2 union select * from t1;
 select * from t3;
 d
-1.234567800
-100000000.000000000
+1.2345678
+100000000
 show create table t3;
 Table	Create Table
 t3	CREATE TABLE `t3` (
-  `d` double(22,9) DEFAULT NULL
+  `d` double default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1, t2, t3;
 create table t1 select  105213674794682365.00 + 0.0 x;
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 447a8e032cb..2c8fa5cb896 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -554,7 +554,7 @@ aa
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` varbinary(20) NOT NULL DEFAULT ''
+  `a` varbinary(2) NOT NULL default ''
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT 12 as a UNION select 12.2 as a;
@@ -655,7 +655,7 @@ f
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `f` varbinary(24) DEFAULT NULL
+  `f` varbinary(12) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT y from t2 UNION select da from t2;