16 files changed, 225 insertions, 34 deletions
diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result
index 49ec2dd85cc..6f667aabac0 100644
--- a/mysql-test/r/compare.result
+++ b/mysql-test/r/compare.result
@@ -39,3 +39,6 @@ DROP TABLE t1;
 SELECT CHAR(31) = '', '' = CHAR(31);
 CHAR(31) = ''	'' = CHAR(31)
 0	0
+SELECT CHAR(30) = '', '' = CHAR(30);
+CHAR(30) = ''	'' = CHAR(30)
+0	0
diff --git a/mysql-test/r/drop_temp_table.result b/mysql-test/r/drop_temp_table.result
index 266196877c8..a486964feb2 100644
--- a/mysql-test/r/drop_temp_table.result
+++ b/mysql-test/r/drop_temp_table.result
@@ -1,7 +1,9 @@
 reset master;
 create database `drop-temp+table-test`;
 use `drop-temp+table-test`;
+create temporary table shortn1 (a int);
 create temporary table `table:name` (a int);
+create temporary table shortn2 (a int);
 select get_lock("a",10);
 get_lock("a",10)
 1
@@ -10,9 +12,13 @@ get_lock("a",10)
 1
 show binlog events;
 Log_name	Pos	Event_type	Server_id	Orig_log_pos	Info
-master-bin.000001	4	Start	1	4	Server ver: VERSION, Binlog ver: 3
-master-bin.000001	79	Query	1	79	create database `drop-temp+table-test`
-master-bin.000001	168	Query	1	168	use `drop-temp+table-test`; create temporary table `table:name` (a int)
-master-bin.000001	262	Query	1	262	use `drop-temp+table-test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `drop-temp+table-test`.`table:name`
-master-bin.000001	391	Query	1	391	use `drop-temp+table-test`; DO RELEASE_LOCK("a")
+master-bin.000001	#	Start	1	#	Server ver: VERSION, Binlog ver: 3
+master-bin.000001	#	Query	1	#	create database `drop-temp+table-test`
+master-bin.000001	#	Query	1	#	use `drop-temp+table-test`; create temporary table shortn1 (a int)
+master-bin.000001	#	Query	1	#	use `drop-temp+table-test`; create temporary table `table:name` (a int)
+master-bin.000001	#	Query	1	#	use `drop-temp+table-test`; create temporary table shortn2 (a int)
+master-bin.000001	#	Query	1	#	use `drop-temp+table-test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `drop-temp+table-test`.`shortn2`
+master-bin.000001	#	Query	1	#	use `drop-temp+table-test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `drop-temp+table-test`.`table:name`
+master-bin.000001	#	Query	1	#	use `drop-temp+table-test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `drop-temp+table-test`.`shortn1`
+master-bin.000001	#	Query	1	#	use `drop-temp+table-test`; DO RELEASE_LOCK("a")
 drop database `drop-temp+table-test`;
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 4bb79a1cb41..fa645700875 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -733,3 +733,15 @@ one	2
 two	2
 three	1
 drop table t1;
+create table t1(f1 datetime);
+insert into t1 values (now());
+create table t2 select f2 from (select max(now()) f2 from t1) a;
+show columns from t2;
+Field	Type	Null	Key	Default	Extra
+f2	datetime			0000-00-00 00:00:00	
+drop table t2;
+create table t2 select f2 from (select now() f2 from t1) a;
+show columns from t2;
+Field	Type	Null	Key	Default	Extra
+f2	datetime			0000-00-00 00:00:00	
+drop table t2, t1;
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index 5a9f0f68228..2d464c891bf 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -28,3 +28,24 @@ length(format('nan', 2)) > 0
 select concat("$",format(2500,2));
 concat("$",format(2500,2))
 $2,500.00
