Merge bk-internal.mysql.com:/data0/bk/mysql-5.0

into  bk-internal.mysql.com:/data0/bk/mysql-5.0-kt

31 files changed, 975 insertions, 149 deletions

diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index aa8c6d3d277..ed83dfb4b49 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -452,7 +452,7 @@ t2	CREATE TABLE `t2` (
   `ifnull(h,h)` decimal(5,4) default NULL,
   `ifnull(i,i)` year(4) default NULL,
   `ifnull(j,j)` date default NULL,
-  `ifnull(k,k)` datetime NOT NULL default '0000-00-00 00:00:00',
+  `ifnull(k,k)` timestamp NOT NULL default '0000-00-00 00:00:00',
   `ifnull(l,l)` datetime default NULL,
   `ifnull(m,m)` varchar(1) default NULL,
   `ifnull(n,n)` varchar(3) default NULL,
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index d8a539da3fe..dc09a68682c 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -641,3 +641,16 @@ select charset(group_concat(c1 order by c2)) from t1;
 charset(group_concat(c1 order by c2))
 latin1
 drop table t1;
+CREATE TABLE t1 (a INT(10), b LONGTEXT, PRIMARY KEY (a));
+SET GROUP_CONCAT_MAX_LEN = 20000000;
+INSERT INTO t1 VALUES (1,REPEAT(CONCAT('A',CAST(CHAR(0) AS BINARY),'B'), 40000));
+INSERT INTO t1 SELECT a + 1, b FROM t1;
+SELECT a, CHAR_LENGTH(b) FROM t1;
+a	CHAR_LENGTH(b)
+1	120000
+2	120000
+SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1;
+CHAR_LENGTH( GROUP_CONCAT(b) )
+240001
+SET GROUP_CONCAT_MAX_LEN = 1024;
+DROP TABLE t1;
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index f693c6190d5..896b79297f8 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -988,3 +988,14 @@ SUM(a)
 6
 DROP TABLE t1;
 set div_precision_increment= @sav_dpi;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2);
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
+INSERT INTO t2 VALUES (1,1), (3,3);
+SELECT SQL_NO_CACHE 
+(SELECT SUM(c.a) FROM t1 ttt, t2 ccc 
+WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid   
+FROM t1 t, t2 c WHERE t.a = c.b;
+minid
+NULL
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index aebf3596751..14da630f61e 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1056,6 +1056,34 @@ a	c
 abc	abc abc
 xyz	xyz xyz
 DROP TABLE t1;
+CREATE TABLE t1 (s varchar(10));
+INSERT INTO t1 VALUES ('yadda'), ('yaddy');
+EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(s) > 'ab';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(`test`.`t1`.`s`) > _latin1'ab')
+EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin1'y' from `test`.`t1`.`s`) > _latin1'ab')
+EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(LEADING 'y' FROM s) > 'ab';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(leading _latin1'y' from `test`.`t1`.`s`) > _latin1'ab')
+EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(TRAILING 'y' FROM s) > 'ab';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(trailing _latin1'y' from `test`.`t1`.`s`) > _latin1'ab')
+EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin1'y' from `test`.`t1`.`s`) > _latin1'ab')
+DROP TABLE t1;
 End of 4.1 tests
 create table t1 (d decimal default null);
 insert into t1 values (null);
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 7bc886022cc..e5c177503fa 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -796,3 +796,28 @@ aaa
 show warnings;
 Level	Code	Message
 drop table t1, t2;
+CREATE TABLE t1 (a tinyint(3), b varchar(255), PRIMARY KEY  (a));
+INSERT INTO t1 VALUES (1,'-----'), (6,'Allemagne'), (17,'Autriche'), 
+(25,'Belgique'), (54,'Danemark'), (62,'Espagne'), (68,'France');
+CREATE TABLE t2 (a tinyint(3), b tinyint(3), PRIMARY KEY  (a), KEY b (b));
+INSERT INTO t2 VALUES (1,1), (2,1), (6,6), (18,17), (15,25), (16,25),
+(17,25), (10,54), (5,62),(3,68);
+CREATE VIEW v1 AS select t1.a, concat(t1.b,'') AS b, t1.b as real_b from t1;
+explain 
+SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1
+where t2.b=v1.a GROUP BY t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	b	b	2	NULL	10	Using index
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	1	test.t2.b	1	
+SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1
+where t2.b=v1.a GROUP BY t2.b;
+a	b	real_b
+1	-----	-----
+6	Allemagne	Allemagne
+17	Autriche	Autriche
+25	Belgique	Belgique
+54	Danemark	Danemark
+62	Espagne	Espagne
+68	France	France
+DROP VIEW v1;
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index d62586dba85..fe6f7c4ca55 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2099,3 +2099,46 @@ SOUTH EAST	SOUTH EAST
 SOUTH WEST	SOUTH WEST
 WESTERN	WESTERN
 DROP TABLE t1;
