From: Tor Didriksen
Date: January 31 2012 7:46am
Subject: bzr push into mysql-trunk branch (tor.didriksen:3469 to 3470)
List-Archive: http://lists.mysql.com/commits/142660
Message-Id: <201201310746.q0V7kbBA009841@acsmt356.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3470 Tor Didriksen	2012-01-31 [merge]
      merge opt-backporting => opt-team, enabling OPTIMIZER_SWITCH_ALL

    modified:
      mysql-test/r/derived.result
      mysql-test/r/func_in_all.result
      mysql-test/r/group_by.result
      mysql-test/r/group_min_max.result
      mysql-test/r/index_merge_myisam.result
      mysql-test/r/innodb_icp_all.result
      mysql-test/r/innodb_mrr_all.result
      mysql-test/r/innodb_mrr_cost_all.result
      mysql-test/r/join_cache_bka.result
      mysql-test/r/join_cache_bka_nixbnl.result
      mysql-test/r/join_cache_bkaunique.result
      mysql-test/r/join_cache_bnl.result
      mysql-test/r/join_cache_nojb.result
      mysql-test/r/myisam_explain_non_select_all.result
      mysql-test/r/mysqld--help-notwin.result
      mysql-test/r/mysqld--help-win.result
      mysql-test/r/optimizer_debug_sync.result
      mysql-test/r/order_by_all.result
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subselect_innodb.result
      mysql-test/suite/innodb/r/innodb_mysql.result
      mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
      mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result
      mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
      mysql-test/t/group_by.test
      mysql-test/t/optimizer_debug_sync.test
      sql/item.cc
      sql/sql_priv.h
 3469 Tor Didriksen	2012-01-30 [merge]
      empty merge trunk => opt-team

=== modified file 'mysql-test/r/derived.result'
--- a/mysql-test/r/derived.result	2012-01-27 14:22:35 +0000
+++ b/mysql-test/r/derived.result	2012-01-30 13:13:15 +0000
@@ -1455,7 +1455,7 @@ FROM (SELECT COUNT(col_varchar_nokey) FR
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-2	DEPENDENT SUBQUERY	<derived3>	ref	auto_key0	auto_key0	8	const	0	Using where; Using index
+1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	8	const	0	Using index; FirstMatch(t1)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
 DROP TABLE t1,t2;
 #
@@ -1629,9 +1629,9 @@ WHERE derived.vc IN (
 SELECT t2.vc1
 FROM t2 JOIN t3 ON t2.vc2=t3.vc);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
-3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize; Scan
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; End materialize; Using join buffer (Block Nested Loop)
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	23	test.t2.vc1	2	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
 SELECT derived.vc
 FROM (SELECT * FROM t1) AS derived
@@ -1815,9 +1815,9 @@ WHERE outr.col_varchar_nokey = 'e'
 AND outr.col_varchar_key <> 'r'
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	Using where
-3	DEPENDENT SUBQUERY	<derived4>	system	NULL	NULL	NULL	NULL	1	
-3	DEPENDENT SUBQUERY	<derived5>	ref	auto_key0	auto_key0	4	func	2	Using where
+1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	<derived5>	ALL	NULL	NULL	NULL	NULL	20	Using where; End temporary; Using join buffer (Block Nested Loop)
 5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	
 4	DERIVED	t3	system	NULL	NULL	NULL	NULL	1	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	20	
@@ -1924,10 +1924,10 @@ AND grandparent.col_int_key IS UNKNOWN
 )
 ORDER BY grandparent.col_varchar_nokey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	Using where; Using filesort
-3	DEPENDENT SUBQUERY	<derived4>	index_subquery	auto_key0	auto_key0	4	func	2	Using where
-5	DEPENDENT SUBQUERY	<derived6>	system	NULL	NULL	NULL	NULL	1	
-5	DEPENDENT SUBQUERY	<derived7>	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	5	const	2	Using where; Using index; Using temporary; Using filesort; Start temporary
+1	PRIMARY	<derived6>	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	<derived4>	ref	auto_key0	auto_key0	4	grandparent.col_varchar_nokey	2	
+1	PRIMARY	<derived7>	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
 7	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
 6	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
 4	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	

