Merge olga.mysql.com:/home/igor/mysql-5.1

into  olga.mysql.com:/home/igor/mysql-5.1-opt-merge


mysql-test/r/alter_table.result:
  Auto merged
mysql-test/r/create.result:
  Auto merged
mysql-test/r/innodb_mysql.result:
  Auto merged
mysql-test/r/sp.result:
  Auto merged
mysql-test/r/user_var.result:
  Auto merged
mysql-test/t/alter_table.test:
  Auto merged
mysql-test/t/sp.test:
  Auto merged
mysql-test/t/strict.test:
  Auto merged
mysql-test/t/subselect3.test:
  Auto merged
sql/field.h:
  Auto merged
sql/handler.h:
  Auto merged
sql/item_func.cc:
  Auto merged
sql/item_func.h:
  Auto merged
sql/mysqld.cc:
  Auto merged
sql/sp_head.cc:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_class.h:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_update.cc:
  Auto merged
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_7ndb.result:
  Manual merge.
sql/sql_table.cc:
  Manual merge.
sql/sql_view.cc:
  Manual merge.

44 files changed, 796 insertions, 386 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 63dc13a4b6d..a9c01f308f7 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -846,6 +846,25 @@ id
 50
 51
 drop table t1;
+set @orig_sql_mode = @@sql_mode;
+set sql_mode="no_zero_date";
+create table t1(f1 int);
+alter table t1 add column f2 datetime not null, add column f21 date not null;
+insert into t1 values(1,'2000-01-01','2000-01-01');
+alter table t1 add column f3 datetime not null;
+ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'f3' at row 1
+alter table t1 add column f3 date not null;
+ERROR 22007: Incorrect date value: '0000-00-00' for column 'f3' at row 1
+alter table t1 add column f4 datetime not null default '2002-02-02',
+add column f41 date not null;
+ERROR 22007: Incorrect date value: '0000-00-00' for column 'f41' at row 1
+alter table t1 add column f4 datetime not null default '2002-02-02',
+add column f41 date not null default '2002-02-02';
+select * from t1;
+f1	f2	f21	f4	f41
+1	2000-01-01 00:00:00	2000-01-01	2002-02-02 00:00:00	2002-02-02
+drop table t1;
+set sql_mode= @orig_sql_mode;
 create table t1 (v varchar(32));
 insert into t1 values ('def'),('abc'),('hij'),('3r4f');
 select * from t1;
@@ -1137,3 +1156,17 @@ Field	Type	Null	Key	Default	Extra
 unsigned_int_field	bigint(20) unsigned	NO	MUL		
 char_field	char(10)	YES		NULL	
 DROP TABLE t2;
+CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
+INSERT INTO t1 VALUES (1, 2, NULL);
+SELECT * FROM t1;
+f1	f2	f3
+1	2	NULL
+ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1;
+SELECT * FROM t1;
+f1	f3	f2
+1	NULL	2
+ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;
+SELECT * FROM t1;
+f1	f2	f3
+1	2	NULL
+DROP TABLE t1;
diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result
index b1ed1ea2bed..c0b16778f9c 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` 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',
+  `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',
   `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` 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',
