After merge fixes of merge with 4.1 that included the new arena code.

Fixed (together with Guilhem) bugs in mysqlbinlog regarding --offset
Prefix addresses with 0x for easier comparisons of debug logs
Fixed problem where MySQL choosed index-read even if there would be a much better range on the same index
This fix changed some 'index' queries to 'range' queries in the test suite
Don't create 'dummy' WHERE clause for trivial WHERE clauses where we can remove the WHERE clause.
This fix removed of a lot of 'Using where' notes in the test suite.
Give NOTE instead of WARNING if table/function doesn't exists when using DROP IF EXISTS
Give NOTE instead of WARNING for safe field-type conversions

22 files changed, 258 insertions, 149 deletions

diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result
index d510ec53dfc..6713d90e4f9 100644
--- a/mysql-test/r/bdb.result
+++ b/mysql-test/r/bdb.result
@@ -146,7 +146,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	level	level	1	const	1	Using index
 explain select level,id,parent_id from t1 where level=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	level	level	1	const	1	Using where
+1	SIMPLE	t1	ref	level	level	1	const	1	
 select level,id from t1 where level=1;
 level	id
 1	1002
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 92c825f547d..8131399872a 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -298,7 +298,7 @@ select * from t3;
 id	name
 create table if not exists t3 like t1;
 Warnings:
-Warning	1050	Table 't3' already exists
+Note	1050	Table 't3' already exists
 select @@warning_count;
 @@warning_count
 1
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 2ec389032f7..9af8fa8c8cc 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -586,7 +586,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain
 select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	k1	k1	3	const	2	Using where; Using index
+1	SIMPLE	t1	range	k1	k1	7	NULL	1	Using where; Using index
 1	SIMPLE	t2	range	k1	k1	3	NULL	4	Using where; Using index
 explain
 select min(a4 - 0.01) from t1;
diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result
index 307d1021cdb..83e103fe57d 100644
--- a/mysql-test/r/func_if.result
+++ b/mysql-test/r/func_if.result
@@ -43,7 +43,7 @@ explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where; Using filesort
 Warnings:
-Note	1003	select if((test.t1.u = 1),test.t1.st,cast(test.t1.st as char charset binary)) AS `s` from test.t1 where (test.t1.st like _latin1'%a%') order by if((test.t1.u = 1),test.t1.st,cast(test.t1.st as char charset binary))
+Note	1003	select if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) AS `s` from `test`.`t1` where (`test`.`t1`.`st` like _latin1'%a%') order by if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary))
 select nullif(u=0, 'test') from t1;
 nullif(u=0, 'test')
 NULL
diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result
index 03625d19d04..8f9349ce316 100644
--- a/mysql-test/r/heap_btree.result
+++ b/mysql-test/r/heap_btree.result
@@ -73,7 +73,7 @@ engine=heap;
 insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
 explain select * from t1 where x=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	x	x	4	const	1	Using where
+1	SIMPLE	t1	ref	x	x	4	const	1	
 select * from t1 where x=1;
 x	y
 1	1
@@ -134,7 +134,7 @@ a	b
 1	1
 explain select * from tx where b=x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-x	SIMPLE	tx	ref	b	b	x	const	x	Using where
+x	SIMPLE	tx	ref	b	b	x	const	x	
 drop table t1;
 create table t1 (id int unsigned not null, primary key  using BTREE (id)) engine=HEAP;
 insert into t1 values(1);
diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result
index f3654db030c..cb4b285430c 100644
--- a/mysql-test/r/index_merge.result
+++ b/mysql-test/r/index_merge.result
@@ -264,11 +264,11 @@ select * from t0,t1 where (t0.key1=t1.key1) and
 (t0.key1=3 or t0.key2<4) and t1.key1=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	ref	i1,i2	i1	4	const	1	Using where
