Merge sunlight.local:/local_work/tmp_merge-5.0-opt-mysql

into  sunlight.local:/local_work/tmp_merge-5.1-opt-mysql


client/mysql.cc:
  Auto merged
mysql-test/r/date_formats.result:
  Auto merged
mysql-test/r/func_gconcat.result:
  Auto merged
mysql-test/r/func_group.result:
  Auto merged
mysql-test/r/func_str.result:
  Auto merged
mysql-test/r/group_min_max.result:
  Auto merged
BitKeeper/deleted/.del-make_win_src_distribution.sh~f80d8fca44e4e5f1:
  Auto merged
BitKeeper/deleted/.del-mysqld.dsp~ffdbf2d234e23e56:
  Auto merged
BitKeeper/deleted/.del-mysqld_ia64.dsp~7f8cf84d81ee04e2:
  Auto merged
BitKeeper/deleted/.del-mysqldump.dsp~a8bd23547d3fc27e:
  Auto merged
BitKeeper/deleted/.del-mysqldump_ia64.dsp~a2aabe898be35b31:
  Auto merged
mysql-test/r/innodb.result:
  Auto merged
mysql-test/r/innodb_mysql.result:
  Auto merged
mysql-test/r/ps_7ndb.result:
  Auto merged
mysql-test/r/type_ranges.result:
  Auto merged
mysql-test/r/udf.result:
  Auto merged
mysql-test/r/union.result:
  Auto merged
mysql-test/r/view.result:
  Auto merged
mysql-test/t/date_formats.test:
  Auto merged
mysql-test/t/func_gconcat.test:
  Auto merged
mysql-test/t/func_group.test:
  Auto merged
mysql-test/t/group_min_max.test:
  Auto merged
mysql-test/t/innodb.test:
  Auto merged
mysql-test/t/innodb_mysql.test:
  Auto merged
mysql-test/t/mysql.test:
  Auto merged
mysql-test/t/select.test:
  Auto merged
mysql-test/t/sp.test:
  Auto merged
mysql-test/t/view.test:
  Auto merged
sql/field.cc:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/item_func.cc:
  Auto merged
sql/item_func.h:
  Auto merged
sql/item_strfunc.cc:
  Auto merged
sql/item_subselect.cc:
  Auto merged
sql/item_subselect.h:
  Auto merged
sql/opt_range.h:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_class.cc:
  Auto merged
sql/sql_insert.cc:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_show.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged
sql/time.cc:
  Auto merged
storage/ndb/test/ndbapi/Makefile.am:
  Auto merged
strings/decimal.c:
  Auto merged
mysql-test/r/analyse.result:
  Manual merge
mysql-test/r/bigint.result:
  Manual merge
mysql-test/r/create.result:
  Manual merge
mysql-test/r/information_schema.result:
  Manual merge
mysql-test/r/ps_2myisam.result:
  Manual merge
mysql-test/r/ps_3innodb.result:
  Manual merge
mysql-test/r/ps_4heap.result:
  Manual merge
mysql-test/r/ps_5merge.result:
  Manual merge
mysql-test/r/ps_6bdb.result:
  Manual merge
mysql-test/r/rpl_insert_id.result:
  Manual merge
mysql-test/r/select.result:
  Manual merge
mysql-test/r/sp.result:
  Manual merge
mysql-test/r/subselect.result:
  Manual merge
mysql-test/t/information_schema.test:
  Manual merge
mysql-test/t/rpl_insert_id.test:
  Manual merge
sql/field.h:
  Manual merge
sql/item.cc:
  Manual merge
sql/item.h:
  Manual merge
sql/item_strfunc.h:
  Manual merge
sql/item_sum.cc:
  Manual merge
sql/mysql_priv.h:
  Manual merge
sql/share/errmsg.txt:
  Manual merge
sql/sql_class.h:
  Manual merge
sql/sql_select.cc:
  Manual merge

29 files changed, 814 insertions, 56 deletions

diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result
index 3bb8e30fc0d..ba56a98b4a9 100644
--- a/mysql-test/r/analyse.result
+++ b/mysql-test/r/analyse.result
@@ -39,10 +39,10 @@ t2	CREATE TABLE `t2` (
   `Field_name` varbinary(255) NOT NULL DEFAULT '',
   `Min_value` varbinary(255) DEFAULT NULL,
   `Max_value` varbinary(255) DEFAULT NULL,
-  `Min_length` bigint(11) NOT NULL DEFAULT '0',
-  `Max_length` bigint(11) NOT NULL DEFAULT '0',
-  `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0',
-  `Nulls` bigint(11) NOT NULL DEFAULT '0',
+  `Min_length` int(11) NOT NULL DEFAULT '0',
+  `Max_length` int(11) NOT NULL DEFAULT '0',
+  `Empties_or_zeros` int(11) NOT NULL DEFAULT '0',
+  `Nulls` int(11) NOT NULL DEFAULT '0',
   `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '',
   `Std` varbinary(255) DEFAULT NULL,
   `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT ''
@@ -58,10 +58,10 @@ t2	CREATE TABLE `t2` (
   `Field_name` varbinary(255) NOT NULL DEFAULT '',
   `Min_value` varbinary(255) DEFAULT NULL,
   `Max_value` varbinary(255) DEFAULT NULL,
-  `Min_length` bigint(11) NOT NULL DEFAULT '0',
-  `Max_length` bigint(11) NOT NULL DEFAULT '0',
-  `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0',
-  `Nulls` bigint(11) NOT NULL DEFAULT '0',
+  `Min_length` int(11) NOT NULL DEFAULT '0',
+  `Max_length` int(11) NOT NULL DEFAULT '0',
+  `Empties_or_zeros` int(11) NOT NULL DEFAULT '0',
+  `Nulls` int(11) NOT NULL DEFAULT '0',
   `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '',
   `Std` varbinary(255) DEFAULT NULL,
   `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT ''
@@ -81,10 +81,10 @@ t2	CREATE TABLE `t2` (
   `Field_name` varbinary(255) NOT NULL DEFAULT '',
   `Min_value` varbinary(255) DEFAULT NULL,
   `Max_value` varbinary(255) DEFAULT NULL,
-  `Min_length` bigint(11) NOT NULL DEFAULT '0',
-  `Max_length` bigint(11) NOT NULL DEFAULT '0',
-  `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0',
-  `Nulls` bigint(11) NOT NULL DEFAULT '0',
+  `Min_length` int(11) NOT NULL DEFAULT '0',
+  `Max_length` int(11) NOT NULL DEFAULT '0',
+  `Empties_or_zeros` int(11) NOT NULL DEFAULT '0',
+  `Nulls` int(11) NOT NULL DEFAULT '0',
   `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '',
   `Std` varbinary(255) DEFAULT NULL,
   `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT ''
diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result
index c76385fa334..c27ce18cfd3 100644
--- a/mysql-test/r/bigint.result
+++ b/mysql-test/r/bigint.result
@@ -174,7 +174,7 @@ create table t1 select 1 as 'a';
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` bigint(1) NOT NULL DEFAULT '0'
+  `a` int(1) NOT NULL DEFAULT '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 select 9223372036854775809 as 'a';
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index b9d97751e0d..9ecaaa66cc3 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -465,7 +465,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,
@@ -681,7 +681,7 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `b` int(11) NOT NULL,
   `a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
-  `c` bigint(1) NOT NULL DEFAULT '0',
+  `c` int(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`a`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
@@ -694,7 +694,7 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `b` int(11) DEFAULT NULL,
   `a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
-  `c` bigint(1) NOT NULL DEFAULT '0',
+  `c` int(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`a`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 773efe50749..5a1ce175765 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -647,3 +647,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 6ea28efd91c..df54e0151eb 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -812,6 +812,144 @@ SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
 MAX(id)
 NULL
 DROP TABLE t1;
+create table t1m (a int) engine=myisam;
+create table t1i (a int) engine=innodb;
+create table t2m (a int) engine=myisam;
+create table t2i (a int) engine=innodb;
+insert into t2m values (5);
+insert into t2i values (5);
+select min(a) from t1m;
+min(a)
+NULL
+select min(7) from t1m;
+min(7)
+NULL
+select min(7) from DUAL;
+min(7)
+NULL
+explain select min(7) from t2m join t1m;
+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 min(7) from t2m join t1m;
+min(7)
+NULL
+select max(a) from t1m;
+max(a)
+NULL
+select max(7) from t1m;
+max(7)
+NULL
+select max(7) from DUAL;
+max(7)
+NULL
+explain select max(7) from t2m join t1m;
+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(7) from t2m join t1m;
+max(7)
+NULL
+select 1, min(a) from t1m where a=99;
+1	min(a)
+1	NULL
+select 1, min(a) from t1m where 1=99;
+1	min(a)
+1	NULL
+select 1, min(1) from t1m where a=99;
+1	min(1)
+1	NULL
+select 1, min(1) from t1m where 1=99;
+1	min(1)
+1	NULL
+select 1, max(a) from t1m where a=99;
+1	max(a)
+1	NULL
+select 1, max(a) from t1m where 1=99;
+1	max(a)
+1	NULL
+select 1, max(1) from t1m where a=99;
+1	max(1)
+1	NULL
+select 1, max(1) from t1m where 1=99;
+1	max(1)
+1	NULL
+select min(a) from t1i;
+min(a)
+NULL
+select min(7) from t1i;
+min(7)
+NULL
+select min(7) from DUAL;
+min(7)
+NULL
+explain select min(7) from t2i join t1i;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
+select min(7) from t2i join t1i;
+min(7)
+NULL
+select max(a) from t1i;
+max(a)
+NULL
+select max(7) from t1i;
+max(7)
+NULL
+select max(7) from DUAL;
+max(7)
+NULL
+explain select max(7) from t2i join t1i;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
+select max(7) from t2i join t1i;
+max(7)
+NULL
+select 1, min(a) from t1i where a=99;
+1	min(a)
+1	NULL
+select 1, min(a) from t1i where 1=99;
+1	min(a)
+1	NULL
+select 1, min(1) from t1i where a=99;
+1	min(1)
+1	NULL
+select 1, min(1) from t1i where 1=99;
+1	min(1)
+1	NULL
+select 1, max(a) from t1i where a=99;
+1	max(a)
+1	NULL
+select 1, max(a) from t1i where 1=99;
+1	max(a)
+1	NULL
+select 1, max(1) from t1i where a=99;
+1	max(1)
+1	NULL
+select 1, max(1) from t1i where 1=99;
+1	max(1)
+1	NULL
+explain select count(*), min(7), max(7) from t1m, t1i;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1m	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
+select count(*), min(7), max(7) from t1m, t1i;
+count(*)	min(7)	max(7)
+0	NULL	NULL
+explain select count(*), min(7), max(7) from t1m, t2i;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1m	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
+select count(*), min(7), max(7) from t1m, t2i;
+count(*)	min(7)	max(7)
+0	NULL	NULL
+explain select count(*), min(7), max(7) from t2m, t1i;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2m	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
+select count(*), min(7), max(7) from t2m, t1i;
+count(*)	min(7)	max(7)
+0	NULL	NULL
+drop table t1m, t1i, t2m, t2i;
 CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));
 INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
 SELECT * FROM t1;
@@ -979,3 +1117,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 467ae3c1571..fed6b5ddb19 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1041,6 +1041,49 @@ select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
 f1	f2
 test	a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
 drop table t1;
+CREATE TABLE t1 (a varchar(10));
+INSERT INTO t1 VALUES ('abc'), ('xyz');
+SELECT a, CONCAT(a,' ',a) AS c FROM t1
+HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;
+a	c
+abc	abc abc
+xyz	xyz xyz
+SELECT a, CONCAT(a,' ',a) AS c FROM t1
+HAVING LEFT(CONCAT(a,' ',a),
+LENGTH(CONCAT(a,' ',a))-
+INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;
+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/information_schema.result b/mysql-test/r/information_schema.result
index a472aa0c68d..32ef2000cf2 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1218,6 +1218,76 @@ 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
 End of 5.0 tests.
 select * from information_schema.engines WHERE ENGINE="MyISAM";
 ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 72ae29185bc..bc1e85c80f7 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/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index d5b52feb06c..9f177e99a17 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -60,6 +60,35 @@ c.c_id = 218 and expiredate is null;
 slai_id
 12
 drop table t1, t2;
+CREATE TABLE t1 (a int, b int, KEY b (b)) Engine=InnoDB;
+CREATE TABLE t2 (a int, b int, PRIMARY KEY  (a,b)) Engine=InnoDB;
+CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY  (a), 
+UNIQUE KEY b (b,c), KEY a (a,b,c)) Engine=InnoDB;
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
+INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
+INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
+INSERT INTO t2 SELECT a + 1, b FROM t2;
+DELETE FROM t2 WHERE a = 1 AND b < 2;
+INSERT INTO t3 VALUES (1,1,1),(2,1,2);
+INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3;
+INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3;
+SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE 
+t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) 
+ORDER BY t1.b LIMIT 2;
+b	a
+1	1
+2	2
+SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE 
+t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) 
+ORDER BY t1.b LIMIT 5;
+b	a
+1	1
+2	2
+2	2
+3	3
+3	3
+DROP TABLE t1, t2, t3;
 create table t1m (a int) engine=myisam;
 create table t1i (a int) engine=innodb;
 create table t2m (a int) engine=myisam;
diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result
index 4b7084e813c..d70366a7589 100644
--- a/mysql-test/r/mysql.result
+++ b/mysql-test/r/mysql.result
@@ -36,19 +36,19 @@ Tables_in_test
 t1
 t2
 t3
-
+_
 Test delimiter : from command line
 a
 1
-
+_
 Test delimiter :; from command line
 a
 1
-
+_
 Test 'go' command(vertical output) G
 *************************** 1. row ***************************
 a: 1
-
+_
 Test  'go' command g
 a
 1
diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result
index 2d9d39393b1..5629d3dab33 100644
--- a/mysql-test/r/odbc.result
+++ b/mysql-test/r/odbc.result
@@ -14,3 +14,14 @@ explain select * from t1 where b is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 drop table t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t1 VALUES (NULL);
+SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL;
+a	last_insert_id()
+1	1
+SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL;
+a	last_insert_id()
+SELECT sql_no_cache a, last_insert_id() FROM t1;
+a	last_insert_id()
+1	1
+DROP TABLE t1;
diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result
index 225e306b3cf..28c1dc59540 100644
--- a/mysql-test/r/olap.result
+++ b/mysql-test/r/olap.result
@@ -620,8 +620,8 @@ CREATE VIEW v1 AS
 SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
 DESC v1;
 Field	Type	Null	Key	Default	Extra