+CREATE TABLE t1 (id1 INT, id2 INT);
+CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
+CREATE TABLE t3 (id3 INT, id4 INT);
+CREATE TABLE t4 (id4 INT);
+CREATE TABLE t5 (id5 INT, id6 INT);
+CREATE TABLE t6 (id6 INT);
+INSERT INTO t1 VALUES(1,1);
+INSERT INTO t2 VALUES(1,1,1);
+INSERT INTO t3 VALUES(1,1);
+INSERT INTO t4 VALUES(1);
+INSERT INTO t5 VALUES(1,1);
+INSERT INTO t6 VALUES(1);
+SELECT * FROM
+t1
+NATURAL JOIN
+(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
+ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
+id2	id1	id3	id5	id4	id3	id6	id5
+1	1	1	1	1	1	1	1
+SELECT * FROM
+t1
+NATURAL JOIN
+(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
+ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));
+id2	id1	id4	id3	id6	id5	id3	id5
+1	1	1	1	1	1	1	1
+SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);
+id2	id1	id3	id4	id6	id5	id3	id5
+1	1	1	1	1	1	1	1
+SELECT * FROM
+(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
+ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
+NATURAL JOIN
+t1;
+id2	id3	id5	id4	id3	id6	id5	id1
+1	1	1	1	1	1	1	1
+SELECT * FROM
+(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
+NATURAL JOIN
+t1;
+id2	id3	id5	id4	id3	id6	id5	id1
+1	1	1	1	1	1	1	1
+DROP TABLE t1,t2,t3,t4,t5,t6;
diff --git a/mysql-test/r/im_daemon_life_cycle.result b/mysql-test/r/im_daemon_life_cycle.result
index ea27fcb6db1..4f7dd77a88f 100644
--- a/mysql-test/r/im_daemon_life_cycle.result
+++ b/mysql-test/r/im_daemon_life_cycle.result
@@ -1,4 +1,7 @@
 Success: the process has been started.
+SHOW VARIABLES LIKE 'server_id';
+Variable_name	Value
+server_id	1
 SHOW INSTANCES;
 instance_name	status
 mysqld1	online
diff --git a/mysql-test/r/im_life_cycle.result b/mysql-test/r/im_life_cycle.result
index 211a82eb9cf..53737b64f28 100644
--- a/mysql-test/r/im_life_cycle.result
+++ b/mysql-test/r/im_life_cycle.result
@@ -1,8 +1,7 @@
-
---------------------------------------------------------------------
--- 1.1.1.
---------------------------------------------------------------------
 Success: the process has been started.
+SHOW VARIABLES LIKE 'server_id';
+Variable_name	Value
+server_id	1
 SHOW INSTANCES;
 instance_name	status
 mysqld1	online
@@ -40,10 +39,6 @@ ERROR HY000: Bad instance name. Check that the instance with such a name exists
 --------------------------------------------------------------------
 -- 1.1.6.
 --------------------------------------------------------------------
-SHOW INSTANCES;
-instance_name	status
-mysqld1	online
-mysqld2	offline
 Killing the process...
 Sleeping...
 Success: the process was restarted.
diff --git a/mysql-test/r/im_options_set.result b/mysql-test/r/im_options_set.result
index 5e6c740624e..c3035079b39 100644
--- a/mysql-test/r/im_options_set.result
+++ b/mysql-test/r/im_options_set.result
@@ -1,8 +1,11 @@
-server_id           = 1
-server_id           = 2
+Success: the process has been started.
 SHOW VARIABLES LIKE 'server_id';
 Variable_name	Value
 server_id	1
+SHOW INSTANCES;
+instance_name	status
+mysqld1	online
+mysqld2	offline
 SET mysqld1.server_id = 11;
 server_id =11
 server_id           = 2
diff --git a/mysql-test/r/im_options_unset.result b/mysql-test/r/im_options_unset.result
index bf54025edb7..ba468c78a5b 100644
--- a/mysql-test/r/im_options_unset.result
+++ b/mysql-test/r/im_options_unset.result
@@ -1,8 +1,11 @@
-server_id           = 1
-server_id           = 2
+Success: the process has been started.
 SHOW VARIABLES LIKE 'server_id';
 Variable_name	Value
 server_id	1
+SHOW INSTANCES;
+instance_name	status
+mysqld1	online
+mysqld2	offline
 UNSET mysqld1.server_id;
 server_id           = 2
 SHOW VARIABLES LIKE 'server_id';
diff --git a/mysql-test/r/im_utils.result b/mysql-test/r/im_utils.result
index e6a5e007ed4..be696921812 100644
--- a/mysql-test/r/im_utils.result
+++ b/mysql-test/r/im_utils.result
@@ -1,4 +1,7 @@
 Success: the process has been started.
+SHOW VARIABLES LIKE 'server_id';
+Variable_name	Value
+server_id	1
 SHOW INSTANCES;
 instance_name	status
 mysqld1	online
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index a2feba7ad5d..652af1c8387 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1170,3 +1170,73 @@ f1()
 DROP FUNCTION f1;
 DROP PROCEDURE p1;
 DROP USER mysql_bug20230@localhost;
+SELECT t.table_name, c1.column_name
+FROM information_schema.tables t
+INNER JOIN
+information_schema.columns c1
+ON t.table_schema = c1.table_schema AND
+t.table_name = c1.table_name
+WHERE t.table_schema = 'information_schema' AND
+c1.ordinal_position =
+( SELECT COALESCE(MIN(c2.ordinal_position),1)
+FROM information_schema.columns c2
+WHERE c2.table_schema = t.table_schema AND
+c2.table_name = t.table_name AND
+c2.column_name LIKE '%SCHEMA%'
+        );
+table_name	column_name
+CHARACTER_SETS	CHARACTER_SET_NAME
+COLLATIONS	COLLATION_NAME
+COLLATION_CHARACTER_SET_APPLICABILITY	COLLATION_NAME
+COLUMNS	TABLE_SCHEMA
+COLUMN_PRIVILEGES	TABLE_SCHEMA
+KEY_COLUMN_USAGE	CONSTRAINT_SCHEMA
+ROUTINES	ROUTINE_SCHEMA
+SCHEMATA	SCHEMA_NAME
+SCHEMA_PRIVILEGES	TABLE_SCHEMA
+STATISTICS	TABLE_SCHEMA
+TABLES	TABLE_SCHEMA
+TABLE_CONSTRAINTS	CONSTRAINT_SCHEMA
+TABLE_PRIVILEGES	TABLE_SCHEMA
+TRIGGERS	TRIGGER_SCHEMA
+USER_PRIVILEGES	GRANTEE
+VIEWS	TABLE_SCHEMA
+SELECT t.table_name, c1.column_name
+FROM information_schema.tables t
+INNER JOIN
+information_schema.columns c1
+ON t.table_schema = c1.table_schema AND
+t.table_name = c1.table_name
+WHERE t.table_schema = 'information_schema' AND
+c1.ordinal_position =
+( SELECT COALESCE(MIN(c2.ordinal_position),1)
+FROM information_schema.columns c2
+WHERE c2.table_schema = 'information_schema' AND
+c2.table_name = t.table_name AND
+c2.column_name LIKE '%SCHEMA%'
+        );
+table_name	column_name
+CHARACTER_SETS	CHARACTER_SET_NAME
+COLLATIONS	COLLATION_NAME
+COLLATION_CHARACTER_SET_APPLICABILITY	COLLATION_NAME
+COLUMNS	TABLE_SCHEMA
+COLUMN_PRIVILEGES	TABLE_SCHEMA
+KEY_COLUMN_USAGE	CONSTRAINT_SCHEMA
+ROUTINES	ROUTINE_SCHEMA
+SCHEMATA	SCHEMA_NAME
+SCHEMA_PRIVILEGES	TABLE_SCHEMA
+STATISTICS	TABLE_SCHEMA
+TABLES	TABLE_SCHEMA
+TABLE_CONSTRAINTS	CONSTRAINT_SCHEMA
+TABLE_PRIVILEGES	TABLE_SCHEMA
+TRIGGERS	TRIGGER_SCHEMA
+USER_PRIVILEGES	GRANTEE
+VIEWS	TABLE_SCHEMA
+SELECT MAX(table_name) FROM information_schema.tables;
+MAX(table_name)
+VIEWS
+SELECT table_name from information_schema.tables
+WHERE table_name=(SELECT MAX(table_name)
+FROM information_schema.tables);
+table_name
+VIEWS
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 77046cc1fd1..1d1f26e4b01 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1473,8 +1473,8 @@ Error	1146	Table 'test.t4' doesn't exist
 drop table t1,t2,t3;
 create table t1 (id int,  name char(10) not null,  name2 char(10) not null) engine=innodb;
 insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
-select name2 from t1  union all  select name from t1 union all select id from t1;
-name2
+select trim(name2) from t1  union all  select trim(name) from t1 union all select trim(id) from t1;
+trim(name2)
 fff
 sss
 ttt
diff --git a/mysql-test/r/mysql_client.result b/mysql-test/r/mysql_client.result
index 87d09428ff6..a20bd60aaf3 100644
--- a/mysql-test/r/mysql_client.result
+++ b/mysql-test/r/mysql_client.result
@@ -2,3 +2,7 @@
 1
 ERROR 1064 (42000) at line 3: 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 '' at line 1
 ERROR at line 1: USE must be followed by a database name
+\
+\\
+';
+';
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index ff745021efb..0160d26527d 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -2220,7 +2220,7 @@ RETURN a+b */;;
 /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
 /*!50003 DROP FUNCTION IF EXISTS `bug9056_func2` */;;
 /*!50003 SET SESSION SQL_MODE=""*/;;
-/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `bug9056_func2`(f1 char binary) RETURNS char(1)
+/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `bug9056_func2`(f1 char binary) RETURNS char(1) CHARSET latin1
 begin
 set f1= concat( 'hello', f1 );
 return f1;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 3d352a02ad2..d73dd03fc57 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -516,77 +516,6 @@ SELECT FOUND_ROWS();
 FOUND_ROWS()
 2
 deallocate prepare stmt;
-create table t1 (a char(3) not null, b char(3) not null,
-c char(3) not null, primary key  (a, b, c));
-create table t2 like t1;
-prepare stmt from
-"select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b)
-  where t1.a=1";
-execute stmt;
-a
-execute stmt;
-a
-execute stmt;
-a
-prepare stmt from
-"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from
-(t1 left outer join t2 on t2.a=? and t1.b=t2.b)
-left outer join t2 t3 on t3.a=? where t1.a=?";
-set @a:=1, @b:=1, @c:=1;
-execute stmt using @a, @b, @c;
-a	b	c	a	b	c
-execute stmt using @a, @b, @c;
-a	b	c	a	b	c
-execute stmt using @a, @b, @c;
-a	b	c	a	b	c
-deallocate prepare stmt;
-drop table t1,t2;
-SET @aux= "SELECT COUNT(*)
-                FROM INFORMATION_SCHEMA.COLUMNS A,
-                INFORMATION_SCHEMA.COLUMNS B
-                WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
-                AND A.TABLE_NAME = B.TABLE_NAME
-                AND A.COLUMN_NAME = B.COLUMN_NAME AND
-                A.TABLE_NAME = 'user'";
-prepare my_stmt from @aux;
-execute my_stmt;
-COUNT(*)
-37
-execute my_stmt;
-COUNT(*)
-37
-execute my_stmt;
-COUNT(*)
-37
-deallocate prepare my_stmt;
-drop procedure if exists p1|
-drop table if exists t1|
-create table t1 (id int)|
-insert into t1 values(1)|
-create procedure p1(a int, b int)
-begin
-declare c int;
-select max(id)+1 into c from t1;
-insert into t1 select a+b;
-insert into t1 select a-b;
-insert into t1 select a-c;
-end|
-set @a= 3, @b= 4|
-prepare stmt from "call p1(?, ?)"|
-execute stmt using @a, @b|
-execute stmt using @a, @b|
-select * from t1|
-id
-1
-7
--1
-1
-7
--1
--5
-deallocate prepare stmt|
-drop procedure p1|
-drop table t1|
 drop table if exists t1;
 Warnings:
 Note	1051	Unknown table 't1'
@@ -650,47 +579,6 @@ id
 3
 deallocate prepare stmt;
 drop table t1, t2;
-create table t1 (a int);
-insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
-prepare stmt from "select * from t1 limit ?, ?";
-set @offset=0, @limit=1;
-execute stmt using @offset, @limit;
-a
-1
-select * from t1 limit 0, 1;
-a
-1
-set @offset=3, @limit=2;
-execute stmt using @offset, @limit;
-a
-4
-5
-select * from t1 limit 3, 2;
-a
-4
-5
-prepare stmt from "select * from t1 limit ?";
-execute stmt using @limit;
-a
-1
-2
-prepare stmt from "select * from t1 where a in (select a from t1 limit ?)";
-ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
-prepare stmt from "select * from t1 union all select * from t1 limit ?, ?";
-set @offset=9;
-set @limit=2;
-execute stmt using @offset, @limit;
-a
-10
-1
-prepare stmt from "(select * from t1 limit ?, ?) union all
-                   (select * from t1 limit ?, ?) order by a limit ?";
-execute stmt using @offset, @limit, @offset, @limit, @limit;
-a
-10
-10
-drop table t1;
-deallocate prepare stmt;
 create table t1 (id int);
 prepare stmt from "insert into t1 (id) select id from t1 union select id from t1";
 execute stmt;
@@ -791,15 +679,6 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 select ? 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 '? from t1' at line 1
 drop table t1;
-CREATE TABLE b12651_T1(a int) ENGINE=MYISAM;
-CREATE TABLE b12651_T2(b int) ENGINE=MYISAM;
-CREATE VIEW  b12651_V1 as SELECT b FROM b12651_T2;
-PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)';
-EXECUTE b12651;
-1
-DROP VIEW b12651_V1;
-DROP TABLE b12651_T1, b12651_T2;
-DEALLOCATE PREPARE b12651;
 prepare stmt from "select @@time_zone";
 execute stmt;
 @@time_zone
@@ -1016,6 +895,147 @@ select @@max_prepared_stmt_count, @@prepared_stmt_count;
 @@max_prepared_stmt_count	@@prepared_stmt_count
 3	0
 set global max_prepared_stmt_count= @old_max_prepared_stmt_count;
+drop table if exists t1;
+create temporary table if not exists t1 (a1 int);
+prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
+drop temporary table t1;
+create temporary table if not exists t1 (a1 int);
+execute stmt;
+drop temporary table t1;
+create temporary table if not exists t1 (a1 int);
+execute stmt;
+drop temporary table t1;
+create temporary table if not exists t1 (a1 int);
+execute stmt;
+drop temporary table t1;
+deallocate prepare stmt;
+End of 4.1 tests
+create table t1 (a varchar(20));
+insert into t1 values ('foo');
+prepare stmt FROM 'SELECT char_length (a) FROM t1';
+ERROR 42000: FUNCTION test.char_length does not exist
+drop table t1;
+create table t1 (a char(3) not null, b char(3) not null,
+c char(3) not null, primary key  (a, b, c));
+create table t2 like t1;
+prepare stmt from
+"select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b)
+  where t1.a=1";
+execute stmt;
+a
+execute stmt;
+a
+execute stmt;
+a
+prepare stmt from
+"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from
+(t1 left outer join t2 on t2.a=? and t1.b=t2.b)
+left outer join t2 t3 on t3.a=? where t1.a=?";
+set @a:=1, @b:=1, @c:=1;
+execute stmt using @a, @b, @c;
+a	b	c	a	b	c
+execute stmt using @a, @b, @c;
+a	b	c	a	b	c
+execute stmt using @a, @b, @c;
+a	b	c	a	b	c
+deallocate prepare stmt;
+drop table t1,t2;
+SET @aux= "SELECT COUNT(*)
+                FROM INFORMATION_SCHEMA.COLUMNS A,
+                INFORMATION_SCHEMA.COLUMNS B
+                WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
+                AND A.TABLE_NAME = B.TABLE_NAME
+                AND A.COLUMN_NAME = B.COLUMN_NAME AND
+                A.TABLE_NAME = 'user'";
+prepare my_stmt from @aux;
+execute my_stmt;
+COUNT(*)
+37
+execute my_stmt;
+COUNT(*)
+37
+execute my_stmt;
+COUNT(*)
+37
+deallocate prepare my_stmt;
+drop procedure if exists p1|
+drop table if exists t1|
+create table t1 (id int)|
+insert into t1 values(1)|
+create procedure p1(a int, b int)
+begin
+declare c int;
+select max(id)+1 into c from t1;
+insert into t1 select a+b;
+insert into t1 select a-b;
+insert into t1 select a-c;
+end|
+set @a= 3, @b= 4|
+prepare stmt from "call p1(?, ?)"|
+execute stmt using @a, @b|
+execute stmt using @a, @b|
+select * from t1|
+id
+1
+7
+-1
+1
+7
+-1
+-5
+deallocate prepare stmt|
+drop procedure p1|
+drop table t1|
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+prepare stmt from "select * from t1 limit ?, ?";
+set @offset=0, @limit=1;
+execute stmt using @offset, @limit;
+a
+1
+select * from t1 limit 0, 1;
+a
+1
+set @offset=3, @limit=2;
+execute stmt using @offset, @limit;
+a
+4
+5
+select * from t1 limit 3, 2;
+a
+4
+5
+prepare stmt from "select * from t1 limit ?";
+execute stmt using @limit;
+a
+1
+2
+prepare stmt from "select * from t1 where a in (select a from t1 limit ?)";
+ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
+prepare stmt from "select * from t1 union all select * from t1 limit ?, ?";
+set @offset=9;
+set @limit=2;
+execute stmt using @offset, @limit;
+a
+10
+1
+prepare stmt from "(select * from t1 limit ?, ?) union all
+                   (select * from t1 limit ?, ?) order by a limit ?";
+execute stmt using @offset, @limit, @offset, @limit, @limit;
+a
+10
+10
+drop table t1;
+deallocate prepare stmt;
+CREATE TABLE b12651_T1(a int) ENGINE=MYISAM;
+CREATE TABLE b12651_T2(b int) ENGINE=MYISAM;
+CREATE VIEW  b12651_V1 as SELECT b FROM b12651_T2;
+PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)';
+EXECUTE b12651;
+1
+DROP VIEW b12651_V1;
+DROP TABLE b12651_T1, b12651_T2;
+DEALLOCATE PREPARE b12651;
 create table t1 (id int);
 prepare ins_call from "insert into t1 (id) values (1)";
 execute ins_call;
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
index 3c736a508d3..ac8ae6def9f 100644
--- a/mysql-test/r/ps_1general.result
+++ b/mysql-test/r/ps_1general.result
@@ -298,7 +298,7 @@ t9	MyISAM	10	Dynamic	2	216	432	#	2048	0	NULL	#	#	#	latin1_swedish_ci	NULL
 prepare stmt4 from ' show status like ''Threads_running'' ';
 execute stmt4;
 Variable_name	Value