-1	SIMPLE	t1	ref	i1	i1	4	const	1	Using where
+1	SIMPLE	t1	ref	i1	i1	4	const	1	
 explain select * from t0,t1 where t0.key1 = 5 and 
 (t1.key1 = t0.key1 or t1.key8 = t0.key1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ref	i1	i1	4	const	1	Using where
+1	SIMPLE	t0	ref	i1	i1	4	const	1	
 1	SIMPLE	t1	index_merge	i1,i8	i1,i8	4,4	NULL	2	Using union(i1,i8); Using where
 explain select * from t0,t1 where t0.key1 < 3 and
 (t1.key1 = t0.key1 or t1.key8 = t0.key1);
diff --git a/mysql-test/r/index_merge_ror.result b/mysql-test/r/index_merge_ror.result
index 15ad1026ca0..6fc9dddef88 100644
--- a/mysql-test/r/index_merge_ror.result
+++ b/mysql-test/r/index_merge_ror.result
@@ -130,7 +130,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b	st_b	4	const	14720	Using where
 explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a	sta_swt12a	12	const,const,const	958	Using where
+1	SIMPLE	t1	ref	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a	sta_swt12a	12	const,const,const	958	
 explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	stb_swt1a_2b,stb_swt1b,st_b	stb_swt1b	8	const,const	3757	Using where
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index b407c1537bc..b46626d0322 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -141,13 +141,13 @@ id	parent_id	level
 1015	102	2
 explain select level from t1 where level=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	level	level	1	const	#	Using where; Using index
+1	SIMPLE	t1	ref	level	level	1	const	#	Using index
 explain select level,id from t1 where level=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	level	level	1	const	#	Using where; Using index
+1	SIMPLE	t1	ref	level	level	1	const	#	Using index
 explain select level,id,parent_id from t1 where level=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	level	level	1	const	#	Using where
+1	SIMPLE	t1	ref	level	level	1	const	#	
 select level,id from t1 where level=1;
 level	id
 1	1002
@@ -609,7 +609,7 @@ id	parent_id	level
 1016	102	2
 explain select level from t1 where level=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	level	level	1	const	#	Using where; Using index
+1	SIMPLE	t1	ref	level	level	1	const	#	Using index
 select level,id from t1 where level=1;
 level	id
 1	1004
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index ec377b92371..6bafe533fac 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -628,7 +628,7 @@ insert into t2 values (10,1),(20,2),(30,3);
 explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	3	Using index
-1	SIMPLE	t1	const	PRIMARY	PRIMARY	2	const	1	Using where; Using index
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	2	const	1	Using index
 select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
 fooID	barID	fooID
 10	1	NULL
@@ -703,13 +703,13 @@ explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from
 order by m.match_id desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	s	ALL	NULL	NULL	NULL	NULL	10	
-1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	Using where
+1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
 explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
 (t2 s left join t1 m on m.match_id = 1) 
 order by UUX desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	s	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
-1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	Using where
+1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
 select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
 (t2 s left join t1 m on m.match_id = 1) 
 order by UUX desc;
@@ -729,7 +729,7 @@ t2 s straight_join t1 m where m.match_id = 1
 order by UUX desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	s	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
-1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	Using where
+1	SIMPLE	m	const	match_id,match_id_2	match_id	1	const	1	
 select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
 t2 s straight_join t1 m where m.match_id = 1 
 order by UUX desc;
diff --git a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result
index 3c1b85e05a1..9fe4edf7b82 100644
--- a/mysql-test/r/mysqlbinlog2.result
+++ b/mysql-test/r/mysqlbinlog2.result
@@ -18,6 +18,8 @@ insert into t1 values(null, "f");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -40,6 +42,8 @@ insert into t1 values(null, "e");
 SET INSERT_ID=1;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "a");
 SET INSERT_ID=2;
 SET TIMESTAMP=1579609942;
@@ -58,6 +62,8 @@ insert into t1 values(null, "e");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609946;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "d");
 SET INSERT_ID=5;
 SET TIMESTAMP=1579609946;
@@ -67,6 +73,8 @@ insert into t1 values(null, "e");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -84,6 +92,8 @@ SET INSERT_ID=4;
 SET INSERT_ID=3;
 use test;
 SET TIMESTAMP=1579609944;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "c");
 SET INSERT_ID=4;
 SET TIMESTAMP=1579609946;
@@ -96,6 +106,8 @@ insert into t1 values(null, "e");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -108,6 +120,8 @@ insert into t1 values(null, "b");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -127,6 +141,8 @@ insert into t1 values(null, "e");
 SET INSERT_ID=6;
 use test;
 SET TIMESTAMP=1579609943;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "f");
 
 --- offset --
@@ -134,6 +150,8 @@ insert into t1 values(null, "f");
 SET INSERT_ID=1;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "a");
 SET INSERT_ID=2;
 SET TIMESTAMP=1579609942;
@@ -150,12 +168,16 @@ insert into t1 values(null, "e");
 SET INSERT_ID=6;
 use test;
 SET TIMESTAMP=1579609943;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "f");
 
 --- start-position --
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609946;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "d");
 SET INSERT_ID=5;
 SET TIMESTAMP=1579609946;
@@ -163,12 +185,16 @@ insert into t1 values(null, "e");
 SET INSERT_ID=6;
 use test;
 SET TIMESTAMP=1579609943;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "f");
 
 --- stop-position --
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -192,6 +218,8 @@ SET INSERT_ID=6;
 SET INSERT_ID=3;
 use test;
 SET TIMESTAMP=1579609944;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "c");
 SET INSERT_ID=4;
 SET TIMESTAMP=1579609946;
