Merge 5.0 -> 5.1

7 files changed, 187 insertions, 182 deletions

diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index 18f11ebcc71..e7e5e1e7165 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -164,7 +164,7 @@ Warnings:
 Warning	1364	Field 'ssl_cipher' doesn't have a default value
 Warning	1364	Field 'x509_issuer' doesn't have a default value
 Warning	1364	Field 'x509_subject' doesn't have a default value
-insert into mysql.db (host, db, user, select_priv) values 
+insert into mysql.db (host, db, user, select_priv) values
 ('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');
 alter table mysql.db order by db asc;
 flush privileges;
@@ -264,7 +264,7 @@ drop user mysqltest_1@localhost;
 SET NAMES koi8r;
 CREATE DATABASE ÂÄ;
 USE ÂÄ;
-CREATE TABLE ÔÁÂ (ËÏÌ int);
+CREATE TABLE ÔÁÂ (ËÏÌ INT);
 GRANT SELECT ON ÂÄ.* TO ÀÚÅÒ@localhost;
 SHOW GRANTS FOR ÀÚÅÒ@localhost;
 Grants for ÀÚÅÒ@localhost
@@ -383,21 +383,21 @@ grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost;
 grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost;
 grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost;
 grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES 
-WHERE GRANTEE = '''mysqltest_3''@''localhost''' 
-ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE GRANTEE = '''mysqltest_3''@''localhost'''
+ ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE;
 GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
 'mysqltest_3'@'localhost'	NULL	mysqltest_1	t1	a	UPDATE	NO
 'mysqltest_3'@'localhost'	NULL	mysqltest_2	t1	c	SELECT	NO
 'mysqltest_3'@'localhost'	NULL	mysqltest_1	t2	b	SELECT	NO
 'mysqltest_3'@'localhost'	NULL	mysqltest_2	t2	d	UPDATE	NO
 SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
-WHERE GRANTEE = '''mysqltest_3''@''localhost''' 
-ORDER BY TABLE_NAME,PRIVILEGE_TYPE;
+WHERE GRANTEE = '''mysqltest_3''@''localhost'''
+ ORDER BY TABLE_NAME,PRIVILEGE_TYPE;
 GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
 SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
-WHERE GRANTEE = '''mysqltest_3''@''localhost''' 
-ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE;
+WHERE GRANTEE = '''mysqltest_3''@''localhost'''
+ ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE;
 GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
 SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES
 WHERE GRANTEE = '''mysqltest_3''@''localhost'''
@@ -884,11 +884,11 @@ flush privileges;
 drop table t2;
 drop table t1;
 CREATE DATABASE mysqltest3;
-use mysqltest3;
+USE mysqltest3;
 CREATE TABLE t_nn (c1 INT);
 CREATE VIEW  v_nn AS SELECT * FROM t_nn;
 CREATE DATABASE mysqltest2;
-use mysqltest2;
+USE mysqltest2;
 CREATE TABLE t_nn (c1 INT);
 CREATE VIEW  v_nn AS SELECT * FROM t_nn;
 CREATE VIEW  v_yn AS SELECT * FROM t_nn;
@@ -958,7 +958,7 @@ DROP TABLE mysqltest3.t_nn;
 DROP DATABASE mysqltest3;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost';
 DROP USER 'mysqltest_1'@'localhost';
-use test;
+USE test;
 create user mysqltest1_thisisreallytoolong;
 ERROR HY000: String 'mysqltest1_thisisreallytoolong' is too long for user name (should be no longer than 16)
 CREATE DATABASE mysqltest1;
@@ -1196,16 +1196,16 @@ DROP DATABASE mysqltest1;
 DROP DATABASE mysqltest2;
 DROP USER mysqltest_1@localhost;
 DROP USER mysqltest_2@localhost;
-use test;
+USE test;
 CREATE TABLE t1 (f1 int, f2 int);
 INSERT INTO t1 VALUES(1,1), (2,2);
 CREATE DATABASE db27878;
 GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost';
 GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost';
 GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost';
-use db27878;
+USE db27878;
 CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1;
-use db27878;
+USE db27878;
 UPDATE v1 SET f2 = 4;
 ERROR HY000: View 'db27878.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
 SELECT * FROM test.t1;
@@ -1217,7 +1217,7 @@ REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost';
 REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost';
 DROP USER mysqltest_1@localhost;
 DROP DATABASE db27878;
-use test;
+USE test;
 DROP TABLE t1;
 #
 #  Bug#33275 Server crash when creating temporary table mysql.user
@@ -1355,6 +1355,6 @@ Warnings:
 Warning	1403	There is no such grant defined for user 'userbug33464' on host 'localhost' on routine 'fn2'
 DROP PROCEDURE sp3;
 DROP USER 'userbug33464'@'localhost';