-Threads_running	1
+Threads_running	#
 prepare stmt4 from ' show variables like ''sql_mode'' ';
 execute stmt4;
 Variable_name	Value
diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result
index 5dfda16c763..7b096b27733 100644
--- a/mysql-test/r/rpl_sp.result
+++ b/mysql-test/r/rpl_sp.result
@@ -420,4 +420,48 @@ SELECT * FROM t1;
 col
 test
 DROP PROCEDURE p1;
+
+---> Test for BUG#20438
+
+---> Preparing environment...
+---> connection: master
+DROP PROCEDURE IF EXISTS p1;
+DROP FUNCTION IF EXISTS f1;
+
+---> Synchronizing slave with master...
+
+---> connection: master
+
+---> Creating procedure...
+/*!50003 CREATE PROCEDURE p1() SET @a = 1 */;
+/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;
+
+---> Checking on master...
+SHOW CREATE PROCEDURE p1;
+Procedure	sql_mode	Create Procedure
+p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+SET @a = 1
+SHOW CREATE FUNCTION f1;
+Function	sql_mode	Create Function
+f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
+RETURN 0
+
+---> Synchronizing slave with master...
+---> connection: master
+
+---> Checking on slave...
+SHOW CREATE PROCEDURE p1;
+Procedure	sql_mode	Create Procedure
+p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+SET @a = 1
+SHOW CREATE FUNCTION f1;
+Function	sql_mode	Create Function
+f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
+RETURN 0
+
+---> connection: master
+
+---> Cleaning up...
+DROP PROCEDURE p1;
+DROP FUNCTION f1;
 drop table t1;