@@ -202,12 +230,16 @@ insert into t1 values(null, "e");
 SET INSERT_ID=6;
 use test;
 SET TIMESTAMP=1579609943;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "f");
 
 --- stop-datetime --
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -220,6 +252,8 @@ insert into t1 values(null, "b");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -242,6 +276,8 @@ insert into t1 values(null, "e");
 SET INSERT_ID=1;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "a");
 SET INSERT_ID=2;
 SET TIMESTAMP=1579609942;
@@ -260,6 +296,8 @@ insert into t1 values(null, "e");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609946;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "d");
 SET INSERT_ID=5;
 SET TIMESTAMP=1579609946;
@@ -269,6 +307,8 @@ insert into t1 values(null, "e");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -286,6 +326,8 @@ SET INSERT_ID=4;
 SET INSERT_ID=3;
 use test;
 SET TIMESTAMP=1579609944;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "c");
 SET INSERT_ID=4;
 SET TIMESTAMP=1579609946;
@@ -298,6 +340,8 @@ insert into t1 values(null, "e");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -310,6 +354,8 @@ insert into t1 values(null, "b");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -329,6 +375,8 @@ insert into t1 values(null, "e");
 SET INSERT_ID=6;
 use test;
 SET TIMESTAMP=1579609943;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "f");
 
 --- offset --
@@ -336,6 +384,8 @@ insert into t1 values(null, "f");
 SET INSERT_ID=1;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "a");
 SET INSERT_ID=2;
 SET TIMESTAMP=1579609942;
@@ -352,12 +402,16 @@ insert into t1 values(null, "e");
 SET INSERT_ID=6;
 use test;
 SET TIMESTAMP=1579609943;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "f");
 
 --- start-position --
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609946;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "d");
 SET INSERT_ID=5;
 SET TIMESTAMP=1579609946;
@@ -365,12 +419,16 @@ insert into t1 values(null, "e");
 SET INSERT_ID=6;
 use test;
 SET TIMESTAMP=1579609943;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "f");
 
 --- stop-position --
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -394,6 +452,8 @@ SET INSERT_ID=6;
 SET INSERT_ID=3;
 use test;
 SET TIMESTAMP=1579609944;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "c");
 SET INSERT_ID=4;
 SET TIMESTAMP=1579609946;
@@ -404,12 +464,16 @@ insert into t1 values(null, "e");
 SET INSERT_ID=6;
 use test;
 SET TIMESTAMP=1579609943;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 insert into t1 values(null, "f");
 
 --- stop-datetime --
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
@@ -422,6 +486,8 @@ insert into t1 values(null, "b");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 use test;
 SET TIMESTAMP=1579609942;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
+SET @@session.sql_mode=0;
 create table t1 (a int auto_increment not null primary key, b char(3));
 SET INSERT_ID=1;
 SET TIMESTAMP=1579609942;