-use test;
+USE test;
 DROP DATABASE dbbug33464;
 SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index f8deef15819..5dd0f159428 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -99,7 +99,7 @@ t2
 t3
 t5
 v1
-select c,table_name from v1 
+select c,table_name from v1
 inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
 where v1.c like "t%";
 c	table_name
@@ -118,7 +118,7 @@ t4	t4
 t2	t2
 t3	t3
 t5	t5
-select c,table_name from v1 
+select c,table_name from v1
 left join information_schema.TABLES v2 on (v1.c=v2.table_name)
 where v1.c like "t%";
 c	table_name
@@ -197,7 +197,7 @@ a	int(11)	YES		NULL
 create view mysqltest.v1 (c) as select a from mysqltest.t1;
 grant select (a) on mysqltest.t1 to mysqltest_2@localhost;
 grant select on mysqltest.v1 to mysqltest_3;
-select table_name, column_name, privileges from information_schema.columns 
+select table_name, column_name, privileges from information_schema.columns
 where table_schema = 'mysqltest' and table_name = 't1';
 table_name	column_name	privileges
 t1	a	select
@@ -275,7 +275,7 @@ begin
 select * from t1;
 select * from t2;
 end|
-select parameter_style, sql_data_access, dtd_identifier 
+select parameter_style, sql_data_access, dtd_identifier
 from information_schema.routines where routine_schema='test';
 parameter_style	sql_data_access	dtd_identifier
 SQL	CONTAINS SQL	NULL
@@ -560,7 +560,7 @@ drop view v1;
 create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2),
 d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3),
 i DOUBLE);
-select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, 
+select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH,
 CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
 from information_schema.columns where table_name= 't1';
 COLUMN_NAME	COLUMN_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE
@@ -621,7 +621,7 @@ TABLE_NAME= "vo";
 CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
 drop view vo;
 select TABLE_NAME,TABLE_TYPE,ENGINE
-from information_schema.tables 
+from information_schema.tables
 where table_schema='information_schema' limit 2;
 TABLE_NAME	TABLE_TYPE	ENGINE
 CHARACTER_SETS	SYSTEM VIEW	MEMORY
@@ -726,7 +726,7 @@ where table_schema="information_schema" and table_name="COLUMNS" and
 column_type
 varchar(32)
 varchar(32)
-select TABLE_ROWS from information_schema.tables where 
+select TABLE_ROWS from information_schema.tables where
 table_schema="information_schema" and table_name="COLUMNS";
 TABLE_ROWS
 NULL
@@ -760,7 +760,7 @@ count(*)
 drop view a2, a1;
 drop table t_crashme;
 select table_schema,table_name, column_name from
-information_schema.columns 
+information_schema.columns
 where data_type = 'longtext';
 table_schema	table_name	column_name
 information_schema	COLUMNS	COLUMN_DEFAULT
@@ -804,7 +804,7 @@ event	last_executed	datetime
 event	starts	datetime
 event	ends	datetime
 SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
-WHERE NOT EXISTS 
+WHERE NOT EXISTS
 (SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
 WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
 AND A.TABLE_NAME = B.TABLE_NAME);
@@ -833,7 +833,7 @@ x_float	NULL	NULL
 x_double_precision	NULL	NULL
 drop table t1;
 grant select on test.* to mysqltest_4@localhost;
-SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 
+SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS
 where COLUMN_NAME='TABLE_NAME';
 TABLE_NAME	COLUMN_NAME	PRIVILEGES
 COLUMNS	TABLE_NAME	select
@@ -1079,7 +1079,7 @@ BEGIN
 SELECT 'foo' FROM DUAL;
 END |
 ERROR 42000: Unknown database 'information_schema'
-select  ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema';
+select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema';
 ROUTINE_NAME
 grant all on information_schema.* to 'user1'@'localhost';
 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
@@ -1634,7 +1634,7 @@ show events where Db= 'information_schema';
 Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
 use test;
 #
-# Bug#34166: Server crash in SHOW OPEN TABLES and prelocking
+# Bug#34166 Server crash in SHOW OPEN TABLES and prelocking
 #
 drop table if exists t1;
 drop function if exists f1;
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index b8f6aa9e4d4..449333a4ae6 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -375,7 +375,7 @@ update t2, t1 set t2.field=t1.field
 where t1.id1=t2.id2 and 0=1;
 delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2
 where 0=1;
-delete t1, t2 from t2,t1 
+delete t1, t2 from t2,t1
 where t1.id1=t2.id2 and 0=1;
 drop table t1,t2;
 CREATE TABLE t1 ( a int );