-a	bigint(11)	YES		NULL	
-LENGTH(a)	bigint(10)	YES		NULL	
+a	int(11)	YES		0	
+LENGTH(a)	int(10)	YES		NULL	
 COUNT(*)	bigint(21)	NO		0	
 SELECT * FROM v1;
 a	LENGTH(a)	COUNT(*)
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index 5ba601bf305..d42693bdb95 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -1775,7 +1775,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL DEFAULT '0',
+  `const01` int(1) NOT NULL DEFAULT '0',
   `param01` bigint(20) DEFAULT NULL,
   `const02` decimal(2,1) NOT NULL DEFAULT '0.0',
   `param02` decimal(65,30) DEFAULT NULL,
@@ -1805,7 +1805,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
+def	test	t5	t5	const01	const01	3	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
 def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 836fb7b58c2..9bc66a00a34 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -1758,7 +1758,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL DEFAULT '0',
+  `const01` int(1) NOT NULL DEFAULT '0',
   `param01` bigint(20) DEFAULT NULL,
   `const02` decimal(2,1) NOT NULL DEFAULT '0.0',
   `param02` decimal(65,30) DEFAULT NULL,
@@ -1788,7 +1788,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
+def	test	t5	t5	const01	const01	3	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
 def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index 150820d92f0..370065351d1 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -1759,7 +1759,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL DEFAULT '0',
+  `const01` int(1) NOT NULL DEFAULT '0',
   `param01` bigint(20) DEFAULT NULL,
   `const02` decimal(2,1) NOT NULL DEFAULT '0.0',
   `param02` decimal(65,30) DEFAULT NULL,
@@ -1789,7 +1789,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
+def	test	t5	t5	const01	const01	3	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
 def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index 72573ce3294..6a304787ffb 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -1695,7 +1695,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL DEFAULT '0',