=== modified file 'mysql-test/r/func_in_all.result'
--- a/mysql-test/r/func_in_all.result	2012-01-30 06:39:02 +0000
+++ b/mysql-test/r/func_in_all.result	2012-01-30 13:08:34 +0000
@@ -470,6 +470,8 @@ SELECT HEX(a) FROM t2 WHERE a IN 
 HEX(a)
 7FFFFFFFFFFFFFFE
 7FFFFFFFFFFFFFFF
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'abc'
 CREATE TABLE t3 (a BIGINT UNSIGNED);
 INSERT INTO t3 VALUES (9223372036854775551);
 SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42);

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2012-01-27 13:57:29 +0000
+++ b/mysql-test/r/group_by.result	2012-01-30 13:57:08 +0000
@@ -1548,8 +1548,8 @@ id	select_type	table	type	possible_keys	
 EXPLAIN SELECT 1 FROM t1 WHERE a IN
 (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	144	Using where; Using index
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	144	Using where
+1	PRIMARY	t1	index	PRIMARY,i2	PRIMARY	4	NULL	144	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	144	Materialize
 CREATE TABLE t2 (a INT, b INT, KEY(a));
 INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
 EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
@@ -1561,8 +1561,8 @@ id	select_type	table	type	possible_keys	
 EXPLAIN SELECT 1 FROM t2 WHERE a IN
 (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	NULL	a	5	NULL	4	Using where; Using index
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	144	Using where
+1	PRIMARY	t2	index	a	a	5	NULL	4	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	144	Materialize
 SHOW VARIABLES LIKE 'old';
 Variable_name	Value
 old	OFF
@@ -2448,6 +2448,37 @@ Warnings:
 Note	1003	/* select#1 */ select NULL AS `field1` from `test`.`t1` join `test`.`t3` `alias2` where 0 group by `field1`
 DROP TABLE t1,t2,t3;
 DROP VIEW view1;
+CREATE TABLE t1 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE TABLE t2 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+CREATE ALGORITHM=MERGE VIEW view1 AS
+SELECT CONCAT( table1.col_varchar_nokey , table2.col_varchar_key ) AS
+field1
+FROM
+t2 AS table1 JOIN t1 AS table2
+ON table2.col_varchar_nokey = table1.col_varchar_key 
+AND
+table2.col_varchar_key >= table1.col_varchar_nokey 
+ORDER BY field1
+;
+EXPLAIN EXTENDED SELECT * FROM view1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	/* select#1 */ select concat(NULL,NULL) AS `field1` from `test`.`t2` `table1` join `test`.`t1` `table2` where 0 order by `field1`
+DROP TABLE t1,t2;
+DROP VIEW view1;
 CREATE TABLE t1 (col_varchar_nokey varchar(1) DEFAULT NULL);
 INSERT INTO t1 VALUES ('v'),('c');
 EXPLAIN EXTENDED SELECT (SELECT 150) AS field5

=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2012-01-27 13:57:29 +0000
+++ b/mysql-test/r/group_min_max.result	2012-01-30 13:13:15 +0000
@@ -2256,7 +2256,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE
 a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1_outer	index	NULL	a	10	NULL	15	Using where; Using index
-2	DEPENDENT SUBQUERY	t1	index	NULL	a	10	NULL	1	Using index
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
 EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
 a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/index_merge_myisam.result'
--- a/mysql-test/r/index_merge_myisam.result	2012-01-27 13:57:29 +0000
+++ b/mysql-test/r/index_merge_myisam.result	2012-01-30 13:13:15 +0000
@@ -1492,19 +1492,19 @@ DROP TABLE t1,t2;
 #
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 set optimizer_switch='index_merge=off,index_merge_union=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 set optimizer_switch='index_merge_union=on';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 set optimizer_switch='default,index_merge_sort_union=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 set optimizer_switch=4;
 set optimizer_switch=NULL;
 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'NULL'
@@ -1530,21 +1530,21 @@ set optimizer_switch=default;
 set optimizer_switch='index_merge=off,index_merge_union=off,default';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 set optimizer_switch=default;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 set @@global.optimizer_switch=default;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 #
 # Check index_merge's @@optimizer_switch flags
 #
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int, b int, c int, filler char(100), 
@@ -1654,5 +1654,5 @@ id	select_type	table	type	possible_keys	
 set optimizer_switch=default;
 show variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 drop table t0, t1;

=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result	2012-01-05 10:17:03 +0000
+++ b/mysql-test/r/innodb_icp_all.result	2012-01-30 13:08:34 +0000
@@ -992,7 +992,7 @@ FROM t2 JOIN t1 ON t2.col_int_key
 WHERE t2.pk < 7 AND t2.col_int_key <> 7
 GROUP BY field1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	PRIMARY,col_int_key	col_int_key	5	NULL	5	Using where
+1	SIMPLE	t2	range	PRIMARY,col_int_key	col_int_key	5	NULL	5	Using where; Using index
 1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	4	Using index
 SELECT t2.col_int_key AS field1
 FROM t2 JOIN t1 ON t2.col_int_key

=== modified file 'mysql-test/r/innodb_mrr_all.result'
--- a/mysql-test/r/innodb_mrr_all.result	2011-12-16 09:47:20 +0000
+++ b/mysql-test/r/innodb_mrr_all.result	2012-01-30 13:08:34 +0000
@@ -539,7 +539,7 @@ WHERE t2.i1 > 5
 AND t2.pk IS  NULL
 ORDER BY i1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
 SELECT i1
 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2

=== modified file 'mysql-test/r/innodb_mrr_cost_all.result'
--- a/mysql-test/r/innodb_mrr_cost_all.result	2011-12-16 09:47:20 +0000
+++ b/mysql-test/r/innodb_mrr_cost_all.result	2012-01-30 13:08:34 +0000
@@ -539,7 +539,7 @@ WHERE t2.i1 > 5
 AND t2.pk IS  NULL
 ORDER BY i1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
 SELECT i1
 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2

=== modified file 'mysql-test/r/join_cache_bka.result'
--- a/mysql-test/r/join_cache_bka.result	2012-01-27 14:22:35 +0000
+++ b/mysql-test/r/join_cache_bka.result	2012-01-30 13:13:15 +0000
@@ -630,8 +630,8 @@ SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	City	ALL	Population	NULL	NULL	NULL	4079	Using where
-2	DEPENDENT SUBQUERY	Country	unique_subquery	PRIMARY,Name	PRIMARY	3	func	1	Using where
+1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (Batched Key Access)
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
@@ -937,8 +937,8 @@ SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	City	ALL	Population	NULL	NULL	NULL	4079	Using where
-2	DEPENDENT SUBQUERY	Country	unique_subquery	PRIMARY,Name	PRIMARY	3	func	1	Using where
+1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (Batched Key Access)
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
@@ -2411,9 +2411,9 @@ id	select_type	table	type	possible_keys	
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	index	NULL	k1	5	NULL	2	Using where; Using index
-2	DEPENDENT SUBQUERY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index
-2	DEPENDENT SUBQUERY	t1	ref	col_int_key	col_int_key	4	func	1	Using where; Using join buffer (Batched Key Access)
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start materialize; Scan
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; End materialize; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index
 SELECT t3.c1 FROM t3
 WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;

=== modified file 'mysql-test/r/join_cache_bka_nixbnl.result'
--- a/mysql-test/r/join_cache_bka_nixbnl.result	2012-01-27 14:22:35 +0000
+++ b/mysql-test/r/join_cache_bka_nixbnl.result	2012-01-30 13:13:15 +0000
@@ -630,8 +630,8 @@ SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	City	ALL	Population	NULL	NULL	NULL	4079	Using where
-2	DEPENDENT SUBQUERY	Country	unique_subquery	PRIMARY,Name	PRIMARY	3	func	1	Using where
+1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (Batched Key Access)
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
@@ -937,8 +937,8 @@ SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	City	ALL	Population	NULL	NULL	NULL	4079	Using where
-2	DEPENDENT SUBQUERY	Country	unique_subquery	PRIMARY,Name	PRIMARY	3	func	1	Using where
+1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (Batched Key Access)
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
@@ -2411,9 +2411,9 @@ id	select_type	table	type	possible_keys	
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	index	NULL	k1	5	NULL	2	Using where; Using index
-2	DEPENDENT SUBQUERY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index
-2	DEPENDENT SUBQUERY	t1	ref	col_int_key	col_int_key	4	func	1	Using where; Using join buffer (Batched Key Access)
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start materialize; Scan
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; End materialize
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index
 SELECT t3.c1 FROM t3
 WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;

=== modified file 'mysql-test/r/join_cache_bkaunique.result'
--- a/mysql-test/r/join_cache_bkaunique.result	2012-01-27 14:22:35 +0000
+++ b/mysql-test/r/join_cache_bkaunique.result	2012-01-30 13:13:15 +0000
@@ -631,8 +631,8 @@ SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	City	ALL	Population	NULL	NULL	NULL	4079	Using where
-2	DEPENDENT SUBQUERY	Country	unique_subquery	PRIMARY,Name	PRIMARY	3	func	1	Using where
+1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (Batched Key Access (unique))
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
@@ -938,8 +938,8 @@ SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	City	ALL	Population	NULL	NULL	NULL	4079	Using where
-2	DEPENDENT SUBQUERY	Country	unique_subquery	PRIMARY,Name	PRIMARY	3	func	1	Using where
+1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (Batched Key Access (unique))
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
@@ -2412,9 +2412,9 @@ id	select_type	table	type	possible_keys	
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	index	NULL	k1	5	NULL	2	Using where; Using index
-2	DEPENDENT SUBQUERY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index
-2	DEPENDENT SUBQUERY	t1	ref	col_int_key	col_int_key	4	func	1	Using where; Using join buffer (Batched Key Access (unique))
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start materialize; Scan
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; End materialize; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index
 SELECT t3.c1 FROM t3
 WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;

=== modified file 'mysql-test/r/join_cache_bnl.result'
--- a/mysql-test/r/join_cache_bnl.result	2012-01-27 14:22:35 +0000
+++ b/mysql-test/r/join_cache_bnl.result	2012-01-30 13:13:15 +0000
@@ -631,8 +631,8 @@ SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	City	ALL	Population	NULL	NULL	NULL	4079	Using where
-2	DEPENDENT SUBQUERY	Country	unique_subquery	PRIMARY,Name	PRIMARY	3	func	1	Using where
+1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
@@ -938,8 +938,8 @@ SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	City	ALL	Population	NULL	NULL	NULL	4079	Using where
-2	DEPENDENT SUBQUERY	Country	unique_subquery	PRIMARY,Name	PRIMARY	3	func	1	Using where
+1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
@@ -2412,9 +2412,9 @@ id	select_type	table	type	possible_keys	
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	index	NULL	k1	5	NULL	2	Using where; Using index
-2	DEPENDENT SUBQUERY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index
-2	DEPENDENT SUBQUERY	t1	ref	col_int_key	col_int_key	4	func	1	Using where
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start materialize; Scan
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; End materialize; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index
 SELECT t3.c1 FROM t3
 WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;

=== modified file 'mysql-test/r/join_cache_nojb.result'
--- a/mysql-test/r/join_cache_nojb.result	2012-01-27 14:22:35 +0000
+++ b/mysql-test/r/join_cache_nojb.result	2012-01-30 13:13:15 +0000
@@ -631,8 +631,8 @@ SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	City	ALL	Population	NULL	NULL	NULL	4079	Using where
-2	DEPENDENT SUBQUERY	Country	unique_subquery	PRIMARY,Name	PRIMARY	3	func	1	Using where
+1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
@@ -938,8 +938,8 @@ SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	City	ALL	Population	NULL	NULL	NULL	4079	Using where
-2	DEPENDENT SUBQUERY	Country	unique_subquery	PRIMARY,Name	PRIMARY	3	func	1	Using where
+1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
 City.Population > 100000;
@@ -2412,9 +2412,9 @@ id	select_type	table	type	possible_keys	
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	index	NULL	k1	5	NULL	2	Using where; Using index
-2	DEPENDENT SUBQUERY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index
-2	DEPENDENT SUBQUERY	t1	ref	col_int_key	col_int_key	4	func	1	Using where
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start materialize; Scan
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; End materialize
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index
 SELECT t3.c1 FROM t3
 WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;

=== modified file 'mysql-test/r/myisam_explain_non_select_all.result'
--- a/mysql-test/r/myisam_explain_non_select_all.result	2012-01-27 13:57:29 +0000
+++ b/mysql-test/r/myisam_explain_non_select_all.result	2012-01-30 13:13:15 +0000
@@ -169,8 +169,6 @@ id	select_type	table	type	possible_keys	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_rnd_next	4
-Handler_write	3
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12                WHERE t11.a = 1;
@@ -182,8 +180,6 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` where (`test`.`t11`.`a` = 1)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_rnd_next	4
-Handler_write	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	12
@@ -344,8 +340,6 @@ id	select_type	table	type	possible_keys	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_rnd_next	4
-Handler_write	3
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12;
@@ -357,8 +351,6 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12`
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_rnd_next	4
-Handler_write	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	12
@@ -448,8 +440,6 @@ id	select_type	table	type	possible_keys	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_rnd_next	4
-Handler_write	3
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12                WHERE t11.a > 1;
@@ -461,8 +451,6 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` where (`test`.`t11`.`a` > 1)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_rnd_next	4
-Handler_write	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	12
@@ -2667,47 +2655,38 @@ INSERT INTO t2 VALUES (1), (2), (3);
 EXPLAIN UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	<derived3>	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	<derived3>	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_first	1
-Handler_read_rnd_next	5
-Handler_write	1
-Sort_rows	3
-Sort_scan	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1            WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	1	100.00	Materialize; Scan
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	5	test.t1.a	2	100.00	Using index; FirstMatch(t1)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where (`test`.`t1`.`a` = `x`.`b`)
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where (`x`.`b` = `test`.`t1`.`a`)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_rnd_next	4
-Handler_write	1
-Sort_rows	3
-Sort_scan	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_rnd_next	12
-Handler_write	2
+Handler_read_key	3
+Handler_read_rnd_next	8
+Handler_write	1
 Sort_rows	3
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_first	1
-Handler_read_rnd_next	9
+Handler_read_rnd_next	13
 Handler_update	1
 Handler_write	1
 Sort_rows	3
@@ -2719,49 +2698,43 @@ Sort_scan	1
 #
 EXPLAIN UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	1	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	5	test.t1.a	2	Using index; FirstMatch(t1)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (Block Nested Loop)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	1	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	5	test.t1.a	2	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (Block Nested Loop)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_rnd_next	4
-Handler_write	1
-Sort_rows	3
-Sort_scan	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1, t2            WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	1	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	5	test.t1.a	2	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (Block Nested Loop)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where (`test`.`t1`.`a` = `x`.`b`)
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where (`x`.`b` = `test`.`t1`.`a`)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_rnd_next	4
-Handler_write	1
-Sort_rows	3
-Sort_scan	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_rnd_next	10
+Handler_read_key	3
+Handler_read_rnd_next	8
 Handler_write	1
 Sort_rows	3
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_rnd_next	10
+Handler_read_key	3
+Handler_read_rnd_next	8
 Handler_write	1
 Sort_rows	3
 Sort_scan	1
@@ -2772,53 +2745,47 @@ Sort_scan	1
 #
 EXPLAIN UPDATE        t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	1	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived4>	ref	auto_key0	auto_key0	5	test.t1.a	2	Using index; FirstMatch(t1)
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (Block Nested Loop)
 4	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	1	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<derived4>	ref	auto_key0	auto_key0	5	test.t1.a	2	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (Block Nested Loop)
 4	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_rnd_next	8
-Handler_write	4
-Sort_rows	3
-Sort_scan	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2) y            WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	1	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<derived4>	ref	auto_key0	auto_key0	5	test.t1.a	2	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (Block Nested Loop)
 4	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`y`.`b` AS `b` from `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `y` where (`test`.`t1`.`a` = `x`.`b`)
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`y`.`b` AS `b` from `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `y` where (`x`.`b` = `test`.`t1`.`a`)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_rnd_next	8
-Handler_write	4
-Sort_rows	3
-Sort_scan	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_rnd_next	14
-Handler_write	4
+Handler_read_key	3
+Handler_read_rnd_next	8
+Handler_write	1
 Sort_rows	3
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_rnd_next	14
-Handler_write	4
+Handler_read_key	3
+Handler_read_rnd_next	8
+Handler_write	1
 Sort_rows	3
 Sort_scan	1
 
@@ -2837,7 +2804,7 @@ JOIN t1 AS a12 ON a12.c1 = a11.c1
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	100.00	
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	SUBQUERY	<derived3>	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 DROP TABLE t1, t2, t3;
 #71

=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result	2012-01-30 13:12:20 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result	2012-01-31 07:45:59 +0000
@@ -441,8 +441,9 @@ The following options may be given as th
  is one of {index_merge, index_merge_union,
  index_merge_sort_union, index_merge_intersection,
  engine_condition_pushdown, index_condition_pushdown, mrr,
- mrr_cost_based, block_nested_loop, batched_key_access}
- and val is one of {on, off, default}
+ mrr_cost_based, materialization, semijoin, loosescan,
+ firstmatch, block_nested_loop, batched_key_access} and
+ val is one of {on, off, default}
  --optimizer-trace=name 
  Controls tracing of the Optimizer:
  optimizer_trace=option=val[,option=val...], where option
@@ -1008,7 +1009,7 @@ old-passwords FALSE
 old-style-user-limits FALSE
 optimizer-prune-level 1
 optimizer-search-depth 62
-optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on
+optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 optimizer-trace 
 optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 optimizer-trace-limit 1

=== modified file 'mysql-test/r/mysqld--help-win.result'
--- a/mysql-test/r/mysqld--help-win.result	2012-01-30 13:12:20 +0000
+++ b/mysql-test/r/mysqld--help-win.result	2012-01-31 07:45:59 +0000
@@ -441,8 +441,9 @@ The following options may be given as th
  is one of {index_merge, index_merge_union,
  index_merge_sort_union, index_merge_intersection,
  engine_condition_pushdown, index_condition_pushdown, mrr,
- mrr_cost_based, block_nested_loop, batched_key_access}
- and val is one of {on, off, default}
+ mrr_cost_based, materialization, semijoin, loosescan,
+ firstmatch, block_nested_loop, batched_key_access} and
+ val is one of {on, off, default}
  --optimizer-trace=name 
  Controls tracing of the Optimizer:
  optimizer_trace=option=val[,option=val...], where option
@@ -1016,7 +1017,7 @@ old-passwords FALSE
 old-style-user-limits FALSE
 optimizer-prune-level 1
 optimizer-search-depth 62
-optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on
+optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 optimizer-trace 
 optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 optimizer-trace-limit 1

=== modified file 'mysql-test/r/optimizer_debug_sync.result'
--- a/mysql-test/r/optimizer_debug_sync.result	2012-01-26 11:34:35 +0000
+++ b/mysql-test/r/optimizer_debug_sync.result	2012-01-31 06:42:46 +0000
@@ -21,20 +21,3 @@ MAX(i)
 MAX(i)
 2
 DROP TABLE t;
-#
-# Bug #13536661: VALGRIND: DEFINITELY LOST: 552 BYTES IN 1 BLOCKS IN
-# CREATE_TMP_TABLE AND HIGHER
-#
-CREATE TABLE t1 ( a INT, b INT );
-INSERT INTO t1 VALUES (4, 40), (1, 10), (2, 20), (2, 20), (3, 30);
-SET debug_sync = "tmp_table_created SIGNAL parked WAIT_FOR go";
-# This should not leak memory.
-SELECT b, COUNT(DISTINCT b) FROM t1 GROUP BY b ORDER BY -b;
-SET debug_sync = "now WAIT_FOR parked";
-# Set locally to shadow the global variable.
-SET debug = '';
-SET GLOBAL debug = '+d,simulate_out_of_memory';
-SET debug_sync = "now SIGNAL go";
-ERROR HY000: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
-SET GLOBAL debug = '';
-DROP TABLE t1;

=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result	2011-12-19 09:22:28 +0000
+++ b/mysql-test/r/order_by_all.result	2012-01-30 13:08:34 +0000
@@ -1461,7 +1461,7 @@ INSERT INTO t1 VALUES (1, 10), (2, NULL)
 EXPLAIN
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref_or_null	a_c,a	a_c	10	const,const	1	Using where
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where; Using filesort
 # Must return 1 row
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
 col
@@ -1470,7 +1470,7 @@ col
 EXPLAIN
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-x	x	x	ref_or_null	a_c,a	x	x	x	x	x
+x	x	x	ref	a_c,a	x	x	x	x	x
 # Must return 1 row
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
 col
@@ -2600,4 +2600,43 @@ SELECT (SELECT 1 FROM t1 WHERE a=b AND c
 NULL
 NULL
 DROP TABLE t1, t2;
+#
+# Bug #13531865
+# TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF
+# 'TYPE' IS REF_OR_NULL 
+# 
+#
+CREATE TABLE t1 (
+a INT,
+c INT,
+UNIQUE KEY a_c (a,c),
+KEY (a)) engine=myisam;
+INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+# Using 'KEY a_c' for order-by opt, would have required 
+# REF_OR_NULL access which never can be order_by skipped.
+# -> Keep initial REF on 'KEY a' selected by cond. optimizer
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+c
+NULL
+10
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_c,a	a	5	const	1	Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+c
+10
+NULL
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2012-01-27 13:57:29 +0000
+++ b/mysql-test/r/subquery_all.result	2012-01-30 13:13:15 +0000
@@ -204,7 +204,7 @@ id	select_type	table	type	possible_keys	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using filesort
 Warnings:
-Note	1003	/* select#1 */ select (/* select#2 */ select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from dual
+Note	1003	/* select#1 */ select (/* select#2 */ select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (/* select#3 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
 select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
 a
 2
@@ -6260,7 +6260,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), 
 
 EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ref	a	a	5	const	1	
 
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
@@ -6421,7 +6421,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), 
 
 EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ref	a	a	5	const	1	
 
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
@@ -6621,26 +6621,26 @@ SELECT DISTINCT f1_key 
 FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
 f1	f1_key
-v	j
-s	j
-v	v
-s	v
 v	c
 s	c
-v	m
-s	m
 v	d
 s	d
 v	d
 s	d
-v	y
-s	y
-v	t
-s	t
 v	d
 s	d
+v	j
+s	j
+v	m
+s	m
 v	s
 s	s
+v	t
+s	t
+v	v
+s	v
+v	y
+s	y
 explain SELECT table1.f1, table2.f1_key
 FROM t1 AS table1, t2 AS table2
 WHERE EXISTS 
@@ -6684,12 +6684,12 @@ FROM t3 AS t3s JOIN
 t1 AS t1s ON t1s.col_int_key = 9 and
 t1s.col_varchar_key = 'e'
 WHERE 'e' <> t1f.col_varchar_nokey )
-224	p	p	4	p	4
-9	e	e	4	p	NULL
-224	p	p	8	e	4
-9	e	e	8	e	NULL
 224	p	p	10	a	4
 9	e	e	10	a	NULL
+224	p	p	8	e	4
+9	e	e	8	e	NULL
+224	p	p	4	p	4
+9	e	e	4	p	NULL
 explain SELECT t1f.*,t3f.*,(
 SELECT MIN(t3s.col_int_key)
 FROM t3 AS t3s JOIN

=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result	2012-01-27 13:57:29 +0000
+++ b/mysql-test/r/subquery_all_bka.result	2012-01-30 13:13:15 +0000
@@ -201,11 +201,11 @@ select (select t3.a from t3 where a<8 or
 explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
 (select * from t2 where a>1) as tt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using filesort
 Warnings:
-Note	1003	/* select#1 */ select (/* select#2 */ select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from dual
+Note	1003	/* select#1 */ select (/* select#2 */ select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (/* select#3 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
 select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
 a
 2
@@ -6261,7 +6261,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), 
 
 EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ref	a	a	5	const	1	
 
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
@@ -6422,7 +6422,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), 
 
 EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ref	a	a	5	const	1	
 
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
@@ -6622,26 +6622,26 @@ SELECT DISTINCT f1_key 
 FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
 f1	f1_key
-v	j
-s	j
-v	v
-s	v
 v	c
 s	c
-v	m
-s	m
 v	d
 s	d
 v	d
 s	d
-v	y
-s	y
-v	t
-s	t
 v	d
 s	d
+v	j
+s	j
+v	m
+s	m
 v	s
 s	s
+v	t
+s	t
+v	v
+s	v
+v	y
+s	y
 explain SELECT table1.f1, table2.f1_key
 FROM t1 AS table1, t2 AS table2
 WHERE EXISTS 
@@ -6685,12 +6685,12 @@ FROM t3 AS t3s JOIN
 t1 AS t1s ON t1s.col_int_key = 9 and
 t1s.col_varchar_key = 'e'
 WHERE 'e' <> t1f.col_varchar_nokey )
-224	p	p	4	p	4
-9	e	e	4	p	NULL
-224	p	p	8	e	4
-9	e	e	8	e	NULL
 224	p	p	10	a	4
 9	e	e	10	a	NULL
+224	p	p	8	e	4
+9	e	e	8	e	NULL
+224	p	p	4	p	4
+9	e	e	4	p	NULL
 explain SELECT t1f.*,t3f.*,(
 SELECT MIN(t3s.col_int_key)
 FROM t3 AS t3s JOIN

=== modified file 'mysql-test/r/subselect_innodb.result'
--- a/mysql-test/r/subselect_innodb.result	2012-01-27 13:57:29 +0000
+++ b/mysql-test/r/subselect_innodb.result	2012-01-30 13:13:15 +0000
@@ -279,13 +279,13 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t2	ALL	DDOCTYPEID_IDX	NULL	NULL	NULL	9	100.00	Using where
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCTYPEID	1	100.00	
 1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
-2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	100.00	Using where
-3	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	100.00	Using where
-4	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	100.00	Using where
-5	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	100.00	Using where
-6	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	func	1	100.00	Using where
+2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	func	1	100.00	Using where
+2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	100.00	Using where
+2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	100.00	Using where
+2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	100.00	Using where
+2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	100.00	Using where
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`t!
 est`.`t4`.`DOCTYPEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and <in_optimizer>(`test`.`t2`.`FOLDERID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`FOLDERID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level3') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level2') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e!
 879db43c1a5b82c21ddc49089') and (<cache>(`test`.`t3`.`PARENTID`) = `te
st`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`))))))
+Note	1003	/* select#1 */ select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`t!
 est`.`t4`.`DOCTYPEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and <in_optimizer>(`test`.`t2`.`FOLDERID`,<exists>(/* select#2 */ select 1 from `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` where ((`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and (<cache>(`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`)))))
 drop table t1, t2, t3, t4;
 CREATE TABLE t1 (
 school_name varchar(45) NOT NULL,

=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result	2012-01-27 13:57:29 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result	2012-01-30 13:13:15 +0000
@@ -2774,9 +2774,9 @@ INSERT INTO t2 VALUES (1, '1');
 EXPLAIN
 SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
-2	DEPENDENT SUBQUERY	t2	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	1	Using index; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (Block Nested Loop)
 SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2);
 b
 1

=== modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result	2012-01-27 14:22:35 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result	2012-01-30 13:13:15 +0000
@@ -750,9 +750,9 @@ field1	( SELECT COUNT( col_int_key )
 FROM t1
 )
 0	4
+1	4
 8	4
 8	4
-1	4
 8	4
 8	4
 select * from information_schema.optimizer_trace;

=== modified file 'mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result	2012-01-30 09:05:57 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result	2012-01-30 13:13:15 +0000
@@ -750,9 +750,9 @@ field1	( SELECT COUNT( col_int_key )
 FROM t1
 )
 0	4
+1	4
 8	4
 8	4
-1	4
 8	4
 8	4
 select * from information_schema.optimizer_trace;

=== modified file 'mysql-test/suite/sys_vars/r/optimizer_switch_basic.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result	2012-01-27 13:57:29 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result	2012-01-30 13:13:15 +0000
@@ -1,57 +1,57 @@
 SET @start_global_value = @@global.optimizer_switch;
 SELECT @start_global_value;
 @start_global_value
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 show global variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 show session variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 select * from information_schema.global_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 select * from information_schema.session_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on
 set global optimizer_switch=10;
 set session optimizer_switch=5;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
 set global optimizer_switch="index_merge_sort_union=on";
 set session optimizer_switch="index_merge=off";
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
 show global variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off