@@ -443,12 +443,12 @@ delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2
 ERROR HY000: You can't specify target table 't1' for update in FROM clause
 drop table t1,t2;
 create table t1 (
-aclid bigint not null primary key, 
-status tinyint(1) not null 
+aclid bigint not null primary key,
+status tinyint(1) not null
 ) engine = innodb;
 create table t2 (
-refid bigint not null primary key, 
-aclid bigint, index idx_acl(aclid) 
+refid bigint not null primary key,
+aclid bigint, index idx_acl(aclid)
 ) engine = innodb;
 insert into t2 values(1,null);
 delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
diff --git a/mysql-test/r/packet.result b/mysql-test/r/packet.result
index fa2fb33d278..ecbb47d4ee0 100644
--- a/mysql-test/r/packet.result
+++ b/mysql-test/r/packet.result
@@ -1,3 +1,5 @@
+set @max_allowed_packet=@@global.max_allowed_packet;
+set @net_buffer_length=@@global.net_buffer_length;
 set global max_allowed_packet=100;
 Warnings:
 Warning	1292	Truncated incorrect max_allowed_packet value: '100'
@@ -23,3 +25,5 @@ len
 select length(repeat('a',2000));
 length(repeat('a',2000))
 2000
+set global max_allowed_packet=@max_allowed_packet;
+set global net_buffer_length=@net_buffer_length;
diff --git a/mysql-test/r/query_cache_notembedded.result b/mysql-test/r/query_cache_notembedded.result
index 64bb40f1d17..d9bf7a6d814 100644
--- a/mysql-test/r/query_cache_notembedded.result
+++ b/mysql-test/r/query_cache_notembedded.result
@@ -375,6 +375,7 @@ id
 show status like 'Qcache_queries_in_cache';
 Variable_name	Value
 Qcache_queries_in_cache	1
+USE test;
 DROP DATABASE bug30269;
 DROP USER 'bug30269'@'localhost';
 set GLOBAL query_cache_type=default;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index aff26454b2d..7f6ebb14c3e 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -192,11 +192,11 @@ select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
 (select a from t3 where a<t2.a*4 order by 1 desc limit 1)	a
 3	1
 7	2
-select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
+select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
 (select * from t2 where a>1) as tt;
 (select t3.a from t3 where a<8 order by 1 desc limit 1)	a
 7	2
-explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
+explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
 (select * from t2 where a>1) as tt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	100.00	
@@ -2303,20 +2303,20 @@ drop table t1,t2;
 CREATE TABLE t1 ( a int, b int );
 CREATE TABLE t2 ( c int, d int );
 INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
-SELECT a AS abc, b FROM t1 outr WHERE b = 
+SELECT a AS abc, b FROM t1 outr WHERE b =
 (SELECT MIN(b) FROM t1 WHERE a=outr.a);
 abc	b
 1	2
 2	3
 3	4
-INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b = 
+INSERT INTO t2 SELECT a AS abc, b FROM t1 outr WHERE b =
 (SELECT MIN(b) FROM t1 WHERE a=outr.a);
 select * from t2;
 c	d
 1	2
 2	3
 3	4
-CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b = 
+CREATE TABLE t3 SELECT a AS abc, b FROM t1 outr WHERE b =
 (SELECT MIN(b) FROM t1 WHERE a=outr.a);
 select * from t3;
 abc	b
@@ -2517,8 +2517,8 @@ INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,680
 INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
 INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
 /*!40000 ALTER TABLE t1 ENABLE KEYS */;
-SELECT DISTINCT Continent AS c FROM t1 outr WHERE 
-Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND 
+SELECT DISTINCT Continent AS c FROM t1 outr WHERE
+Code <> SOME ( SELECT Code FROM t1 WHERE Continent = outr.Continent AND
 Population < 200);
 c
 Oceania
@@ -2628,32 +2628,32 @@ select
 count(distinct t2.userid) pass,
 groupstuff.*,
 count(t2.courseid) crse,
-t1.categoryid, 
+t1.categoryid,
 t2.courseid,
 date_format(date, '%b%y') as colhead