+  `const01` int(1) NOT NULL DEFAULT '0',
   `param01` bigint(20) DEFAULT NULL,
   `const02` decimal(2,1) NOT NULL DEFAULT '0.0',
   `param02` decimal(65,30) DEFAULT NULL,
@@ -1725,7 +1725,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
+def	test	t5	t5	const01	const01	3	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
 def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
@@ -4709,7 +4709,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL DEFAULT '0',
+  `const01` int(1) NOT NULL DEFAULT '0',
   `param01` bigint(20) DEFAULT NULL,
   `const02` decimal(2,1) NOT NULL DEFAULT '0.0',
   `param02` decimal(65,30) DEFAULT NULL,
@@ -4739,7 +4739,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
+def	test	t5	t5	const01	const01	3	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
 def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result
index 44b4ebf33d4..94e6da6ccd1 100644
--- a/mysql-test/r/ps_6bdb.result
+++ b/mysql-test/r/ps_6bdb.result
@@ -1758,7 +1758,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL DEFAULT '0',
+  `const01` int(1) NOT NULL DEFAULT '0',
   `param01` bigint(20) DEFAULT NULL,
   `const02` decimal(2,1) NOT NULL DEFAULT '0.0',
   `param02` decimal(65,30) DEFAULT NULL,
@@ -1788,7 +1788,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
+def	test	t5	t5	const01	const01	3	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
 def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index 543435e4cd9..772848dcf38 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -1758,7 +1758,7 @@ NULL                        as const12, @arg12 as param12,
 show create table t5 ;
 Table	Create Table
 t5	CREATE TABLE `t5` (
-  `const01` bigint(1) NOT NULL default '0',
+  `const01` int(1) NOT NULL default '0',
   `param01` bigint(20) default NULL,
   `const02` decimal(2,1) NOT NULL default '0.0',
   `param02` decimal(65,30) default NULL,
@@ -1788,7 +1788,7 @@ t5	CREATE TABLE `t5` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t5 ;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def	test	t5	t5	const01	const01	8	1	1	N	32769	0	63
+def	test	t5	t5	const01	const01	3	1	1	N	32769	0	63
 def	test	t5	t5	param01	param01	8	20	1	Y	32768	0	63
 def	test	t5	t5	const02	const02	246	4	3	N	1	1	63
 def	test	t5	t5	param02	param02	246	67	32	Y	0	30	63
diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result
index dbdbcf70944..6252d8b8292 100644
--- a/mysql-test/r/rpl_insert_id.result
+++ b/mysql-test/r/rpl_insert_id.result
@@ -73,6 +73,19 @@ CREATE TABLE t1 ( a INT UNIQUE );
 SET FOREIGN_KEY_CHECKS=0;
 INSERT INTO t1 VALUES (1),(1);
 Got one of the listed errors
+create table t1(a int auto_increment, key(a));
+create table t2(a int);
+insert into t1 (a) values (null);
+insert into t2 (a) select a from t1 where a is null;
+insert into t2 (a) select a from t1 where a is null;
+select * from t2;
+a
+1
+select * from t2;
+a
+1
+drop table t1;
+drop table t2;
 drop function if exists bug15728;
 drop function if exists bug15728_insert;
 drop table if exists t1, t2;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index f2d60a682e7..dacf1254b51 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2730,6 +2730,32 @@ ERROR 42000: Key 'a' doesn't exist in table 't1'
 EXPLAIN SELECT * FROM t1 FORCE INDEX (a);
 ERROR 42000: Key 'a' doesn't exist in table 't1'
 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 (i BIGINT UNSIGNED NOT NULL);
 INSERT INTO t1 VALUES (10);
 SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;
@@ -3125,6 +3151,55 @@ select count(*)
 from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id;
 count(*)
 6
+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;
 drop table t1,t2,t3;
 create table t1 (a int);
 create table t2 (b int);
@@ -3395,3 +3470,12 @@ a	t1.b + 0	t1.c + 0	a	t2.b + 0	c	d
 1	0	1	1	0	1	NULL
 2	0	1	NULL	NULL	NULL	NULL
 drop table t1,t2;
+SELECT 0.9888889889 * 1.011111411911;
+0.9888889889 * 1.011111411911
+0.9998769417899202067879
+prepare stmt from 'select 1 as " a "';
+Warnings:
+Warning	1466	Leading spaces are removed from name ' a '
+execute stmt;
+a 
+1
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index db72d190441..d9c4577e2b2 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4915,7 +4915,7 @@ create table t3 as select * from v1|
 show create table t3|
 Table	Create Table
 t3	CREATE TABLE `t3` (
-  `j` bigint(11) DEFAULT NULL
+  `j` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t3|
 j
@@ -5051,4 +5051,23 @@ concat('data was: /', var1, '/')
 data was: /1/
 drop table t3|
 drop procedure bug15217|
+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 t1,t2;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 75a9b422691..f2852e94071 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1087,24 +1087,24 @@ CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` bigint(1) NOT NULL DEFAULT '0',
-  `(SELECT 1)` bigint(1) NOT NULL DEFAULT '0'
+  `a` int(1) NOT NULL DEFAULT '0',
+  `(SELECT 1)` int(1) NOT NULL DEFAULT '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` bigint(1) NOT NULL DEFAULT '0',
-  `(SELECT a)` bigint(1) NOT NULL DEFAULT '0'
+  `a` int(1) NOT NULL DEFAULT '0',
+  `(SELECT a)` int(1) NOT NULL DEFAULT '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` bigint(1) NOT NULL DEFAULT '0',
-  `(SELECT a+0)` bigint(3) NOT NULL DEFAULT '0'
+  `a` int(1) NOT NULL DEFAULT '0',
+  `(SELECT a+0)` int(3) NOT NULL DEFAULT '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
@@ -2854,6 +2854,67 @@ a
 3
 4
 DROP TABLE t1,t2,t3;
+purge master logs before (select adddate(current_timestamp(), interval -4 day));
+CREATE TABLE t1 (f1 INT);
+CREATE TABLE t2 (f2 INT);
+INSERT INTO t1 VALUES (1);
+SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2);
+f1
+1
+SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0);
+f1
+1
+INSERT INTO t2 VALUES (1);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
+f1
+1
+DROP TABLE t1, t2;
+select 1 from dual where 1 < any (select 2);
+1
+1
+select 1 from dual where 1 < all (select 2);
+1
+1
+select 1 from dual where 2 > any (select 1);
+1
+1
+select 1 from dual where 2 > all (select 1);
+1
+1
+select 1 from dual where 1 < any (select 2 from dual);
+1
+1
+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);
@@ -3204,3 +3265,84 @@ i
 10000000000000000000
 DROP TABLE t1;
 DROP TABLE t2;
