Merge

24 files changed, 409 insertions, 66 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 95c652055ec..d81086682f1 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -915,3 +915,19 @@ check table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
 drop table t1;
+create table t1 (a tinytext character set latin1);
+alter table t1 convert to character set utf8;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` text
+) ENGINE=MyISAM DEFAULT CHARSET=utf8
+drop table t1;
+create table t1 (a mediumtext character set latin1);
+alter table t1 convert to character set utf8;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` longtext
+) ENGINE=MyISAM DEFAULT CHARSET=utf8
+drop table t1;
diff --git a/mysql-test/r/binlog_index.result b/mysql-test/r/binlog_index.result
index 82fc26092f4..b307b97e460 100644
--- a/mysql-test/r/binlog_index.result
+++ b/mysql-test/r/binlog_index.result
@@ -21,6 +21,7 @@ flush logs;
 *** must be a warning master-bin.000001 was not found ***
 Warnings:
 Warning	1477	Being purged log MYSQLTEST_VARDIR/log/master-bin.000001 was not found
+Warning	1477	Being purged log MYSQLTEST_VARDIR/log/master-bin.000001 was not found
 *** must show one record, of the active binlog, left in the index file after PURGE ***
 show binary logs;
 Log_name	File_size
diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result
index dca4e349c8a..ac1aee6cc9a 100644
--- a/mysql-test/r/csv.result
+++ b/mysql-test/r/csv.result
@@ -5071,4 +5071,19 @@ select * from t1;
 a
 foo
 drop table t1;
+create table bug39616_1(id int NOT NULL, d varchar(50) NOT NULL) ENGINE=csv;
+select * from bug39616_1;
+id	d
+1	integer sans quotes
+1	string sans quotes
+1	string end quotes"
+1	quotes"in between" strings 
+1	Integer with quote and string with no quote
+1	escape sequence 
+ " \ 
 \a within quotes
+drop table bug39616_1;
+create table bug39616_1(id int NOT NULL, d varchar(50) NOT NULL) ENGINE=csv;
+select * from bug39616_1;
+id	d
+drop table bug39616_1;
 End of 5.0 tests
diff --git a/mysql-test/r/ctype_filesystem.result b/mysql-test/r/ctype_filesystem.result
new file mode 100644
index 00000000000..b461cf2a1a4
--- /dev/null
+++ b/mysql-test/r/ctype_filesystem.result
@@ -0,0 +1,11 @@
+SET CHARACTER SET utf8;
+SHOW VARIABLES like 'character_sets_dir';
+Variable_name	Value
+character_sets_dir	MYSQL_TEST_DIR/ß/
+SHOW VARIABLES like 'character_set_filesystem';
+Variable_name	Value
+character_set_filesystem	latin1
+SHOW VARIABLES like 'character_set_client';
+Variable_name	Value
+character_set_client	utf8
+SET CHARACTER SET default;
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 5ca1d578d2a..a4d7ca2558f 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -1813,3 +1813,35 @@ select hex(_utf8 B'001111111111');
 ERROR HY000: Invalid utf8 character string: 'FF'
 select (_utf8 X'616263FF');
 ERROR HY000: Invalid utf8 character string: 'FF'
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
+INSERT INTO t1 VALUES (70000, 1092), (70001, 1085), (70002, 1065);
+SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;
+CONVERT(a, CHAR)	CONVERT(b, CHAR)
+70002	1065
+70001	1085
+70000	1092
+SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1;
+CONVERT(a, CHAR)	CONVERT(b, CHAR)
+70000	1092
+70001	1085
+70002	1065
+ALTER TABLE t1 ADD UNIQUE (b);
+SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;
+CONVERT(a, CHAR)	CONVERT(b, CHAR)
+70002	1065
+70001	1085
+70000	1092
+DROP INDEX b ON t1;
+SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;
+CONVERT(a, CHAR)	CONVERT(b, CHAR)
+70002	1065
+70001	1085
+70000	1092
+ALTER TABLE t1 ADD INDEX (b);
+SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) from t1 GROUP BY b;
+CONVERT(a, CHAR)	CONVERT(b, CHAR)
+70002	1065
+70001	1085
+70000	1092
+DROP TABLE t1;
+End of 5.0 tests
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index a4c8432d2a4..3aa189f4a9d 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -107,3 +107,51 @@ X	X	X	X	X	X	X	X	X
 X	X	X	X	X	X	X	X	X	Range checked for each record (index map: 0xFFFFFFFFFF)
 DROP TABLE t2;
 DROP TABLE t1;