-from t2   
-join t1 on t2.courseid=t1.courseid  
+from t2
+join t1 on t2.courseid=t1.courseid
 join
 (
-select 
-t5.userid,  
-parentid,  
-parentgroup,  
-childid,  
-groupname,  
-grouptypeid  
-from t5 
-join 
+select
+t5.userid,
+parentid,
+parentgroup,
+childid,
+groupname,
+grouptypeid
+from t5
+join
 (
-select t4.id as parentid,  
-t4.name as parentgroup,  
-t4.id as childid,  
-t4.name as groupname,  
-t4.grouptypeid  
-from   t4  
-) as gin on t5.groupid=gin.childid 
-) as groupstuff on t2.userid = groupstuff.userid 
-group by 
+select t4.id as parentid,
+t4.name as parentgroup,
+t4.id as childid,
+t4.name as groupname,
+t4.grouptypeid
+from t4
+) as gin on t5.groupid=gin.childid
+) as groupstuff on t2.userid = groupstuff.userid
+group by
 groupstuff.groupname, colhead , t2.courseid;
 pass	userid	parentid	parentgroup	childid	groupname	grouptypeid	crse	categoryid	courseid	colhead
 1	5141	12	group2	12	group2	5	1	5	12	Aug04
@@ -2929,9 +2929,9 @@ INSERT INTO t1 VALUES("0037", "1", "2005-12-06 12:18:56");
 INSERT INTO t1 VALUES("0037", "2", "2006-01-06 12:25:53");
 INSERT INTO t1 VALUES("0048", "1", "2006-01-06 12:37:50");
 INSERT INTO t1 VALUES("0059", "1", "2006-01-06 12:37:50");