+  `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',
   `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` 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',
+  `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',
   `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/archive_gis.result b/mysql-test/r/archive_gis.result
index 71eeb063d59..901f8a9acc2 100644
--- a/mysql-test/r/archive_gis.result
+++ b/mysql-test/r/archive_gis.result
@@ -402,8 +402,8 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort; Using join cache
-1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
+1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer
 Warnings:
 Note	1003	select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result
index e46b56f4215..68206c0dc0e 100644
--- a/mysql-test/r/compress.result
+++ b/mysql-test/r/compress.result
@@ -1434,8 +1434,8 @@ companynr	companynr
 41	40
 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer
 select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
 fld1	companynr	fld3	period
 038008	37	reporters	1008
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 3abba528164..8601e6890de 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -1454,8 +1454,8 @@ insert into t1 values ('123'), ('456');
 explain
 select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	Y	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort; Using join cache
-1	SIMPLE	Z	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	Y	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	SIMPLE	Z	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
 select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
 substr(Z.a,-1)	a
 3	123
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 750d542d14d..7da82ada121 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -57,8 +57,8 @@ a	b	a	b
 3	c	3	c
 explain select * from t1 as x1, (select * from t1) as x2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	4	Using join cache
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	Using join buffer
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	
 drop table if exists  t2,t3;
 select * from (select 1) as a;
@@ -188,14 +188,14 @@ pla_id	test
 105	3
 explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	Using join cache
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	Using where
+1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	Using join cache
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	Using where
+1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 drop table t1,t2;
@@ -245,8 +245,8 @@ a	a
 2	2
 explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using join cache
-1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer
 4	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
 5	UNION	t1	ALL	NULL	NULL	NULL	NULL	2	
 NULL	UNION RESULT	<union4,5>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -312,8 +312,8 @@ b	3.5000
 explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
-2	DERIVED	x	ALL	NULL	NULL	NULL	NULL	17	Using temporary; Using filesort; Using join cache
-2	DERIVED	y	ALL	NULL	NULL	NULL	NULL	17	Using where
+2	DERIVED	x	ALL	NULL	NULL	NULL	NULL	17	Using temporary; Using filesort
+2	DERIVED	y	ALL	NULL	NULL	NULL	NULL	17	Using where; Using join buffer
 drop table t1;
 create table t2 (a int, b int, primary key (a));
 insert into t2 values (1,7),(2,7);
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 3419ce150eb..002dbc6ccb5 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -174,8 +174,8 @@ INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
 explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	Using temporary
-1	SIMPLE	t3	ref	a	a	5	test.t1.b	2	Using where; Using index; Using join cache
-1	SIMPLE	t2	index	a	a	4	NULL	5	Using where; Using index; Distinct
+1	SIMPLE	t3	ref	a	a	5	test.t1.b	2	Using where; Using index
+1	SIMPLE	t2	index	a	a	4	NULL	5	Using where; Using index; Distinct; Using join buffer
 SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
 a
 1
@@ -299,12 +299,12 @@ WHERE
 ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
 AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	id	id	4	NULL	2	Using index; Using temporary; Using join cache
-1	SIMPLE	t2	index	id	id	8	NULL	1	Using index; Distinct; Using join cache
-1	SIMPLE	t3	index	id	id	8	NULL	1	Using index; Distinct; Using join cache
-1	SIMPLE	j_lj_t2	index	id	id	4	NULL	2	Using where; Using index; Distinct
-1	SIMPLE	t2_lj	ref	id	id	4	test.j_lj_t2.id	1	Using where; Using index; Distinct; Using join cache
-1	SIMPLE	j_lj_t3	index	id	id	4	NULL	2	Using where; Using index; Distinct
+1	SIMPLE	t1	index	id	id	4	NULL	2	Using index; Using temporary
+1	SIMPLE	t2	index	id	id	8	NULL	1	Using index; Distinct; Using join buffer
+1	SIMPLE	t3	index	id	id	8	NULL	1	Using index; Distinct; Using join buffer
+1	SIMPLE	j_lj_t2	index	id	id	4	NULL	2	Using where; Using index; Distinct; Using join buffer
+1	SIMPLE	t2_lj	ref	id	id	4	test.j_lj_t2.id	1	Using where; Using index; Distinct
+1	SIMPLE	j_lj_t3	index	id	id	4	NULL	2	Using where; Using index; Distinct; Using join buffer
 1	SIMPLE	t3_lj	ref	id	id	4	test.j_lj_t3.id	1	Using where; Using index; Distinct
 SELECT DISTINCT
 t1.id
@@ -514,8 +514,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
 EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1_1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using join cache
-1	SIMPLE	t1_2	index	NULL	PRIMARY	4	NULL	3	Using index; Distinct
+1	SIMPLE	t1_1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
+1	SIMPLE	t1_2	index	NULL	PRIMARY	4	NULL	3	Using index; Distinct; Using join buffer
 EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
 WHERE t1_1.a = t1_2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
diff --git a/mysql-test/r/func_date_add.result b/mysql-test/r/func_date_add.result
index ac5709260fd..a7f2383848d 100644
--- a/mysql-test/r/func_date_add.result
+++ b/mysql-test/r/func_date_add.result
@@ -84,4 +84,15 @@ CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR
 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK;
 CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK
 2006-10-03
+create table t1 (a int, b varchar(10));
+insert into t1 values (1, '2001-01-01'),(2, '2002-02-02');
+select '2007-01-01' + interval a day from t1;
+'2007-01-01' + interval a day
+2007-01-02
+2007-01-03
+select b + interval a day from t1;
+b + interval a day
+2001-01-02
+2002-02-04
+drop table t1;
 End of 5.0 tests
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index bb09217d9d7..3fd204c84c9 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -769,4 +769,51 @@ Warnings:
 Warning	1260	1 line(s) were cut by GROUP_CONCAT()
 SET group_concat_max_len = DEFAULT;
 DROP TABLE t1;
+SET group_concat_max_len= 65535;
+CREATE TABLE t1( a TEXT, b INTEGER );
+INSERT INTO t1 VALUES ( 'a', 0 ), ( 'b', 1 );
+SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
+GROUP_CONCAT( a ORDER BY b )
+a,b
+SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
+GROUP_CONCAT(DISTINCT a ORDER BY b)
+a,b
+SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
+GROUP_CONCAT(DISTINCT a)
+a,b
+SET group_concat_max_len= 10;
+SELECT GROUP_CONCAT(a ORDER BY b) FROM t1;
+GROUP_CONCAT(a ORDER BY b)
+a,b
+SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
+GROUP_CONCAT(DISTINCT a ORDER BY b)
+a,b
+SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
+GROUP_CONCAT(DISTINCT a)
+a,b
+SET group_concat_max_len= 65535;
+CREATE TABLE t2( a TEXT );
+INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) );
+INSERT INTO t2 VALUES( REPEAT( 'b', 5000 ) );
+INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) );
+SELECT LENGTH( GROUP_CONCAT( DISTINCT a ) ) FROM t2;
+LENGTH( GROUP_CONCAT( DISTINCT a ) )
+10001
+CREATE TABLE t3( a TEXT, b INT  );
+INSERT INTO t3 VALUES( REPEAT( 'a', 65534 ), 1 );
+INSERT INTO t3 VALUES( REPEAT( 'a', 65535 ), 2 );
+INSERT INTO t3 VALUES( REPEAT( 'a', 65536 ), 3 );
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1;
+LENGTH( GROUP_CONCAT( a ) )
+65534
+SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2;
+LENGTH( GROUP_CONCAT( a ) )
+65535
+SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3;
+LENGTH( GROUP_CONCAT( a ) )
+65535
+SET group_concat_max_len= DEFAULT;
+DROP TABLE t1, t2, t3;
 End of 5.0 tests
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 1f640dec0a4..fce01ffd171 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -613,8 +613,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain
 select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	k1	k1	7	NULL	1	Using where; Using index; Using join cache
-1	SIMPLE	t2	range	k1	k1	3	NULL	4	Using where; Using index
+1	SIMPLE	t1	range	k1	k1	7	NULL	1	Using where; Using index
+1	SIMPLE	t2	range	k1	k1	3	NULL	4	Using where; Using index; Using join buffer
 explain
 select min(a4 - 0.01) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -650,8 +650,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain
 select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	k2	k2	4	NULL	6	Using where; Using index; Using join cache
-1	SIMPLE	t1	index	NULL	PRIMARY	3	NULL	15	Using index
+1	SIMPLE	t2	range	k2	k2	4	NULL	6	Using where; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	3	NULL	15	Using index; Using join buffer
 drop table t1, t2;
 create table t1 (a char(10));
 insert into t1 values ('a'),('b'),('c');
diff --git a/mysql-test/r/func_group_innodb.result b/mysql-test/r/func_group_innodb.result
index dba546a72de..908e85c1652 100644
--- a/mysql-test/r/func_group_innodb.result
+++ b/mysql-test/r/func_group_innodb.result
@@ -78,8 +78,8 @@ min(7)
 7
 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	Using join cache
-1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
 select min(7) from t2i join t1i;
 min(7)
 NULL
@@ -94,8 +94,8 @@ max(7)
 7
 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	Using join cache
-1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
 select max(7) from t2i join t1i;
 max(7)
 NULL
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 62486936e02..b50eb40182d 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -394,8 +394,8 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort; Using join cache
-1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
+1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer
 Warnings:
 Note	1003	select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
diff --git a/mysql-test/r/greedy_optimizer.result b/mysql-test/r/greedy_optimizer.result
index ebd2468c9af..9e8c66722e3 100644
--- a/mysql-test/r/greedy_optimizer.result
+++ b/mysql-test/r/greedy_optimizer.result
@@ -120,72 +120,72 @@ select @@optimizer_search_depth;
 63
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
 show status like 'Last_query_cost';
 Variable_name	Value
@@ -200,72 +200,72 @@ select @@optimizer_search_depth;
 0
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using join buffer
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	289.418727
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using join buffer
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	289.418727
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	289.418727
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
 show status like 'Last_query_cost';
 Variable_name	Value
@@ -276,24 +276,24 @@ select @@optimizer_search_depth;
 1
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
@@ -303,10 +303,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
@@ -315,10 +315,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
@@ -327,10 +327,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
@@ -339,10 +339,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
@@ -352,72 +352,72 @@ select @@optimizer_search_depth;
 62
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using join buffer
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	289.418727
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using join buffer
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	289.418727
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	289.418727
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
 show status like 'Last_query_cost';
 Variable_name	Value
@@ -432,72 +432,72 @@ select @@optimizer_search_depth;
 0
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
 show status like 'Last_query_cost';
 Variable_name	Value
@@ -508,24 +508,24 @@ select @@optimizer_search_depth;
 1
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
@@ -535,10 +535,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
@@ -547,10 +547,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
@@ -559,10 +559,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
@@ -571,10 +571,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
@@ -584,72 +584,72 @@ select @@optimizer_search_depth;
 62
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.c22	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.c42	1	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t6.c62	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	821.837037
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
 show status like 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	794.837037
 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where; Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where; Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using where
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer
 1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
 show status like 'Last_query_cost';
 Variable_name	Value
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 807986c5f4e..e3cd90b9b3d 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -536,12 +536,12 @@ a	b
 1	1
 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort; Using join cache
-1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	4	Using where; Using join buffer
 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using join cache
-1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	4	Using where; Using join buffer
 drop table t1,t2;
 create table t1 (a int, b int);
 insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index bc07b933895..fcd6c92585e 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2266,8 +2266,8 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2
 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) 
 AND t1_outer1.b = t1_outer2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1_outer1	ref	a	a	5	const	1	Using where; Using index; Using join cache
-1	PRIMARY	t1_outer2	index	NULL	a	10	NULL	15	Using where; Using index
+1	PRIMARY	t1_outer1	ref	a	a	5	const	1	Using where; Using index
+1	PRIMARY	t1_outer2	index	NULL	a	10	NULL	15	Using where; Using index; Using join buffer
 2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
 EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
 FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result
index e8a37257782..f135d849f76 100644
--- a/mysql-test/r/index_merge_myisam.result
+++ b/mysql-test/r/index_merge_myisam.result
@@ -270,8 +270,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain select * from t0,t1 where t0.key1 = 5 and
 (t1.key1 = t0.key1 or t1.key8 = t0.key1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t0	ref	i1	i1	4	const	1	Using join cache
-1	SIMPLE	t1	index_merge	i1,i8	i1,i8	4,4	NULL	2	Using union(i1,i8); Using where
+1	SIMPLE	t0	ref	i1	i1	4	const	1	
+1	SIMPLE	t1	index_merge	i1,i8	i1,i8	4,4	NULL	2	Using union(i1,i8); Using where; Using join buffer
 explain select * from t0,t1 where t0.key1 < 3 and
 (t1.key1 = t0.key1 or t1.key8 = t0.key1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -347,8 +347,8 @@ from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
 where (A.key1 < 500000 or A.key2 < 3)
 and   (B.key1 < 500000 or B.key2 < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	A	index_merge	i1,i2	i1,i2	4,4	NULL	1013	Using sort_union(i1,i2); Using where; Using join cache
-1	SIMPLE	B	index_merge	i1,i2	i1,i2	4,4	NULL	1013	Using sort_union(i1,i2); Using where
+1	SIMPLE	A	index_merge	i1,i2	i1,i2	4,4	NULL	1013	Using sort_union(i1,i2); Using where
+1	SIMPLE	B	index_merge	i1,i2	i1,i2	4,4	NULL	1013	Using sort_union(i1,i2); Using where; Using join buffer
 select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
 from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
 where (A.key1 < 500000 or A.key2 < 3)
@@ -361,8 +361,8 @@ from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
 where (A.key1 = 1 or A.key2 = 1)
 and   (B.key1 = 1 or B.key2 = 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	A	index_merge	i1,i2	i1,i2	4,4	NULL	1020	Using union(i1,i2); Using where; Using join cache
-1	SIMPLE	B	index_merge	i1,i2	i1,i2	4,4	NULL	1020	Using union(i1,i2); Using where
+1	SIMPLE	A	index_merge	i1,i2	i1,i2	4,4	NULL	1020	Using union(i1,i2); Using where
+1	SIMPLE	B	index_merge	i1,i2	i1,i2	4,4	NULL	1020	Using union(i1,i2); Using where; Using join buffer
 select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
 from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
 where (A.key1 = 1 or A.key2 = 1)
@@ -376,8 +376,8 @@ from t0 as A, t0 as B
 where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
 and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	A	index_merge	i1,i2,i3,i4,i5,i6,i7?,i8	i2,i3,i4,i5,i6,i7?,i8	X	NULL	#	Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where; Using join cache
-1	SIMPLE	B	index_merge	i1,i2,i3,i4,i5,i6,i7?,i8	i2,i3,i4,i5,i6,i7?,i8	X	NULL	#	Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where
+1	SIMPLE	A	index_merge	i1,i2,i3,i4,i5,i6,i7?,i8	i2,i3,i4,i5,i6,i7?,i8	X	NULL	#	Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where
+1	SIMPLE	B	index_merge	i1,i2,i3,i4,i5,i6,i7?,i8	i2,i3,i4,i5,i6,i7?,i8	X	NULL	#	Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where; Using join buffer
 select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
 from t0 as A, t0 as B
 where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 709246edcf1..745ec2e2248 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -289,8 +289,8 @@ explain select a.ROUTINE_NAME from information_schema.ROUTINES a,
 information_schema.SCHEMATA b where
 a.ROUTINE_SCHEMA = b.SCHEMA_NAME;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	2	Using join cache
-1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
 select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a,
 mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) order by 1;
 ROUTINE_NAME	name
diff --git a/mysql-test/r/innodb_gis.result b/mysql-test/r/innodb_gis.result
index 945eca234bb..7f54a78087b 100644
--- a/mysql-test/r/innodb_gis.result
+++ b/mysql-test/r/innodb_gis.result
@@ -402,8 +402,8 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort; Using join cache
-1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
+1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer
 Warnings:
 Note	1003	select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 65e02df29de..ec911a8e3a5 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -184,8 +184,8 @@ min(7)
 7
 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	Using join cache
-1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
 select min(7) from t2i join t1i;
 min(7)
 NULL
@@ -200,8 +200,8 @@ max(7)
 7
 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	Using join cache
-1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
 select max(7) from t2i join t1i;
 max(7)
 NULL
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 86c206195b1..fcb141a3510 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -403,8 +403,8 @@ EXPLAIN
 SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
 ORDER BY t1.b, t1.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	32	Using temporary; Using filesort; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	32	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	16	Using where; Using join buffer
 SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
 ORDER BY t1.b, t1.c;
 e
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index d080b1a2a46..2dd9af8719e 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -228,8 +228,8 @@ LEFT JOIN
 t8
 ON t7.b=t8.b AND t6.b < 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join cache
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer
 1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	
 Warnings:
 Note	1003	select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10))) where 1
@@ -543,8 +543,8 @@ WHERE t0.a=1 AND
 t0.b=t1.b AND          
 (t2.a >= 4 OR t2.c IS NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join cache
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -638,16 +638,16 @@ t0.b=t1.b AND
 (t8.b=t9.b OR t8.c IS NULL) AND
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join cache
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join cache
-1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 Warnings:
 Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
 SELECT t9.a,t9.b
@@ -835,8 +835,8 @@ t2
 ON t3.a=1 AND t2.b=t4.b
 WHERE t1.a <= 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	
 Warnings:
@@ -849,8 +849,8 @@ LEFT JOIN
 (t1,t2)
 ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join cache
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer
 1	SIMPLE	t2	ref	idx_b	idx_b	5	test.t3.b	2	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
@@ -905,16 +905,16 @@ t0.b=t1.b AND
 (t8.b=t9.b OR t8.c IS NULL) AND
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join cache
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join cache
-1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 Warnings:
 Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
 CREATE INDEX idx_b ON t4(b);
@@ -955,16 +955,16 @@ t0.b=t1.b AND
 (t8.b=t9.b OR t8.c IS NULL) AND
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join cache
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join cache
-1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 Warnings:
 Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
 CREATE INDEX idx_b ON t8(b);
@@ -1004,16 +1004,16 @@ t0.b=t1.b AND
 (t8.b=t9.b OR t8.c IS NULL) AND
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join cache
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where; Using join cache
-1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where
+1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 Warnings:
 Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
 CREATE INDEX idx_b ON t1(b);
@@ -1062,8 +1062,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where; Using join cache
-1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where
+1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 Warnings:
 Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 62dfb36bb52..1e4fc91b8bd 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1239,3 +1239,18 @@ Handler_read_prev	0
 Handler_read_rnd	0
 Handler_read_rnd_next	6
 DROP TABLE t1,t2;
+CREATE TABLE t1 (c int  PRIMARY KEY, e int NOT NULL);
+INSERT INTO t1 VALUES (1,0), (2,1);
+CREATE TABLE t2 (d int PRIMARY KEY);
+INSERT INTO t2 VALUES (1), (2), (3);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	3	Using where; Using index; Not exists
+SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
+c	e	d
+1	0	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
+c	e	d
+1	0	NULL
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/key_diff.result b/mysql-test/r/key_diff.result
index 35aad39edbf..9d26bee4557 100644
--- a/mysql-test/r/key_diff.result
+++ b/mysql-test/r/key_diff.result
@@ -35,8 +35,8 @@ D	E	a	a
 a	a	a	a
 explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	Using join cache
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	5	Using where; Using join buffer
 select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a;
 a	b	a	b
 A	B	a	a
diff --git a/mysql-test/r/kill.result b/mysql-test/r/kill.result
index d198c943496..0d5cd171d2d 100644
--- a/mysql-test/r/kill.result
+++ b/mysql-test/r/kill.result
@@ -41,3 +41,87 @@ select 1;
 select RELEASE_LOCK("a");
 RELEASE_LOCK("a")
 1
+create table t1(f1 int);
+create function bug27563() returns int(11)
+deterministic
+begin
+declare continue handler for sqlstate '70100' set @a:= 'killed';
+declare continue handler for sqlexception set @a:= 'exception';
+set @a= get_lock("lock27563", 10);
+return 1;
+end|
+select get_lock("lock27563",10);
+get_lock("lock27563",10)
+1
+insert into t1 values (bug27563());
+ERROR 70100: Query execution was interrupted
+select @a;
+@a
+NULL
+select * from t1;
+f1
+insert into t1 values(0);
+update t1 set f1= bug27563();
+ERROR 70100: Query execution was interrupted
+select @a;
+@a
+NULL
+select * from t1;
+f1
+0
+insert into t1 values(1);
+delete from t1 where bug27563() is null;
+ERROR 70100: Query execution was interrupted
+select @a;
+@a
+NULL
+select * from t1;
+f1
+0
+1
+select * from t1 where f1= bug27563();
+ERROR 70100: Query execution was interrupted
+select @a;
+@a
+NULL
+create procedure proc27563()
+begin
+declare continue handler for sqlstate '70100' set @a:= 'killed';
+declare continue handler for sqlexception set @a:= 'exception';
+select get_lock("lock27563",10);
+select "shouldn't be selected";
+end|
+call proc27563();
+get_lock("lock27563",10)
+NULL
+ERROR 70100: Query execution was interrupted
+select @a;
+@a
+NULL
+create table t2 (f2 int);
+create trigger trg27563 before insert on t1 for each row 
+begin 
+declare continue handler for sqlstate '70100' set @a:= 'killed';
+declare continue handler for sqlexception set @a:= 'exception';
+set @a:= get_lock("lock27563",10);
+insert into t2 values(1);
+end|
+insert into t1 values(2),(3);
+ERROR 70100: Query execution was interrupted
+select @a;
+@a
+NULL
+select * from t1;
+f1
+0
+1
+select * from t2;
+f2
+select release_lock("lock27563");
+release_lock("lock27563")
+1
+drop table t1, t2;
+drop function bug27563;
+drop procedure proc27563;
+PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40 WHERE a1=a2 AND a2=a3 AND a3=a4 AND a4=a5 AND a5=a6 AND a6=a7 AND a7=a8 AND a8=a9 AND a9=a10 AND a10=a11 AND a11=a12 AND a12=a13 AND a13=a14 AND a14=a15 AND a15=a16 AND a16=a17 AND a17=a18 AND a18=a19 AND a19=a20 AND a20=a21 AND a21=a22 AND a22=a23 AND a23=a24 AND a24=a25 AND a25=a26 AND a26=a27 AND a27=a28 AND a28=a29 AND a29=a30 AND a30=a31 AND a31=a32 AND a32=a33 AND a33=a34 AND a34=a35 AND a35=a36 AND a36=a37 AND a37=a38 AND a38=a39 AND a39=a40  ';
+EXECUTE stmt;
diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result
index 34e961395c4..d33fb038b79 100644
--- a/mysql-test/r/metadata.result
+++ b/mysql-test/r/metadata.result
@@ -130,3 +130,14 @@ def			v3		renamed	8	12	0	Y	32896	0	63
 renamed
 drop table t1;
 drop view v1,v2,v3;
+select a.* from (select 2147483648 as v_large) a;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def			a	v_large	v_large	8	10	10	N	32769	0	63
+v_large
+2147483648
+select a.* from (select 214748364 as v_small) a;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def			a	v_small	v_small	3	9	9	N	32769	0	63
+v_small
+214748364
+End of 5.0 tests
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index e9d798d998d..73661897ee1 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -345,12 +345,12 @@ t1	1	c_2	1	c	A	5	NULL	NULL	YES	BTREE
 t1	1	c_2	2	a	A	5	NULL	NULL		BTREE	
 explain select * from t1,t2 where t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	Using join cache
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer
 explain select * from t1,t2 force index(a) where t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	Using join cache
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer
 explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	
@@ -361,8 +361,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	b	b	5	test.t2.b	1	Using where
 explain select * from t1,t2 force index(c) where t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using join cache
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer
 explain select * from t1 where a=0 or a=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	Using where
@@ -1768,6 +1768,32 @@ create table t3 (c1 int) engine=myisam pack_keys=default;
 create table t4 (c1 int) engine=myisam pack_keys=2;
 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 '2' at line 1
 drop table t1, t2, t3;
+CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)) ENGINE=MyISAM;
+INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
+SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
+a
+1
+ALTER TABLE t1 DISABLE KEYS;
+SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
+a
+1
+SELECT a FROM t1 USE INDEX (inx) WHERE a=1;
+a
+1
+SELECT b FROM t1 FORCE INDEX (uinx) WHERE b=1;
+b
+1
+SELECT b FROM t1 USE INDEX (uinx) WHERE b=1;
+b
+1
+SELECT a FROM t1 FORCE INDEX (inx,uinx) WHERE a=1;
+a
+1
+ALTER TABLE t1 ENABLE KEYS;
+SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
+a
+1
+DROP TABLE t1;
 End of 5.0 tests
 create table t1 (a int not null, key `a` (a) key_block_size=1024);
 show create table t1;
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 657f364f53b..b6a0e506eda 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -3309,6 +3309,17 @@ drop user user1;
 drop user user2;
 drop database mysqldump_test_db;
 #
+# Bug #28522: buffer overrun by '\0' byte using --hex-blob.
+#
+CREATE TABLE t1 (c1 INT, c2 LONGBLOB);
+INSERT INTO t1 SET c1=11, c2=REPEAT('q',509);
+CREATE TABLE `t1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` longblob
+);
+INSERT INTO `t1` VALUES (11,0x7171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171717171);
+DROP TABLE t1;
+#
 # End of 5.0 tests
 #
 drop table if exists t1;
diff --git a/mysql-test/r/ndb_condition_pushdown.result b/mysql-test/r/ndb_condition_pushdown.result
index df281230c9e..8d1dcc4ef18 100644
--- a/mysql-test/r/ndb_condition_pushdown.result
+++ b/mysql-test/r/ndb_condition_pushdown.result
@@ -1730,8 +1730,8 @@ pk1	attr1	attr2	attr3	attr4
 explain
 select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	#	Using where with pushed condition; Using temporary; Using filesort; Using join cache
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	#	Using where with pushed condition
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	#	Using where with pushed condition; Using temporary; Using filesort
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	#	Using where with pushed condition; Using join buffer
 select * from t2,t3 where t2.attr1 < 1 and t2.attr2 = t3.attr2 and t3.attr1 < 5 order by t2.pk1;
 pk1	attr1	attr2	attr3	pk1	attr1	attr2	attr3	attr4
 0	0	0	a	0	0	0	0	a
@@ -1746,8 +1746,8 @@ pk1	attr1	attr2	attr3	attr4
 explain
 select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	range	attr1	attr1	4	NULL	#	Using where with pushed condition; Using temporary; Using filesort; Using join cache
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	#	Using where
+1	SIMPLE	t4	range	attr1	attr1	4	NULL	#	Using where with pushed condition; Using temporary; Using filesort
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	#	Using where; Using join buffer
 select * from t3,t4 where t4.attr1 > 1 and t4.attr2 = t3.attr2 and t4.attr3 < 5 order by t4.pk1;
 pk1	attr1	attr2	attr3	attr4	pk1	attr1	attr2	attr3	attr4
 2	2	9223372036854775804	2	c	2	2	9223372036854775804	2	c
diff --git a/mysql-test/r/ndb_gis.result b/mysql-test/r/ndb_gis.result
index c01c1673e44..3af7b71d72d 100644
--- a/mysql-test/r/ndb_gis.result
+++ b/mysql-test/r/ndb_gis.result
@@ -402,8 +402,8 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort; Using join cache
-1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
+1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer
 Warnings:
 Note	1003	select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
@@ -952,8 +952,8 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort; Using join cache
-1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
+1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer
 Warnings:
 Note	1003	select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result
index f5544b4d115..67c45b698ce 100644
--- a/mysql-test/r/olap.result
+++ b/mysql-test/r/olap.result
@@ -696,8 +696,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	int(11)	YES		0	
-LENGTH(a)	int(10)	YES		NULL	
+a	bigint(11)	YES		NULL	
+LENGTH(a)	bigint(10)	YES		NULL	
 COUNT(*)	bigint(21)	NO		0	
 SELECT * FROM v1;
 a	LENGTH(a)	COUNT(*)
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index c2cb7426a74..108264b3bf2 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -220,28 +220,28 @@ insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
 update t1 set y=x;
 explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	y	y	5	const	1	Using where; Using join cache
-1	SIMPLE	t2	range	x	x	5	NULL	2	Using where
+1	SIMPLE	t1	ref	y	y	5	const	1	Using where
+1	SIMPLE	t2	range	x	x	5	NULL	2	Using where; Using join buffer
 explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	y	y	5	const	1	Using where; Using join cache
-1	SIMPLE	t2	range	x	x	5	NULL	2	Using where
+1	SIMPLE	t1	ref	y	y	5	const	1	Using where
+1	SIMPLE	t2	range	x	x	5	NULL	2	Using where; Using join buffer
 explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	y	y	5	const	1	Using where; Using join cache
-1	SIMPLE	t2	range	x	x	5	NULL	3	Using where
+1	SIMPLE	t1	ref	y	y	5	const	1	Using where
+1	SIMPLE	t2	range	x	x	5	NULL	3	Using where; Using join buffer
 explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	y	y	5	const	1	Using where; Using join cache
-1	SIMPLE	t2	range	x	x	5	NULL	3	Using where
+1	SIMPLE	t1	ref	y	y	5	const	1	Using where
+1	SIMPLE	t2	range	x	x	5	NULL	3	Using where; Using join buffer
 explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	y	y	5	const	1	Using where; Using join cache
-1	SIMPLE	t2	range	x	x	5	NULL	2	Using where
+1	SIMPLE	t1	ref	y	y	5	const	1	Using where
+1	SIMPLE	t2	range	x	x	5	NULL	2	Using where; Using join buffer
 explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	y	y	5	const	1	Using where; Using join cache
-1	SIMPLE	t2	range	x	x	5	NULL	2	Using where
+1	SIMPLE	t1	ref	y	y	5	const	1	Using where
+1	SIMPLE	t2	range	x	x	5	NULL	2	Using where; Using join buffer
 explain select count(*) from t1 where x in (1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	x	x	5	const	1	Using where; Using index
@@ -255,13 +255,13 @@ CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya));
 INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2);
 explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	j1	j1	4	const	1	Using index; Using join cache
-1	SIMPLE	t1	index	i1	i1	4	NULL	7	Using where; Using index
+1	SIMPLE	t2	ref	j1	j1	4	const	1	Using index
+1	SIMPLE	t1	index	i1	i1	4	NULL	7	Using where; Using index; Using join buffer
 explain select * from t1 force index(i1), t2 force index(j1) where 
 (t1.key1 <t2.keya + 1) and t2.keya=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	j1	j1	4	const	1	Using index; Using join cache
-1	SIMPLE	t1	index	i1	i1	4	NULL	7	Using where; Using index
+1	SIMPLE	t2	ref	j1	j1	4	const	1	Using index
+1	SIMPLE	t1	index	i1	i1	4	NULL	7	Using where; Using index; Using join buffer
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 a int(11) default NULL,
diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result
index 684aee864b3..d3d7988316e 100644
--- a/mysql-test/r/row.result
+++ b/mysql-test/r/row.result
@@ -376,8 +376,8 @@ a	b	a	b	c
 3	2	3	1	3
 EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	8	NULL	6	100.00	Using index; Using join cache
-1	SIMPLE	t2	index	NULL	PRIMARY	12	NULL	7	100.00	Using where; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	8	NULL	6	100.00	Using index
+1	SIMPLE	t2	index	NULL	PRIMARY	12	NULL	7	100.00	Using where; Using index; Using join buffer
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t1`.`a` - 1) = (`test`.`t2`.`a` - 1)) and (`test`.`t1`.`b` = (`test`.`t2`.`b` + 1)))
 SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1);