diff --git a/mysql-test/r/rpl_trigger.result b/mysql-test/r/rpl_trigger.result
index 3e4a3349e13..49f0f5c4c44 100644
--- a/mysql-test/r/rpl_trigger.result
+++ b/mysql-test/r/rpl_trigger.result
@@ -896,3 +896,50 @@ Tables_in_test (t_)
 SHOW TRIGGERS;
 Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer
 RESET MASTER;
+START SLAVE;
+
+---> Test for BUG#20438
+
+---> Preparing environment...
+---> connection: master
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+
+---> Synchronizing slave with master...
+
+---> connection: master
+
+---> Creating objects...
+CREATE TABLE t1(c INT);
+CREATE TABLE t2(c INT);
+/*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
+FOR EACH ROW
+INSERT INTO t2 VALUES(NEW.c * 10) */;
+
+---> Inserting value...
+INSERT INTO t1 VALUES(1);
+
+---> Checking on master...
+SELECT * FROM t1;
+c
+1
+SELECT * FROM t2;
+c
+10
+
+---> Synchronizing slave with master...
+---> connection: master
+
+---> Checking on slave...
+SELECT * FROM t1;
+c
+1
+SELECT * FROM t2;
+c
+10
+
+---> connection: master
+
+---> Cleaning up...
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/mysql-test/r/rpl_view.result b/mysql-test/r/rpl_view.result
index cf4c161b296..5a101defe38 100644
--- a/mysql-test/r/rpl_view.result
+++ b/mysql-test/r/rpl_view.result
@@ -54,3 +54,40 @@ slave-bin.000001	#	Query	1	#	use `test`; delete from v1 where a=2
 slave-bin.000001	#	Query	1	#	use `test`; ALTER ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW v1 AS select a as b from t1
 slave-bin.000001	#	Query	1	#	use `test`; drop view v1
 slave-bin.000001	#	Query	1	#	use `test`; drop table t1