+CREATE TABLE t1(a INT);
+CREATE TABLE t2(a INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+EXPLAIN EXTENDED SELECT 1
+FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
+Warnings:
+Note	1003	select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
+EXPLAIN EXTENDED SELECT 1
+FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
+Warnings:
+Note	1003	select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
+prepare s1 from 
+'EXPLAIN EXTENDED SELECT 1  
+ FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
+execute s1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
+Warnings:
+Note	1003	select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
+prepare s1 from 
+'EXPLAIN EXTENDED SELECT 1  
+ FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1';
+execute s1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
+Warnings:
+Note	1003	select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
+execute s1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
+Warnings:
+Note	1003	select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result
index 29fb430bb97..6821691c9d0 100644
--- a/mysql-test/r/fulltext.result
+++ b/mysql-test/r/fulltext.result
@@ -469,3 +469,40 @@ SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE);
 a
  aaaaa aaaa
 DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b));
+INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1),
+('test', 1),('test', 2),('test', 3),('test', 4);
+EXPLAIN SELECT * FROM t1
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	b,a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 USE INDEX(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 FORCE INDEX(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 IGNORE INDEX(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	b	b	5	const	4	Using where
+EXPLAIN SELECT * FROM t1 USE INDEX(b)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	b	b	5	const	4	Using where
+EXPLAIN SELECT * FROM t1 FORCE INDEX(b)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	b	b	5	const	4	Using where
+DROP TABLE t1;
+CREATE TABLE t1(a CHAR(10));
+INSERT INTO t1 VALUES('aaa15');
+SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1;
+MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE)
+1
+SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1;
+MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE)
+2
+DROP TABLE t1;
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 772e432355b..a7f4c58f4af 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1425,4 +1425,27 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1;
 AVG(a)	CAST(AVG(a) AS DECIMAL)
 15	15
 DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1), (1,2), (1,3);