diff --git a/mysql-test/r/rpl_view.result b/mysql-test/r/rpl_view.result
index f5ddb9e13ba..0d862a2a912 100644
--- a/mysql-test/r/rpl_view.result
+++ b/mysql-test/r/rpl_view.result
@@ -95,4 +95,10 @@ a	b
 1	6
 drop table t1;
 drop view v1;
+CREATE TABLE t1(a INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE VIEW v1 AS SELECT * FROM t1;
+ERROR 42S01: Table 'v1' already exists
+DROP VIEW v1;
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 1d6ef6fee5c..92ceca2162f 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -1430,8 +1430,8 @@ companynr	companynr
 41	40
 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer
 select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
 fld1	companynr	fld3	period
 038008	37	reporters	1008
@@ -2362,16 +2362,16 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2);
 insert into t2 values (1,3), (2,3), (3,4), (4,4);
 explain select * from t1 left join t2 on a=c where d in (4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	c,d	d	5	const	2	Using where; Using join cache
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t2	ref	c,d	d	5	const	2	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where; Using join buffer
 select * from t1 left join t2 on a=c where d in (4);
 a	b	c	d
 3	2	3	4
 4	2	4	4
 explain select * from t1 left join t2 on a=c where d = 4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	c,d	d	5	const	2	Using where; Using join cache
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t2	ref	c,d	d	5	const	2	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where; Using join buffer
 select * from t1 left join t2 on a=c where d = 4;
 a	b	c	d
 3	2	3	4
@@ -2716,8 +2716,8 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2
 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 
 t2.b like '%%' order by t2.b limit 0,1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	b,c	b	5	const	1	Using where; Using temporary; Using filesort; Using join cache
-1	SIMPLE	t3	index	PRIMARY,a,b	PRIMARY	8	NULL	2	Using index
+1	SIMPLE	t1	ref	b,c	b	5	const	1	Using where; Using temporary; Using filesort
+1	SIMPLE	t3	index	PRIMARY,a,b	PRIMARY	8	NULL	2	Using index; Using join buffer
 1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	2	Range checked for each record (index map: 0x1)
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (a int, INDEX idx(a));
@@ -2737,8 +2737,8 @@ 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; Using join cache
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	index	b	b	5	NULL	2	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer
 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
@@ -2747,8 +2747,8 @@ a	b	a	b
 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; Using join cache
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	index	b	b	5	NULL	2	Using index
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer
 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
@@ -2901,12 +2901,12 @@ a
 4
 EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer
 EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using join cache
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer
 DROP TABLE t1,t2;
 select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
 x'10' + 0	X'10' + 0	b'10' + 0	B'10' + 0
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 34945f0cb60..86ede7a8f00 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4910,7 +4910,7 @@ create table t3 as select * from v1|
 show create table t3|
 Table	Create Table
 t3	CREATE TABLE `t3` (
-  `j` int(11) DEFAULT NULL
+  `j` bigint(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t3|
 j
@@ -6272,3 +6272,14 @@ count(*)
 3
 drop table t1,t2;
 drop function   bug27354;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE FUNCTION metered(a INT) RETURNS INT RETURN 12;
+CREATE VIEW v1 AS SELECT test.metered(a) as metered FROM t1;
+SHOW CREATE VIEW v1;
+View	Create View
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`metered`(`t1`.`a`) AS `metered` from `t1`
+DROP VIEW v1;
+DROP FUNCTION metered;
+DROP TABLE t1;
+End of 5.0 tests
diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result
index 47b86d1a039..1f1a6ec9e22 100644
--- a/mysql-test/r/ssl.result
+++ b/mysql-test/r/ssl.result
@@ -1431,8 +1431,8 @@ companynr	companynr
 41	40
 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer
 select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
 fld1	companynr	fld3	period
 038008	37	reporters	1008
diff --git a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result
index e19dcb712cf..e77fcefeafd 100644
--- a/mysql-test/r/ssl_compress.result
+++ b/mysql-test/r/ssl_compress.result
@@ -1434,8 +1434,8 @@ companynr	companynr
 41	40
 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary; Using join cache
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer
 select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
 fld1	companynr	fld3	period
 038008	37	reporters	1008
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 3dc1795a65a..1ad47a3c465 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -904,8 +904,8 @@ a	t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
 explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	100.00	Using index
-2	DEPENDENT SUBQUERY	t2	ref_or_null	a	a	5	func	2	100.00	Using where; Using index; Using join cache
-2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ref_or_null	a	a	5	func	2	100.00	Using where; Using index
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
 drop table t1,t2,t3;
@@ -1354,8 +1354,8 @@ a
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	a	5	NULL	4	100.00	Using where; Using index
-2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	1001	100.00	Using where; Using index; Using join cache
-2	DEPENDENT SUBQUERY	t3	index	a	a	5	NULL	3	100.00	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	1001	100.00	Using where; Using index
+2	DEPENDENT SUBQUERY	t3	index	a	a	5	NULL	3	100.00	Using where; Using index; Using join buffer
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
 insert into t1 values (3,31);
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index eca6cd3315c..2f3ea31a2a4 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -254,8 +254,8 @@ select a,b, oref,
 from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
-2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	2	100.00	Using where; Full scan on NULL key; Using join cache
-2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where
+2	DEPENDENT SUBQUERY	t1	ref_or_null	a	a	5	func	2	100.00	Using where; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where; Using join buffer
 Warnings:
 Note	1276	Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
@@ -742,3 +742,21 @@ x
 0
 0
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(-1), (65),(66);
+CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY);
+INSERT INTO t2 VALUES (65),(66);
+SELECT a FROM t1 WHERE a NOT IN (65,66);
+a
+1
+-1
+SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
+a
+1
+-1
+EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	t2	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
+DROP TABLE t1;
+End of 5.0 tests
diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result
index 70ef98af420..fe63ad6d69e 100644
--- a/mysql-test/r/type_enum.result
+++ b/mysql-test/r/type_enum.result
@@ -1755,6 +1755,13 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 End of 4.1 tests
+create table t1(f1 set('a','b'), index(f1));
+insert into t1 values(''),(''),('a'),('b');
+select * from t1 where f1='';
+f1
+
+
+drop table t1;
 create table t1(russian enum('E','F','EÿF','FÿE') NOT NULL DEFAULT'E');
 show create table t1;
 Table	Create Table
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 855c452cb21..fc6955f11d2 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -1475,6 +1475,12 @@ Error	1264	Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
 Error	1264	Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
 Error	1264	Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
 Error	1264	Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
+create table t1 (s varchar(100));
+insert into t1 values (0.00000000010000000000000000364321973154977415791655470655996396089904010295867919921875);
+drop table t1;
+SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b;
+a	b
+0.9999999999999800000000000000	0.9999999999999800000000000000
 End of 5.0 tests
 select cast(143.481 as decimal(4,1));
 cast(143.481 as decimal(4,1))
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index acf237bf125..6fc630ec33c 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -499,8 +499,8 @@ a	b
 explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t2.a from t1,t2 where t1.a=t2.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	UNION	t1	index	PRIMARY	PRIMARY	4	NULL	4	Using index; Using join cache
-2	UNION	t2	index	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
+2	UNION	t1	index	PRIMARY	PRIMARY	4	NULL	4	Using index
+2	UNION	t2	index	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index; Using join buffer
 NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
 explain (select * from t1 where a=1) union (select * from t1 where b=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result
index 5e9c62a1fb3..6fd7b39f226 100644
--- a/mysql-test/r/user_var.result
+++ b/mysql-test/r/user_var.result
@@ -317,3 +317,39 @@ SHOW COUNT(*) WARNINGS;
 SHOW COUNT(*) ERRORS;
 @@session.error_count
 1
+create table t1(f1 int, f2 varchar(2), f3 float, f4 decimal(2,1));
+insert into t1 values 
+(1, "a", 1.5, 1.6), (1, "a", 1.5, 1.6), (2, "b", 2.5, 2.6),
+(3, "c", 3.5, 3.6), (4, "d", 4.5, 4.6), (1, "a", 1.5, 1.6),
+(3, "c", 3.5, 3.6), (1, "a", 1.5, 1.6);
+select @a:=f1, count(f1) from t1 group by 1 desc;
+@a:=f1	count(f1)
+4	1
+3	2
+2	1
+1	4
+select @a:=f1, count(f1) from t1 group by 1 asc;
+@a:=f1	count(f1)
+1	4
+2	1
+3	2
+4	1
+select @a:=f2, count(f2) from t1 group by 1 desc;
+@a:=f2	count(f2)
+d	1
+c	2
+b	1
+a	4
+select @a:=f3, count(f3) from t1 group by 1 desc;
+@a:=f3	count(f3)
+4.5	1
+3.5	2
+2.5	1
+1.5	4
+select @a:=f4, count(f4) from t1 group by 1 desc;
+@a:=f4	count(f4)
+4.6	1
+3.6	2
+2.6	1
+1.6	4
+drop table t1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index d1098020b25..5ed06855f19 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2317,8 +2317,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	a	a	10	const,test.t1.b	2	Using where; Using index
 EXPLAIN SELECT * FROM v2 WHERE a=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a	a	5	const	1	Using where; Using index; Using join cache
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	ref	a	a	5	const	1	Using where; Using index
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer
 DROP VIEW v1,v2;
 DROP TABLE t1,t2,t3;
 create table t1 (f1 int);
@@ -2771,7 +2771,7 @@ 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	
+COALESCE(i,j)	bigint(11)	YES		NULL	
 CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1;
 DESCRIBE t2;
 Field	Type	Null	Key	Default	Extra
@@ -3359,6 +3359,94 @@ SHOW CREATE VIEW v1;
 View	Create View
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1.23456789 as decimal(8,0)) AS `col`
 DROP VIEW v1;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (b INT, c INT DEFAULT 0);
+INSERT INTO t1 (a) VALUES (1), (2);
+INSERT INTO t2 (b) VALUES (1), (2);
+CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2
+WHERE t1.a=t2.b AND t2.b < 3 WITH CHECK OPTION;
+SELECT * FROM v1;
+b	c
+1	0
+2	0
+UPDATE v1 SET c=1 WHERE b=1;
+SELECT * FROM v1;
+b	c
+1	1
+2	0
+DROP VIEW v1;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int, c int DEFAULT 0);
+INSERT INTO t1 (id) VALUES (1);
+INSERT INTO t2 (id) VALUES (1);
+CREATE VIEW v1 AS 
+SELECT t2.c FROM t1, t2 
+WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
+UPDATE v1 SET c=1;
+DROP VIEW v1;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a1 INT, c INT DEFAULT 0);
+CREATE TABLE t2 (a2 INT);
+CREATE TABLE t3 (a3 INT);
+CREATE TABLE t4 (a4 INT);
+INSERT INTO t1 (a1) VALUES (1),(2);
+INSERT INTO t2 (a2) VALUES (1),(2);
+INSERT INTO t3 (a3) VALUES (1),(2);
+INSERT INTO t4 (a4) VALUES (1),(2);
+CREATE VIEW v1 AS
+SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3
+WITH CHECK OPTION;
+SELECT * FROM v1;
+a1	c
+1	0
+2	0
+UPDATE v1 SET c=3;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+PREPARE t FROM 'UPDATE v1 SET c=3';
+EXECUTE t;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+EXECUTE t;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+INSERT INTO v1(a1, c) VALUES (3, 3);
+ERROR HY000: CHECK OPTION failed 'test.v1'
+UPDATE v1 SET c=1 WHERE a1=1;
+SELECT * FROM v1;
+a1	c
+1	1
+2	0
+SELECT * FROM t1;
+a1	c
+1	1
+2	0
+CREATE VIEW v2 AS SELECT t1.a1, t1.c
+FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3)
+JOIN (t3 JOIN t4 ON t3.a3=t4.a4)
+ON t2.a2=t3.a3 WITH CHECK OPTION;
+SELECT * FROM v2;
+a1	c
+1	1
+2	0
+UPDATE v2 SET c=3;
+ERROR HY000: CHECK OPTION failed 'test.v2'
+PREPARE t FROM 'UPDATE v2 SET c=3';
+EXECUTE t;
+ERROR HY000: CHECK OPTION failed 'test.v2'
+EXECUTE t;
+ERROR HY000: CHECK OPTION failed 'test.v2'
+INSERT INTO v2(a1, c) VALUES (3, 3);
+ERROR HY000: CHECK OPTION failed 'test.v2'
+UPDATE v2 SET c=2 WHERE a1=1;
+SELECT * FROM v2;
+a1	c
+1	2
+2	0
+SELECT * FROM t1;
+a1	c
+1	2
+2	0
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3,t4;
 End of 5.0 tests.
 DROP DATABASE IF EXISTS `d-1`;
 CREATE DATABASE `d-1`;