+
+---> Test for BUG#20438
+
+---> Preparing environment...
+---> connection: master
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+
+---> Synchronizing slave with master...
+
+---> connection: master
+
+---> Creating objects...
+CREATE TABLE t1(c INT);
+/*!50003 CREATE VIEW v1 AS SELECT * FROM t1 */;
+
+---> Inserting value...
+INSERT INTO t1 VALUES(1);
+
+---> Checking on master...
+SELECT * FROM t1;
+c
+1
+
+---> Synchronizing slave with master...
+---> connection: master
+
+---> Checking on slave...
+SELECT * FROM t1;
+c
+1
+
+---> connection: master
+
+---> Cleaning up...
+DROP VIEW v1;
+DROP TABLE t1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index c2218585f7c..b5d059be4c5 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2736,6 +2736,81 @@ SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;
 i='1e+01'	i=1e+01	i in (1e+01,1e+01)	i in ('1e+01','1e+01')
 0	1	1	1
 DROP TABLE t1;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,1), (2,1), (4,10);
+CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b));
+INSERT INTO t2 VALUES (1,NULL), (2,10);
+ALTER TABLE t1 ENABLE KEYS;
+EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	b	b	5	NULL	2	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
+a	b	a	b
+1	NULL	1	1
+1	NULL	2	1
+1	NULL	4	10
+2	10	4	10
+EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	b	b	5	NULL	2	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
+a	b	a	b
+1	NULL	1	1
+1	NULL	2	1
+1	NULL	4	10
+2	10	4	10
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1));
+CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2));
+INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);
+INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);
+explain select max(key1) from t1 where key1 <= 0.6158;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select max(key2) from t2 where key2 <= 1.6158;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select min(key1) from t1 where key1 >= 0.3762;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select min(key2) from t2 where key2 >= 1.3762;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select max(key1), min(key2) from t1, t2
+where key1 <= 0.6158 and key2 >= 1.3762;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+select max(key1) from t1 where key1 <= 0.6158;
+max(key1)
+0.61580002307892
+select max(key2) from t2 where key2 <= 1.6158;
+max(key2)
+1.6158000230789
+select min(key1) from t1 where key1 >= 0.3762;
+min(key1)
+0.37619999051094
+select min(key2) from t2 where key2 >= 1.3762;
+min(key2)
+1.3761999607086
+select max(key1), min(key2) from t1, t2
+where key1 <= 0.6158 and key2 >= 1.3762;
+max(key1)	min(key2)
+0.61580002307892	1.3761999607086
+select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
+max(key1)
+0.61580002307892
+select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
+min(key1)
+0.37619999051094
+DROP TABLE t1,t2;
 CREATE TABLE t1 ( 
 K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', 
 K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000', 
diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
index 994501767ba..7bdfa78066c 100644
--- a/mysql-test/r/show_check.result
+++ b/mysql-test/r/show_check.result
@@ -625,3 +625,7 @@ View	Create View
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_cache 1 AS `1`
 DROP PROCEDURE p1;
 DROP VIEW v1;
+SHOW TABLES FROM no_such_database;
+ERROR 42000: Unknown database 'no_such_database'
+SHOW COLUMNS FROM no_such_table;
+ERROR 42S02: Table 'test.no_such_table' doesn't exist
diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result
index 8bdb9a724d7..4ae38861d29 100644
--- a/mysql-test/r/sp-code.result
+++ b/mysql-test/r/sp-code.result
@@ -155,11 +155,11 @@ Pos	Instruction
 0	stmt 9 "drop temporary table if exists sudoku..."
 1	stmt 1 "create temporary table sudoku_work ( ..."
 2	stmt 1 "create temporary table sudoku_schedul..."
-3	stmt 95 "call sudoku_init("
+3	stmt 95 "call sudoku_init()"
 4	jump_if_not 7(8) p_naive@0
 5	stmt 4 "update sudoku_work set cnt = 0 where ..."
 6	jump 8
-7	stmt 95 "call sudoku_count("
+7	stmt 95 "call sudoku_count()"
 8	stmt 6 "insert into sudoku_schedule (row,col)..."
 9	set v_scounter@2 0
 10	set v_i@3 1
@@ -199,3 +199,10 @@ Pos	Instruction
 44	jump 14
 45	stmt 9 "drop temporary table sudoku_work, sud..."
 drop procedure sudoku_solve;
+DROP PROCEDURE IF EXISTS p1;
+CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1);
+SHOW PROCEDURE CODE p1;
+Pos	Instruction
+0	stmt 2 "CREATE INDEX idx ON t1 (c1)"
+DROP PROCEDURE p1;
+End of 5.0 tests.
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 924963017eb..da1fc58db57 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -1174,3 +1174,16 @@ drop procedure bug15091;
 drop function if exists bug16896;
 create aggregate function bug16896() returns int return 1;
 ERROR 42000: AGGREGATE is not supported for stored functions