+CREATE TABLE t1 (
+id bigint(20) unsigned NOT NULL auto_increment,
+name varchar(255) NOT NULL,
+PRIMARY KEY  (id)
+);
+INSERT INTO t1 VALUES
+(1, 'Balazs'), (2, 'Joe'), (3, 'Frank');
+CREATE TABLE t2 (
+id bigint(20) unsigned NOT NULL auto_increment,
+mid bigint(20) unsigned NOT NULL,
+date date NOT NULL,
+PRIMARY KEY  (id)
+);
+INSERT INTO t2 VALUES 
+(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'),
+(4, 2, '2006-04-20'), (5, 1, '2006-05-01');
+SELECT *,
+(SELECT date FROM t2 WHERE mid = t1.id
+ORDER BY date DESC LIMIT 0, 1) AS date_last,
+(SELECT date FROM t2 WHERE mid = t1.id
+ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last
+FROM t1;
+id	name	date_last	date_next_to_last
+1	Balazs	2006-05-01	NULL
+2	Joe	2006-04-20	NULL
+3	Frank	2006-04-13	NULL
+SELECT *,
+(SELECT COUNT(*) FROM t2 WHERE mid = t1.id
+ORDER BY date DESC LIMIT 1, 1) AS date_count
+FROM t1;
+id	name	date_count
+1	Balazs	NULL
+2	Joe	NULL
+3	Frank	NULL
+SELECT *,
+(SELECT date FROM t2 WHERE mid = t1.id
+ORDER BY date DESC LIMIT 0, 1) AS date_last,
+(SELECT date FROM t2 WHERE mid = t1.id
+ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last
+FROM t1;
+id	name	date_last	date_next_to_last
+1	Balazs	2006-05-01	2006-03-30
+2	Joe	2006-04-20	2006-04-06
+3	Frank	2006-04-13	NULL
+DROP TABLE t1,t2;
+CREATE TABLE t1 (
+i1 int(11) NOT NULL default '0',
+i2 int(11) NOT NULL default '0',
+t datetime NOT NULL default '0000-00-00 00:00:00',
+PRIMARY KEY  (i1,i2,t)
+);
+INSERT INTO t1 VALUES 
+(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),
+(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),
+(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),
+(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),
+(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),
+(24,2,'2005-05-27 12:40:06');
+CREATE TABLE t2 (
+i1 int(11) NOT NULL default '0',
+i2 int(11) NOT NULL default '0',
+t datetime default NULL,
+PRIMARY KEY  (i1)
+);
+INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40');
+EXPLAIN
+SELECT * FROM t1,t2
+WHERE t1.t = (SELECT t1.t FROM t1 
+WHERE t1.t < t2.t  AND t1.i2=1 AND t2.i1=t1.i1
+ORDER BY t1.t DESC LIMIT 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	index	NULL	PRIMARY	16	NULL	11	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	range	PRIMARY	PRIMARY	16	NULL	5	Using where; Using index
+SELECT * FROM t1,t2
+WHERE t1.t = (SELECT t1.t FROM t1 
+WHERE t1.t < t2.t  AND t1.i2=1 AND t2.i1=t1.i1
+ORDER BY t1.t DESC LIMIT 1);
+i1	i2	t	i1	i2	t
+24	1	2005-05-27 12:40:30	24	1	2006-06-20 12:29:40
+DROP TABLE t1, t2;
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/type_ranges.result b/mysql-test/r/type_ranges.result
index df3298bad1a..32bb6abf7ac 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -273,7 +273,7 @@ create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, r
 show full columns from t2;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 auto	bigint(12) unsigned	NULL	NO	PRI	0		#	
-t1	bigint(1)	NULL	NO		0		#	
+t1	int(1)	NULL	NO		0		#	
 t2	varchar(1)	latin1_swedish_ci	NO				#	
 t3	varchar(256)	latin1_swedish_ci	NO				#	
 t4	varbinary(256)	NULL	NO				#	
@@ -301,7 +301,7 @@ show full columns from t3;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 c1	int(11)	NULL	YES		NULL		#	
 c2	int(11)	NULL	YES		NULL		#	
-const	bigint(1)	NULL	NO		0		#	
+const	int(1)	NULL	NO		0		#	
 drop table t1,t2,t3;
 create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));
 drop table t1;
diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index d6b96bae8d4..624895f71c9 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -93,6 +93,12 @@ NULL
 0R
 FR
 DROP TABLE bug19904;
+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 43598caa86f..f1eff937e1a 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 af131be7241..f51654f3682 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2735,4 +2735,88 @@ m	e
 4	a
 1	b
 DROP VIEW v1;
-DROP TABLE IF EXISTS t1,t2;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL);
+CREATE VIEW v1 AS SELECT a, b FROM t1;
+INSERT INTO v1 (b) VALUES (2);
+Warnings:
+Warning	1423	Field of view 'test.v1' underlying table doesn't have a default value
+SET SQL_MODE = STRICT_ALL_TABLES;
+INSERT INTO v1 (b) VALUES (4);
+ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value
+SET SQL_MODE = '';
+SELECT * FROM t1;
+a	b
+0	2
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (firstname text, surname text);
+INSERT INTO t1 VALUES
+("Bart","Simpson"),("Milhouse","van Houten"),("Montgomery","Burns");
+CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1;
+SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
+LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))) AS f1
+FROM v1;
+f1
+BartBart
+Milhouse vanMilhouse van
+MontgomeryMontgomery
+DROP VIEW v1;
+DROP TABLE t1;
+CREATE TABLE t1 (i int, j int);
+CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1;
+DESCRIBE v1;
+Field	Type	Null	Key	Default	Extra
+COALESCE(i,j)	int(11)	YES		NULL	
+CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1;
+DESCRIBE t2;
+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 (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;