diff --git a/mysql-test/r/negation_elimination.result b/mysql-test/r/negation_elimination.result
index 9193a125cd1..a7dbe0d9052 100644
--- a/mysql-test/r/negation_elimination.result
+++ b/mysql-test/r/negation_elimination.result
@@ -387,5 +387,5 @@ explain extended select a, not(not(a)), not(a <= 2 and not(a)), not(a not like "
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	a	5	NULL	5	Using where; Using index
 Warnings:
-Note	1003	select test.t1.a AS `a`,(test.t1.a <> 0) AS `not(not(a))`,((test.t1.a > 2) or test.t1.a) AS `not(a <= 2 and not(a))`,(test.t1.a like _latin1'1') AS `not(a not like "1")`,(test.t1.a in (1,2)) AS `not (a not in (1,2))`,(test.t1.a = 2) AS `not(a != 2)` from test.t1 where test.t1.a having test.t1.a
+Note	1003	select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like _latin1'1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where `test`.`t1`.`a` having `test`.`t1`.`a`
 drop table t1;
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index bd90b3fe3f3..ced80a033e7 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -167,11 +167,20 @@ insert into t1 select i*2 from t1;
 insert into t1 select i*2 from t1;
 insert into t1 select i*2 from t1;
 insert into t1 select i*2 from t1;
+insert into t1 values(null);
 explain select * from t1 where i=2 or i is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref_or_null	i	i	5	const	10	Using where; Using index
+select count(*) from t1 where i=2 or i is null;
+count(*)
+10
 alter table t1 change i i int not null;
+Warnings:
+Warning	1265	Data truncated for column 'i' at row 513
 explain select * from t1 where i=2 or i is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	i	i	4	const	7	Using where; Using index
+1	SIMPLE	t1	ref	i	i	4	const	8	Using index
+select count(*) from t1 where i=2 or i is null;
+count(*)
+9
 drop table t1;
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 3044b188d06..7980c3f7f91 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -33,10 +33,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	2	Using where; Using index
 explain select * from t1 where a > 1 and a < 3 limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	a	a	9	NULL	12	Using where; Using index
+1	SIMPLE	t1	range	a	a	5	NULL	1	Using where; Using index
 explain select * from t1 where a > 8 and a < 9;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	a	a	9	NULL	12	Using where; Using index
+1	SIMPLE	t1	range	a	a	5	NULL	1	Using where; Using index
 select * from t1 where a is null;
 a	b
 NULL	7
@@ -66,6 +66,11 @@ a	b
 NULL	7
 NULL	9
 NULL	9
+select * from t1 where a > 1 and a < 3 limit 1;
+a	b
+2	2
+select * from t1 where a > 8 and a < 9;
+a	b
 create table t2 like t1;
 insert into t2 select * from t1;
 alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
@@ -153,12 +158,12 @@ a	b
 7	NULL
 explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,b	a	10	NULL	3	Using where; Using index
+1	SIMPLE	t1	ref_or_null	a,b	a	5	const	4	Using where; Using index
 select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
 a	b
-NULL	7
 7	NULL
 7	7
+NULL	7
 explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref_or_null	a	a	5	const	5	Using where; Using index
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index a431807a560..e010f4a42e2 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -328,7 +328,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a	a	9	NULL	5	Using where; Using index
 explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a	a	4	const	2	Using where; Using index
+1	SIMPLE	t1	range	a	a	9	NULL	2	Using where; Using index
 explain select * from t1 where a = 1 order by b desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a	a	4	const	5	Using where; Using index
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 01ee018a434..31c65f64256 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -257,6 +257,11 @@ explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	j1	j1	4	const	1	Using index
 1	SIMPLE	t1	ALL	i1	NULL	NULL	NULL	4	Range checked for each record (index map: 0x1)
+explain select * from t1 force index(i1), t2 force index(j1) where 
+(t1.key1 <t2.keya + 1) and t2.keya=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	j1	j1	4	const	1	Using index
+1	SIMPLE	t1	ALL	i1	NULL	NULL	NULL	4	Range checked for each record (index map: 0x1)
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 a int(11) default NULL,
@@ -533,8 +538,7 @@ count(*)
 0
 drop table t1;
 create table t1 (x bigint unsigned not null primary key) engine=innodb;
-insert into t1(x) values (0xfffffffffffffff0);
-insert into t1(x) values (0xfffffffffffffff1);
+insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
 select * from t1;
 x
 18446744073709551600
@@ -554,6 +558,9 @@ count(*)
 select count(*) from t1 where x = -16;
 count(*)
 0
+explain select count(*) from t1 where x > -16;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	8	NULL	1	Using where; Using index
 select count(*) from t1 where x > -16;
 count(*)
 1
diff --git a/mysql-test/r/rpl_charset.result b/mysql-test/r/rpl_charset.result
index d961173f849..a427e77a18d 100644
--- a/mysql-test/r/rpl_charset.result
+++ b/mysql-test/r/rpl_charset.result
@@ -105,62 +105,62 @@ drop database mysqltest2;
 drop database mysqltest3;
 show binlog events from 95;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	95	Query	1	181	use `test`; drop database if exists mysqltest2
-master-bin.000001	181	Query	1	267	use `test`; drop database if exists mysqltest3
-master-bin.000001	267	Query	1	366	use `test`; create database mysqltest2 character set latin2
-master-bin.000001	366	Query	1	522	use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=30
-master-bin.000001	522	Query	1	600	use `test`; create database mysqltest3
-master-bin.000001	600	Query	1	756	use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64
-master-bin.000001	756	Query	1	832	use `test`; drop database mysqltest3
-master-bin.000001	832	Query	1	988	use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64
-master-bin.000001	988	Query	1	1066	use `test`; create database mysqltest3
-master-bin.000001	1066	Query	1	1223	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	1223	Query	1	1347	use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100))
-master-bin.000001	1347	Query	1	1505	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	1505	Intvar	1	1533	INSERT_ID=1
-master-bin.000001	1533	Query	1	1640	use `mysqltest2`; insert into t1 (b) values(@@character_set_server)
-master-bin.000001	1640	Query	1	1798	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	1798	Intvar	1	1826	INSERT_ID=2
-master-bin.000001	1826	Query	1	1929	use `mysqltest2`; insert into t1 (b) values(@@collation_server)
-master-bin.000001	1929	Query	1	2087	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	2087	Intvar	1	2115	INSERT_ID=3
-master-bin.000001	2115	Query	1	2222	use `mysqltest2`; insert into t1 (b) values(@@character_set_client)
-master-bin.000001	2222	Query	1	2380	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	2380	Intvar	1	2408	INSERT_ID=4
-master-bin.000001	2408	Query	1	2519	use `mysqltest2`; insert into t1 (b) values(@@character_set_connection)
-master-bin.000001	2519	Query	1	2677	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	2677	Intvar	1	2705	INSERT_ID=5
-master-bin.000001	2705	Query	1	2812	use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
-master-bin.000001	2812	Query	1	2969	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	2969	Query	1	3044	use `mysqltest2`; truncate table t1
-master-bin.000001	3044	Query	1	3201	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	3201	Intvar	1	3229	INSERT_ID=1
-master-bin.000001	3229	Query	1	3336	use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
-master-bin.000001	3336	Query	1	3493	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	3493	Intvar	1	3521	INSERT_ID=2
-master-bin.000001	3521	Query	1	3631	use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler"))
-master-bin.000001	3631	Query	1	3789	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	3789	Intvar	1	3817	INSERT_ID=3
-master-bin.000001	3817	Query	1	3924	use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
-master-bin.000001	3924	Query	1	4082	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	4082	Intvar	1	4110	INSERT_ID=4
-master-bin.000001	4110	Query	1	4220	use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler"))
-master-bin.000001	4220	Query	1	4378	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	4378	Intvar	1	4406	INSERT_ID=74
-master-bin.000001	4406	Create_file	1	5074	db=mysqltest2;table=t1;file_id=1;block_len=581
-master-bin.000001	5074	Query	1	5232	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	5232	Intvar	1	5260	INSERT_ID=5
-master-bin.000001	5260	Exec_load	1	5283	;file_id=1
-master-bin.000001	5283	Query	1	5441	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	5441	Query	1	5516	use `mysqltest2`; truncate table t1
-master-bin.000001	5516	Query	1	5674	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	5674	Intvar	1	5702	INSERT_ID=1
-master-bin.000001	5702	User var	1	5742	@`a`=_cp850 0x4DFC6C6C6572 COLLATE cp850_general_ci
-master-bin.000001	5742	Query	1	5840	use `mysqltest2`; insert into t1 (b) values(collation(@a))
-master-bin.000001	5840	Query	1	5998	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	5998	Query	1	6075	use `mysqltest2`; drop database mysqltest2
-master-bin.000001	6075	Query	1	6228	SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
-master-bin.000001	6228	Query	1	6300	drop database mysqltest3
+master-bin.000001	#	Query	1	#	use `test`; drop database if exists mysqltest2
+master-bin.000001	#	Query	1	#	use `test`; drop database if exists mysqltest3
+master-bin.000001	#	Query	1	#	use `test`; create database mysqltest2 character set latin2
+master-bin.000001	#	Query	1	#	use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=30
+master-bin.000001	#	Query	1	#	use `test`; create database mysqltest3
+master-bin.000001	#	Query	1	#	use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64
+master-bin.000001	#	Query	1	#	use `test`; drop database mysqltest3
+master-bin.000001	#	Query	1	#	use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64
+master-bin.000001	#	Query	1	#	use `test`; create database mysqltest3
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Query	1	#	use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100))
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=1
+master-bin.000001	#	Query	1	#	use `mysqltest2`; insert into t1 (b) values(@@character_set_server)
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=2
+master-bin.000001	#	Query	1	#	use `mysqltest2`; insert into t1 (b) values(@@collation_server)
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=3
+master-bin.000001	#	Query	1	#	use `mysqltest2`; insert into t1 (b) values(@@character_set_client)
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=4
+master-bin.000001	#	Query	1	#	use `mysqltest2`; insert into t1 (b) values(@@character_set_connection)
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=5
+master-bin.000001	#	Query	1	#	use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Query	1	#	use `mysqltest2`; truncate table t1
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=1
+master-bin.000001	#	Query	1	#	use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=2
+master-bin.000001	#	Query	1	#	use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler"))
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=3
+master-bin.000001	#	Query	1	#	use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=4
+master-bin.000001	#	Query	1	#	use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler"))
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=74
+master-bin.000001	#	Create_file	1	#	db=mysqltest2;table=t1;file_id=1;block_len=581
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=5
+master-bin.000001	#	Exec_load	1	#	;file_id=1
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Query	1	#	use `mysqltest2`; truncate table t1
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Intvar	1	#	INSERT_ID=1
+master-bin.000001	#	User var	1	#	@`a`=_cp850 0x4DFC6C6C6572 COLLATE cp850_general_ci
+master-bin.000001	#	Query	1	#	use `mysqltest2`; insert into t1 (b) values(collation(@a))
+master-bin.000001	#	Query	1	#	use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Query	1	#	use `mysqltest2`; drop database mysqltest2
+master-bin.000001	#	Query	1	#	SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
+master-bin.000001	#	Query	1	#	drop database mysqltest3
 set global character_set_server=latin2;
 ERROR HY000: Binary logging and replication forbid changing the global server character set or collation
 set global character_set_server=latin2;
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 6b36b3bb2bf..46fe452d9e4 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -44,7 +44,7 @@ call foo()|
 ERROR 42000: PROCEDURE test.foo does not exist
 drop procedure if exists foo|
 Warnings:
-Warning	1304	PROCEDURE foo does not exist
+Note	1305	PROCEDURE foo does not exist
 show create procedure foo|
 ERROR 42000: PROCEDURE foo does not exist
 create procedure foo()
@@ -121,7 +121,7 @@ declare y int;
 set x = y;
 end|
 Warnings:
-Warning	1310	Referring to uninitialized variable y
+Warning	1311	Referring to uninitialized variable y
 drop procedure foo|
 create procedure foo()
 return 42|
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index afd8c3430b3..7ea95a067c4 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1,6 +1,28 @@
 use test;
-drop table if exists t1;
-drop table if exists t2;
+drop table if exists t1,t2,t3,t4;
+drop procedure if exists goto1;
+drop procedure if exists goto2;
+drop procedure if exists goto3;
+drop procedure if exists goto4;
+drop procedure if exists goto5;
+drop procedure if exists goto6;
+drop procedure if exists into_outfile;
+drop procedure if exists into_dumpfile;
+drop procedure if exists create_select;
+drop procedure if exists bar;
+drop procedure if exists hndlr1;
+drop procedure if exists hndlr2;
+drop procedure if exists hndlr3;
+drop procedure if exists hndlr4;
+drop procedure if exists cur1;
+drop procedure if exists cur2;
+drop procedure if exists chistics;
+drop procedure if exists chistics2;
+drop procedure if exists modes;
+drop procedure if exists dummy;
+drop procedure if exists bug;
+drop procedure if exists bug2;
+drop function if exists fac;
 create table t1 (
 id   char(16) not null,
 data int not null
@@ -78,7 +100,6 @@ foo	1
 kaka	3
 delete from t1|
 drop procedure setcontext|
-drop table if exists t3|
 create table t3 ( d date, i int, f double, s varchar(32) )|
 create procedure nullset()
 begin
@@ -962,7 +983,7 @@ select row_count()|
 row_count()
 -1
 drop procedure rc|
-create procedure bug822(a_id char(16), a_data int)
+create procedure bug(a_id char(16), a_data int)
 begin
 declare n int;
 select count(*) into n from t1 where id = a_id and data = a_data;
@@ -970,16 +991,16 @@ if n = 0 then
 insert into t1 (id, data) values (a_id, a_data);
 end if;
 end|
-call bug822('foo', 42)|
-call bug822('foo', 42)|
-call bug822('bar', 666)|
+call bug('foo', 42)|
+call bug('foo', 42)|
+call bug('bar', 666)|
 select * from t1|
 id	data
 foo	42
 bar	666
 delete from t1|
-drop procedure bug822|
-create procedure bug1495()
+drop procedure bug|
+create procedure bug()
 begin
 declare x int;
 select data into x from t1 order by id limit 1;
@@ -990,18 +1011,18 @@ insert into t1 values ("more", x+10);
 end if;
 end|
 insert into t1 values ('foo', 12)|
-call bug1495()|
+call bug()|
 delete from t1 where id='foo'|
 insert into t1 values ('bar', 7)|
-call bug1495()|
+call bug()|
 delete from t1 where id='bar'|
 select * from t1|
 id	data
 less	2
 more	17
 delete from t1|
-drop procedure bug1495|
-create procedure bug1547(s char(16))
+drop procedure bug|
+create procedure bug(s char(16))
 begin
 declare x int;
 select data into x from t1 where s = id limit 1;
@@ -1012,8 +1033,8 @@ insert into t1 values ("more", x+10);
 end if;
 end|
 insert into t1 values ("foo", 12), ("bar", 7)|
-call bug1547("foo")|
-call bug1547("bar")|
+call bug("foo")|
+call bug("bar")|
 select * from t1|
 id	data
 foo	12
@@ -1021,34 +1042,34 @@ bar	7
 less	2
 more	17
 delete from t1|
-drop procedure bug1547|
+drop procedure bug|
 drop table if exists t70|
 create table t70 (s1 int,s2 int)|
 insert into t70 values (1,2)|
-create procedure bug1656(out p1 int, out p2 int)
+create procedure bug(out p1 int, out p2 int)
 select * into p1, p1 from t70|
-call bug1656(@1, @2)|
+call bug(@1, @2)|
 select @1, @2|
 @1	@2
 2	NULL
 drop table t70|
-drop procedure bug1656|
+drop procedure bug|
 drop table if exists t3|
 create table t3(a int)|
-create procedure bug1862()
+create procedure bug()
 begin
 insert into t3 values(2);    
 flush tables;
 end|
-call bug1862()|
-call bug1862()|
+call bug()|
+call bug()|
 select * from t3|
 a
 2
 2
 drop table t3|
-drop procedure bug1862|
-create procedure bug1874()
+drop procedure bug|
+create procedure bug()
 begin
 declare x int;
 declare y double;
@@ -1062,7 +1083,7 @@ select avg(data) into y from t1;
 insert into t2 values ("avg", 0, y);
 end|
 insert into t1 (data) values (3), (1), (5), (9), (4)|
-call bug1874()|
+call bug()|
 select * from t2|
 s	i	d
 max	9	0
@@ -1071,8 +1092,8 @@ sum	22	0
 avg	0	4.4
 delete from t1|
 delete from t2|
-drop procedure bug1874|
-create procedure bug2260()
+drop procedure bug|
+create procedure bug()
 begin
 declare v1 int;
 declare c1 cursor for select data from t1;
@@ -1082,11 +1103,11 @@ fetch c1 into v1;
 set @x2 = 2;
 close c1;
 end|
-call bug2260()|
+call bug()|
 select @x2|
 @x2
 2
-drop procedure bug2260|
+drop procedure bug|
 create procedure bug2267_1()
 begin
 show procedure status;
@@ -1133,35 +1154,35 @@ drop procedure bug2267_1|
 drop procedure bug2267_2|
 drop procedure bug2267_3|
 drop procedure bug2267_4|
-create procedure bug2227(x int)
+create procedure bug(x int)
 begin
 declare y float default 2.6;
 declare z char(16) default "zzz";
 select 1.3, x, y, 42, z;
 end|
-call bug2227(9)|
+call bug(9)|
 1.3	x	y	42	z
 1.3	9	2.6	42	zzz
-drop procedure bug2227|
-create procedure bug2614()
+drop procedure bug|
+create procedure bug()
 begin
 drop table if exists t3;
 create table t3 (id int default '0' not null);
 insert into t3 select 12;
 insert into t3 select * from t3;
 end|
-call bug2614()|
-call bug2614()|
+call bug()|
+call bug()|
 drop table t3|
-drop procedure bug2614|
-create function bug2674 () returns int
+drop procedure bug|
+create function bug () returns int
 return @@sort_buffer_size|
 set @osbs = @@sort_buffer_size|
 set @@sort_buffer_size = 262000|
-select bug2674()|
-bug2674()
+select bug()|
+bug()
 262000
-drop function bug2674|
+drop function bug|
 set @@sort_buffer_size = @osbs|
 create procedure bug3259_1 () begin end|
 create procedure BUG3259_2 () begin end|
@@ -1175,48 +1196,48 @@ call bUG3259_3()|
 drop procedure bUg3259_1|
 drop procedure BuG3259_2|
 drop procedure BUG3259_3|
-create function bug2772() returns char(10) character set latin2
+create function bug() returns char(10) character set latin2
 return 'a'|
-select bug2772()|
-bug2772()
+select bug()|
+bug()
 a
-drop function bug2772|
-create procedure bug2776_1(out x int)
+drop function bug|
+create procedure bug(out x int)
 begin
 declare v int;
 set v = default;
 set x = v;
 end|
-create procedure bug2776_2(out x int)
+create procedure bug2(out x int)
 begin
 declare v int default 42;
 set v = default;
 set x = v;
 end|
 set @x = 1|
-call bug2776_1(@x)|
+call bug(@x)|
 select @x|
 @x
 NULL
-call bug2776_2(@x)|
+call bug2(@x)|
 select @x|
 @x
 42
-drop procedure bug2776_1|
-drop procedure bug2776_2|
+drop procedure bug|
+drop procedure bug2|
 drop table if exists t3|
 create table t3 (s1 smallint)|
 insert into t3 values (123456789012)|
 Warnings:
 Warning	1264	Data truncated; out of range for column 's1' at row 1
-create procedure bug2780()
+create procedure bug()
 begin
 declare exit handler for sqlwarning set @x = 1; 
 set @x = 0;
 insert into t3 values (123456789012);
 insert into t3 values (0);
 end|
-call bug2780()|
+call bug()|
 select @x|
 @x
 1
@@ -1224,13 +1245,11 @@ select * from t3|
 s1
 32767
 32767
-drop procedure bug2780|
+drop procedure bug|
 drop table t3|
-drop table if exists t3|
 create table t3 (content varchar(10) )|
 insert into t3 values ("test1")|
 insert into t3 values ("test2")|
-drop table if exists t4|
 create table t4 (f1 int, rc int, t3 int)|
 create procedure bug1863(in1 int)
 begin 
@@ -1406,7 +1425,6 @@ select @a|
 @a
 5
 drop procedure bug3863|
-drop table if exists t3|
 create table t3 (
 id int(10) unsigned not null default 0,
 rid int(10) unsigned not null default 0,
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index 95bba1d4ec7..2f564112e40 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -9,9 +9,9 @@ d	mediumtext	YES		NULL
 e	longtext	YES		NULL	
 CREATE TABLE t2 (a char(257), b varbinary(70000), c varchar(70000000));
 Warnings:
-Warning	1246	Converting column 'a' from CHAR to TEXT
-Warning	1246	Converting column 'b' from CHAR to BLOB
-Warning	1246	Converting column 'c' from CHAR to TEXT
+Note	1246	Converting column 'a' from CHAR to TEXT
+Note	1246	Converting column 'b' from CHAR to BLOB
+Note	1246	Converting column 'c' from CHAR to TEXT
 show columns from t2;
 Field	Type	Null	Key	Default	Extra
 a	text	YES		NULL	
diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result
index 9bbfefeb34a..80d8af095cb 100644
--- a/mysql-test/r/user_var.result
+++ b/mysql-test/r/user_var.result
@@ -23,7 +23,11 @@ i	@vv1:=if(sv1.i,1,0)	@vv2:=if(sv2.i,1,0)	@vv3:=if(sv3.i,1,0)	@vv1+@vv2+@vv3
 2	1	0	0	1
 explain select * from t1 where i=@vv1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	i	i	4	const	1	Using where
+1	SIMPLE	t1	ref	i	i	4	const	1	
+select @vv1,i,v from t1 where i=@vv1;
+@vv1	i	v
+1	1	1
+1	1	3
 explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -32,7 +36,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	i	4	NULL	3	Using where; Using index
 explain select * from t1 where i=@vv1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	i	i	4	const	1	Using where
+1	SIMPLE	t1	ref	i	i	4	const	1	
 drop table t1,t2;
 set @a=0,@b=0;
 select @a:=10,   @b:=1,   @a > @b, @a < @b;
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index a84cf733f21..399e6af848c 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -364,7 +364,7 @@ set sql_log_bin=1;
 set sql_log_off=1;
 set sql_log_update=1;
 Warnings:
-Note	1314	The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored
+Note	1315	The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored
 set sql_low_priority_updates=1;
 set sql_max_join_size=200;
 select @@sql_max_join_size,@@max_join_size;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 18da4882894..ef3743877c6 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -761,7 +761,7 @@ set sql_updatable_view_key=LIMIT1;
 update v1 set x=x+1;
 update v2 set x=x+1;
 Warnings:
-Note	1354	View being updated does not have complete key of underlying table in it
+Note	1355	View being updated does not have complete key of underlying table in it
 update v1 set x=x+1 limit 1;
 update v2 set x=x+1 limit 1;
 ERROR HY000: The target table v2 of the UPDATE is not updatable
@@ -769,7 +769,7 @@ set sql_updatable_view_key=NO;
 update v1 set x=x+1 limit 1;
 update v2 set x=x+1 limit 1;
 Warnings:
-Note	1354	View being updated does not have complete key of underlying table in it
+Note	1355	View being updated does not have complete key of underlying table in it
 set sql_updatable_view_key=DEFAULT;
 select * from t1;
 a	b	c
@@ -979,7 +979,7 @@ create view v1 (a,a) as select 'a','a';
 ERROR 42S21: Duplicate column name 'a'
 create procedure p1 () begin declare v int; create view v1 as select v; end;//
 Warnings:
-Warning	1310	Referring to uninitialized variable v
+Warning	1311	Referring to uninitialized variable v
 call p1();
 ERROR HY000: View's SELECT contains a variable or parameter
 drop procedure p1;