+DROP PROCEDURE IF EXISTS bug14702;
+CREATE IF NOT EXISTS PROCEDURE bug14702()
+BEGIN
+END;
+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 'IF NOT EXISTS PROCEDURE bug14702()
+BEGIN
+END' at line 1
+CREATE PROCEDURE IF NOT EXISTS bug14702()
+BEGIN
+END;
+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 'IF NOT EXISTS bug14702()
+BEGIN
+END' at line 1
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result
index a53b4c4d246..1198efc4f3b 100644
--- a/mysql-test/r/sp-security.result
+++ b/mysql-test/r/sp-security.result
@@ -451,3 +451,55 @@ SELECT Host,User,Password FROM mysql.user WHERE User='user19857';
 Host	User	Password
 localhost	user19857	*82DC221D557298F6CE9961037DB1C90604792F5C
 DROP USER user19857@localhost;
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+DROP FUNCTION IF EXISTS f_suid;
+DROP PROCEDURE IF EXISTS p_suid;
+DROP FUNCTION IF EXISTS f_evil;
+DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%';
+DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%';
+DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%';
+DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%';
+FLUSH PRIVILEGES;
+CREATE TABLE t1 (i INT);
+CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0;
+CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0;
+CREATE USER mysqltest_u1@localhost;
+GRANT EXECUTE ON test.* TO mysqltest_u1@localhost;
+CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT
+SQL SECURITY INVOKER
+BEGIN
+SET @a:= CURRENT_USER();
+SET @b:= (SELECT COUNT(*) FROM t1);
+RETURN @b;
+END|
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil();
+SELECT COUNT(*) FROM t1;
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
+SELECT f_evil();
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
+SELECT @a, @b;
+@a	@b
+mysqltest_u1@localhost	NULL
+SELECT f_suid(f_evil());
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
+SELECT @a, @b;
+@a	@b
+mysqltest_u1@localhost	NULL
+CALL p_suid(f_evil());
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
+SELECT @a, @b;
+@a	@b
+mysqltest_u1@localhost	NULL
+SELECT * FROM v1;
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v1'
+SELECT @a, @b;
+@a	@b
+mysqltest_u1@localhost	NULL
+DROP VIEW v1;
+DROP FUNCTION f_evil;
+DROP USER mysqltest_u1@localhost;
+DROP PROCEDURE p_suid;
+DROP FUNCTION f_suid;
+DROP TABLE t1;
+End of 5.0 tests.
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 7807b7b52ce..88a860d6c8a 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -5057,6 +5057,190 @@ concat('data was: /', var1, '/')
 data was: /1/
 drop table t3|
 drop procedure bug15217|