+optimizer_switch	index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
 show session variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off
+optimizer_switch	index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
 select * from information_schema.global_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off
+OPTIMIZER_SWITCH	index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
 select * from information_schema.session_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off
+OPTIMIZER_SWITCH	index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
 set session optimizer_switch="default";
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off
 set global optimizer_switch=1.1;
 ERROR 42000: Incorrect argument type to variable 'optimizer_switch'
 set global optimizer_switch=1e1;
@@ -70,4 +70,4 @@ ERROR 42000: Variable 'optimizer_switch'
 SET @@global.optimizer_switch = @start_global_value;
 SELECT @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2012-01-17 09:45:08 +0000
+++ b/mysql-test/t/group_by.test	2012-01-30 13:57:08 +0000
@@ -1759,6 +1759,40 @@ GROUP BY
 DROP TABLE t1,t2,t3;
 DROP VIEW view1;
 
+# Another one with Item_direct_view_ref:
+
+CREATE TABLE t1 (
+  col_int_key int(11) DEFAULT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  col_varchar_nokey varchar(1) DEFAULT NULL,
+  KEY col_int_key (col_int_key),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+
+CREATE TABLE t2 (
+  col_int_key int(11) DEFAULT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  col_varchar_nokey varchar(1) DEFAULT NULL,
+  KEY col_int_key (col_int_key),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+
+CREATE ALGORITHM=MERGE VIEW view1 AS
+  SELECT CONCAT( table1.col_varchar_nokey , table2.col_varchar_key ) AS
+field1
+  FROM
+    t2 AS table1 JOIN t1 AS table2
+    ON table2.col_varchar_nokey = table1.col_varchar_key 
+       AND
+       table2.col_varchar_key >= table1.col_varchar_nokey 
+ORDER BY field1
+;
+
+EXPLAIN EXTENDED SELECT * FROM view1;
+
+DROP TABLE t1,t2;
+DROP VIEW view1;
+
 # And a bug with Item_singlerow_subselect:
 
 CREATE TABLE t1 (col_varchar_nokey varchar(1) DEFAULT NULL);

=== modified file 'mysql-test/t/optimizer_debug_sync.test'
--- a/mysql-test/t/optimizer_debug_sync.test	2012-01-26 11:34:35 +0000
+++ b/mysql-test/t/optimizer_debug_sync.test	2012-01-31 06:42:46 +0000
@@ -59,7 +59,6 @@ DROP TABLE t;
 --exit
 
 --echo # End of BUG#56080
-
 --echo #
 --echo # Bug #13536661: VALGRIND: DEFINITELY LOST: 552 BYTES IN 1 BLOCKS IN
 --echo # CREATE_TMP_TABLE AND HIGHER
@@ -86,3 +85,6 @@ reap;
 SET GLOBAL debug = '';
 
 DROP TABLE t1;
+--disconnect con1
+--connection default
+

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2012-01-30 13:12:20 +0000
+++ b/sql/item.cc	2012-01-31 07:45:59 +0000
@@ -687,7 +687,7 @@ void Item::print_for_order(String *str,
 {
   if (used_alias)
   {
-    DBUG_ASSERT(name && !is_autogenerated_name);
+    DBUG_ASSERT(name != NULL);
     // In the clause, user has referenced expression using an alias; we use it
     append_identifier(current_thd, str, name, (uint) strlen(name));
   }

=== modified file 'sql/sql_priv.h'
--- a/sql/sql_priv.h	2012-01-27 13:57:29 +0000
+++ b/sql/sql_priv.h	2012-01-30 13:13:15 +0000
@@ -178,7 +178,7 @@
    If OPTIMIZER_SWITCH_ALL is defined, optimizer_switch flags for newer 
    optimizer features (semijoin) will be available.
  */
-#undef OPTIMIZER_SWITCH_ALL
+#define OPTIMIZER_SWITCH_ALL 1
 
 /* 
   The following must be kept in sync with optimizer_switch string in 

No bundle (reason: useless for push emails).