-select * from t1 r1 
-where (r1.retailerID,(r1.changed)) in 
-(SELECT r2.retailerId,(max(changed)) from t1 r2 
+select * from t1 r1
+where (r1.retailerID,(r1.changed)) in
+(SELECT r2.retailerId,(max(changed)) from t1 r2
 group by r2.retailerId);
 retailerID	statusID	changed
 0026	2	2006-01-06 12:25:53
@@ -2943,41 +2943,41 @@ create table t1(a int, primary key (a));
 insert into t1 values (10);
 create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
 insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
-explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
-ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
-ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
+explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
+             ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	PRIMARY	r	const	PRIMARY	PRIMARY	4	const	1	
 2	DEPENDENT SUBQUERY	t2	range	b	b	40	NULL	2	Using where
-SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
-ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
-ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
+SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
+            ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 a	a	b
 10	3	35989
-explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
-ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
-ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
+explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
+            ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	PRIMARY	r	const	PRIMARY	PRIMARY	4	const	1	
 2	DEPENDENT SUBQUERY	t2	range	b	b	40	NULL	2	Using where
-SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
-ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
-ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
+SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
+            ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 a	a	b
 10	1	359
 drop table t1,t2;
-CREATE TABLE t1 (                  
-field1 int NOT NULL,                 
-field2 int NOT NULL,                 
-field3 int NOT NULL,                 
-PRIMARY KEY  (field1,field2,field3)  
+CREATE TABLE t1 (
+field1 int NOT NULL,
+field2 int NOT NULL,
+field3 int NOT NULL,
+PRIMARY KEY  (field1,field2,field3)
 );
-CREATE TABLE t2 (             
-fieldA int NOT NULL,            
-fieldB int NOT NULL,            
-PRIMARY KEY  (fieldA,fieldB)     
+CREATE TABLE t2 (
+fieldA int NOT NULL,
+fieldB int NOT NULL,
+PRIMARY KEY  (fieldA,fieldB)
 );
 INSERT INTO t1 VALUES
 (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
@@ -2991,14 +2991,14 @@ field1	field2	COUNT(*)
 SELECT field1, field2
 FROM  t1
 GROUP BY field1, field2
-HAVING COUNT(*) >= ALL (SELECT fieldB 
+HAVING COUNT(*) >= ALL (SELECT fieldB
 FROM t2 WHERE fieldA = field1);
 field1	field2
 1	2
 SELECT field1, field2
 FROM  t1
 GROUP BY field1, field2
-HAVING COUNT(*) < ANY (SELECT fieldB 
+HAVING COUNT(*) < ANY (SELECT fieldB
 FROM t2 WHERE fieldA = field1);
 field1	field2
 1	1
@@ -3021,8 +3021,8 @@ a	a IN (SELECT a FROM t1)
 DROP TABLE t1,t2;
 CREATE TABLE t1 (a DATETIME);
 INSERT INTO t1 VALUES ('1998-09-23'), ('2003-03-25');
-CREATE TABLE t2 AS SELECT 
-(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a 
+CREATE TABLE t2 AS SELECT
+(SELECT a FROM t1 WHERE a < '2000-01-01') AS sub_a
 FROM t1 WHERE a > '2000-01-01';
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -3188,7 +3188,7 @@ INSERT INTO t2 VALUES ( 6 );
 CREATE TABLE t3 ( c3 integer );
 INSERT INTO t3 VALUES ( 7 );
 INSERT INTO t3 VALUES ( 8 );
-SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 
+SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
 WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
 c1	c2
 2	NULL
@@ -3231,20 +3231,20 @@ E1
 DROP TABLE t1,t2;
 CREATE TABLE t1(select_id BIGINT, values_id BIGINT);
 INSERT INTO t1 VALUES (1, 1);
-CREATE TABLE t2 (select_id BIGINT, values_id BIGINT, 
+CREATE TABLE t2 (select_id BIGINT, values_id BIGINT,
 PRIMARY KEY(select_id,values_id));
 INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5);
-SELECT values_id FROM t1 
+SELECT values_id FROM t1
 WHERE values_id IN (SELECT values_id FROM t2
 WHERE select_id IN (1, 0));
 values_id
 1
-SELECT values_id FROM t1 
+SELECT values_id FROM t1
 WHERE values_id IN (SELECT values_id FROM t2
 WHERE select_id BETWEEN 0 AND 1);
 values_id
 1
-SELECT values_id FROM t1 
+SELECT values_id FROM t1
 WHERE values_id IN (SELECT values_id FROM t2
 WHERE select_id = 0 OR select_id = 1);
 values_id
@@ -3259,7 +3259,7 @@ drop table t1;
 CREATE TABLE t1 (a int, b int);
 CREATE TABLE t2 (c int, d int);
 CREATE TABLE t3 (e int);
-INSERT INTO t1 VALUES 
+INSERT INTO t1 VALUES
 (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
 INSERT INTO t2 VALUES
 (2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
@@ -3322,7 +3322,7 @@ a
 2
 SELECT a FROM t1 GROUP BY a
 HAVING a IN (SELECT c FROM t2
-WHERE MIN(b) < d AND 
+WHERE MIN(b) < d AND
 EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
 a
 2
@@ -3373,7 +3373,7 @@ a
 4
 SELECT t1.a FROM t1 GROUP BY t1.a
 HAVING t1.a > ALL(SELECT t2.c FROM t2
-WHERE EXISTS(SELECT t3.e FROM t3 
+WHERE EXISTS(SELECT t3.e FROM t3
 WHERE SUM(t1.a+t2.c) < t3.e/4));
 ERROR HY000: Invalid use of group function
 SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20;
@@ -3486,7 +3486,7 @@ mid bigint(20) unsigned NOT NULL,
 date date NOT NULL,
 PRIMARY KEY  (id)
 );
-INSERT INTO t2 VALUES 
+INSERT INTO t2 VALUES
 (1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
 (4, 2, '2006-04-20'), (5, 1, '2006-05-01');
 SELECT *,
@@ -3524,7 +3524,7 @@ i2 int(11) NOT NULL default '0',
 t datetime NOT NULL default '0000-00-00 00:00:00',
 PRIMARY KEY  (i1,i2,t)
 );
-INSERT INTO t1 VALUES 
+INSERT INTO t1 VALUES
 (24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
 (24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
 (24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
@@ -3540,7 +3540,7 @@ PRIMARY KEY  (i1)
 INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
 EXPLAIN
 SELECT * FROM t1,t2
-WHERE t1.t = (SELECT t1.t FROM t1 
+WHERE t1.t = (SELECT t1.t FROM t1
 WHERE t1.t < t2.t  AND t1.i2=1 AND t2.i1=t1.i1
 ORDER BY t1.t DESC LIMIT 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -3548,7 +3548,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	PRIMARY	16	NULL	11	Using where; Using index
 2	DEPENDENT SUBQUERY	t1	range	PRIMARY	PRIMARY	16	NULL	5	Using where; Using index
 SELECT * FROM t1,t2
-WHERE t1.t = (SELECT t1.t FROM t1 
+WHERE t1.t = (SELECT t1.t FROM t1
 WHERE t1.t < t2.t  AND t1.i2=1 AND t2.i1=t1.i1
 ORDER BY t1.t DESC LIMIT 1);
 i1	i2	t	i1	i2	t
@@ -3557,22 +3557,22 @@ DROP TABLE t1, t2;
 CREATE TABLE t1 (i INT);
 (SELECT i FROM t1) UNION (SELECT i FROM t1);
 i
-SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS 
+SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS
 (
-(SELECT i FROM t1) UNION 
+(SELECT i FROM t1) UNION
 (SELECT i FROM t1)
 );
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION 
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
 (SELECT i FROM t1)
 )' at line 3
-SELECT * FROM t1 
+SELECT * FROM t1
 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT i FROM t1)))' at line 2
 explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
 from t1;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))
 from t1' at line 1
-explain select * from t1 where not exists 
+explain select * from t1 where not exists
 ((select t11.i from t1 t11) union (select t12.i from t1 t12));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select t12.i from t1 t12))' at line 2
 DROP TABLE t1;
@@ -3591,9 +3591,9 @@ insert into t1 (a) select FLOOR(rand() * 100) from t1;
 insert into t1 (a) select FLOOR(rand() * 100) from t1;
 insert into t1 (a) select FLOOR(rand() * 100) from t1;
 insert into t1 (a) select FLOOR(rand() * 100) from t1;
-SELECT a, 
-(SELECT REPEAT(' ',250) FROM t1 i1 
-WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a 
+SELECT a,
+(SELECT REPEAT(' ',250) FROM t1 i1
+WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a
 FROM t1 ORDER BY a LIMIT 5;
 a	a
 0	NULL
@@ -3622,7 +3622,7 @@ COUNT(DISTINCT t1.b)	(SELECT COUNT(DISTINCT t1.b))
 2	2
 1	1
 1	1
-SELECT COUNT(DISTINCT t1.b), 
+SELECT COUNT(DISTINCT t1.b),
 (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
 FROM t1 GROUP BY t1.a;
 COUNT(DISTINCT t1.b)	(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
@@ -3633,7 +3633,7 @@ SELECT (
 SELECT (
 SELECT COUNT(DISTINCT t1.b)
 )
-) 
+)
 FROM t1 GROUP BY t1.a;
 (
 SELECT (
@@ -3648,8 +3648,8 @@ SELECT (
 SELECT (
 SELECT COUNT(DISTINCT t1.b)
 )
-) 
-FROM t1 GROUP BY t1.a LIMIT 1) 
+)
+FROM t1 GROUP BY t1.a LIMIT 1)
 FROM t1 t2
 GROUP BY t2.a;
 (
@@ -3657,7 +3657,7 @@ SELECT (
 SELECT (
 SELECT COUNT(DISTINCT t1.b)
 )
-) 
+)
 FROM t1 GROUP BY t1.a LIMIT 1)
 2
 2
@@ -3669,13 +3669,13 @@ PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
 SET SESSION sort_buffer_size = 32 * 1024;
 Warnings:
 Warning	1292	Truncated incorrect sort_buffer_size value: '32768'
-SELECT SQL_NO_CACHE COUNT(*) 
+SELECT SQL_NO_CACHE COUNT(*)
 FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
 FROM t1) t;
 COUNT(*)
 3000
 SET SESSION sort_buffer_size = 8 * 1024 * 1024;
-SELECT SQL_NO_CACHE COUNT(*) 
+SELECT SQL_NO_CACHE COUNT(*)
 FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
 FROM t1) t;
 COUNT(*)
@@ -3736,7 +3736,7 @@ sq
 2
 4
 DEALLOCATE PREPARE stmt1;
-SELECT f2, AVG(f21), 
+SELECT f2, AVG(f21),
 (SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test
 FROM t2 GROUP BY f2;
 f2	AVG(f21)	test
@@ -3744,12 +3744,12 @@ f2	AVG(f21)	test
 2	2.0000	2004-02-29 11:11:11
 DROP TABLE t1,t2;
 CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL);
-INSERT INTO t1 VALUES                                                         
-(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),                      
-(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'),                      
+INSERT INTO t1 VALUES
+(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'),
 (3,2,'k'), (3,1,'l'), (1,9,'m');
-SELECT a, MAX(b),                                                             
-(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test      
+SELECT a, MAX(b),
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test
 FROM t1 GROUP BY a;
 a	MAX(b)	test
 1	9	m
@@ -3900,7 +3900,7 @@ COUNT(*)	a	(SELECT MIN(m) FROM t2 WHERE m = count(*))
 2	2	2
 3	3	3
 1	4	1
-SELECT COUNT(*), a       
+SELECT COUNT(*), a
 FROM t1 GROUP BY a
 HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
 COUNT(*)	a
@@ -3931,7 +3931,7 @@ INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
 (1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
 (3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
 SELECT a, MAX(b),
-(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test 
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
 FROM t1 GROUP BY a;
 a	MAX(b)	test
 1	9	m
@@ -3953,7 +3953,7 @@ a	AVG(b)	test
 3	2.5000	NULL
 SELECT tt.a,
 (SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
-LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test 
+LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
 FROM t1 as tt;
 a	test
 1	n
@@ -3975,7 +3975,7 @@ a	test
 SELECT tt.a,
 (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
 LIMIT 1)
-FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test 
+FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
 FROM t1 as tt GROUP BY tt.a;
 a	test
 1	n
@@ -3984,7 +3984,7 @@ a	test
 SELECT tt.a, MAX(
 (SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
 LIMIT 1)
-FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test 
+FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
 FROM t1 as tt GROUP BY tt.a;
 a	test
 1	n
@@ -4027,11 +4027,11 @@ COUNT(1)
 1
 SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) FROM t1;
 ERROR HY000: Invalid use of group function
-SELECT 
+SELECT
 SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) )
 FROM t1;
 ERROR HY000: Invalid use of group function
-SELECT t1.a as XXA, 
+SELECT t1.a as XXA,
 SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
 FROM t1;
 ERROR HY000: Invalid use of group function
@@ -4048,25 +4048,25 @@ INSERT INTO t1 VALUES
 (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
 CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
 INSERT INTO t2 VALUES (7), (5), (1), (3);
-SELECT id, st FROM t1 
+SELECT id, st FROM t1
 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
 id	st
 3	FL
 1	GA
 7	FL
-SELECT id, st FROM t1 
+SELECT id, st FROM t1
 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
 GROUP BY id;
 id	st
 1	GA
 3	FL
 7	FL
-SELECT id, st FROM t1 
+SELECT id, st FROM t1
 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
 id	st
 2	GA
 4	FL
-SELECT id, st FROM t1 
+SELECT id, st FROM t1
 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
 GROUP BY id;
 id	st
@@ -4237,7 +4237,7 @@ a	b
 DROP TABLE t1,t2;
 CREATE TABLE t1(a INT, b INT);
 INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
-EXPLAIN 
+EXPLAIN
 SELECT a AS out_a, MIN(b) FROM t1
 WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
 GROUP BY a;
@@ -4246,7 +4246,7 @@ SELECT a AS out_a, MIN(b) FROM t1
 WHERE b > (SELECT MIN(b) FROM t1 WHERE a = out_a)
 GROUP BY a;
 ERROR 42S22: Unknown column 'out_a' in 'where clause'
-EXPLAIN 
+EXPLAIN
 SELECT a AS out_a, MIN(b) FROM t1 t1_outer
 WHERE b > (SELECT MIN(b) FROM t1 WHERE a = t1_outer.a)
 GROUP BY a;
@@ -4277,16 +4277,16 @@ Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 Note	1003	select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))
 EXPLAIN EXTENDED
-SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION 
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
 (SELECT 1 FROM t2 WHERE t1.a = t2.a));
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION 
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
 (SELECT 1 FROM t2 WHERE t1.a = t2.a))' at line 2
 DROP TABLE t1,t2;
 create table t1(f11 int, f12 int);
 create table t2(f21 int unsigned not null, f22 int, f23 varchar(10));
 insert into t1 values(1,1),(2,2), (3, 3);
 set session sort_buffer_size= 33*1024;
-select count(*) from t1 where f12 = 
+select count(*) from t1 where f12 =
 (select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1);
 count(*)
 3
@@ -4327,12 +4327,12 @@ IF(
 FROM t2 VPC, t4 a2, t2 a3
 WHERE
 VPC.f4 = a2.f10 AND a3.f2 = a4
-LIMIT 1) IS NULL, 
-0, 
+LIMIT 1) IS NULL,
+0,
 t3.f5
 )
 ) AS a6
-FROM 
+FROM
 t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4
 GROUP BY a4;
 a4	f3	a6
@@ -4341,7 +4341,7 @@ a4	f3	a6
 DROP TABLE t1, t2, t3, t4;
 create table t1 (a float(5,4) zerofill);
 create table t2 (a float(5,4),b float(2,0));
-select t1.a from t1 where   
+select t1.a from t1 where
 t1.a= (select b from t2 limit 1) and not
 t1.a= (select a from t2 limit 1) ;
 a
@@ -4362,7 +4362,7 @@ Warnings:
 Note	1003	select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
 DROP TABLE t1;
 End of 5.0 tests.
-CREATE TABLE t1 (a int, b int);
+CREATE TABLE t1 (a INT, b INT);
 INSERT INTO t1 VALUES (2,22),(1,11),(2,22);
 SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
 a
@@ -4385,22 +4385,22 @@ WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
 ERROR HY000: Invalid use of group function
 SET @@sql_mode=default;
 DROP TABLE t1;
-CREATE TABLE t1 (s1 char(1));
+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(c int, KEY(c));
-CREATE TABLE t2(a int, b int);
+CREATE TABLE t1(c INT, KEY(c));
+CREATE TABLE t2(a INT, b INT);
 INSERT INTO t2 VALUES (1, 10), (2, NULL);
 INSERT INTO t1 VALUES (1), (3);
 SELECT * FROM t2 WHERE b NOT IN (SELECT max(t.c) FROM t1, t1 t WHERE t.c>10);
 a	b
 DROP TABLE t1,t2;
-CREATE TABLE t1(pk int PRIMARY KEY, a int, INDEX idx(a));
+CREATE TABLE t1(pk INT PRIMARY KEY, a INT, INDEX idx(a));
 INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20);
-CREATE TABLE t2(pk int PRIMARY KEY, a int, b int, INDEX idxa(a));
+CREATE TABLE t2(pk INT PRIMARY KEY, a INT, b INT, INDEX idxa(a));
 INSERT INTO t2 VALUES (2, 20, 700), (1, 10, 200), (4, 10, 100);
 SELECT * FROM t1
 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
@@ -4409,36 +4409,36 @@ pk	a
 3	30
 2	20
 DROP TABLE t1,t2;
-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY b (b));
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b));
 INSERT INTO t1 VALUES (1,NULL), (9,NULL);
 CREATE TABLE t2 (
-a int,
-b int,
-c int,
-d int,
+a INT,
+b INT,
+c INT,
+d INT,
 PRIMARY KEY (a),
 UNIQUE KEY b (b,c,d),
 KEY b_2 (b),
 KEY c (c),
 KEY d (d)
 );
-INSERT INTO t2 VALUES 
+INSERT INTO t2 VALUES
 (43, 2, 11 ,30),
 (44, 2, 12 ,30),
 (45, 1, 1  ,10000),
 (46, 1, 2  ,10000),
 (556,1, 32 ,10000);
 CREATE TABLE t3 (
-a int,
-b int,
-c int,
+a INT,
+b INT,
+c INT,
 PRIMARY KEY (a),
 UNIQUE KEY b (b,c),
 KEY c (c),
 KEY b_2 (b)
 );
 INSERT INTO t3 VALUES (1,1,1), (2,32,1);
-explain 
+explain
 SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	index	b,b_2	b	10	NULL	2	Using index
@@ -4479,7 +4479,7 @@ DELETE FROM v3;
 DROP VIEW v1,v2,v3;
 DROP TABLE t1,t2;
 #
-# BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
+# Bug#37822 Correlated subquery with IN and IS UNKNOWN provides wrong result
 #
 create table t1(id integer primary key, g integer, v integer, s char(1));
 create table t2(id integer primary key, g integer, v integer, s char(1));
diff --git a/mysql-test/r/synchronization.result b/mysql-test/r/synchronization.result
index 5d8585f1f88..3f6d16178dd 100644
--- a/mysql-test/r/synchronization.result
+++ b/mysql-test/r/synchronization.result
@@ -1,6 +1,6 @@
-drop table if exists t1,t2;
-CREATE TABLE t1 (x1 int);
-ALTER TABLE t1 CHANGE x1 x2 int;
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1 (x1 INT);
+ALTER TABLE t1 CHANGE x1 x2 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -8,7 +8,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x2 x1 int;
+ALTER TABLE t1 CHANGE x2 x1 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -16,7 +16,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x1 x2 int;
+ALTER TABLE t1 CHANGE x1 x2 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -24,7 +24,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x2 x1 int;
+ALTER TABLE t1 CHANGE x2 x1 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -32,7 +32,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x1 x2 int;
+ALTER TABLE t1 CHANGE x1 x2 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -40,7 +40,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x2 x1 int;
+ALTER TABLE t1 CHANGE x2 x1 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -48,7 +48,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x1 x2 int;
+ALTER TABLE t1 CHANGE x1 x2 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -56,7 +56,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x2 x1 int;
+ALTER TABLE t1 CHANGE x2 x1 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -64,7 +64,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x1 x2 int;
+ALTER TABLE t1 CHANGE x1 x2 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -72,7 +72,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x2 x1 int;
+ALTER TABLE t1 CHANGE x2 x1 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -80,7 +80,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x1 x2 int;
+ALTER TABLE t1 CHANGE x1 x2 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -88,7 +88,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x2 x1 int;
+ALTER TABLE t1 CHANGE x2 x1 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -96,7 +96,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x1 x2 int;
+ALTER TABLE t1 CHANGE x1 x2 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -104,7 +104,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x2 x1 int;
+ALTER TABLE t1 CHANGE x2 x1 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -112,7 +112,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x1 x2 int;
+ALTER TABLE t1 CHANGE x1 x2 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -120,7 +120,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x2 x1 int;
+ALTER TABLE t1 CHANGE x2 x1 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -128,7 +128,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x1 x2 int;
+ALTER TABLE t1 CHANGE x1 x2 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -136,7 +136,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x2 x1 int;
+ALTER TABLE t1 CHANGE x2 x1 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -144,7 +144,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x1 x2 int;
+ALTER TABLE t1 CHANGE x1 x2 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
@@ -152,7 +152,7 @@ t2	CREATE TABLE `t2` (
   `xx` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
-ALTER TABLE t1 CHANGE x2 x1 int;
+ALTER TABLE t1 CHANGE x2 x1 INT;
 CREATE TABLE t2 LIKE t1;
 SHOW CREATE TABLE t2;
 Table	Create Table