+create table t1 ( a timestamp );
+insert into t1 values ( '2004-01-06 12:34' );
+select a from t1 where left(a+0,6) in ( left(20040106,6) );
+a
+2004-01-06 12:34:00
+select a from t1 where left(a+0,6) = ( left(20040106,6) );
+a
+2004-01-06 12:34:00
+select a from t1 where right(a+0,6) in ( right(20040106123400,6) );
+a
+2004-01-06 12:34:00
+select a from t1 where right(a+0,6) = ( right(20040106123400,6) );
+a
+2004-01-06 12:34:00
+select a from t1 where mid(a+0,6,3) in ( mid(20040106123400,6,3) );
+a
+2004-01-06 12:34:00
+select a from t1 where mid(a+0,6,3) = ( mid(20040106123400,6,3) );
+a
+2004-01-06 12:34:00
+drop table t1;
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 58d66c7f712..88b1a5ea743 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -325,6 +325,19 @@ trim(trailing 'foo' from 'foo')
 select trim(leading 'foo' from 'foo');
 trim(leading 'foo' from 'foo')
 
+select quote(ltrim(concat('    ', 'a')));
+quote(ltrim(concat('    ', 'a')))
+'a'
+select quote(trim(concat('    ', 'a')));
+quote(trim(concat('    ', 'a')))
+'a'
+CREATE TABLE t1 SELECT 1 UNION SELECT 2 UNION SELECT 3;
+SELECT QUOTE('A') FROM t1;
+QUOTE('A')
+'A'
+'A'
+'A'
+DROP TABLE t1;
 select 1=_latin1'1';
 1=_latin1'1'
 1
@@ -691,12 +704,6 @@ select count(*) as total, left(c,10) as reg from t1 group by reg order by reg de
 total	reg
 10	2004-12-10
 drop table t1;
-select quote(ltrim(concat('    ', 'a')));
-quote(ltrim(concat('    ', 'a')))
-'a'
-select quote(trim(concat('    ', 'a')));
-quote(trim(concat('    ', 'a')))
-'a'
 select trim(null from 'kate') as "must_be_null";
 must_be_null
 NULL
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index f92b3ea4f4d..17b1bb03d1d 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -629,15 +629,6 @@ explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
 DROP TABLE t1;
-create table t1 ( col1 int, col2 int );
-insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
-select group_concat( distinct col1 ) as alias from t1
-group by col2 having alias like '%';
-alias
-1,2
-1,2
-1
-drop table t1;
 create table t1 (a int);
 insert into t1 values(null);
 select min(a) is null from t1;
@@ -650,3 +641,39 @@ select 1 and min(a) is null from t1;
 1 and min(a) is null
 1
 drop table t1;
+create table t1 ( col1 int, col2 int );
+insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
+select group_concat( distinct col1 ) as alias from t1
+group by col2 having alias like '%';
+alias
+1,2
+1,2
+1
+drop table t1;
+create table t1 (a integer, b integer, c integer);
+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, rand()*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	2
+select a,sum(b) from t1 where a=1 group by c;
+a	sum(b)
+1	5
+select a*sum(b) from t1 where a=1 group by c;
+a*sum(b)
+5
+select sum(a)*sum(b) from t1 where a=1 group by c;
+sum(a)*sum(b)
+10
+select a,sum(b) from t1 where a=1 group by c having a=1;
+a	sum(b)
+1	5
+select a as d,sum(b) from t1 where a=1 group by c having d=1;
+d	sum(b)
+1	5
+select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
+d
+10
+drop table t1;
diff --git a/mysql-test/r/insert_select.result.es b/mysql-test/r/insert_select.result.es
index 9e11402733d..9cac6d31b8f 100644
--- a/mysql-test/r/insert_select.result.es
+++ b/mysql-test/r/insert_select.result.es
@@ -633,3 +633,15 @@ No	Field	Count
 0	1	100
 0	2	100
 drop table t1, t2;
+CREATE TABLE t1 (
+ID           int(11) NOT NULL auto_increment,
+NO           int(11) NOT NULL default '0',
+SEQ          int(11) NOT NULL default '0',
+PRIMARY KEY  (ID),
+KEY t1$NO    (SEQ,NO)
+) ENGINE=MyISAM;
+INSERT INTO t1 (SEQ, NO) SELECT "1" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 1);
+select SQL_BUFFER_RESULT * from t1 WHERE (SEQ = 1);
+ID	NO	SEQ
+1	1	1
+drop table t1;
diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result
index c82105e6a49..6a3d2bffab0 100644
--- a/mysql-test/r/limit.result
+++ b/mysql-test/r/limit.result
@@ -67,3 +67,12 @@ SELECT * FROM t1;
 id	id2
 3	0
 DROP TABLE t1;