+DROP PROCEDURE IF EXISTS bug21013 |
+CREATE PROCEDURE bug21013(IN lim INT)
+BEGIN
+DECLARE i INT DEFAULT 0;
+WHILE (i < lim) DO
+SET @b = LOCATE(_latin1'b', @a, 1);
+SET i = i + 1;
+END WHILE;
+END |
+SET @a = _latin2"aaaaaaaaaa" |
+CALL bug21013(10) |
+DROP PROCEDURE bug21013 |
+DROP DATABASE IF EXISTS mysqltest1|
+DROP DATABASE IF EXISTS mysqltest2|
+CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
+CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8|
+use mysqltest1|
+CREATE FUNCTION bug16211_f1() RETURNS CHAR(10)
+RETURN ""|
+CREATE FUNCTION bug16211_f2() RETURNS CHAR(10) CHARSET koi8r
+RETURN ""|
+CREATE FUNCTION mysqltest2.bug16211_f3() RETURNS CHAR(10)
+RETURN ""|
+CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r
+RETURN ""|
+SHOW CREATE FUNCTION bug16211_f1|
+Function	sql_mode	Create Function
+bug16211_f1		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8
+RETURN ""
+SHOW CREATE FUNCTION bug16211_f2|
+Function	sql_mode	Create Function
+bug16211_f2		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r
+RETURN ""
+SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
+Function	sql_mode	Create Function
+bug16211_f3		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8
+RETURN ""
+SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
+Function	sql_mode	Create Function
+bug16211_f4		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r
+RETURN ""
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
+dtd_identifier
+char(10) CHARSET utf8
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
+dtd_identifier
+char(10) CHARSET koi8r
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
+dtd_identifier
+char(10) CHARSET utf8
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
+dtd_identifier
+char(10) CHARSET koi8r
+SELECT CHARSET(bug16211_f1())|
+CHARSET(bug16211_f1())
+utf8
+SELECT CHARSET(bug16211_f2())|
+CHARSET(bug16211_f2())
+koi8r
+SELECT CHARSET(mysqltest2.bug16211_f3())|
+CHARSET(mysqltest2.bug16211_f3())
+utf8
+SELECT CHARSET(mysqltest2.bug16211_f4())|
+CHARSET(mysqltest2.bug16211_f4())
+koi8r
+ALTER DATABASE mysqltest1 CHARACTER SET cp1251|
+ALTER DATABASE mysqltest2 CHARACTER SET cp1251|
+SHOW CREATE FUNCTION bug16211_f1|
+Function	sql_mode	Create Function
+bug16211_f1		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8
+RETURN ""
+SHOW CREATE FUNCTION bug16211_f2|
+Function	sql_mode	Create Function
+bug16211_f2		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r
+RETURN ""
+SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
+Function	sql_mode	Create Function
+bug16211_f3		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8
+RETURN ""
+SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
+Function	sql_mode	Create Function
+bug16211_f4		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r
+RETURN ""
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
+dtd_identifier
+char(10) CHARSET utf8
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
+dtd_identifier
+char(10) CHARSET koi8r
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
+dtd_identifier
+char(10) CHARSET utf8
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
+dtd_identifier
+char(10) CHARSET koi8r
+SELECT CHARSET(bug16211_f1())|
+CHARSET(bug16211_f1())
+utf8
+SELECT CHARSET(bug16211_f2())|
+CHARSET(bug16211_f2())
+koi8r
+SELECT CHARSET(mysqltest2.bug16211_f3())|
+CHARSET(mysqltest2.bug16211_f3())
+utf8
+SELECT CHARSET(mysqltest2.bug16211_f4())|
+CHARSET(mysqltest2.bug16211_f4())
+koi8r
+use test|
+DROP DATABASE mysqltest1|
+DROP DATABASE mysqltest2|
+DROP DATABASE IF EXISTS mysqltest1|
+CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
+use mysqltest1|
+CREATE PROCEDURE bug16676_p1(
+IN p1 CHAR(10),
+INOUT p2 CHAR(10),
+OUT p3 CHAR(10))
+BEGIN
+SELECT CHARSET(p1), COLLATION(p1);
+SELECT CHARSET(p2), COLLATION(p2);
+SELECT CHARSET(p3), COLLATION(p3);
+END|
+CREATE PROCEDURE bug16676_p2(
+IN p1 CHAR(10) CHARSET koi8r,
+INOUT p2 CHAR(10) CHARSET cp1251,
+OUT p3 CHAR(10) CHARSET greek)
+BEGIN
+SELECT CHARSET(p1), COLLATION(p1);
+SELECT CHARSET(p2), COLLATION(p2);
+SELECT CHARSET(p3), COLLATION(p3);
+END|
+SET @v2 = 'b'|
+SET @v3 = 'c'|
+CALL bug16676_p1('a', @v2, @v3)|
+CHARSET(p1)	COLLATION(p1)
+utf8	utf8_general_ci
+CHARSET(p2)	COLLATION(p2)
+utf8	utf8_general_ci
+CHARSET(p3)	COLLATION(p3)
+utf8	utf8_general_ci
+CALL bug16676_p2('a', @v2, @v3)|
+CHARSET(p1)	COLLATION(p1)
+koi8r	koi8r_general_ci
+CHARSET(p2)	COLLATION(p2)
+cp1251	cp1251_general_ci
+CHARSET(p3)	COLLATION(p3)
+greek	greek_general_ci
+use test|
+DROP DATABASE mysqltest1|
+drop procedure if exists bug19862|
+CREATE TABLE t11 (a INT)|
+CREATE TABLE t12 (a INT)|
+CREATE FUNCTION bug19862(x INT) RETURNS INT
+BEGIN
+INSERT INTO t11 VALUES (x);
+RETURN x+1;
+END|
+INSERT INTO t12 VALUES (1), (2)|
+SELECT bug19862(a) FROM t12 ORDER BY 1|
+bug19862(a)
+2
+3
+SELECT * FROM t11|
+a
+1
+2
+DROP TABLE t11, t12|
+DROP FUNCTION bug19862|
 drop table if exists t3|
 drop database if exists mysqltest1|
 create table t3 (a int)|
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index ae929cf9c2e..b7f0866019c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -2888,6 +2888,33 @@ select 1 from dual where 1 < any (select 2 from dual);
 select 1 from dual where 1 < all (select 2 from dual where 1!=1);
 1
 1
+create table t1 (s1 char);
+insert into t1 values (1),(2);
+select * from t1 where (s1 < any (select s1 from t1));
+s1
+1
+select * from t1 where not (s1 < any (select s1 from t1));
+s1
+2
+select * from t1 where (s1 < ALL (select s1+1 from t1));
+s1
+1
+select * from t1 where not(s1 < ALL (select s1+1 from t1));
+s1
+2
+select * from t1 where (s1+1 = ANY (select s1 from t1));
+s1
+1
+select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
+s1
+2
+select * from t1 where (s1 = ALL (select s1/s1 from t1));
+s1
+1
+select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
+s1
+2
+drop table t1;
 create table t1 (df decimal(5,1));
 insert into t1 values(1.1);
 insert into t1 values(2.2);
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index 026bcb4b370..75aa339fb29 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -132,3 +132,15 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 5	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	Using index; Using where
 6	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	func	1	Using index; Using where
 drop table t1, t2, t3, t4;
+CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (a int(10), PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (a int(10), b int(10), c int(10),
+PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t3 VALUES (1,2,1);
+SELECT t1.* FROM t1 WHERE (SELECT COUNT(*) FROM t3,t2 WHERE t3.c=t2.a 
+and t2.a='1' AND t1.a=t3.b) > 0;
+a
+2
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index 484c42c41bf..8e37cca6aa9 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -93,6 +93,18 @@ NULL
 0R
 FR
 DROP TABLE bug19904;
+CREATE DEFINER=CURRENT_USER() FUNCTION should_not_parse
+RETURNS STRING SONAME "should_not_parse.so";
+ERROR HY000: Incorrect usage of SONAME and DEFINER
+CREATE DEFINER=someone@somewhere FUNCTION should_not_parse
+RETURNS STRING SONAME "should_not_parse.so";
+ERROR HY000: Incorrect usage of SONAME and DEFINER
+create table t1(f1 int);
+insert into t1 values(1),(2);
+explain select myfunc_int(f1) from t1 order by 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+drop table t1;
 End of 5.0 tests.
 DROP FUNCTION metaphon;
 DROP FUNCTION myfunc_double;
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 426387e04f5..5cce540f058 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -691,9 +691,9 @@ t1	CREATE TABLE `t1` (
   `da` datetime default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
-create table t1 SELECT dt from t2 UNION select sc from t2;
-select * from t1;
-dt
+create table t1 SELECT dt from t2 UNION select trim(sc) from t2;
+select trim(dt) from t1;
+trim(dt)
 1972-10-22 11:50:00
 testc
 show create table t1;
@@ -732,7 +732,7 @@ tetetetetest
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `dt` longblob
+  `dt` blob
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT sv from t2 UNION select b from t2;
@@ -743,7 +743,7 @@ tetetetetest
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `sv` longblob
+  `sv` blob
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;
@@ -755,7 +755,7 @@ tetetetetest
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `i` longblob
+  `i` blob
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT sv from t2 UNION select tx from t2;
@@ -766,7 +766,7 @@ teeeeeeeeeeeest
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `sv` longtext
+  `sv` text
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 SELECT b from t2 UNION select tx from t2;
@@ -777,7 +777,7 @@ teeeeeeeeeeeest
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `b` longblob
+  `b` blob
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1,t2;
 create table t1 select 1 union select -1;
@@ -1306,6 +1306,21 @@ id
 5
 99
 drop table t1;
+create table t1(f1 char(1), f2 char(5), f3 binary(1), f4 binary(5), f5 timestamp, f6 varchar(1) character set utf8 collate utf8_general_ci, f7 text);
+create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1;
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `f1` char(1) default NULL,
+  `f2` char(5) default NULL,
+  `f3` binary(1) default NULL,
+  `f4` binary(5) default NULL,
+  `f5` timestamp NOT NULL default '0000-00-00 00:00:00',
+  `f6` varchar(1) character set utf8 default NULL,
+  `f7` text,
+  `f8` text character set utf8
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1, t2;
 (select avg(1)) union (select avg(1)) union (select avg(1)) union
 (select avg(1)) union (select avg(1)) union (select avg(1)) union
 (select avg(1)) union (select avg(1)) union (select avg(1)) union
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 7d2ab63ca77..534065a33b6 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -193,7 +193,7 @@ c	d
 2	5
 3	10
 drop view v100;
-ERROR 42S02: Unknown table 'test.v100'
+ERROR 42S02: Unknown table 'v100'
 drop view t1;
 ERROR HY000: 'test.t1' is not VIEW
 drop table v1;
@@ -2774,3 +2774,79 @@ Field	Type	Null	Key	Default	Extra
 COALESCE(i,j)	int(11)	YES		NULL	
 DROP VIEW v1;
 DROP TABLE t1,t2;
+CREATE TABLE t1 (s varchar(10));
+INSERT INTO t1 VALUES ('yadda'), ('yady');
+SELECT TRIM(BOTH 'y' FROM s) FROM t1;
+TRIM(BOTH 'y' FROM s)
+adda
+ad
+CREATE VIEW v1 AS SELECT TRIM(BOTH 'y' FROM s) FROM t1;
+SELECT * FROM v1;
+TRIM(BOTH 'y' FROM s)
+adda
+ad
+DROP VIEW v1;
+SELECT TRIM(LEADING 'y' FROM s) FROM t1;
+TRIM(LEADING 'y' FROM s)
+adda
+ady
+CREATE VIEW v1 AS SELECT TRIM(LEADING 'y' FROM s) FROM t1;
+SELECT * FROM v1;
+TRIM(LEADING 'y' FROM s)
+adda
+ady
+DROP VIEW v1;
+SELECT TRIM(TRAILING 'y' FROM s) FROM t1;
+TRIM(TRAILING 'y' FROM s)
+yadda
+yad
+CREATE VIEW v1 AS SELECT TRIM(TRAILING 'y' FROM s) FROM t1;
+SELECT * FROM v1;
+TRIM(TRAILING 'y' FROM s)
+yadda
+yad
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (x INT, y INT);
+CREATE ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1;
+SHOW CREATE VIEW v1;
+View	Create View
+v1	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x` from `t1`
+ALTER VIEW v1 AS SELECT x, y FROM t1;
+SHOW CREATE VIEW v1;
+View	Create View
+v1	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select `t1`.`x` AS `x`,`t1`.`y` AS `y` from `t1`
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (s1 char);
+INSERT INTO t1 VALUES ('Z');
+CREATE VIEW v1 AS SELECT s1 collate latin1_german1_ci AS col FROM t1;
+CREATE VIEW v2 (col) AS SELECT s1 collate latin1_german1_ci FROM t1;
+INSERT INTO v1 (col) VALUES ('b');
+INSERT INTO v2 (col) VALUES ('c');
+SELECT s1 FROM t1;
+s1
+Z
+b
+c
+DROP VIEW v1, v2;
+DROP TABLE t1;
+CREATE TABLE t1 (id INT);
+CREATE VIEW v1 AS SELECT id FROM t1;
+SHOW TABLES;
+Tables_in_test
+t1
+v1
+DROP VIEW v2,v1;
+ERROR 42S02: Unknown table 'v2'
+SHOW TABLES;
+Tables_in_test
+t1
+CREATE VIEW v1 AS SELECT id FROM t1;
+DROP VIEW t1,v1;
+ERROR HY000: 'test.t1' is not VIEW
+SHOW TABLES;
+Tables_in_test
+t1
+DROP TABLE t1;
+DROP VIEW IF EXISTS v1;