+SET SQL_MODE='ONLY_FULL_GROUP_BY';
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+3
+SELECT COUNT(*) FROM t1 where a=1;
+COUNT(*)
+3
+SELECT COUNT(*),a FROM t1;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT COUNT(*) FROM t1 a JOIN t1 b ON a.a= b.a;
+COUNT(*)
+9
+SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a) 
+FROM t1 outr;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT COUNT(*) FROM t1 a JOIN t1 outr 
+ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a);
+COUNT(*)
+0
+SET SQL_MODE=default;
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result
index 7ffc957e285..16d6b435dc7 100644
--- a/mysql-test/r/func_if.result
+++ b/mysql-test/r/func_if.result
@@ -176,4 +176,13 @@ IF((ROUND(t1.a,2)=1), 2,
 IF((ROUND(t1.a,2)=1), 2,
 IF((R
 DROP TABLE t1;
+CREATE TABLE t1 (c LONGTEXT);
+INSERT INTO t1 VALUES(1), (2), (3), (4), ('12345678901234567890');
+SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te;
+MAX(IF(1, CAST(c AS UNSIGNED), 0))
+12345678901234567890
+SELECT * FROM (SELECT MAX(IFNULL(CAST(c AS UNSIGNED), 0)) FROM t1) AS te;
+MAX(IFNULL(CAST(c AS UNSIGNED), 0))
+12345678901234567890
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
index 4db0416bdce..a06d7004908 100644
--- a/mysql-test/r/func_sapdb.result
+++ b/mysql-test/r/func_sapdb.result
@@ -256,3 +256,15 @@ a
 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f");
 str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f")
 2003-01-02 10:11:12.001200
+select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10'),time('00:00:00');
+timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')	time('00:00:00')
+-24:00:00	00:00:00
+select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00');
+timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00')
+0
+select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00');
+timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00')
+1
+SELECT CAST(time('-73:42:12') AS DECIMAL);
+CAST(time('-73:42:12') AS DECIMAL)
+-734212
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index d0809eca65b..c121c8937d7 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -717,8 +717,6 @@ insert(_latin2'abcd',2,3,_latin2'ef'),
 replace(_latin2'abcd',_latin2'b',_latin2'B'),
 encode('abcd','ab')
 ;
-Warnings:
-Warning	1265	Data truncated for column 'format(130,10)' at row 1
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -727,7 +725,7 @@ t1	CREATE TABLE `t1` (
   `conv(130,16,10)` varchar(64) default NULL,
   `hex(130)` varchar(6) NOT NULL default '',
   `char(130)` varbinary(4) NOT NULL default '',
-  `format(130,10)` varchar(4) NOT NULL default '',
+  `format(130,10)` varchar(16) NOT NULL default '',
   `left(_latin2'a',1)` varchar(1) character set latin2 NOT NULL default '',
   `right(_latin2'a',1)` varchar(1) character set latin2 NOT NULL default '',
   `lcase(_latin2'a')` varchar(1) character set latin2 NOT NULL default '',
@@ -2175,4 +2173,12 @@ SELECT HEX(c1) from v1;
 HEX(c1)
 414243
 DROP VIEW v1;
+create table t1(a float);
+insert into t1 values (1.33);
+select format(a, 2) from t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					format(a, 2)	253	20	4	Y	0	2	8
+format(a, 2)
+1.33
+drop table t1;
 End of 5.0 tests
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 47fa331c9ab..682cc2e82e2 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -1261,4 +1261,10 @@ a	b	c
 5	1	1
 4	1	1
 DROP TABLE t1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
+CREATE INDEX i1 on t1 (a(3));
+SELECT * FROM t1 WHERE a = 'abcde';
+a
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 18394bfc88a..b85fb559e8c 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -378,29 +378,6 @@ where 0=1;
 delete t1, t2 from t2,t1 
 where t1.id1=t2.id2 and 0=1;
 drop table t1,t2;
-create table t1 ( a int not null, b int not null) ;
-alter table t1 add index i1(a);
-delete from t1 where a > 2000000;
-create table t2 like t1;
-insert into t2 select * from t1;
-select 't2 rows before small delete', count(*) from t1;
-t2 rows before small delete	count(*)
-t2 rows before small delete	2000000
-delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 2;
-select 't2 rows after small delete', count(*) from t2;
-t2 rows after small delete	count(*)
-t2 rows after small delete	1999999
-select 't1 rows after small delete', count(*) from t1;
-t1 rows after small delete	count(*)
-t1 rows after small delete	1999999
-delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000;
-select 't2 rows after big delete', count(*) from t2;
-t2 rows after big delete	count(*)
-t2 rows after big delete	1900001
-select 't1 rows after big delete', count(*) from t1;
-t1 rows after big delete	count(*)
-t1 rows after big delete	1900001
-drop table t1,t2;
 CREATE TABLE t1 ( a int );
 CREATE TABLE t2 ( a int );
 DELETE t1 FROM t1, t2 AS t3;
diff --git a/mysql-test/r/multi_update2.result b/mysql-test/r/multi_update2.result
new file mode 100644
index 00000000000..3712e638f40
--- /dev/null
+++ b/mysql-test/r/multi_update2.result
@@ -0,0 +1,25 @@
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL) ;
+# The protocolling of many inserts into t1 is suppressed.
+ALTER TABLE t1 ADD INDEX i1(a);
+DELETE FROM t1 WHERE a > 2000000;
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 SELECT * FROM t1;
+SELECT 't2 rows before small delete', COUNT(*) FROM t1;
+t2 rows before small delete	COUNT(*)
+t2 rows before small delete	2000000
+DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 2;
+SELECT 't2 rows after small delete', COUNT(*) FROM t2;
+t2 rows after small delete	COUNT(*)
+t2 rows after small delete	1999999
+SELECT 't1 rows after small delete', COUNT(*) FROM t1;
+t1 rows after small delete	COUNT(*)
+t1 rows after small delete	1999999
+DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 100*1000;
+SELECT 't2 rows after big delete', COUNT(*) FROM t2;
+t2 rows after big delete	COUNT(*)
+t2 rows after big delete	1900001
+SELECT 't1 rows after big delete', COUNT(*) FROM t1;
+t1 rows after big delete	COUNT(*)
+t1 rows after big delete	1900001
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result
index eded1a3fc3b..95bdcab6ba1 100644
--- a/mysql-test/r/mysql.result
+++ b/mysql-test/r/mysql.result
@@ -180,4 +180,10 @@ ERROR at line 1: DELIMITER cannot contain a backslash character
 1
 This is a file starting with UTF8 BOM 0xEFBBBF
 This is a file starting with UTF8 BOM 0xEFBBBF
+delimiter
+1
+2
+2
+2
+2
 End of 5.0 tests
diff --git a/mysql-test/r/perror-win.result b/mysql-test/r/perror-win.result
new file mode 100644
index 00000000000..1d42235cc14
--- /dev/null
+++ b/mysql-test/r/perror-win.result
@@ -0,0 +1,6 @@
+MySQL error code 150: Foreign key constraint is incorrectly formed
+Win32 error code 150: System trace information was not specified in your CONFIG.SYS file, or tracing is disallowed.

+OS error code  23:  Too many open files in system
+Win32 error code 23: Data error (cyclic redundancy check).

+Win32 error code 1062: The service has not been started.

+Illegal error code: 30000
diff --git a/mysql-test/r/rpl_packet.result b/mysql-test/r/rpl_packet.result
index 88c63994fff..f84ce18810e 100644
--- a/mysql-test/r/rpl_packet.result
+++ b/mysql-test/r/rpl_packet.result
@@ -23,39 +23,9 @@ SET @@global.max_allowed_packet=4096;
 SET @@global.net_buffer_length=4096;
 STOP SLAVE;
 START SLAVE;
-CREATE TABLe `t1` (`f1` LONGTEXT) ENGINE=MyISAM;
+CREATE TABLE `t1` (`f1` LONGTEXT) ENGINE=MyISAM;
 INSERT INTO `t1`(`f1`) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2048');
-show slave status;
-Slave_IO_State	#
-Master_Host	127.0.0.1
-Master_User	root
-Master_Port	MASTER_MYPORT
-Connect_Retry	1
-Master_Log_File	master-bin.000001
-Read_Master_Log_Pos	2138
-Relay_Log_File	#
-Relay_Log_Pos	#
-Relay_Master_Log_File	master-bin.000001
-Slave_IO_Running	No
-Slave_SQL_Running	#
-Replicate_Do_DB	
-Replicate_Ignore_DB	
-Replicate_Do_Table	
-Replicate_Ignore_Table	
-Replicate_Wild_Do_Table	
-Replicate_Wild_Ignore_Table	
-Last_Errno	0
-Last_Error	
-Skip_Counter	0
-Exec_Master_Log_Pos	2138
-Relay_Log_Space	#
-Until_Condition	None
-Until_Log_File	
-Until_Log_Pos	0
-Master_SSL_Allowed	No
-Master_SSL_CA_File	
-Master_SSL_CA_Path	
-Master_SSL_Cert	
-Master_SSL_Cipher	
-Master_SSL_Key	
-Seconds_Behind_Master	#
+Slave_IO_Running = No (expect No)
+==== clean up ====
+DROP TABLE t1;
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index c5bae840214..8830ea11f97 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4407,4 +4407,49 @@ pk	a
 3	30
 2	20
 DROP TABLE t1,t2;
+CREATE TABLE t1 (s1 char(1));
+INSERT INTO t1 VALUES ('a');
+SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
+s1
+a
+DROP TABLE t1;
+CREATE TABLE t1(id BIGINT);
+CREATE TABLE t2(id1 BIGINT, id2 BIGINT);
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t2 VALUES (2,1),(3,1);
+SELECT * FROM t1 i WHERE 1 IN (SELECT l.id2 FROM t2 l WHERE i.id=l.id1);
+id
+2
+3
+DROP TABLE t1, t2;
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int, c int);
+INSERT INTO t1 (id) VALUES (1);
+INSERT INTO t2 (id) VALUES (1);
+INSERT INTO t1 (id) VALUES (1);
+INSERT INTO t2 (id) VALUES (1);
+CREATE VIEW v1 AS
+SELECT t2.c AS c FROM t1, t2
+WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
+UPDATE v1 SET c=1;
+CREATE VIEW v2 (a,b) AS
+SELECT t2.id, t2.c AS c FROM t1, t2
+WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
+INSERT INTO v2(a,b) VALUES (2,2);
+ERROR HY000: CHECK OPTION failed 'test.v2'
+INSERT INTO v2(a,b) VALUES (1,2);
+SELECT * FROM v1;
+c
+1
+1
+1
+1
+2
+2
+CREATE VIEW v3 AS
+SELECT t2.c AS c FROM t2
+WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
+DELETE FROM v3;
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2;
 End of 5.0 tests.
diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result
index bb1d764ac8c..e115ce16fa4 100644
--- a/mysql-test/r/timezone2.result
+++ b/mysql-test/r/timezone2.result
@@ -110,7 +110,7 @@ i	ts
 362793610	1981-07-01 04:00:00
 select from_unixtime(362793609);
 from_unixtime(362793609)
-1981-07-01 03:59:60
+1981-07-01 03:59:59
 drop table t1;
 create table t1 (ts timestamp);
 set time_zone='UTC';
diff --git a/mysql-test/r/timezone3.result b/mysql-test/r/timezone3.result
index ec0b6045f93..ceac4a5aefb 100644
--- a/mysql-test/r/timezone3.result
+++ b/mysql-test/r/timezone3.result
@@ -17,6 +17,9 @@ insert into t1 values
 insert into t1 values
 (unix_timestamp('1981-07-01 03:59:59'),'1981-07-01 03:59:59'),
 (unix_timestamp('1981-07-01 04:00:00'),'1981-07-01 04:00:00');
+insert into t1 values
+(unix_timestamp('2009-01-01 02:59:59'),'2009-01-01 02:59:59'),
+(unix_timestamp('2009-01-01 03:00:00'),'2009-01-01 03:00:00');
 select i, from_unixtime(i), c from t1;
 i	from_unixtime(i)	c
 1072904422	2004-01-01 00:00:00	2004-01-01 00:00:00
@@ -31,6 +34,8 @@ i	from_unixtime(i)	c
 1099180821	2004-10-31 02:59:59	2004-10-31 02:59:59
 362793608	1981-07-01 03:59:59	1981-07-01 03:59:59
 362793610	1981-07-01 04:00:00	1981-07-01 04:00:00
+1230768022	2009-01-01 02:59:59	2009-01-01 02:59:59
+1230768024	2009-01-01 03:00:00	2009-01-01 03:00:00
 drop table t1;
 create table t1 (ts timestamp);
 insert into t1 values (19730101235900), (20040101235900);
@@ -39,3 +44,6 @@ ts
 1973-01-01 23:59:00
 2004-01-01 23:59:00
 drop table t1;
+SELECT FROM_UNIXTIME(1230768022), FROM_UNIXTIME(1230768023), FROM_UNIXTIME(1230768024);
+FROM_UNIXTIME(1230768022)	FROM_UNIXTIME(1230768023)	FROM_UNIXTIME(1230768024)
+2009-01-01 02:59:59	2009-01-01 02:59:59	2009-01-01 03:00:00
diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result
index 68c0e1635a3..63dec0297d0 100644
--- a/mysql-test/r/type_bit.result
+++ b/mysql-test/r/type_bit.result
@@ -708,4 +708,45 @@ HEX(b1)	HEX(b2)	i2
 1	0	100
 1	0	200
 DROP TABLE t1, t2;
+CREATE TABLE IF NOT EXISTS t1 (
+f1 bit(2) NOT NULL default b'10',
+f2 bit(14) NOT NULL default b'11110000111100'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` bit(2) NOT NULL default b'10',
+  `f2` bit(14) NOT NULL default b'11110000111100'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
+DROP TABLE t1;
+CREATE TABLE IF NOT EXISTS t1 (
+f1 bit(2) NOT NULL default b''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
+ERROR 42000: Invalid default value for 'f1'
+create table t1bit7 (a1 bit(7) not null) engine=MyISAM;
+create table t2bit7 (b1 bit(7)) engine=MyISAM;
+insert into t1bit7 values (b'1100000');
+insert into t1bit7 values (b'1100001');
+insert into t1bit7 values (b'1100010');
+insert into t2bit7 values (b'1100001');
+insert into t2bit7 values (b'1100010');
+insert into t2bit7 values (b'1100110');
+select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1;
+bin(a1)
+1100001
+1100010
+drop table t1bit7, t2bit7;
+create table t1bit7 (a1 bit(15) not null) engine=MyISAM;
+create table t2bit7 (b1 bit(15)) engine=MyISAM;
+insert into t1bit7 values (b'110000011111111');
+insert into t1bit7 values (b'110000111111111');
+insert into t1bit7 values (b'110001011111111');
+insert into t2bit7 values (b'110000111111111');
+insert into t2bit7 values (b'110001011111111');
+insert into t2bit7 values (b'110011011111111');
+select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1;
+bin(a1)
+110000111111111
+110001011111111
+drop table t1bit7, t2bit7;
 End of 5.0 tests
diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result
index f1075604ca9..8caabbff047 100644
--- a/mysql-test/r/type_float.result
+++ b/mysql-test/r/type_float.result
@@ -392,4 +392,13 @@ f1 + 0e0
 1.0000000150475e+30
 -1.0000000150475e+30
 drop table t1;
+create table t1(d double, u bigint unsigned);
+insert into t1(d) values (9.22337203685479e18),
+(1.84e19);
+update t1 set u = d;
+select u from t1;
+u
+9223372036854790144
+18400000000000000000
+drop table t1;
 End of 5.0 tests
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 1bb07534a7f..90b6f524692 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -1524,4 +1524,19 @@ select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
 (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
 1.01500000 * 1.01500000 * 0.99500000)
 0.812988073953673124592306939480
+create table t1 as select 5.05 / 0.014;
+Warnings:
+Note	1265	Data truncated for column '5.05 / 0.014' at row 1
+show warnings;
+Level	Code	Message
+Note	1265	Data truncated for column '5.05 / 0.014' at row 1
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `5.05 / 0.014` decimal(10,6) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t1;
+5.05 / 0.014
+360.714286
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 8cbe3fc36cf..311b77e7a99 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -625,7 +625,7 @@ drop table t1;
 create table t1 (a int, b int);
 create view v1 as select a, sum(b) from t1 group by a;
 select b from v1 use index (some_index) where b=1;
-ERROR HY000: Incorrect usage of USE INDEX and VIEW
+ERROR HY000: Key 'some_index' doesn't exist in table 'v1'
 drop view v1;
 drop table t1;
 create table t1 (col1 char(5),col2 char(5));
@@ -3567,11 +3567,11 @@ CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1),(2);
 CREATE VIEW v1 AS SELECT * FROM t1;
 SELECT * FROM v1 USE KEY(non_existant);
-ERROR HY000: Incorrect usage of USE INDEX and VIEW
+ERROR HY000: Key 'non_existant' doesn't exist in table 'v1'
 SELECT * FROM v1 FORCE KEY(non_existant);
-ERROR HY000: Incorrect usage of FORCE INDEX and VIEW
+ERROR HY000: Key 'non_existant' doesn't exist in table 'v1'
 SELECT * FROM v1 IGNORE KEY(non_existant);
-ERROR HY000: Incorrect usage of IGNORE INDEX and VIEW
+ERROR HY000: Key 'non_existant' doesn't exist in table 'v1'
 DROP VIEW v1;
 DROP TABLE t1;
 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0,
@@ -3679,6 +3679,31 @@ DROP VIEW v1;
 
 CREATE VIEW v1 AS SELECT 1;
 DROP VIEW v1;
+CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2));
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2;
+c1	c2
+2	2
+SELECT * FROM t1 USE INDEX (c2) WHERE c2=2;
+c1	c2
+2	2
+CREATE VIEW v1 AS SELECT c1, c2 FROM t1;
+SHOW INDEX FROM v1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
+SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 USE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+DROP VIEW v1;
+DROP TABLE t1;
 # -----------------------------------------------------------------
 # -- End of 5.0 tests.
 # -----------------------------------------------------------------