+create table t1 (a integer);
+insert into t1 values (1);
+select 1 as a from t1 union all select 1 from dual limit 1;
+a
+1
+(select 1 as a from t1) union all (select 1 from dual) limit 1;
+a
+1
+drop table t1;
diff --git a/mysql-test/r/ndb_basic.result b/mysql-test/r/ndb_basic.result
index 6ec5338acbe..a6396080ef0 100644
--- a/mysql-test/r/ndb_basic.result
+++ b/mysql-test/r/ndb_basic.result
@@ -573,3 +573,37 @@ select * from t1 where a12345678901234567890123456789a1234567890=2;
 a1234567890123456789012345678901234567890	a12345678901234567890123456789a1234567890
 5	2
 drop table t1;
+create table t1
+(a bigint, b bigint, c bigint, d bigint, 
+primary key (a,b,c,d)) 
+engine=ndb
+max_rows=200000000;
+Warnings:
+Warning	1105	Ndb might have problems storing the max amount of rows specified
+insert into t1 values
+(1,2,3,4),(2,3,4,5),(3,4,5,6),
+(3,2,3,4),(1,3,4,5),(2,4,5,6),
+(1,2,3,5),(2,3,4,8),(3,4,5,9),
+(3,2,3,5),(1,3,4,8),(2,4,5,9),
+(1,2,3,6),(2,3,4,6),(3,4,5,7),
+(3,2,3,6),(1,3,4,6),(2,4,5,7),
+(1,2,3,7),(2,3,4,7),(3,4,5,8),
+(3,2,3,7),(1,3,4,7),(2,4,5,8),
+(1,3,3,4),(2,4,4,5),(3,5,5,6),
+(3,3,3,4),(1,4,4,5),(2,5,5,6),
+(1,3,3,5),(2,4,4,8),(3,5,5,9),
+(3,3,3,5),(1,4,4,8),(2,5,5,9),
+(1,3,3,6),(2,4,4,6),(3,5,5,7),
+(3,3,3,6),(1,4,4,6),(2,5,5,7),
+(1,3,3,7),(2,4,4,7),(3,5,5,8),
+(3,3,3,7),(1,4,4,7),(2,5,5,8);
+select count(*) from t1;
+count(*)
+48
+drop table t1;
+create table t1
+(a bigint, b bigint, c bigint, d bigint, 
+primary key (a)) 
+engine=ndb
+max_rows=1;
+drop table t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 437fd624ae1..03dcc23c919 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -2196,15 +2196,3 @@ ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
 select 1 = ALL (select 1 from t1 where 1 = xx ), 1 as xx from DUAL;
 ERROR 42S22: Reference 'xx' not supported (forward reference in item list)
 drop table t1;
-CREATE TABLE `t1` ( `a` char(3) NOT NULL default '', `b` char(3) NOT NULL default '', `c` char(3) NOT NULL default '', PRIMARY KEY  (`a`,`b`,`c`)) ENGINE=InnoDB;
-CREATE TABLE t2 LIKE t1;
-INSERT INTO t1 VALUES (1,1,1);
-INSERT INTO t2 VALUES (1,1,1);
-PREPARE my_stmt FROM "SELECT t1.b, count(*) FROM t1 group by t1.b having
-count(*) > ALL (SELECT COUNT(*) FROM t2 WHERE t2.a=1 GROUP By t2.b)";
-EXECUTE my_stmt;
-b	count(*)
-EXECUTE my_stmt;
-b	count(*)
-deallocate prepare my_stmt;
-drop table t1,t2;
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index 0b813a07a1d..0666fd76661 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -140,3 +140,15 @@ id	date1	coworkerid	description	sum_used	sum_remaining	comments
 6	2004-01-01	1	test	22	33	comment
 7	2004-01-01	1	test	22	33	comment
 drop table t1;
+CREATE TABLE `t1` ( `a` char(3) NOT NULL default '', `b` char(3) NOT NULL default '', `c` char(3) NOT NULL default '', PRIMARY KEY  (`a`,`b`,`c`)) ENGINE=InnoDB;
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t1 VALUES (1,1,1);
+INSERT INTO t2 VALUES (1,1,1);
+PREPARE my_stmt FROM "SELECT t1.b, count(*) FROM t1 group by t1.b having
+count(*) > ALL (SELECT COUNT(*) FROM t2 WHERE t2.a=1 GROUP By t2.b)";
+EXECUTE my_stmt;
+b	count(*)
+EXECUTE my_stmt;
+b	count(*)
+deallocate prepare my_stmt;
+drop table t1,t2;
diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 71d1b9ad381..3428b5969d9 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -96,3 +96,11 @@ f2
 19781126
 19781126
 DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (y YEAR);
+INSERT INTO t1 VALUES ('abc');
+Warnings:
+Warning	1265	Data truncated for column 'y' at row 1
+SELECT * FROM t1;
+y
+0000
+DROP TABLE t1;
diff --git a/mysql-test/r/type_float.result.es b/mysql-test/r/type_float.result.es
index b93539b6bea..5fcf9213f83 100644
--- a/mysql-test/r/type_float.result.es
+++ b/mysql-test/r/type_float.result.es
@@ -143,6 +143,15 @@ drop table t1;
 create table t1 (f float(54));
 ERROR 42000: Incorrect column specifier for column 'f'
 drop table if exists t1;
+create table t1 (d1 double, d2 double unsigned);
+insert into t1 set d1 = -1.0;
+update t1 set d2 = d1;
+Warnings:
+Warning	1264	Data truncated; out of range for column 'd2' at row 1
+select * from t1;
+d1	d2
+-1	0
+drop table t1;
 create table t1 (f float(4,3));
 insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
 Warnings:
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index f07bdad9021..115ef6a47f9 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1137,3 +1137,39 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 drop table t2;
+create table t1(a1 int, f1 char(10));
+create table t2
+select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a
+union
+select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a
+order by f2, a1;
+show columns from t2;
+Field	Type	Null	Key	Default	Extra
+f2	date	YES		NULL	
+a1	int(11)	YES		NULL	
+drop table t1, t2;
+create table t1 (f1 int);
+create table t2 (f1 int, f2 int ,f3 date);
+create table t3 (f1 int, f2 char(10));
+create table t4
+(
+select t2.f3 as sdate
+from t1
+left outer join t2 on (t1.f1 = t2.f1)
+inner join t3 on (t2.f2 = t3.f1)
+order by t1.f1, t3.f1, t2.f3
+)
+union
+(
+select cast('2004-12-31' as date) as sdate
+from t1
+left outer join t2 on (t1.f1 = t2.f1)
+inner join t3 on (t2.f2 = t3.f1)
+group by t1.f1
+order by t1.f1, t3.f1, t2.f3
+)
+order by sdate;
+show columns from t4;
+Field	Type	Null	Key	Default	Extra
+sdate	date	YES		NULL	
+drop table t1, t2, t3, t4;
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index beab6105f79..ac370db9ecc 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -212,3 +212,10 @@ insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
 update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
 update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
 drop table t1, t2;
+create table t1 (id int not null auto_increment primary key, id_str varchar(32));
+insert into t1 (id_str) values ("test");
+update t1 set id_str = concat(id_str, id) where id = last_insert_id();
+select * from t1;
+id	id_str
+1	test1
+drop table t1;
diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result
index 81846391795..041d1b836b7 100644
--- a/mysql-test/r/user_var.result
+++ b/mysql-test/r/user_var.result
@@ -109,8 +109,8 @@ select @a:=0;
 select @a, @a:=@a+count(*), count(*), @a from t1 group by i;
 @a	@a:=@a+count(*)	count(*)	@a
 0	1	1	0
-0	2	2	0
-0	3	3	0
+0	3	2	0
+0	6	3	0
 select @a:=0;
 @a:=0
 0