List:Commits« Previous MessageNext Message »
From:Marc Alff Date:February 23 2012 11:48pm
Subject:bzr push into mysql-trunk-wl5259 branch (marc.alff:3389 to 3390)
View as plain text  
 3390 Marc Alff	2012-02-24 [merge]
      Merge mysql-trunk --> mysql-trunk-wl5259

    added:
      mysql-test/include/greedy_search_drop_tables.inc
      mysql-test/include/greedy_search_load_tables.inc
      mysql-test/include/print_greedy_search_count.inc
      mysql-test/r/greedy_search.result
      mysql-test/t/greedy_search.test
      sql/merge_sort.h
      unittest/gunit/join_tab_sort-t.cc
    modified:
      mysql-test/include/select.inc
      mysql-test/r/ctype_binary.result
      mysql-test/r/ctype_cp1251.result
      mysql-test/r/ctype_latin1.result
      mysql-test/r/ctype_ucs.result
      mysql-test/r/ctype_utf8.result
      mysql-test/r/func_str.result
      mysql-test/r/func_time.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/join_nested.result
      mysql-test/r/join_nested_bka.result
      mysql-test/r/join_nested_bka_nixbnl.result
      mysql-test/r/metadata.result
      mysql-test/r/mysql_client_test_embedded.result
      mysql-test/r/ps_2myisam.result
      mysql-test/r/ps_3innodb.result
      mysql-test/r/ps_4heap.result
      mysql-test/r/ps_5merge.result
      mysql-test/r/select_all.result
      mysql-test/r/select_all_bka.result
      mysql-test/r/select_all_bka_nixbnl.result
      mysql-test/r/select_icp_mrr.result
      mysql-test/r/select_icp_mrr_bka.result
      mysql-test/r/select_icp_mrr_bka_nixbnl.result
      mysql-test/r/select_none.result
      mysql-test/r/select_none_bka.result
      mysql-test/r/select_none_bka_nixbnl.result
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_mat_all.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/type_temporal_fractional.result
      mysql-test/r/type_timestamp.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/opt_trace/r/subquery_no_prot.result
      mysql-test/suite/opt_trace/r/subquery_ps_prot.result
      mysql-test/suite/parts/t/partition_alter4_myisam.test
      mysql-test/suite/rpl/t/rpl_row_event_max_size.test
      mysql-test/t/disabled.def
      mysql-test/t/func_str.test
      mysql-test/t/mysql_client_test_embedded.test
      mysql-test/t/type_timestamp.test
      sql/field.cc
      sql/item.cc
      sql/item_func.cc
      sql/item_strfunc.cc
      sql/item_timefunc.cc
      sql/item_timefunc.h
      sql/mysqld.cc
      sql/sql_class.h
      sql/sql_const.h
      sql/sql_executor.cc
      sql/sql_planner.cc
      sql/sql_planner.h
      sql/sql_select.h
      unittest/gunit/CMakeLists.txt
 3389 Marc Alff	2012-02-22 [merge]
      Merge mysql-trunk --> mysql-trunk-wl5259

    added:
      mysql-test/r/mysql_client_test_embedded.result
      mysql-test/t/mysql_client_test_embedded.cnf
      mysql-test/t/mysql_client_test_embedded.test
    modified:
      client/sql_string.cc
      client/sql_string.h
      mysql-test/r/func_group.result
      mysql-test/r/func_set.result
      mysql-test/r/group_by.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/select_found.result
      mysql-test/r/subquery_mat_none.result
      mysql-test/suite/innodb/t/innodb_bug34300.test
      mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
      mysql-test/suite/opt_trace/r/bugs_no_prot_none.result
      mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result
      mysql-test/suite/opt_trace/r/bugs_ps_prot_none.result
      mysql-test/suite/opt_trace/r/range_no_prot.result
      mysql-test/suite/opt_trace/r/range_ps_prot.result
      mysql-test/t/disabled.def
      mysql-test/t/func_group.test
      mysql-test/t/func_set.test
      sql/item_strfunc.cc
      sql/item_strfunc.h
      sql/log_event.cc
      sql/mysqld.cc
      sql/rpl_handler.cc
      sql/sql_class.cc
      sql/sql_class.h
      sql/sql_executor.cc
      sql/sql_list.h
      sql/sql_load.cc
      sql/sql_optimizer.cc
      sql/sql_optimizer.h
      sql/sql_select.cc
      sql/sql_show.cc
      sql/sql_string.cc
      sql/sql_string.h
      sql/sys_vars.cc
      tests/mysql_client_test.c
      unittest/gunit/opt_range-t.cc
=== added file 'mysql-test/include/greedy_search_drop_tables.inc'
--- a/mysql-test/include/greedy_search_drop_tables.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/greedy_search_drop_tables.inc	2012-02-23 11:22:34 +0000
@@ -0,0 +1,3 @@
+DROP TABLE tbl10, tbl100;
+DROP TABLE t10_1,t10_2,t10_3,t10_4,t10_5,t10_6,t10_7,t10_8,t10_9;
+DROP TABLE t100_1,t100_2,t100_3,t100_4,t100_5,t100_6,t100_7,t100_8,t100_9;

=== added file 'mysql-test/include/greedy_search_load_tables.inc'
--- a/mysql-test/include/greedy_search_load_tables.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/greedy_search_load_tables.inc	2012-02-23 11:22:34 +0000
@@ -0,0 +1,35 @@
+--disable_query_log
+--disable_result_log
+
+# tbl10 and tbl100 are only used to fill the tables used during testing
+CREATE TABLE tbl10(
+  k INT NOT NULL AUTO_INCREMENT,
+  i INT,
+  PRIMARY KEY(k)
+);
+INSERT INTO tbl10(i) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
+
+CREATE TABLE tbl100 LIKE tbl10;
+INSERT INTO tbl100(i) SELECT x.i FROM tbl10 AS x, tbl10 AS y;
+
+let $i= 1;
+WHILE ($i < 10)
+{
+  --let $stmt= CREATE TABLE t10_$i (pk int PRIMARY KEY AUTO_INCREMENT, colidx int, col int, index (colidx))
+  --eval $stmt;
+  --let $stmt= INSERT INTO t10_$i (colidx, col) SELECT k, k FROM tbl10
+  --eval $stmt;
+  # Update index statistics
+  ANALYZE TABLE t10_$i;
+  
+  --let $stmt= CREATE TABLE t100_$i (pk int PRIMARY KEY AUTO_INCREMENT, colidx int, col int, index (colidx))
+  --eval $stmt;
+  --let $stmt= INSERT INTO t100_$i (colidx, col) SELECT a.k, a.k FROM tbl10 a JOIN tbl10 b;
+  --eval $stmt;
+  # Update index statistics
+  ANALYZE TABLE t10_$i;
+
+  inc $i;
+}  
+--enable_result_log
+--enable_query_log

=== added file 'mysql-test/include/print_greedy_search_count.inc'
--- a/mysql-test/include/print_greedy_search_count.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/print_greedy_search_count.inc	2012-02-23 11:22:34 +0000
@@ -0,0 +1,25 @@
+# include/print_greedy_search_count.inc
+#
+# SUMMARY
+#
+#    $query should be assigned a select statement. 
+#    The query will be EXPLAINed and the number of 
+#    partial plans greedy search went through to 
+#    create the QEP is printed.
+#
+# USAGE
+#
+#    let $query= <query>;
+#    --source include/print_greedy_search_count.inc
+#
+# EXAMPLE
+#    t/greedy_search.test
+#
+
+eval EXPLAIN $query;
+
+let $greedy_search_partial_plans=
+  query_get_value(SHOW STATUS LIKE 'Opt_partial_plans', Value, 1);
+
+--echo ### Opt_partial_plans: $greedy_search_partial_plans
+FLUSH STATUS;

=== modified file 'mysql-test/include/select.inc'
--- a/mysql-test/include/select.inc	2011-12-15 11:54:45 +0000
+++ b/mysql-test/include/select.inc	2012-02-23 13:27:28 +0000
@@ -4292,3 +4292,12 @@ DROP VIEW view_t1;
 
 --echo # End of test  BUG#63020
 
+--echo #
+--echo # Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+--echo #
+
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+DROP TABLE t1;
+
+--echo # End of test BUG#13571700
\ No newline at end of file

=== modified file 'mysql-test/r/ctype_binary.result'
--- a/mysql-test/r/ctype_binary.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_binary.result	2012-02-23 16:32:32 +0000
@@ -2818,8 +2818,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					field_str1	254	29	10	Y	128	31	63
 def					field1_str2	254	29	19	Y	128	31	63
-def					field_date	10	10	10	Y	160	0	63
-def					field_datetime	12	19	19	Y	160	0	63
+def					field_date	10	10	10	Y	128	0	63
+def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
 2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
 SELECT

=== modified file 'mysql-test/r/ctype_cp1251.result'
--- a/mysql-test/r/ctype_cp1251.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_cp1251.result	2012-02-23 16:32:32 +0000
@@ -3210,8 +3210,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					field_str1	254	29	10	Y	0	31	51
 def					field1_str2	254	29	19	Y	0	31	51
-def					field_date	10	10	10	Y	160	0	63
-def					field_datetime	12	19	19	Y	160	0	63
+def					field_date	10	10	10	Y	128	0	63
+def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
 2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
 SELECT

=== modified file 'mysql-test/r/ctype_latin1.result'
--- a/mysql-test/r/ctype_latin1.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_latin1.result	2012-02-23 16:32:32 +0000
@@ -3237,8 +3237,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					field_str1	254	29	10	Y	0	31	8
 def					field1_str2	254	29	19	Y	0	31	8
-def					field_date	10	10	10	Y	160	0	63
-def					field_datetime	12	19	19	Y	160	0	63
+def					field_date	10	10	10	Y	128	0	63
+def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
 2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
 SELECT

=== modified file 'mysql-test/r/ctype_ucs.result'
--- a/mysql-test/r/ctype_ucs.result	2011-11-01 11:52:24 +0000
+++ b/mysql-test/r/ctype_ucs.result	2012-02-23 16:32:32 +0000
@@ -4137,8 +4137,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					field_str1	254	29	10	Y	0	31	8
 def					field1_str2	254	29	19	Y	0	31	8
-def					field_date	10	10	10	Y	160	0	63
-def					field_datetime	12	19	19	Y	160	0	63
+def					field_date	10	10	10	Y	128	0	63
+def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
 2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
 SELECT

=== modified file 'mysql-test/r/ctype_utf8.result'
--- a/mysql-test/r/ctype_utf8.result	2012-01-23 10:02:54 +0000
+++ b/mysql-test/r/ctype_utf8.result	2012-02-23 16:32:32 +0000
@@ -5064,8 +5064,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS D
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
 def					field_str1	254	87	10	Y	0	31	33
 def					field1_str2	254	87	19	Y	0	31	33
-def					field_date	10	10	10	Y	160	0	63
-def					field_datetime	12	19	19	Y	160	0	63
+def					field_date	10	10	10	Y	128	0	63
+def					field_datetime	12	19	19	Y	128	0	63
 field_str1	field1_str2	field_date	field_datetime
 2007-08-02	2007-08-03 17:32:00	2007-08-02	2007-08-03 17:32:00
 SELECT

=== modified file 'mysql-test/r/func_str.result'
--- a/mysql-test/r/func_str.result	2012-01-25 15:49:57 +0000
+++ b/mysql-test/r/func_str.result	2012-02-23 10:08:33 +0000
@@ -119,7 +119,7 @@ substring_index('aaaaaaaaa1','aaa',-3)
 aaaaaa1
 select substring_index('aaaaaaaaa1','aaa',-4);
 substring_index('aaaaaaaaa1','aaa',-4)
-
+aaaaaaaaa1
 select substring_index('the king of thethe hill','the',-2);
 substring_index('the king of thethe hill','the',-2)
 the hill
@@ -4464,5 +4464,28 @@ EXECUTE stmt;
 COLLATION(space(2))
 latin2_general_ci
 #
+# Bug#11829861: SUBSTRING_INDEX() RESULTS IN MISSING CHARACTERS WHEN USED
+# INSIDE LOWER()
+#
+SET @user_at_host = 'root@stripped';
+SELECT LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1));
+LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1))
+mytinyhost-pc.local
+# End of test  BUG#11829861
+#
+# Bug#42404: SUBSTRING_INDEX() RESULTS ARE INCONSISTENT
+#
+CREATE TABLE t (i INT NOT NULL, c CHAR(255) NOT NULL);
+INSERT INTO t VALUES (0,'.www.mysql.com'),(1,'.wwwmysqlcom');
+SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t WHERE i = 1;
+i	SUBSTRING_INDEX(c, '.', -2)
+1	.wwwmysqlcom
+SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t;
+i	SUBSTRING_INDEX(c, '.', -2)
+0	mysql.com
+1	.wwwmysqlcom
+DROP TABLE t;
+# End of test  BUG#42404
+#
 # End of 5.6 tests
 #

=== modified file 'mysql-test/r/func_time.result'
--- a/mysql-test/r/func_time.result	2012-01-24 11:57:25 +0000
+++ b/mysql-test/r/func_time.result	2012-02-23 16:32:32 +0000
@@ -929,10 +929,10 @@ sec_to_time(1) + 0, from_unixtime(1) + 0
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `now() - now()` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `curtime() - curtime()` int(10) NOT NULL DEFAULT '0',
-  `sec_to_time(1) + 0` bigint(12) DEFAULT NULL,
-  `from_unixtime(1) + 0` bigint(20) unsigned DEFAULT NULL
+  `now() - now()` bigint(16) NOT NULL DEFAULT '0',
+  `curtime() - curtime()` int(9) NOT NULL DEFAULT '0',
+  `sec_to_time(1) + 0` int(9) DEFAULT NULL,
+  `from_unixtime(1) + 0` bigint(16) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 SELECT SEC_TO_TIME(3300000);

=== added file 'mysql-test/r/greedy_search.result'
--- a/mysql-test/r/greedy_search.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/greedy_search.result	2012-02-23 11:22:34 +0000
@@ -0,0 +1,194 @@
+#
+# TEST 1
+# Greedy search iteration test for 16-way join: star schema
+#
+# Creation of 16 tables hidden
+#
+SET SESSION optimizer_search_depth = 25;
+FLUSH STATUS;
+#
+# 16-way join - all 15 fact tables joined on column with key
+#
+EXPLAIN SELECT *
+FROM vehicles
+JOIN models        ON vehicles.model_id        = models.id_pk
+JOIN subtypes      ON vehicles.subtype_id      = subtypes.id_pk
+JOIN colors        ON vehicles.color_id        = colors.id_pk
+JOIN heating       ON vehicles.heating_id      = heating.id_pk
+JOIN windows       ON vehicles.window_id       = windows.id_pk
+JOIN fuels         ON vehicles.fuel_id         = fuels.id_pk
+JOIN transmissions ON vehicles.transmission_id = transmissions.id_pk
+JOIN steerings     ON vehicles.steering_id     = steerings.id_pk
+JOIN interiors     ON vehicles.interior_id     = interiors.id_pk
+JOIN drives        ON vehicles.drive_id        = drives.id_pk
+JOIN wheels        ON vehicles.wheels_id       = wheels.id_pk
+JOIN engine        ON vehicles.engine_id       = engine.id_pk
+JOIN price_ranges  ON vehicles.price_range_id  = price_ranges.id_pk
+JOIN countries     ON vehicles.assembled_in    = countries.id_pk
+JOIN brands        ON models.brand_id          = brands.id_pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	vehicles	ALL	NULL	NULL	NULL	NULL	80	Using where
+1	SIMPLE	subtypes	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.subtype_id	1	
+1	SIMPLE	heating	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.heating_id	1	
+1	SIMPLE	windows	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.window_id	1	
+1	SIMPLE	fuels	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.fuel_id	1	
+1	SIMPLE	transmissions	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.transmission_id	1	
+1	SIMPLE	steerings	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.steering_id	1	
+1	SIMPLE	drives	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.drive_id	1	
+1	SIMPLE	wheels	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.wheels_id	1	
+1	SIMPLE	engine	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.engine_id	1	
+1	SIMPLE	price_ranges	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.price_range_id	1	
+1	SIMPLE	colors	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.color_id	1	
+1	SIMPLE	interiors	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.interior_id	1	
+1	SIMPLE	countries	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.assembled_in	1	
+1	SIMPLE	models	eq_ref	PRIMARY,brand_id	PRIMARY	4	test.vehicles.model_id	1	Using where
+1	SIMPLE	brands	eq_ref	PRIMARY	PRIMARY	4	test.models.brand_id	1	
+### Opt_partial_plans: 50922
+FLUSH STATUS;
+#
+# 16-way join - 10 fact tables joined on column with key and
+#                5 fact tables joined on column without key
+#
+EXPLAIN SELECT *
+FROM vehicles
+JOIN models        ON vehicles.model_id        = models.id_nokey
+JOIN subtypes      ON vehicles.subtype_id      = subtypes.id_pk
+JOIN colors        ON vehicles.color_id        = colors.id_pk
+JOIN heating       ON vehicles.heating_id      = heating.id_nokey
+JOIN windows       ON vehicles.window_id       = windows.id_pk
+JOIN fuels         ON vehicles.fuel_id         = fuels.id_pk
+JOIN transmissions ON vehicles.transmission_id = transmissions.id_nokey
+JOIN steerings     ON vehicles.steering_id     = steerings.id_pk
+JOIN interiors     ON vehicles.interior_id     = interiors.id_pk
+JOIN drives        ON vehicles.drive_id        = drives.id_pk
+JOIN wheels        ON vehicles.wheels_id       = wheels.id_nokey
+JOIN engine        ON vehicles.engine_id       = engine.id_pk
+JOIN price_ranges  ON vehicles.price_range_id  = price_ranges.id_pk
+JOIN countries     ON vehicles.assembled_in    = countries.id_pk
+JOIN brands        ON models.brand_id          = brands.id_nokey;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	brands	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	SIMPLE	models	ref	brand_id	brand_id	5	test.brands.id_nokey	4	
+1	SIMPLE	heating	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	transmissions	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	wheels	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	vehicles	ALL	NULL	NULL	NULL	NULL	80	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	windows	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.window_id	1	
+1	SIMPLE	fuels	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.fuel_id	1	
+1	SIMPLE	steerings	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.steering_id	1	
+1	SIMPLE	drives	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.drive_id	1	
+1	SIMPLE	engine	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.engine_id	1	
+1	SIMPLE	price_ranges	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.price_range_id	1	
+1	SIMPLE	interiors	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.interior_id	1	
+1	SIMPLE	countries	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.assembled_in	1	
+1	SIMPLE	subtypes	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.subtype_id	1	
+1	SIMPLE	colors	eq_ref	PRIMARY	PRIMARY	4	test.vehicles.color_id	1	
+### Opt_partial_plans: 483932
+FLUSH STATUS;
+select @@optimizer_search_depth;
+@@optimizer_search_depth
+25
+select @@optimizer_prune_level;
+@@optimizer_prune_level
+1
+DROP TABLE vehicles, models, subtypes, colors, heating, windows, 
+fuels, transmissions, steerings, interiors, drives, 
+price_ranges, countries, brands, wheels, engine;
+#
+# TEST 2
+# Greedy search iteration test for chain of tables
+#
+#
+# Chain test a:      colidx):(pk,colidx):(pk,colidx)
+#
+EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.colidx = t100_1.pk JOIN t10_2 ON t100_1.colidx = t10_2.pk JOIN t100_2 ON t10_2.colidx = t100_2.pk JOIN t10_3 ON t100_2.colidx = t10_3.pk JOIN t100_3 ON t10_3.colidx = t100_3.pk JOIN t10_4 ON t100_3.colidx = t10_4.pk JOIN t100_4 ON t10_4.colidx = t100_4.pk JOIN t10_5 ON t100_4.colidx = t10_5.pk JOIN t100_5 ON t10_5.colidx = t100_5.pk JOIN t10_6 ON t100_5.colidx = t10_6.pk JOIN t100_6 ON t10_6.colidx = t100_6.pk JOIN t10_7 ON t100_6.colidx = t10_7.pk JOIN t100_7 ON t10_7.colidx = t100_7.pk JOIN t10_8 ON t100_7.colidx = t10_8.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t10_1	ALL	colidx	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t100_1	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t10_1.colidx	1	Using where
+1	SIMPLE	t10_2	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_1.colidx	1	Using where
+1	SIMPLE	t100_2	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t10_2.colidx	1	Using where
+1	SIMPLE	t10_3	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_2.colidx	1	Using where
+1	SIMPLE	t100_3	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t10_3.colidx	1	Using where
+1	SIMPLE	t10_4	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_3.colidx	1	Using where
+1	SIMPLE	t100_4	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t10_4.colidx	1	Using where
+1	SIMPLE	t10_5	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_4.colidx	1	Using where
+1	SIMPLE	t100_5	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t10_5.colidx	1	Using where
+1	SIMPLE	t10_6	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_5.colidx	1	Using where
+1	SIMPLE	t100_6	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t10_6.colidx	1	Using where
+1	SIMPLE	t10_7	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_6.colidx	1	Using where
+1	SIMPLE	t100_7	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t10_7.colidx	1	Using where
+1	SIMPLE	t10_8	eq_ref	PRIMARY	PRIMARY	4	test.t100_7.colidx	1	
+### Opt_partial_plans: 3669
+FLUSH STATUS;
+#
+# Chain test b: (...,col):(colidx, col):(pk,col):(colidx,col):(pk,...)
+#
+EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.col = t100_1.colidx JOIN t10_2 ON t100_1.col = t10_2.pk JOIN t100_2 ON t10_2.col = t100_2.colidx JOIN t10_3 ON t100_2.col = t10_3.pk JOIN t100_3 ON t10_3.col = t100_3.colidx JOIN t10_4 ON t100_3.col = t10_4.pk JOIN t100_4 ON t10_4.col = t100_4.colidx JOIN t10_5 ON t100_4.col = t10_5.pk JOIN t100_5 ON t10_5.col = t100_5.colidx JOIN t10_6 ON t100_5.col = t10_6.pk JOIN t100_6 ON t10_6.col = t100_6.colidx JOIN t10_7 ON t100_6.col = t10_7.pk JOIN t100_7 ON t10_7.col = t100_7.colidx JOIN t10_8 ON t100_7.col = t10_8.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t10_1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t100_1	ref	colidx	colidx	5	test.t10_1.col	10	Using where
+1	SIMPLE	t10_2	eq_ref	PRIMARY	PRIMARY	4	test.t100_1.col	1	Using where
+1	SIMPLE	t100_2	ref	colidx	colidx	5	test.t10_2.col	10	Using where
+1	SIMPLE	t10_3	eq_ref	PRIMARY	PRIMARY	4	test.t100_2.col	1	Using where
+1	SIMPLE	t100_3	ref	colidx	colidx	5	test.t10_3.col	10	Using where
+1	SIMPLE	t10_4	eq_ref	PRIMARY	PRIMARY	4	test.t100_3.col	1	Using where
+1	SIMPLE	t100_4	ref	colidx	colidx	5	test.t10_4.col	10	Using where
+1	SIMPLE	t10_5	eq_ref	PRIMARY	PRIMARY	4	test.t100_4.col	1	Using where
+1	SIMPLE	t100_5	ref	colidx	colidx	5	test.t10_5.col	10	Using where
+1	SIMPLE	t10_6	eq_ref	PRIMARY	PRIMARY	4	test.t100_5.col	1	Using where
+1	SIMPLE	t100_6	ref	colidx	colidx	5	test.t10_6.col	10	Using where
+1	SIMPLE	t10_7	eq_ref	PRIMARY	PRIMARY	4	test.t100_6.col	1	Using where
+1	SIMPLE	t100_7	ref	colidx	colidx	5	test.t10_7.col	10	Using where
+1	SIMPLE	t10_8	eq_ref	PRIMARY	PRIMARY	4	test.t100_7.col	1	
+### Opt_partial_plans: 692133
+FLUSH STATUS;
+#
+# Chain test c: (...,colidx):(col, pk):(col,colidx):(col,...)
+#
+EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.colidx = t100_1.col JOIN t10_2 ON t100_1.pk = t10_2.col JOIN t100_2 ON t10_2.colidx = t100_2.col JOIN t10_3 ON t100_2.pk = t10_3.col JOIN t100_3 ON t10_3.colidx = t100_3.col JOIN t10_4 ON t100_3.pk = t10_4.col JOIN t100_4 ON t10_4.colidx = t100_4.col JOIN t10_5 ON t100_4.pk = t10_5.col JOIN t100_5 ON t10_5.colidx = t100_5.col JOIN t10_6 ON t100_5.pk = t10_6.col JOIN t100_6 ON t10_6.colidx = t100_6.col JOIN t10_7 ON t100_6.pk = t10_7.col JOIN t100_7 ON t10_7.colidx = t100_7.col JOIN t10_8 ON t100_7.pk = t10_8.col;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t10_8	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t100_7	eq_ref	PRIMARY	PRIMARY	4	test.t10_8.col	1	Using where
+1	SIMPLE	t10_7	ref	colidx	colidx	5	test.t100_7.col	1	Using where
+1	SIMPLE	t100_6	eq_ref	PRIMARY	PRIMARY	4	test.t10_7.col	1	Using where
+1	SIMPLE	t10_6	ref	colidx	colidx	5	test.t100_6.col	1	Using where
+1	SIMPLE	t100_5	eq_ref	PRIMARY	PRIMARY	4	test.t10_6.col	1	Using where
+1	SIMPLE	t10_5	ref	colidx	colidx	5	test.t100_5.col	1	Using where
+1	SIMPLE	t100_4	eq_ref	PRIMARY	PRIMARY	4	test.t10_5.col	1	Using where
+1	SIMPLE	t10_4	ref	colidx	colidx	5	test.t100_4.col	1	Using where
+1	SIMPLE	t100_3	eq_ref	PRIMARY	PRIMARY	4	test.t10_4.col	1	Using where
+1	SIMPLE	t10_3	ref	colidx	colidx	5	test.t100_3.col	1	Using where
+1	SIMPLE	t100_2	eq_ref	PRIMARY	PRIMARY	4	test.t10_3.col	1	Using where
+1	SIMPLE	t10_2	ref	colidx	colidx	5	test.t100_2.col	1	Using where
+1	SIMPLE	t100_1	eq_ref	PRIMARY	PRIMARY	4	test.t10_2.col	1	Using where
+1	SIMPLE	t10_1	ref	colidx	colidx	5	test.t100_1.col	1	
+### Opt_partial_plans: 12025
+FLUSH STATUS;
+#
+# Chain test d: (...,colidx):(pk, col):(pk,colidx):(pk,col):(pk,...)
+#
+EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.colidx = t100_1.pk JOIN t10_2 ON t100_1.col = t10_2.pk JOIN t100_2 ON t10_2.colidx = t100_2.pk JOIN t10_3 ON t100_2.col = t10_3.pk JOIN t100_3 ON t10_3.colidx = t100_3.pk JOIN t10_4 ON t100_3.col = t10_4.pk JOIN t100_4 ON t10_4.colidx = t100_4.pk JOIN t10_5 ON t100_4.col = t10_5.pk JOIN t100_5 ON t10_5.colidx = t100_5.pk JOIN t10_6 ON t100_5.col = t10_6.pk JOIN t100_6 ON t10_6.colidx = t100_6.pk JOIN t10_7 ON t100_6.col = t10_7.pk JOIN t100_7 ON t10_7.colidx = t100_7.pk JOIN t10_8 ON t100_7.col = t10_8.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t10_1	ALL	colidx	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t100_1	eq_ref	PRIMARY	PRIMARY	4	test.t10_1.colidx	1	Using where
+1	SIMPLE	t10_2	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_1.col	1	Using where
+1	SIMPLE	t100_2	eq_ref	PRIMARY	PRIMARY	4	test.t10_2.colidx	1	Using where
+1	SIMPLE	t10_3	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_2.col	1	Using where
+1	SIMPLE	t100_3	eq_ref	PRIMARY	PRIMARY	4	test.t10_3.colidx	1	Using where
+1	SIMPLE	t10_4	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_3.col	1	Using where
+1	SIMPLE	t100_4	eq_ref	PRIMARY	PRIMARY	4	test.t10_4.colidx	1	Using where
+1	SIMPLE	t10_5	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_4.col	1	Using where
+1	SIMPLE	t100_5	eq_ref	PRIMARY	PRIMARY	4	test.t10_5.colidx	1	Using where
+1	SIMPLE	t10_6	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_5.col	1	Using where
+1	SIMPLE	t100_6	eq_ref	PRIMARY	PRIMARY	4	test.t10_6.colidx	1	Using where
+1	SIMPLE	t10_7	eq_ref	PRIMARY,colidx	PRIMARY	4	test.t100_6.col	1	Using where
+1	SIMPLE	t100_7	eq_ref	PRIMARY	PRIMARY	4	test.t10_7.colidx	1	Using where
+1	SIMPLE	t10_8	eq_ref	PRIMARY	PRIMARY	4	test.t100_7.col	1	
+### Opt_partial_plans: 1445
+FLUSH STATUS;
+#
+# Cleanup after TEST 2
+#
+DROP TABLE tbl10, tbl100;
+DROP TABLE t10_1,t10_2,t10_3,t10_4,t10_5,t10_6,t10_7,t10_8,t10_9;
+DROP TABLE t100_1,t100_2,t100_3,t100_4,t100_5,t100_6,t100_7,t100_8,t100_9;

=== modified file 'mysql-test/r/join_cache_bka.result'
--- a/mysql-test/r/join_cache_bka.result	2012-02-21 10:31:44 +0000
+++ b/mysql-test/r/join_cache_bka.result	2012-02-23 11:22:34 +0000
@@ -2387,33 +2387,33 @@ WHERE t3.c1 = SOME (SELECT t1.c2_key FRO
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 NOT 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 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	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
-1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
 SELECT t3.c1 FROM t3
 WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)

=== modified file 'mysql-test/r/join_cache_bka_nixbnl.result'
--- a/mysql-test/r/join_cache_bka_nixbnl.result	2012-02-21 10:31:44 +0000
+++ b/mysql-test/r/join_cache_bka_nixbnl.result	2012-02-23 11:22:34 +0000
@@ -2387,33 +2387,33 @@ WHERE t3.c1 = SOME (SELECT t1.c2_key FRO
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 NOT 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 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	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
-1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
 SELECT t3.c1 FROM t3
 WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)

=== modified file 'mysql-test/r/join_cache_bkaunique.result'
--- a/mysql-test/r/join_cache_bkaunique.result	2012-02-21 10:31:44 +0000
+++ b/mysql-test/r/join_cache_bkaunique.result	2012-02-23 11:22:34 +0000
@@ -2388,33 +2388,33 @@ WHERE t3.c1 = SOME (SELECT t1.c2_key FRO
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 NOT 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 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	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
-1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
 SELECT t3.c1 FROM t3
 WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)

=== modified file 'mysql-test/r/join_cache_bnl.result'
--- a/mysql-test/r/join_cache_bnl.result	2012-02-21 10:31:44 +0000
+++ b/mysql-test/r/join_cache_bnl.result	2012-02-23 11:22:34 +0000
@@ -2388,33 +2388,33 @@ WHERE t3.c1 = SOME (SELECT t1.c2_key FRO
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 NOT 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 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	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
-1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
 SELECT t3.c1 FROM t3
 WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)

=== modified file 'mysql-test/r/join_cache_nojb.result'
--- a/mysql-test/r/join_cache_nojb.result	2012-02-21 10:31:44 +0000
+++ b/mysql-test/r/join_cache_nojb.result	2012-02-23 11:22:34 +0000
@@ -2388,33 +2388,33 @@ WHERE t3.c1 = SOME (SELECT t1.c2_key FRO
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
 XOR TRUE;
 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 explain SELECT t3.c1 FROM t3
 WHERE t3.c1 NOT 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; Full scan on NULL key
+2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 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	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
-1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
 1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
 SELECT t3.c1 FROM t3
 WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)

=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result	2012-01-20 15:30:14 +0000
+++ b/mysql-test/r/join_nested.result	2012-02-23 11:22:34 +0000
@@ -546,8 +546,8 @@ id	select_type	table	type	possible_keys	
 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 (Block Nested Loop)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (Block Nested Loop)
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)

=== modified file 'mysql-test/r/join_nested_bka.result'
--- a/mysql-test/r/join_nested_bka.result	2012-01-20 15:30:14 +0000
+++ b/mysql-test/r/join_nested_bka.result	2012-02-23 11:22:34 +0000
@@ -547,8 +547,8 @@ id	select_type	table	type	possible_keys	
 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 (Block Nested Loop)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (Block Nested Loop)
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)

=== modified file 'mysql-test/r/join_nested_bka_nixbnl.result'
--- a/mysql-test/r/join_nested_bka_nixbnl.result	2012-01-20 15:30:14 +0000
+++ b/mysql-test/r/join_nested_bka_nixbnl.result	2012-02-23 11:22:34 +0000
@@ -547,8 +547,8 @@ id	select_type	table	type	possible_keys	
 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
 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	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	
 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

=== modified file 'mysql-test/r/metadata.result'
--- a/mysql-test/r/metadata.result	2011-11-21 11:58:25 +0000
+++ b/mysql-test/r/metadata.result	2012-02-23 16:32:32 +0000
@@ -21,7 +21,7 @@ def	test	t1	t1	g	g	5	4	0	Y	32768	3	63
 def	test	t1	t1	h	h	246	7	0	Y	32768	4	63
 def	test	t1	t1	i	i	13	4	0	Y	32864	0	63
 def	test	t1	t1	j	j	10	10	0	Y	128	0	63
-def	test	t1	t1	k	k	7	19	0	N	9441	0	63
+def	test	t1	t1	k	k	7	19	0	N	9345	0	63
 def	test	t1	t1	l	l	12	19	0	Y	128	0	63
 def	test	t1	t1	m	m	254	1	0	Y	256	0	8
 def	test	t1	t1	n	n	254	3	0	Y	2048	0	8
@@ -284,7 +284,7 @@ def	test	t1	t1	double_precision_col	doub
 def	test	t1	t1	dcol_uns	dcol_uns	5	22	0	Y	32800	31	63
 def	test	t1	t1	date_col	date_col	10	10	0	Y	128	0	63
 def	test	t1	t1	time_col	time_col	11	10	0	Y	128	0	63
-def	test	t1	t1	timestamp_col	timestamp_col	7	19	0	N	9441	0	63
+def	test	t1	t1	timestamp_col	timestamp_col	7	19	0	N	9345	0	63
 def	test	t1	t1	year_col	year_col	13	4	0	Y	32864	0	63
 def	test	t1	t1	datetime_col	datetime_col	12	19	0	Y	128	0	63
 def	test	t1	t1	char_col	char_col	254	5	0	Y	0	0	8
@@ -332,12 +332,12 @@ def	test	t1	t1	dt3	dt3	12	23	0	Y	128	3	6
 def	test	t1	t1	dt2	dt2	12	22	0	Y	128	2	63
 def	test	t1	t1	dt1	dt1	12	21	0	Y	128	1	63
 def	test	t1	t1	dt0	dt0	12	19	0	Y	128	0	63
-def	test	t1	t1	ts6	ts6	7	26	0	N	9441	6	63
-def	test	t1	t1	ts5	ts5	7	25	0	N	225	5	63
-def	test	t1	t1	ts4	ts4	7	24	0	N	225	4	63
-def	test	t1	t1	ts3	ts3	7	23	0	N	225	3	63
-def	test	t1	t1	ts2	ts2	7	22	0	N	225	2	63
-def	test	t1	t1	ts1	ts1	7	21	0	N	225	1	63
-def	test	t1	t1	ts0	ts0	7	19	0	N	225	0	63
+def	test	t1	t1	ts6	ts6	7	26	0	N	9345	6	63
+def	test	t1	t1	ts5	ts5	7	25	0	N	129	5	63
+def	test	t1	t1	ts4	ts4	7	24	0	N	129	4	63
+def	test	t1	t1	ts3	ts3	7	23	0	N	129	3	63
+def	test	t1	t1	ts2	ts2	7	22	0	N	129	2	63
+def	test	t1	t1	ts1	ts1	7	21	0	N	129	1	63
+def	test	t1	t1	ts0	ts0	7	19	0	N	129	0	63
 t6	t5	t4	t3	t2	t1	t0	dt6	dt5	dt4	dt3	dt2	dt1	dt0	ts6	ts5	ts4	ts3	ts2	ts1	ts0
 DROP TABLE t1;

=== modified file 'mysql-test/r/mysql_client_test_embedded.result'
--- a/mysql-test/r/mysql_client_test_embedded.result	2011-12-05 10:11:58 +0000
+++ b/mysql-test/r/mysql_client_test_embedded.result	2012-02-23 10:39:15 +0000
@@ -1,92 +1,2 @@
 # Run the start/stop test 17 times (* 64 = 1088 restarts)
 # --silent to avoid printing out server version etc.
-
-
-#####################################
-1 of (1/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (2/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (3/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (4/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (5/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (6/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (7/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (8/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (9/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (10/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (11/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (12/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (13/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (14/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (15/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (16/17): test_embedded_start_stop  
-#####################################
-
-
-#####################################
-1 of (17/17): test_embedded_start_stop  
-#####################################
-
-
-All '1' tests were successful (in '17' iterations)
-
-!!! SUCCESS !!!

=== modified file 'mysql-test/r/ps_2myisam.result'
--- a/mysql-test/r/ps_2myisam.result	2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_2myisam.result	2012-02-23 16:32:32 +0000
@@ -63,7 +63,7 @@ def	test	t9	t9	c11	c11	246	9	6	Y	32768	4
 def	test	t9	t9	c12	c12	246	10	6	Y	32768	4	63
 def	test	t9	t9	c13	c13	10	10	10	Y	128	0	63
 def	test	t9	t9	c14	c14	12	19	19	Y	128	0	63
-def	test	t9	t9	c15	c15	7	19	19	N	9441	0	63
+def	test	t9	t9	c15	c15	7	19	19	N	9345	0	63
 def	test	t9	t9	c16	c16	11	10	8	Y	128	0	63
 def	test	t9	t9	c17	c17	13	4	4	Y	32864	0	63
 def	test	t9	t9	c18	c18	1	4	1	Y	32768	0	63

=== modified file 'mysql-test/r/ps_3innodb.result'
--- a/mysql-test/r/ps_3innodb.result	2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_3innodb.result	2012-02-23 16:32:32 +0000
@@ -63,7 +63,7 @@ def	test	t9	t9	c11	c11	246	9	6	Y	32768	4
 def	test	t9	t9	c12	c12	246	10	6	Y	32768	4	63
 def	test	t9	t9	c13	c13	10	10	10	Y	128	0	63
 def	test	t9	t9	c14	c14	12	19	19	Y	128	0	63
-def	test	t9	t9	c15	c15	7	19	19	N	9441	0	63
+def	test	t9	t9	c15	c15	7	19	19	N	9345	0	63
 def	test	t9	t9	c16	c16	11	10	8	Y	128	0	63
 def	test	t9	t9	c17	c17	13	4	4	Y	32864	0	63
 def	test	t9	t9	c18	c18	1	4	1	Y	32768	0	63

=== modified file 'mysql-test/r/ps_4heap.result'
--- a/mysql-test/r/ps_4heap.result	2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_4heap.result	2012-02-23 16:32:32 +0000
@@ -64,7 +64,7 @@ def	test	t9	t9	c11	c11	246	9	6	Y	32768	4
 def	test	t9	t9	c12	c12	246	10	6	Y	32768	4	63
 def	test	t9	t9	c13	c13	10	10	10	Y	128	0	63
 def	test	t9	t9	c14	c14	12	19	19	Y	128	0	63
-def	test	t9	t9	c15	c15	7	19	19	N	9441	0	63
+def	test	t9	t9	c15	c15	7	19	19	N	9345	0	63
 def	test	t9	t9	c16	c16	11	10	8	Y	128	0	63
 def	test	t9	t9	c17	c17	13	4	4	Y	32864	0	63
 def	test	t9	t9	c18	c18	1	4	1	Y	32768	0	63

=== modified file 'mysql-test/r/ps_5merge.result'
--- a/mysql-test/r/ps_5merge.result	2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/ps_5merge.result	2012-02-23 16:32:32 +0000
@@ -106,7 +106,7 @@ def	test	t9	t9	c11	c11	246	9	6	Y	32768	4
 def	test	t9	t9	c12	c12	246	10	6	Y	32768	4	63
 def	test	t9	t9	c13	c13	10	10	10	Y	128	0	63
 def	test	t9	t9	c14	c14	12	19	19	Y	128	0	63
-def	test	t9	t9	c15	c15	7	19	19	N	9441	0	63
+def	test	t9	t9	c15	c15	7	19	19	N	9345	0	63
 def	test	t9	t9	c16	c16	11	10	8	Y	128	0	63
 def	test	t9	t9	c17	c17	13	4	4	Y	32864	0	63
 def	test	t9	t9	c18	c18	1	4	1	Y	32768	0	63
@@ -3132,7 +3132,7 @@ def	test	t9	t9	c11	c11	246	9	6	Y	32768	4
 def	test	t9	t9	c12	c12	246	10	6	Y	32768	4	63
 def	test	t9	t9	c13	c13	10	10	10	Y	128	0	63
 def	test	t9	t9	c14	c14	12	19	19	Y	128	0	63
-def	test	t9	t9	c15	c15	7	19	19	N	9441	0	63
+def	test	t9	t9	c15	c15	7	19	19	N	9345	0	63
 def	test	t9	t9	c16	c16	11	10	8	Y	128	0	63
 def	test	t9	t9	c17	c17	13	4	4	Y	32864	0	63
 def	test	t9	t9	c18	c18	1	4	1	Y	32768	0	63

=== modified file 'mysql-test/r/select_all.result'
--- a/mysql-test/r/select_all.result	2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_all.result	2012-02-23 13:27:28 +0000
@@ -5026,4 +5026,13 @@ f1
 DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a	COUNT(*)
+NULL	0
+DROP TABLE t1;
+# End of test BUG#13571700
 set optimizer_switch=default;

=== modified file 'mysql-test/r/select_all_bka.result'
--- a/mysql-test/r/select_all_bka.result	2011-12-16 10:57:51 +0000
+++ b/mysql-test/r/select_all_bka.result	2012-02-23 13:27:28 +0000
@@ -5027,5 +5027,14 @@ f1
 DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a	COUNT(*)
+NULL	0
+DROP TABLE t1;
+# End of test BUG#13571700
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/select_all_bka_nixbnl.result'
--- a/mysql-test/r/select_all_bka_nixbnl.result	2011-12-16 10:57:51 +0000
+++ b/mysql-test/r/select_all_bka_nixbnl.result	2012-02-23 13:27:28 +0000
@@ -5027,5 +5027,14 @@ f1
 DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a	COUNT(*)
+NULL	0
+DROP TABLE t1;
+# End of test BUG#13571700
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/select_icp_mrr.result'
--- a/mysql-test/r/select_icp_mrr.result	2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_icp_mrr.result	2012-02-23 13:27:28 +0000
@@ -5026,4 +5026,13 @@ f1
 DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a	COUNT(*)
+NULL	0
+DROP TABLE t1;
+# End of test BUG#13571700
 set optimizer_switch=default;

=== modified file 'mysql-test/r/select_icp_mrr_bka.result'
--- a/mysql-test/r/select_icp_mrr_bka.result	2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_icp_mrr_bka.result	2012-02-23 13:27:28 +0000
@@ -5027,5 +5027,14 @@ f1
 DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a	COUNT(*)
+NULL	0
+DROP TABLE t1;
+# End of test BUG#13571700
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/select_icp_mrr_bka_nixbnl.result'
--- a/mysql-test/r/select_icp_mrr_bka_nixbnl.result	2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_icp_mrr_bka_nixbnl.result	2012-02-23 13:27:28 +0000
@@ -5027,5 +5027,14 @@ f1
 DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a	COUNT(*)
+NULL	0
+DROP TABLE t1;
+# End of test BUG#13571700
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/select_none.result'
--- a/mysql-test/r/select_none.result	2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_none.result	2012-02-23 13:27:28 +0000
@@ -5025,4 +5025,13 @@ f1
 DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a	COUNT(*)
+NULL	0
+DROP TABLE t1;
+# End of test BUG#13571700
 set optimizer_switch=default;

=== modified file 'mysql-test/r/select_none_bka.result'
--- a/mysql-test/r/select_none_bka.result	2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_none_bka.result	2012-02-23 13:27:28 +0000
@@ -5026,5 +5026,14 @@ f1
 DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a	COUNT(*)
+NULL	0
+DROP TABLE t1;
+# End of test BUG#13571700
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/select_none_bka_nixbnl.result'
--- a/mysql-test/r/select_none_bka_nixbnl.result	2011-12-15 11:54:45 +0000
+++ b/mysql-test/r/select_none_bka_nixbnl.result	2012-02-23 13:27:28 +0000
@@ -5026,5 +5026,14 @@ f1
 DROP TABLE t1;
 DROP VIEW view_t1;
 # End of test  BUG#63020
+#
+# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA
+#
+CREATE TABLE t1 (a TINYBLOB NOT NULL);
+SELECT a, COUNT(*) FROM t1 WHERE 0;
+a	COUNT(*)
+NULL	0
+DROP TABLE t1;
+# End of test BUG#13571700
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/subquery_all.result	2012-02-23 11:22:34 +0000
@@ -904,10 +904,10 @@ a	t1.a in (select t2.a from t2,t3 where 
 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	SUBQUERY	t2	index	a	a	5	NULL	3	100.00	Using index
-2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	
+2	SUBQUERY	t2	index	a	a	5	NULL	3	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where (`test`.`t3`.`a` = `test`.`t2`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`a`))))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where (`test`.`t2`.`a` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`a`))))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
 drop table t1,t2,t3;
 create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);

=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result	2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/subquery_all_bka.result	2012-02-23 11:22:34 +0000
@@ -905,10 +905,10 @@ a	t1.a in (select t2.a from t2,t3 where 
 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	SUBQUERY	t2	index	a	a	5	NULL	3	100.00	Using index
-2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	
+2	SUBQUERY	t2	index	a	a	5	NULL	3	100.00	Using where; Using index; Using join buffer (Block Nested Loop)
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where (`test`.`t3`.`a` = `test`.`t2`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`a`))))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where (`test`.`t2`.`a` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`a`))))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
 drop table t1,t2,t3;
 create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);

=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result	2012-02-08 15:25:17 +0000
+++ b/mysql-test/r/subquery_mat_all.result	2012-02-23 11:22:34 +0000
@@ -630,10 +630,10 @@ insert into t2 values (1,10);
 explain extended
 select a from t1 where a in (select c from t2 where d >= 20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	index	it1a	it1a	4	NULL	7	100.00	Using index
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; Materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; Materialize; Scan
+1	PRIMARY	t1	ref	it1a	it1a	4	test.t2.c	2	100.00	Using index
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t2`.`d` >= 20))
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`d` >= 20))
 select a from t1 where a in (select c from t2 where d >= 20);
 a
 2
@@ -682,12 +682,12 @@ explain extended
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	index	it1a,iab	iab	8	NULL	7	100.00	Using index
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	100.00	Start temporary
+1	PRIMARY	t1	ref	it1a,iab	iab	4	test.t2.c	1	100.00	Using where; Using index; End temporary
 3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 a

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-02-23 11:22:34 +0000
@@ -5039,9 +5039,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5053,14 +5053,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6630,8 +6630,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN
@@ -7532,8 +7532,8 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	GP2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	GP1	ALL	NULL	NULL	NULL	NULL	3	Using where
 2	DEPENDENT SUBQUERY	t2	index	col_varchar_key	col_varchar_key	4	NULL	1	Using index; Start temporary
-2	DEPENDENT SUBQUERY	t3	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	index	col_varchar_key	col_varchar_key	4	NULL	1	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 SELECT GP1.id
 FROM t1 AS GP1 JOIN t3 AS GP2
 ON GP2.col_varchar_key <> GP1.col_varchar_nokey

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-02-23 11:22:34 +0000
@@ -5040,9 +5040,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5054,14 +5054,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6631,8 +6631,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN
@@ -7533,8 +7533,8 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	GP2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	GP1	ALL	NULL	NULL	NULL	NULL	3	Using where
 2	DEPENDENT SUBQUERY	t2	index	col_varchar_key	col_varchar_key	4	NULL	1	Using index; Start temporary
-2	DEPENDENT SUBQUERY	t3	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	index	col_varchar_key	col_varchar_key	4	NULL	1	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 SELECT GP1.id
 FROM t1 AS GP1 JOIN t3 AS GP2
 ON GP2.col_varchar_key <> GP1.col_varchar_nokey

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-02-23 11:22:34 +0000
@@ -5040,9 +5040,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ref	t1_IDX	t1_IDX	3	test.t3.EMPNUM	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5054,14 +5054,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ref	t1_IDX	t1_IDX	3	test.t3.EMPNUM	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End temporary
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ref	t1_IDX	t1_IDX	3	test.t3.EMPNUM	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6631,8 +6631,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-02-23 11:22:34 +0000
@@ -5041,9 +5041,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5055,14 +5055,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6632,8 +6632,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN
@@ -7534,8 +7534,8 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	GP2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	GP1	ALL	NULL	NULL	NULL	NULL	3	Using where
 2	DEPENDENT SUBQUERY	t2	index	col_varchar_key	col_varchar_key	4	NULL	1	Using index; Start temporary
-2	DEPENDENT SUBQUERY	t3	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	index	col_varchar_key	col_varchar_key	4	NULL	1	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 SELECT GP1.id
 FROM t1 AS GP1 JOIN t3 AS GP2
 ON GP2.col_varchar_key <> GP1.col_varchar_nokey

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-02-23 11:22:34 +0000
@@ -5040,9 +5040,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5054,14 +5054,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6631,8 +6631,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN
@@ -7533,8 +7533,8 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	GP2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	GP1	ALL	NULL	NULL	NULL	NULL	3	Using where
 2	DEPENDENT SUBQUERY	t2	index	col_varchar_key	col_varchar_key	4	NULL	1	Using index; Start temporary
-2	DEPENDENT SUBQUERY	t3	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	index	col_varchar_key	col_varchar_key	4	NULL	1	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 SELECT GP1.id
 FROM t1 AS GP1 JOIN t3 AS GP2
 ON GP2.col_varchar_key <> GP1.col_varchar_nokey

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-02-23 11:22:34 +0000
@@ -5041,9 +5041,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5055,14 +5055,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6632,8 +6632,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN
@@ -7534,8 +7534,8 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	GP2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	GP1	ALL	NULL	NULL	NULL	NULL	3	Using where
 2	DEPENDENT SUBQUERY	t2	index	col_varchar_key	col_varchar_key	4	NULL	1	Using index; Start temporary
-2	DEPENDENT SUBQUERY	t3	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	index	col_varchar_key	col_varchar_key	4	NULL	1	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 SELECT GP1.id
 FROM t1 AS GP1 JOIN t3 AS GP2
 ON GP2.col_varchar_key <> GP1.col_varchar_nokey

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-02-23 11:22:34 +0000
@@ -5041,9 +5041,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; FirstMatch(t1)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ref	t1_IDX	t1_IDX	3	test.t3.EMPNUM	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5055,14 +5055,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; FirstMatch(t1)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ref	t1_IDX	t1_IDX	3	test.t3.EMPNUM	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; FirstMatch(t1)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ref	t1_IDX	t1_IDX	3	test.t3.EMPNUM	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6632,8 +6632,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-02-23 11:22:34 +0000
@@ -5042,9 +5042,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5056,14 +5056,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6633,8 +6633,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN
@@ -7535,8 +7535,8 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	GP2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	GP1	ALL	NULL	NULL	NULL	NULL	3	Using where
 2	DEPENDENT SUBQUERY	t2	index	col_varchar_key	col_varchar_key	4	NULL	1	Using index; Start temporary
-2	DEPENDENT SUBQUERY	t3	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
-2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	index	col_varchar_key	col_varchar_key	4	NULL	1	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
 SELECT GP1.id
 FROM t1 AS GP1 JOIN t3 AS GP2
 ON GP2.col_varchar_key <> GP1.col_varchar_nokey

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-02-23 11:22:34 +0000
@@ -5040,9 +5040,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5054,14 +5054,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6631,8 +6631,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-02-23 11:22:34 +0000
@@ -5041,9 +5041,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5055,14 +5055,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6632,8 +6632,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-02-23 11:22:34 +0000
@@ -5041,9 +5041,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ref	t1_IDX	t1_IDX	3	test.t3.EMPNUM	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5055,14 +5055,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ref	t1_IDX	t1_IDX	3	test.t3.EMPNUM	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End temporary
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ref	t1_IDX	t1_IDX	3	test.t3.EMPNUM	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6632,8 +6632,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN

=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-02-16 08:51:53 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-02-23 11:22:34 +0000
@@ -5042,9 +5042,9 @@ WHERE PNUM IN
 FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -5056,14 +5056,14 @@ WHERE EMPNUM IN
         WHERE PTYPE = 'Design'))";
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (Block Nested Loop)
-1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; End temporary; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Start temporary
+1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -6633,8 +6633,8 @@ AND grandparent1.col_varchar_key IS NOT 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	parent1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
-2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where
-2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index; End temporary
+2	DEPENDENT SUBQUERY	parent2	eq_ref	PRIMARY	PRIMARY	4	test.parent1.pk	1	Using index
+2	DEPENDENT SUBQUERY	grandparent1	ref	col_varchar_key	col_varchar_key	3	test.parent1.col_varchar_nokey	1	Using where; End temporary
 SELECT *
 FROM t1
 WHERE g1 NOT IN

=== modified file 'mysql-test/r/type_temporal_fractional.result'
--- a/mysql-test/r/type_temporal_fractional.result	2012-02-17 13:55:18 +0000
+++ b/mysql-test/r/type_temporal_fractional.result	2012-02-23 16:32:32 +0000
@@ -3104,13 +3104,13 @@ MAX(t4) + 1, MAX(t5) + 1, MAX(t6) + 1
 FROM t1;
 SHOW COLUMNS FROM t2;
 Field	Type	Null	Key	Default	Extra
-MAX(t0) + 1	bigint(12)	YES		NULL	
-MAX(t1) + 1	decimal(13,1)	YES		NULL	
-MAX(t2) + 1	decimal(14,2)	YES		NULL	
-MAX(t3) + 1	decimal(15,3)	YES		NULL	
-MAX(t4) + 1	decimal(16,4)	YES		NULL	
-MAX(t5) + 1	decimal(17,5)	YES		NULL	
-MAX(t6) + 1	decimal(18,6)	YES		NULL	
+MAX(t0) + 1	int(9)	YES		NULL	
+MAX(t1) + 1	decimal(9,1)	YES		NULL	
+MAX(t2) + 1	decimal(10,2)	YES		NULL	
+MAX(t3) + 1	decimal(11,3)	YES		NULL	
+MAX(t4) + 1	decimal(12,4)	YES		NULL	
+MAX(t5) + 1	decimal(13,5)	YES		NULL	
+MAX(t6) + 1	decimal(14,6)	YES		NULL	
 SELECT * FROM t2;
 MAX(t0) + 1	MAX(t1) + 1	MAX(t2) + 1	MAX(t3) + 1	MAX(t4) + 1	MAX(t5) + 1	MAX(t6) + 1
 101011	101011.9	101011.99	101011.999	101011.9999	101011.99999	101011.999999
@@ -3123,13 +3123,13 @@ MAX(t4) + 1.1, MAX(t5) + 1.1, MAX(t6) + 
 FROM t1;
 SHOW COLUMNS FROM t2;
 Field	Type	Null	Key	Default	Extra
-MAX(t0) + 1.1	decimal(12,1)	YES		NULL	
-MAX(t1) + 1.1	decimal(13,1)	YES		NULL	
-MAX(t2) + 1.1	decimal(14,2)	YES		NULL	
-MAX(t3) + 1.1	decimal(15,3)	YES		NULL	
-MAX(t4) + 1.1	decimal(16,4)	YES		NULL	
-MAX(t5) + 1.1	decimal(17,5)	YES		NULL	
-MAX(t6) + 1.1	decimal(18,6)	YES		NULL	
+MAX(t0) + 1.1	decimal(9,1)	YES		NULL	
+MAX(t1) + 1.1	decimal(9,1)	YES		NULL	
+MAX(t2) + 1.1	decimal(10,2)	YES		NULL	
+MAX(t3) + 1.1	decimal(11,3)	YES		NULL	
+MAX(t4) + 1.1	decimal(12,4)	YES		NULL	
+MAX(t5) + 1.1	decimal(13,5)	YES		NULL	
+MAX(t6) + 1.1	decimal(14,6)	YES		NULL	
 SELECT * FROM t2;
 MAX(t0) + 1.1	MAX(t1) + 1.1	MAX(t2) + 1.1	MAX(t3) + 1.1	MAX(t4) + 1.1	MAX(t5) + 1.1	MAX(t6) + 1.1
 101011.1	101012.0	101012.09	101012.099	101012.0999	101012.09999	101012.099999
@@ -3597,8 +3597,8 @@ CREATE TABLE t2 AS SELECT a + 1 AS i, a 
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `i` decimal(18,6) DEFAULT NULL,
-  `d` decimal(18,6) DEFAULT NULL,
+  `i` decimal(14,6) DEFAULT NULL,
+  `d` decimal(14,6) DEFAULT NULL,
   `f` double DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1, t2;
@@ -3627,30 +3627,30 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `t0 + 1` bigint(12) DEFAULT NULL,
-  `t3 + 1` decimal(15,3) DEFAULT NULL,
-  `t6 + 1` decimal(18,6) DEFAULT NULL,
-  `t0 - 1` bigint(12) DEFAULT NULL,
-  `t3 - 1` decimal(15,3) DEFAULT NULL,
-  `t6 - 1` decimal(18,6) DEFAULT NULL,
-  `t0 * 1` bigint(12) DEFAULT NULL,
-  `t3 * 1` decimal(15,3) DEFAULT NULL,
-  `t6 * 1` decimal(18,6) DEFAULT NULL,
-  `t0 / 1` decimal(14,4) DEFAULT NULL,
-  `t3 / 1` decimal(18,7) DEFAULT NULL,
-  `t6 / 1` decimal(21,10) DEFAULT NULL,
-  `TIME'10:10:10' + 1` bigint(12) NOT NULL DEFAULT '0',
-  `TIME'10:10:10.123' + 1` decimal(15,3) NOT NULL DEFAULT '0.000',
-  `TIME'10:10:10.123456' + 1` decimal(18,6) NOT NULL DEFAULT '0.000000',
-  `TIME'10:10:10' - 1` bigint(12) NOT NULL DEFAULT '0',
-  `TIME'10:10:10.123' - 1` decimal(15,3) NOT NULL DEFAULT '0.000',
-  `TIME'10:10:10.123456' - 1` decimal(18,6) NOT NULL DEFAULT '0.000000',
-  `TIME'10:10:10' * 1` bigint(12) NOT NULL DEFAULT '0',
-  `TIME'10:10:10.123' * 1` decimal(15,3) NOT NULL DEFAULT '0.000',
-  `TIME'10:10:10.123456' * 1` decimal(18,6) NOT NULL DEFAULT '0.000000',
-  `TIME'10:10:10' / 1` decimal(14,4) DEFAULT NULL,
-  `TIME'10:10:10.123' / 1` decimal(18,7) DEFAULT NULL,
-  `TIME'10:10:10.123456' / 1` decimal(21,10) DEFAULT NULL
+  `t0 + 1` int(9) DEFAULT NULL,
+  `t3 + 1` decimal(11,3) DEFAULT NULL,
+  `t6 + 1` decimal(14,6) DEFAULT NULL,
+  `t0 - 1` int(9) DEFAULT NULL,
+  `t3 - 1` decimal(11,3) DEFAULT NULL,
+  `t6 - 1` decimal(14,6) DEFAULT NULL,
+  `t0 * 1` int(9) DEFAULT NULL,
+  `t3 * 1` decimal(11,3) DEFAULT NULL,
+  `t6 * 1` decimal(14,6) DEFAULT NULL,
+  `t0 / 1` decimal(11,4) DEFAULT NULL,
+  `t3 / 1` decimal(14,7) DEFAULT NULL,
+  `t6 / 1` decimal(17,10) DEFAULT NULL,
+  `TIME'10:10:10' + 1` int(9) NOT NULL DEFAULT '0',
+  `TIME'10:10:10.123' + 1` decimal(11,3) NOT NULL DEFAULT '0.000',
+  `TIME'10:10:10.123456' + 1` decimal(14,6) NOT NULL DEFAULT '0.000000',
+  `TIME'10:10:10' - 1` int(9) NOT NULL DEFAULT '0',
+  `TIME'10:10:10.123' - 1` decimal(11,3) NOT NULL DEFAULT '0.000',
+  `TIME'10:10:10.123456' - 1` decimal(14,6) NOT NULL DEFAULT '0.000000',
+  `TIME'10:10:10' * 1` int(9) NOT NULL DEFAULT '0',
+  `TIME'10:10:10.123' * 1` decimal(11,3) NOT NULL DEFAULT '0.000',
+  `TIME'10:10:10.123456' * 1` decimal(14,6) NOT NULL DEFAULT '0.000000',
+  `TIME'10:10:10' / 1` decimal(11,4) DEFAULT NULL,
+  `TIME'10:10:10.123' / 1` decimal(14,7) DEFAULT NULL,
+  `TIME'10:10:10.123456' / 1` decimal(17,10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -3702,16 +3702,16 @@ Table	Create Table
 t2	CREATE TABLE `t2` (
   `MIN(t0)` time DEFAULT NULL,
   `MAX(t0)` time DEFAULT NULL,
-  `AVG(t0)` decimal(14,4) DEFAULT NULL,
-  `SUM(t0)` decimal(32,0) DEFAULT NULL,
+  `AVG(t0)` decimal(11,4) DEFAULT NULL,
+  `SUM(t0)` decimal(29,0) DEFAULT NULL,
   `MIN(t3)` time(3) DEFAULT NULL,
   `MAX(t3)` time(3) DEFAULT NULL,
-  `AVG(t3)` decimal(18,7) DEFAULT NULL,
-  `SUM(t3)` decimal(36,3) DEFAULT NULL,
+  `AVG(t3)` decimal(14,7) DEFAULT NULL,
+  `SUM(t3)` decimal(32,3) DEFAULT NULL,
   `MIN(t6)` time(6) DEFAULT NULL,
   `MAX(t6)` time(6) DEFAULT NULL,
-  `AVG(t6)` decimal(21,10) DEFAULT NULL,
-  `SUM(t6)` decimal(39,6) DEFAULT NULL
+  `AVG(t6)` decimal(17,10) DEFAULT NULL,
+  `SUM(t6)` decimal(35,6) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -8041,13 +8041,13 @@ MAX(t4) + 1, MAX(t5) + 1, MAX(t6) + 1
 FROM t1;
 SHOW COLUMNS FROM t2;
 Field	Type	Null	Key	Default	Extra
-MAX(t0) + 1	bigint(21)	YES		NULL	
-MAX(t1) + 1	decimal(22,1)	YES		NULL	
-MAX(t2) + 1	decimal(23,2)	YES		NULL	
-MAX(t3) + 1	decimal(24,3)	YES		NULL	
-MAX(t4) + 1	decimal(25,4)	YES		NULL	
-MAX(t5) + 1	decimal(26,5)	YES		NULL	
-MAX(t6) + 1	decimal(27,6)	YES		NULL	
+MAX(t0) + 1	bigint(16)	YES		NULL	
+MAX(t1) + 1	decimal(16,1)	YES		NULL	
+MAX(t2) + 1	decimal(17,2)	YES		NULL	
+MAX(t3) + 1	decimal(18,3)	YES		NULL	
+MAX(t4) + 1	decimal(19,4)	YES		NULL	
+MAX(t5) + 1	decimal(20,5)	YES		NULL	
+MAX(t6) + 1	decimal(21,6)	YES		NULL	
 SELECT * FROM t2;
 MAX(t0) + 1	MAX(t1) + 1	MAX(t2) + 1	MAX(t3) + 1	MAX(t4) + 1	MAX(t5) + 1	MAX(t6) + 1
 20010101101011	20010101101011.9	20010101101011.99	20010101101011.999	20010101101011.9999	20010101101011.99999	20010101101011.999999
@@ -8060,13 +8060,13 @@ MAX(t4) + 1.1, MAX(t5) + 1.1, MAX(t6) + 
 FROM t1;
 SHOW COLUMNS FROM t2;
 Field	Type	Null	Key	Default	Extra
-MAX(t0) + 1.1	decimal(21,1)	YES		NULL	
-MAX(t1) + 1.1	decimal(22,1)	YES		NULL	
-MAX(t2) + 1.1	decimal(23,2)	YES		NULL	
-MAX(t3) + 1.1	decimal(24,3)	YES		NULL	
-MAX(t4) + 1.1	decimal(25,4)	YES		NULL	
-MAX(t5) + 1.1	decimal(26,5)	YES		NULL	
-MAX(t6) + 1.1	decimal(27,6)	YES		NULL	
+MAX(t0) + 1.1	decimal(16,1)	YES		NULL	
+MAX(t1) + 1.1	decimal(16,1)	YES		NULL	
+MAX(t2) + 1.1	decimal(17,2)	YES		NULL	
+MAX(t3) + 1.1	decimal(18,3)	YES		NULL	
+MAX(t4) + 1.1	decimal(19,4)	YES		NULL	
+MAX(t5) + 1.1	decimal(20,5)	YES		NULL	
+MAX(t6) + 1.1	decimal(21,6)	YES		NULL	
 SELECT * FROM t2;
 MAX(t0) + 1.1	MAX(t1) + 1.1	MAX(t2) + 1.1	MAX(t3) + 1.1	MAX(t4) + 1.1	MAX(t5) + 1.1	MAX(t6) + 1.1
 20010101101011.1	20010101101012.0	20010101101012.09	20010101101012.099	20010101101012.0999	20010101101012.09999	20010101101012.099999
@@ -8470,8 +8470,8 @@ CREATE TABLE t2 AS SELECT a + 1 AS i, a 
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `i` decimal(27,6) DEFAULT NULL,
-  `d` decimal(27,6) DEFAULT NULL,
+  `i` decimal(21,6) DEFAULT NULL,
+  `d` decimal(21,6) DEFAULT NULL,
   `f` double DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1, t2;
@@ -8500,30 +8500,30 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `t0 + 1` bigint(21) DEFAULT NULL,
-  `t3 + 1` decimal(24,3) DEFAULT NULL,
-  `t6 + 1` decimal(27,6) DEFAULT NULL,
-  `t0 - 1` bigint(21) DEFAULT NULL,
-  `t3 - 1` decimal(24,3) DEFAULT NULL,
-  `t6 - 1` decimal(27,6) DEFAULT NULL,
-  `t0 * 1` bigint(21) DEFAULT NULL,
-  `t3 * 1` decimal(24,3) DEFAULT NULL,
-  `t6 * 1` decimal(27,6) DEFAULT NULL,
-  `t0 / 1` decimal(23,4) DEFAULT NULL,
-  `t3 / 1` decimal(27,7) DEFAULT NULL,
-  `t6 / 1` decimal(30,10) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(23,4) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(27,7) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(30,10) DEFAULT NULL
+  `t0 + 1` bigint(16) DEFAULT NULL,
+  `t3 + 1` decimal(18,3) DEFAULT NULL,
+  `t6 + 1` decimal(21,6) DEFAULT NULL,
+  `t0 - 1` bigint(16) DEFAULT NULL,
+  `t3 - 1` decimal(18,3) DEFAULT NULL,
+  `t6 - 1` decimal(21,6) DEFAULT NULL,
+  `t0 * 1` bigint(16) DEFAULT NULL,
+  `t3 * 1` decimal(18,3) DEFAULT NULL,
+  `t6 * 1` decimal(21,6) DEFAULT NULL,
+  `t0 / 1` decimal(18,4) DEFAULT NULL,
+  `t3 / 1` decimal(21,7) DEFAULT NULL,
+  `t6 / 1` decimal(24,10) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(18,4) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(21,7) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(24,10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -8575,16 +8575,16 @@ Table	Create Table
 t2	CREATE TABLE `t2` (
   `MIN(t0)` datetime DEFAULT NULL,
   `MAX(t0)` datetime DEFAULT NULL,
-  `AVG(t0)` decimal(23,4) DEFAULT NULL,
-  `SUM(t0)` decimal(41,0) DEFAULT NULL,
+  `AVG(t0)` decimal(18,4) DEFAULT NULL,
+  `SUM(t0)` decimal(36,0) DEFAULT NULL,
   `MIN(t3)` datetime(3) DEFAULT NULL,
   `MAX(t3)` datetime(3) DEFAULT NULL,
-  `AVG(t3)` decimal(27,7) DEFAULT NULL,
-  `SUM(t3)` decimal(45,3) DEFAULT NULL,
+  `AVG(t3)` decimal(21,7) DEFAULT NULL,
+  `SUM(t3)` decimal(39,3) DEFAULT NULL,
   `MIN(t6)` datetime(6) DEFAULT NULL,
   `MAX(t6)` datetime(6) DEFAULT NULL,
-  `AVG(t6)` decimal(30,10) DEFAULT NULL,
-  `SUM(t6)` decimal(48,6) DEFAULT NULL
+  `AVG(t6)` decimal(24,10) DEFAULT NULL,
+  `SUM(t6)` decimal(42,6) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -13161,8 +13161,8 @@ CREATE TABLE t2 AS SELECT a + 1 AS i, a 
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `i` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `d` decimal(27,6) NOT NULL DEFAULT '0.000000',
+  `i` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `d` decimal(21,6) NOT NULL DEFAULT '0.000000',
   `f` double NOT NULL DEFAULT '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1, t2;
@@ -13191,30 +13191,30 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `t0 + 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `t3 + 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `t6 + 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `t0 - 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `t3 - 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `t6 - 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `t0 * 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `t3 * 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `t6 * 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `t0 / 1` decimal(23,4) DEFAULT NULL,
-  `t3 / 1` decimal(27,7) DEFAULT NULL,
-  `t6 / 1` decimal(30,10) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(20) unsigned NOT NULL DEFAULT '0',
-  `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(24,3) NOT NULL DEFAULT '0.000',
-  `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(27,6) NOT NULL DEFAULT '0.000000',
-  `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(23,4) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(27,7) DEFAULT NULL,
-  `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(30,10) DEFAULT NULL
+  `t0 + 1` bigint(16) NOT NULL DEFAULT '0',
+  `t3 + 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `t6 + 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `t0 - 1` bigint(16) NOT NULL DEFAULT '0',
+  `t3 - 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `t6 - 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `t0 * 1` bigint(16) NOT NULL DEFAULT '0',
+  `t3 * 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `t6 * 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `t0 / 1` decimal(18,4) DEFAULT NULL,
+  `t3 / 1` decimal(21,7) DEFAULT NULL,
+  `t6 / 1` decimal(24,10) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10' + 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' + 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' + 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' - 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' - 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' - 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' * 1` bigint(16) NOT NULL DEFAULT '0',
+  `TIMESTAMP'2001-01-01 10:10:10.123' * 1` decimal(18,3) NOT NULL DEFAULT '0.000',
+  `TIMESTAMP'2001-01-01 10:10:10.123456' * 1` decimal(21,6) NOT NULL DEFAULT '0.000000',
+  `TIMESTAMP'2001-01-01 10:10:10' / 1` decimal(18,4) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10.123' / 1` decimal(21,7) DEFAULT NULL,
+  `TIMESTAMP'2001-01-01 10:10:10.123456' / 1` decimal(24,10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
@@ -13266,16 +13266,16 @@ Table	Create Table
 t2	CREATE TABLE `t2` (
   `MIN(t0)` datetime DEFAULT NULL,
   `MAX(t0)` datetime DEFAULT NULL,
-  `AVG(t0)` decimal(23,4) DEFAULT NULL,
-  `SUM(t0)` decimal(41,0) DEFAULT NULL,
+  `AVG(t0)` decimal(18,4) DEFAULT NULL,
+  `SUM(t0)` decimal(36,0) DEFAULT NULL,
   `MIN(t3)` datetime(3) DEFAULT NULL,
   `MAX(t3)` datetime(3) DEFAULT NULL,
-  `AVG(t3)` decimal(27,7) DEFAULT NULL,
-  `SUM(t3)` decimal(45,3) DEFAULT NULL,
+  `AVG(t3)` decimal(21,7) DEFAULT NULL,
+  `SUM(t3)` decimal(39,3) DEFAULT NULL,
   `MIN(t6)` datetime(6) DEFAULT NULL,
   `MAX(t6)` datetime(6) DEFAULT NULL,
-  `AVG(t6)` decimal(30,10) DEFAULT NULL,
-  `SUM(t6)` decimal(48,6) DEFAULT NULL
+  `AVG(t6)` decimal(24,10) DEFAULT NULL,
+  `SUM(t6)` decimal(42,6) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;

=== modified file 'mysql-test/r/type_timestamp.result'
--- a/mysql-test/r/type_timestamp.result	2012-01-31 15:16:16 +0000
+++ b/mysql-test/r/type_timestamp.result	2012-02-23 16:32:32 +0000
@@ -622,3 +622,30 @@ MAX(dt) = '2011-01-06 12:34:30'
 1
 DROP TABLE t1;
 End of 5.5 tests
+#
+# Start of 5.6 tests
+#
+#
+# Bug#13596893 - "ERROR 1690 (22003): BIGINT UNSIGNED VALUE IS OUT OF RANGE" ON DATE OPERATION
+#
+CREATE TABLE t1 (
+`c1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+`c2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
+);
+INSERT INTO t1 VALUES ('2003-05-16 23:53:29','2000-01-27 23:13:41');
+SELECT c2-c1 FROM t1;
+c2-c1
+-30389003988
+SELECT * FROM t1;
+c1	c2
+2003-05-16 23:53:29	2000-01-27 23:13:41
+SELECT TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29';
+TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29'
+-30389003988
+SELECT TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29');
+TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29')
+-30389003988
+DROP TABLE t1;
+#
+# End of 5.6 tests
+#

=== 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-02-21 10:31:44 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result	2012-02-23 11:22:34 +0000
@@ -1259,77 +1259,137 @@ ON table2 .col_int_key = table1 .col_int
             "considered_execution_plans": [
               {
                 "database": "test",
-                "table": "table1",
+                "table": "where_subselect_20070",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "ref",
-                      "index": "col_int_key",
-                      "usable": false,
-                      "chosen": false
-                    },
-                    {
                       "access_type": "scan",
-                      "rows": 4,
-                      "cost": 2.0068,
+                      "rows": 6,
+                      "cost": 2.019,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
-                "cost_for_plan": 2.8068,
-                "rows_for_plan": 4,
+                "cost_for_plan": 3.219,
+                "rows_for_plan": 6,
                 "semijoin_strategy_choice": [
                 ] /* semijoin_strategy_choice */,
                 "rest_of_plan": [
                   {
                     "database": "test",
-                    "table": "table2",
+                    "table": "table1",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
                           "access_type": "ref",
                           "index": "col_int_key",
                           "rows": 2,
-                          "cost": 4.493,
+                          "cost": 6.5395,
                           "chosen": true
                         },
                         {
                           "access_type": "scan",
                           "using_join_cache": true,
                           "rows": 3,
-                          "cost": 2.2071,
+                          "cost": 2.208,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 7.414,
-                    "rows_for_plan": 12,
+                    "cost_for_plan": 9.0271,
+                    "rows_for_plan": 18,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "where_subselect_20070",
+                        "table": "table2",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
+                              "access_type": "ref",
+                              "index": "col_int_key",
+                              "rows": 2,
+                              "cost": 18.819,
+                              "chosen": true
+                            },
+                            {
                               "access_type": "scan",
                               "using_join_cache": true,
-                              "rows": 6,
-                              "cost": 2.0209,
+                              "rows": 3,
+                              "cost": 2.2118,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 23.835,
-                        "rows_for_plan": 72,
+                        "cost_for_plan": 22.039,
+                        "rows_for_plan": 54,
                         "semijoin_strategy_choice": [
                           {
-                            "strategy": "DuplicatesWeedout",
-                            "cost": 41.435,
+                            "strategy": "FirstMatch",
+                            "recalculate_access_paths_and_cost": {
+                              "tables": [
+                                {
+                                  "database": "test",
+                                  "table": "table1",
+                                  "best_access_path": {
+                                    "considered_access_paths": [
+                                      {
+                                        "access_type": "ref",
+                                        "index": "col_int_key",
+                                        "rows": 2,
+                                        "cost": 6.5395,
+                                        "chosen": true
+                                      },
+                                      {
+                                        "access_type": "scan",
+                                        "rows": 3,
+                                        "cost": 13.241,
+                                        "chosen": false
+                                      }
+                                    ] /* considered_access_paths */
+                                  } /* best_access_path */
+                                },
+                                {
+                                  "database": "test",
+                                  "table": "table2",
+                                  "best_access_path": {
+                                    "considered_access_paths": [
+                                      {
+                                        "access_type": "ref",
+                                        "index": "col_int_key",
+                                        "rows": 2,
+                                        "cost": 12.679,
+                                        "chosen": true
+                                      },
+                                      {
+                                        "access_type": "scan",
+                                        "rows": 3,
+                                        "cost": 26.482,
+                                        "chosen": false
+                                      }
+                                    ] /* considered_access_paths */
+                                  } /* best_access_path */
+                                }
+                              ] /* tables */
+                            } /* recalculate_access_paths_and_cost */,
+                            "cost": 28.838,
                             "rows": 6,
-                            "duplicate_tables_left": true,
                             "chosen": true
+                          },
+                          {
+                            "strategy": "MaterializeLookup",
+                            "cost": 16.233,
+                            "rows": 6,
+                            "duplicate_tables_left": false,
+                            "chosen": true
+                          },
+                          {
+                            "strategy": "DuplicatesWeedout",
+                            "cost": 36.039,
+                            "rows": 6,
+                            "duplicate_tables_left": false,
+                            "chosen": false
                           }
                         ] /* semijoin_strategy_choice */,
                         "chosen": true
@@ -1338,53 +1398,119 @@ ON table2 .col_int_key = table1 .col_int
                   },
                   {
                     "database": "test",
-                    "table": "where_subselect_20070",
+                    "table": "table2",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
+                          "access_type": "ref",
+                          "index": "col_int_key",
+                          "rows": 2,
+                          "cost": 6.5395,
+                          "chosen": true
+                        },
+                        {
                           "access_type": "scan",
                           "using_join_cache": true,
-                          "rows": 6,
-                          "cost": 2.0194,
+                          "rows": 3,
+                          "cost": 2.208,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 9.6262,
-                    "rows_for_plan": 24,
+                    "cost_for_plan": 9.0271,
+                    "rows_for_plan": 18,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "table2",
+                        "table": "table1",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
                               "access_type": "ref",
                               "index": "col_int_key",
                               "rows": 2,
-                              "cost": 24.958,
+                              "cost": 18.819,
                               "chosen": true
                             },
                             {
                               "access_type": "scan",
                               "using_join_cache": true,
                               "rows": 3,
-                              "cost": 2.2135,
+                              "cost": 2.2118,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 26.24,
-                        "rows_for_plan": 72,
+                        "cost_for_plan": 22.039,
+                        "rows_for_plan": 54,
                         "semijoin_strategy_choice": [
                           {
-                            "strategy": "DuplicatesWeedout",
-                            "cost": 43.84,
+                            "strategy": "FirstMatch",
+                            "recalculate_access_paths_and_cost": {
+                              "tables": [
+                                {
+                                  "database": "test",
+                                  "table": "table2",
+                                  "best_access_path": {
+                                    "considered_access_paths": [
+                                      {
+                                        "access_type": "ref",
+                                        "index": "col_int_key",
+                                        "rows": 2,
+                                        "cost": 6.5395,
+                                        "chosen": true
+                                      },
+                                      {
+                                        "access_type": "scan",
+                                        "rows": 3,
+                                        "cost": 13.241,
+                                        "chosen": false
+                                      }
+                                    ] /* considered_access_paths */
+                                  } /* best_access_path */
+                                },
+                                {
+                                  "database": "test",
+                                  "table": "table1",
+                                  "best_access_path": {
+                                    "considered_access_paths": [
+                                      {
+                                        "access_type": "ref",
+                                        "index": "col_int_key",
+                                        "rows": 2,
+                                        "cost": 12.679,
+                                        "chosen": true
+                                      },
+                                      {
+                                        "access_type": "scan",
+                                        "rows": 3,
+                                        "cost": 26.482,
+                                        "chosen": false
+                                      }
+                                    ] /* considered_access_paths */
+                                  } /* best_access_path */
+                                }
+                              ] /* tables */
+                            } /* recalculate_access_paths_and_cost */,
+                            "cost": 28.838,
                             "rows": 6,
-                            "duplicate_tables_left": true,
                             "chosen": true
+                          },
+                          {
+                            "strategy": "MaterializeLookup",
+                            "cost": 16.233,
+                            "rows": 6,
+                            "duplicate_tables_left": false,
+                            "chosen": true
+                          },
+                          {
+                            "strategy": "DuplicatesWeedout",
+                            "cost": 36.039,
+                            "rows": 6,
+                            "duplicate_tables_left": false,
+                            "chosen": false
                           }
                         ] /* semijoin_strategy_choice */,
                         "pruned_by_cost": true
@@ -1395,14 +1521,13 @@ ON table2 .col_int_key = table1 .col_int
               },
               {
                 "database": "test",
-                "table": "table2",
+                "table": "table1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
                       "access_type": "ref",
                       "index": "col_int_key",
-                      "rows": 2e308,
-                      "cost": 0,
+                      "usable": false,
                       "chosen": false
                     },
                     {
@@ -1420,50 +1545,50 @@ ON table2 .col_int_key = table1 .col_int
                 "rest_of_plan": [
                   {
                     "database": "test",
-                    "table": "table1",
+                    "table": "where_subselect_20070",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "ref",
-                          "index": "col_int_key",
-                          "rows": 2,
-                          "cost": 4.493,
-                          "chosen": true
-                        },
-                        {
                           "access_type": "scan",
                           "using_join_cache": true,
-                          "rows": 3,
-                          "cost": 2.2071,
+                          "rows": 6,
+                          "cost": 2.0194,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 7.414,
-                    "rows_for_plan": 12,
+                    "cost_for_plan": 9.6262,
+                    "rows_for_plan": 24,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "where_subselect_20070",
+                        "table": "table2",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
+                              "access_type": "ref",
+                              "index": "col_int_key",
+                              "rows": 2,
+                              "cost": 24.958,
+                              "chosen": true
+                            },
+                            {
                               "access_type": "scan",
                               "using_join_cache": true,
-                              "rows": 6,
-                              "cost": 2.0209,
+                              "rows": 3,
+                              "cost": 2.2135,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 23.835,
+                        "cost_for_plan": 26.24,
                         "rows_for_plan": 72,
                         "semijoin_strategy_choice": [
                           {
                             "strategy": "DuplicatesWeedout",
-                            "cost": 41.435,
+                            "cost": 43.84,
                             "rows": 6,
                             "duplicate_tables_left": true,
                             "chosen": true
@@ -1475,50 +1600,50 @@ ON table2 .col_int_key = table1 .col_int
                   },
                   {
                     "database": "test",
-                    "table": "where_subselect_20070",
+                    "table": "table2",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
+                          "access_type": "ref",
+                          "index": "col_int_key",
+                          "rows": 2,
+                          "cost": 4.493,
+                          "chosen": true
+                        },
+                        {
                           "access_type": "scan",
                           "using_join_cache": true,
-                          "rows": 6,
-                          "cost": 2.0194,
+                          "rows": 3,
+                          "cost": 2.2071,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 9.6262,
-                    "rows_for_plan": 24,
+                    "cost_for_plan": 7.414,
+                    "rows_for_plan": 12,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "table1",
+                        "table": "where_subselect_20070",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
-                              "access_type": "ref",
-                              "index": "col_int_key",
-                              "rows": 2,
-                              "cost": 24.958,
-                              "chosen": true
-                            },
-                            {
                               "access_type": "scan",
                               "using_join_cache": true,
-                              "rows": 3,
-                              "cost": 2.2135,
+                              "rows": 6,
+                              "cost": 2.0209,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 26.24,
+                        "cost_for_plan": 23.835,
                         "rows_for_plan": 72,
                         "semijoin_strategy_choice": [
                           {
                             "strategy": "DuplicatesWeedout",
-                            "cost": 43.84,
+                            "cost": 41.435,
                             "rows": 6,
                             "duplicate_tables_left": true,
                             "chosen": true
@@ -1532,258 +1657,133 @@ ON table2 .col_int_key = table1 .col_int
               },
               {
                 "database": "test",
-                "table": "where_subselect_20070",
+                "table": "table2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
+                      "access_type": "ref",
+                      "index": "col_int_key",
+                      "rows": 2e308,
+                      "cost": 0,
+                      "chosen": false
+                    },
+                    {
                       "access_type": "scan",
-                      "rows": 6,
-                      "cost": 2.019,
+                      "rows": 4,
+                      "cost": 2.0068,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
-                "cost_for_plan": 3.219,
-                "rows_for_plan": 6,
+                "cost_for_plan": 2.8068,
+                "rows_for_plan": 4,
                 "semijoin_strategy_choice": [
                 ] /* semijoin_strategy_choice */,
                 "rest_of_plan": [
                   {
                     "database": "test",
-                    "table": "table1",
+                    "table": "where_subselect_20070",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "ref",
-                          "index": "col_int_key",
-                          "rows": 2,
-                          "cost": 6.5395,
-                          "chosen": true
-                        },
-                        {
                           "access_type": "scan",
                           "using_join_cache": true,
-                          "rows": 3,
-                          "cost": 2.208,
+                          "rows": 6,
+                          "cost": 2.0194,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 9.0271,
-                    "rows_for_plan": 18,
+                    "cost_for_plan": 9.6262,
+                    "rows_for_plan": 24,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "table2",
+                        "table": "table1",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
                               "access_type": "ref",
                               "index": "col_int_key",
                               "rows": 2,
-                              "cost": 18.819,
+                              "cost": 24.958,
                               "chosen": true
                             },
                             {
                               "access_type": "scan",
                               "using_join_cache": true,
                               "rows": 3,
-                              "cost": 2.2118,
+                              "cost": 2.2135,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 22.039,
-                        "rows_for_plan": 54,
+                        "cost_for_plan": 26.24,
+                        "rows_for_plan": 72,
                         "semijoin_strategy_choice": [
                           {
-                            "strategy": "FirstMatch",
-                            "recalculate_access_paths_and_cost": {
-                              "tables": [
-                                {
-                                  "database": "test",
-                                  "table": "table1",
-                                  "best_access_path": {
-                                    "considered_access_paths": [
-                                      {
-                                        "access_type": "ref",
-                                        "index": "col_int_key",
-                                        "rows": 2,
-                                        "cost": 6.5395,
-                                        "chosen": true
-                                      },
-                                      {
-                                        "access_type": "scan",
-                                        "rows": 3,
-                                        "cost": 13.241,
-                                        "chosen": false
-                                      }
-                                    ] /* considered_access_paths */
-                                  } /* best_access_path */
-                                },
-                                {
-                                  "database": "test",
-                                  "table": "table2",
-                                  "best_access_path": {
-                                    "considered_access_paths": [
-                                      {
-                                        "access_type": "ref",
-                                        "index": "col_int_key",
-                                        "rows": 2,
-                                        "cost": 12.679,
-                                        "chosen": true
-                                      },
-                                      {
-                                        "access_type": "scan",
-                                        "rows": 3,
-                                        "cost": 26.482,
-                                        "chosen": false
-                                      }
-                                    ] /* considered_access_paths */
-                                  } /* best_access_path */
-                                }
-                              ] /* tables */
-                            } /* recalculate_access_paths_and_cost */,
-                            "cost": 28.838,
-                            "rows": 6,
-                            "chosen": true
-                          },
-                          {
-                            "strategy": "MaterializeLookup",
-                            "cost": 16.233,
-                            "rows": 6,
-                            "duplicate_tables_left": false,
-                            "chosen": true
-                          },
-                          {
                             "strategy": "DuplicatesWeedout",
-                            "cost": 36.039,
+                            "cost": 43.84,
                             "rows": 6,
-                            "duplicate_tables_left": false,
-                            "chosen": false
+                            "duplicate_tables_left": true,
+                            "chosen": true
                           }
                         ] /* semijoin_strategy_choice */,
-                        "chosen": true
+                        "pruned_by_cost": true
                       }
                     ] /* rest_of_plan */
                   },
                   {
                     "database": "test",
-                    "table": "table2",
+                    "table": "table1",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
                           "access_type": "ref",
                           "index": "col_int_key",
                           "rows": 2,
-                          "cost": 6.5395,
+                          "cost": 4.493,
                           "chosen": true
                         },
                         {
                           "access_type": "scan",
                           "using_join_cache": true,
                           "rows": 3,
-                          "cost": 2.208,
+                          "cost": 2.2071,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 9.0271,
-                    "rows_for_plan": 18,
+                    "cost_for_plan": 7.414,
+                    "rows_for_plan": 12,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "table1",
+                        "table": "where_subselect_20070",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
-                              "access_type": "ref",
-                              "index": "col_int_key",
-                              "rows": 2,
-                              "cost": 18.819,
-                              "chosen": true
-                            },
-                            {
                               "access_type": "scan",
                               "using_join_cache": true,
-                              "rows": 3,
-                              "cost": 2.2118,
+                              "rows": 6,
+                              "cost": 2.0209,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 22.039,
-                        "rows_for_plan": 54,
+                        "cost_for_plan": 23.835,
+                        "rows_for_plan": 72,
                         "semijoin_strategy_choice": [
                           {
-                            "strategy": "FirstMatch",
-                            "recalculate_access_paths_and_cost": {
-                              "tables": [
-                                {
-                                  "database": "test",
-                                  "table": "table2",
-                                  "best_access_path": {
-                                    "considered_access_paths": [
-                                      {
-                                        "access_type": "ref",
-                                        "index": "col_int_key",
-                                        "rows": 2,
-                                        "cost": 6.5395,
-                                        "chosen": true
-                                      },
-                                      {
-                                        "access_type": "scan",
-                                        "rows": 3,
-                                        "cost": 13.241,
-                                        "chosen": false
-                                      }
-                                    ] /* considered_access_paths */
-                                  } /* best_access_path */
-                                },
-                                {
-                                  "database": "test",
-                                  "table": "table1",
-                                  "best_access_path": {
-                                    "considered_access_paths": [
-                                      {
-                                        "access_type": "ref",
-                                        "index": "col_int_key",
-                                        "rows": 2,
-                                        "cost": 12.679,
-                                        "chosen": true
-                                      },
-                                      {
-                                        "access_type": "scan",
-                                        "rows": 3,
-                                        "cost": 26.482,
-                                        "chosen": false
-                                      }
-                                    ] /* considered_access_paths */
-                                  } /* best_access_path */
-                                }
-                              ] /* tables */
-                            } /* recalculate_access_paths_and_cost */,
-                            "cost": 28.838,
-                            "rows": 6,
-                            "chosen": true
-                          },
-                          {
-                            "strategy": "MaterializeLookup",
-                            "cost": 16.233,
-                            "rows": 6,
-                            "duplicate_tables_left": false,
-                            "chosen": true
-                          },
-                          {
                             "strategy": "DuplicatesWeedout",
-                            "cost": 36.039,
+                            "cost": 41.435,
                             "rows": 6,
-                            "duplicate_tables_left": false,
-                            "chosen": false
+                            "duplicate_tables_left": true,
+                            "chosen": true
                           }
                         ] /* semijoin_strategy_choice */,
                         "pruned_by_cost": true

=== 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-02-21 10:31:44 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result	2012-02-23 11:22:34 +0000
@@ -1259,77 +1259,137 @@ ON table2 .col_int_key = table1 .col_int
             "considered_execution_plans": [
               {
                 "database": "test",
-                "table": "table1",
+                "table": "where_subselect_20070",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "ref",
-                      "index": "col_int_key",
-                      "usable": false,
-                      "chosen": false
-                    },
-                    {
                       "access_type": "scan",
-                      "rows": 4,
-                      "cost": 2.0068,
+                      "rows": 6,
+                      "cost": 2.019,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
-                "cost_for_plan": 2.8068,
-                "rows_for_plan": 4,
+                "cost_for_plan": 3.219,
+                "rows_for_plan": 6,
                 "semijoin_strategy_choice": [
                 ] /* semijoin_strategy_choice */,
                 "rest_of_plan": [
                   {
                     "database": "test",
-                    "table": "table2",
+                    "table": "table1",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
                           "access_type": "ref",
                           "index": "col_int_key",
                           "rows": 2,
-                          "cost": 4.493,
+                          "cost": 6.5395,
                           "chosen": true
                         },
                         {
                           "access_type": "scan",
                           "using_join_cache": true,
                           "rows": 3,
-                          "cost": 2.2071,
+                          "cost": 2.208,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 7.414,
-                    "rows_for_plan": 12,
+                    "cost_for_plan": 9.0271,
+                    "rows_for_plan": 18,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "where_subselect_20070",
+                        "table": "table2",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
+                              "access_type": "ref",
+                              "index": "col_int_key",
+                              "rows": 2,
+                              "cost": 18.819,
+                              "chosen": true
+                            },
+                            {
                               "access_type": "scan",
                               "using_join_cache": true,
-                              "rows": 6,
-                              "cost": 2.0209,
+                              "rows": 3,
+                              "cost": 2.2118,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 23.835,
-                        "rows_for_plan": 72,
+                        "cost_for_plan": 22.039,
+                        "rows_for_plan": 54,
                         "semijoin_strategy_choice": [
                           {
-                            "strategy": "DuplicatesWeedout",
-                            "cost": 41.435,
+                            "strategy": "FirstMatch",
+                            "recalculate_access_paths_and_cost": {
+                              "tables": [
+                                {
+                                  "database": "test",
+                                  "table": "table1",
+                                  "best_access_path": {
+                                    "considered_access_paths": [
+                                      {
+                                        "access_type": "ref",
+                                        "index": "col_int_key",
+                                        "rows": 2,
+                                        "cost": 6.5395,
+                                        "chosen": true
+                                      },
+                                      {
+                                        "access_type": "scan",
+                                        "rows": 3,
+                                        "cost": 13.241,
+                                        "chosen": false
+                                      }
+                                    ] /* considered_access_paths */
+                                  } /* best_access_path */
+                                },
+                                {
+                                  "database": "test",
+                                  "table": "table2",
+                                  "best_access_path": {
+                                    "considered_access_paths": [
+                                      {
+                                        "access_type": "ref",
+                                        "index": "col_int_key",
+                                        "rows": 2,
+                                        "cost": 12.679,
+                                        "chosen": true
+                                      },
+                                      {
+                                        "access_type": "scan",
+                                        "rows": 3,
+                                        "cost": 26.482,
+                                        "chosen": false
+                                      }
+                                    ] /* considered_access_paths */
+                                  } /* best_access_path */
+                                }
+                              ] /* tables */
+                            } /* recalculate_access_paths_and_cost */,
+                            "cost": 28.838,
                             "rows": 6,
-                            "duplicate_tables_left": true,
                             "chosen": true
+                          },
+                          {
+                            "strategy": "MaterializeLookup",
+                            "cost": 16.233,
+                            "rows": 6,
+                            "duplicate_tables_left": false,
+                            "chosen": true
+                          },
+                          {
+                            "strategy": "DuplicatesWeedout",
+                            "cost": 36.039,
+                            "rows": 6,
+                            "duplicate_tables_left": false,
+                            "chosen": false
                           }
                         ] /* semijoin_strategy_choice */,
                         "chosen": true
@@ -1338,53 +1398,119 @@ ON table2 .col_int_key = table1 .col_int
                   },
                   {
                     "database": "test",
-                    "table": "where_subselect_20070",
+                    "table": "table2",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
+                          "access_type": "ref",
+                          "index": "col_int_key",
+                          "rows": 2,
+                          "cost": 6.5395,
+                          "chosen": true
+                        },
+                        {
                           "access_type": "scan",
                           "using_join_cache": true,
-                          "rows": 6,
-                          "cost": 2.0194,
+                          "rows": 3,
+                          "cost": 2.208,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 9.6262,
-                    "rows_for_plan": 24,
+                    "cost_for_plan": 9.0271,
+                    "rows_for_plan": 18,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "table2",
+                        "table": "table1",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
                               "access_type": "ref",
                               "index": "col_int_key",
                               "rows": 2,
-                              "cost": 24.958,
+                              "cost": 18.819,
                               "chosen": true
                             },
                             {
                               "access_type": "scan",
                               "using_join_cache": true,
                               "rows": 3,
-                              "cost": 2.2135,
+                              "cost": 2.2118,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 26.24,
-                        "rows_for_plan": 72,
+                        "cost_for_plan": 22.039,
+                        "rows_for_plan": 54,
                         "semijoin_strategy_choice": [
                           {
-                            "strategy": "DuplicatesWeedout",
-                            "cost": 43.84,
+                            "strategy": "FirstMatch",
+                            "recalculate_access_paths_and_cost": {
+                              "tables": [
+                                {
+                                  "database": "test",
+                                  "table": "table2",
+                                  "best_access_path": {
+                                    "considered_access_paths": [
+                                      {
+                                        "access_type": "ref",
+                                        "index": "col_int_key",
+                                        "rows": 2,
+                                        "cost": 6.5395,
+                                        "chosen": true
+                                      },
+                                      {
+                                        "access_type": "scan",
+                                        "rows": 3,
+                                        "cost": 13.241,
+                                        "chosen": false
+                                      }
+                                    ] /* considered_access_paths */
+                                  } /* best_access_path */
+                                },
+                                {
+                                  "database": "test",
+                                  "table": "table1",
+                                  "best_access_path": {
+                                    "considered_access_paths": [
+                                      {
+                                        "access_type": "ref",
+                                        "index": "col_int_key",
+                                        "rows": 2,
+                                        "cost": 12.679,
+                                        "chosen": true
+                                      },
+                                      {
+                                        "access_type": "scan",
+                                        "rows": 3,
+                                        "cost": 26.482,
+                                        "chosen": false
+                                      }
+                                    ] /* considered_access_paths */
+                                  } /* best_access_path */
+                                }
+                              ] /* tables */
+                            } /* recalculate_access_paths_and_cost */,
+                            "cost": 28.838,
                             "rows": 6,
-                            "duplicate_tables_left": true,
                             "chosen": true
+                          },
+                          {
+                            "strategy": "MaterializeLookup",
+                            "cost": 16.233,
+                            "rows": 6,
+                            "duplicate_tables_left": false,
+                            "chosen": true
+                          },
+                          {
+                            "strategy": "DuplicatesWeedout",
+                            "cost": 36.039,
+                            "rows": 6,
+                            "duplicate_tables_left": false,
+                            "chosen": false
                           }
                         ] /* semijoin_strategy_choice */,
                         "pruned_by_cost": true
@@ -1395,14 +1521,13 @@ ON table2 .col_int_key = table1 .col_int
               },
               {
                 "database": "test",
-                "table": "table2",
+                "table": "table1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
                       "access_type": "ref",
                       "index": "col_int_key",
-                      "rows": 2e308,
-                      "cost": 0,
+                      "usable": false,
                       "chosen": false
                     },
                     {
@@ -1420,50 +1545,50 @@ ON table2 .col_int_key = table1 .col_int
                 "rest_of_plan": [
                   {
                     "database": "test",
-                    "table": "table1",
+                    "table": "where_subselect_20070",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "ref",
-                          "index": "col_int_key",
-                          "rows": 2,
-                          "cost": 4.493,
-                          "chosen": true
-                        },
-                        {
                           "access_type": "scan",
                           "using_join_cache": true,
-                          "rows": 3,
-                          "cost": 2.2071,
+                          "rows": 6,
+                          "cost": 2.0194,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 7.414,
-                    "rows_for_plan": 12,
+                    "cost_for_plan": 9.6262,
+                    "rows_for_plan": 24,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "where_subselect_20070",
+                        "table": "table2",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
+                              "access_type": "ref",
+                              "index": "col_int_key",
+                              "rows": 2,
+                              "cost": 24.958,
+                              "chosen": true
+                            },
+                            {
                               "access_type": "scan",
                               "using_join_cache": true,
-                              "rows": 6,
-                              "cost": 2.0209,
+                              "rows": 3,
+                              "cost": 2.2135,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 23.835,
+                        "cost_for_plan": 26.24,
                         "rows_for_plan": 72,
                         "semijoin_strategy_choice": [
                           {
                             "strategy": "DuplicatesWeedout",
-                            "cost": 41.435,
+                            "cost": 43.84,
                             "rows": 6,
                             "duplicate_tables_left": true,
                             "chosen": true
@@ -1475,50 +1600,50 @@ ON table2 .col_int_key = table1 .col_int
                   },
                   {
                     "database": "test",
-                    "table": "where_subselect_20070",
+                    "table": "table2",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
+                          "access_type": "ref",
+                          "index": "col_int_key",
+                          "rows": 2,
+                          "cost": 4.493,
+                          "chosen": true
+                        },
+                        {
                           "access_type": "scan",
                           "using_join_cache": true,
-                          "rows": 6,
-                          "cost": 2.0194,
+                          "rows": 3,
+                          "cost": 2.2071,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 9.6262,
-                    "rows_for_plan": 24,
+                    "cost_for_plan": 7.414,
+                    "rows_for_plan": 12,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "table1",
+                        "table": "where_subselect_20070",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
-                              "access_type": "ref",
-                              "index": "col_int_key",
-                              "rows": 2,
-                              "cost": 24.958,
-                              "chosen": true
-                            },
-                            {
                               "access_type": "scan",
                               "using_join_cache": true,
-                              "rows": 3,
-                              "cost": 2.2135,
+                              "rows": 6,
+                              "cost": 2.0209,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 26.24,
+                        "cost_for_plan": 23.835,
                         "rows_for_plan": 72,
                         "semijoin_strategy_choice": [
                           {
                             "strategy": "DuplicatesWeedout",
-                            "cost": 43.84,
+                            "cost": 41.435,
                             "rows": 6,
                             "duplicate_tables_left": true,
                             "chosen": true
@@ -1532,258 +1657,133 @@ ON table2 .col_int_key = table1 .col_int
               },
               {
                 "database": "test",
-                "table": "where_subselect_20070",
+                "table": "table2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
+                      "access_type": "ref",
+                      "index": "col_int_key",
+                      "rows": 2e308,
+                      "cost": 0,
+                      "chosen": false
+                    },
+                    {
                       "access_type": "scan",
-                      "rows": 6,
-                      "cost": 2.019,
+                      "rows": 4,
+                      "cost": 2.0068,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
-                "cost_for_plan": 3.219,
-                "rows_for_plan": 6,
+                "cost_for_plan": 2.8068,
+                "rows_for_plan": 4,
                 "semijoin_strategy_choice": [
                 ] /* semijoin_strategy_choice */,
                 "rest_of_plan": [
                   {
                     "database": "test",
-                    "table": "table1",
+                    "table": "where_subselect_20070",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "ref",
-                          "index": "col_int_key",
-                          "rows": 2,
-                          "cost": 6.5395,
-                          "chosen": true
-                        },
-                        {
                           "access_type": "scan",
                           "using_join_cache": true,
-                          "rows": 3,
-                          "cost": 2.208,
+                          "rows": 6,
+                          "cost": 2.0194,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 9.0271,
-                    "rows_for_plan": 18,
+                    "cost_for_plan": 9.6262,
+                    "rows_for_plan": 24,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "table2",
+                        "table": "table1",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
                               "access_type": "ref",
                               "index": "col_int_key",
                               "rows": 2,
-                              "cost": 18.819,
+                              "cost": 24.958,
                               "chosen": true
                             },
                             {
                               "access_type": "scan",
                               "using_join_cache": true,
                               "rows": 3,
-                              "cost": 2.2118,
+                              "cost": 2.2135,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 22.039,
-                        "rows_for_plan": 54,
+                        "cost_for_plan": 26.24,
+                        "rows_for_plan": 72,
                         "semijoin_strategy_choice": [
                           {
-                            "strategy": "FirstMatch",
-                            "recalculate_access_paths_and_cost": {
-                              "tables": [
-                                {
-                                  "database": "test",
-                                  "table": "table1",
-                                  "best_access_path": {
-                                    "considered_access_paths": [
-                                      {
-                                        "access_type": "ref",
-                                        "index": "col_int_key",
-                                        "rows": 2,
-                                        "cost": 6.5395,
-                                        "chosen": true
-                                      },
-                                      {
-                                        "access_type": "scan",
-                                        "rows": 3,
-                                        "cost": 13.241,
-                                        "chosen": false
-                                      }
-                                    ] /* considered_access_paths */
-                                  } /* best_access_path */
-                                },
-                                {
-                                  "database": "test",
-                                  "table": "table2",
-                                  "best_access_path": {
-                                    "considered_access_paths": [
-                                      {
-                                        "access_type": "ref",
-                                        "index": "col_int_key",
-                                        "rows": 2,
-                                        "cost": 12.679,
-                                        "chosen": true
-                                      },
-                                      {
-                                        "access_type": "scan",
-                                        "rows": 3,
-                                        "cost": 26.482,
-                                        "chosen": false
-                                      }
-                                    ] /* considered_access_paths */
-                                  } /* best_access_path */
-                                }
-                              ] /* tables */
-                            } /* recalculate_access_paths_and_cost */,
-                            "cost": 28.838,
-                            "rows": 6,
-                            "chosen": true
-                          },
-                          {
-                            "strategy": "MaterializeLookup",
-                            "cost": 16.233,
-                            "rows": 6,
-                            "duplicate_tables_left": false,
-                            "chosen": true
-                          },
-                          {
                             "strategy": "DuplicatesWeedout",
-                            "cost": 36.039,
+                            "cost": 43.84,
                             "rows": 6,
-                            "duplicate_tables_left": false,
-                            "chosen": false
+                            "duplicate_tables_left": true,
+                            "chosen": true
                           }
                         ] /* semijoin_strategy_choice */,
-                        "chosen": true
+                        "pruned_by_cost": true
                       }
                     ] /* rest_of_plan */
                   },
                   {
                     "database": "test",
-                    "table": "table2",
+                    "table": "table1",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
                           "access_type": "ref",
                           "index": "col_int_key",
                           "rows": 2,
-                          "cost": 6.5395,
+                          "cost": 4.493,
                           "chosen": true
                         },
                         {
                           "access_type": "scan",
                           "using_join_cache": true,
                           "rows": 3,
-                          "cost": 2.208,
+                          "cost": 2.2071,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 9.0271,
-                    "rows_for_plan": 18,
+                    "cost_for_plan": 7.414,
+                    "rows_for_plan": 12,
                     "semijoin_strategy_choice": [
                     ] /* semijoin_strategy_choice */,
                     "rest_of_plan": [
                       {
                         "database": "test",
-                        "table": "table1",
+                        "table": "where_subselect_20070",
                         "best_access_path": {
                           "considered_access_paths": [
                             {
-                              "access_type": "ref",
-                              "index": "col_int_key",
-                              "rows": 2,
-                              "cost": 18.819,
-                              "chosen": true
-                            },
-                            {
                               "access_type": "scan",
                               "using_join_cache": true,
-                              "rows": 3,
-                              "cost": 2.2118,
+                              "rows": 6,
+                              "cost": 2.0209,
                               "chosen": true
                             }
                           ] /* considered_access_paths */
                         } /* best_access_path */,
-                        "cost_for_plan": 22.039,
-                        "rows_for_plan": 54,
+                        "cost_for_plan": 23.835,
+                        "rows_for_plan": 72,
                         "semijoin_strategy_choice": [
                           {
-                            "strategy": "FirstMatch",
-                            "recalculate_access_paths_and_cost": {
-                              "tables": [
-                                {
-                                  "database": "test",
-                                  "table": "table2",
-                                  "best_access_path": {
-                                    "considered_access_paths": [
-                                      {
-                                        "access_type": "ref",
-                                        "index": "col_int_key",
-                                        "rows": 2,
-                                        "cost": 6.5395,
-                                        "chosen": true
-                                      },
-                                      {
-                                        "access_type": "scan",
-                                        "rows": 3,
-                                        "cost": 13.241,
-                                        "chosen": false
-                                      }
-                                    ] /* considered_access_paths */
-                                  } /* best_access_path */
-                                },
-                                {
-                                  "database": "test",
-                                  "table": "table1",
-                                  "best_access_path": {
-                                    "considered_access_paths": [
-                                      {
-                                        "access_type": "ref",
-                                        "index": "col_int_key",
-                                        "rows": 2,
-                                        "cost": 12.679,
-                                        "chosen": true
-                                      },
-                                      {
-                                        "access_type": "scan",
-                                        "rows": 3,
-                                        "cost": 26.482,
-                                        "chosen": false
-                                      }
-                                    ] /* considered_access_paths */
-                                  } /* best_access_path */
-                                }
-                              ] /* tables */
-                            } /* recalculate_access_paths_and_cost */,
-                            "cost": 28.838,
-                            "rows": 6,
-                            "chosen": true
-                          },
-                          {
-                            "strategy": "MaterializeLookup",
-                            "cost": 16.233,
-                            "rows": 6,
-                            "duplicate_tables_left": false,
-                            "chosen": true
-                          },
-                          {
                             "strategy": "DuplicatesWeedout",
-                            "cost": 36.039,
+                            "cost": 41.435,
                             "rows": 6,
-                            "duplicate_tables_left": false,
-                            "chosen": false
+                            "duplicate_tables_left": true,
+                            "chosen": true
                           }
                         ] /* semijoin_strategy_choice */,
                         "pruned_by_cost": true

=== modified file 'mysql-test/suite/opt_trace/r/subquery_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_no_prot.result	2012-02-16 13:09:08 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_no_prot.result	2012-02-23 11:22:34 +0000
@@ -1563,16 +1563,10 @@ field4,field5,field6	{
                                   "considered_execution_plans": [
                                     {
                                       "database": "test",
-                                      "table": "sq1_alias2",
+                                      "table": "sq1_alias3",
                                       "best_access_path": {
                                         "considered_access_paths": [
                                           {
-                                            "access_type": "ref",
-                                            "index": "col_varchar_key",
-                                            "usable": false,
-                                            "chosen": false
-                                          },
-                                          {
                                             "access_type": "scan",
                                             "rows": 3,
                                             "cost": 2.0342,
@@ -1585,14 +1579,21 @@ field4,field5,field6	{
                                       "rest_of_plan": [
                                         {
                                           "database": "test",
-                                          "table": "sq1_alias3",
+                                          "table": "sq1_alias2",
                                           "best_access_path": {
                                             "considered_access_paths": [
                                               {
+                                                "access_type": "ref",
+                                                "index": "col_varchar_key",
+                                                "rows": 1,
+                                                "cost": 4.1,
+                                                "chosen": true
+                                              },
+                                              {
                                                 "access_type": "scan",
                                                 "using_join_cache": true,
                                                 "rows": 3,
-                                                "cost": 2.0345,
+                                                "cost": 2.0344,
                                                 "chosen": true
                                               }
                                             ] /* considered_access_paths */
@@ -1605,10 +1606,16 @@ field4,field5,field6	{
                                     },
                                     {
                                       "database": "test",
-                                      "table": "sq1_alias3",
+                                      "table": "sq1_alias2",
                                       "best_access_path": {
                                         "considered_access_paths": [
                                           {
+                                            "access_type": "ref",
+                                            "index": "col_varchar_key",
+                                            "usable": false,
+                                            "chosen": false
+                                          },
+                                          {
                                             "access_type": "scan",
                                             "rows": 3,
                                             "cost": 2.0342,
@@ -1618,33 +1625,7 @@ field4,field5,field6	{
                                       } /* best_access_path */,
                                       "cost_for_plan": 2.6342,
                                       "rows_for_plan": 3,
-                                      "rest_of_plan": [
-                                        {
-                                          "database": "test",
-                                          "table": "sq1_alias2",
-                                          "best_access_path": {
-                                            "considered_access_paths": [
-                                              {
-                                                "access_type": "ref",
-                                                "index": "col_varchar_key",
-                                                "rows": 1,
-                                                "cost": 4.1,
-                                                "chosen": true
-                                              },
-                                              {
-                                                "access_type": "scan",
-                                                "using_join_cache": true,
-                                                "rows": 3,
-                                                "cost": 2.0344,
-                                                "chosen": true
-                                              }
-                                            ] /* considered_access_paths */
-                                          } /* best_access_path */,
-                                          "cost_for_plan": 6.4686,
-                                          "rows_for_plan": 9,
-                                          "pruned_by_cost": true
-                                        }
-                                      ] /* rest_of_plan */
+                                      "pruned_by_heuristic": true
                                     }
                                   ] /* considered_execution_plans */
                                 }

=== modified file 'mysql-test/suite/opt_trace/r/subquery_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_ps_prot.result	2012-02-16 13:09:08 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_ps_prot.result	2012-02-23 11:22:34 +0000
@@ -1541,16 +1541,10 @@ field4,field5,field6	{
                                   "considered_execution_plans": [
                                     {
                                       "database": "test",
-                                      "table": "sq1_alias2",
+                                      "table": "sq1_alias3",
                                       "best_access_path": {
                                         "considered_access_paths": [
                                           {
-                                            "access_type": "ref",
-                                            "index": "col_varchar_key",
-                                            "usable": false,
-                                            "chosen": false
-                                          },
-                                          {
                                             "access_type": "scan",
                                             "rows": 3,
                                             "cost": 2.0342,
@@ -1563,14 +1557,21 @@ field4,field5,field6	{
                                       "rest_of_plan": [
                                         {
                                           "database": "test",
-                                          "table": "sq1_alias3",
+                                          "table": "sq1_alias2",
                                           "best_access_path": {
                                             "considered_access_paths": [
                                               {
+                                                "access_type": "ref",
+                                                "index": "col_varchar_key",
+                                                "rows": 1,
+                                                "cost": 4.1,
+                                                "chosen": true
+                                              },
+                                              {
                                                 "access_type": "scan",
                                                 "using_join_cache": true,
                                                 "rows": 3,
-                                                "cost": 2.0345,
+                                                "cost": 2.0344,
                                                 "chosen": true
                                               }
                                             ] /* considered_access_paths */
@@ -1583,10 +1584,16 @@ field4,field5,field6	{
                                     },
                                     {
                                       "database": "test",
-                                      "table": "sq1_alias3",
+                                      "table": "sq1_alias2",
                                       "best_access_path": {
                                         "considered_access_paths": [
                                           {
+                                            "access_type": "ref",
+                                            "index": "col_varchar_key",
+                                            "usable": false,
+                                            "chosen": false
+                                          },
+                                          {
                                             "access_type": "scan",
                                             "rows": 3,
                                             "cost": 2.0342,
@@ -1596,33 +1603,7 @@ field4,field5,field6	{
                                       } /* best_access_path */,
                                       "cost_for_plan": 2.6342,
                                       "rows_for_plan": 3,
-                                      "rest_of_plan": [
-                                        {
-                                          "database": "test",
-                                          "table": "sq1_alias2",
-                                          "best_access_path": {
-                                            "considered_access_paths": [
-                                              {
-                                                "access_type": "ref",
-                                                "index": "col_varchar_key",
-                                                "rows": 1,
-                                                "cost": 4.1,
-                                                "chosen": true
-                                              },
-                                              {
-                                                "access_type": "scan",
-                                                "using_join_cache": true,
-                                                "rows": 3,
-                                                "cost": 2.0344,
-                                                "chosen": true
-                                              }
-                                            ] /* considered_access_paths */
-                                          } /* best_access_path */,
-                                          "cost_for_plan": 6.4686,
-                                          "rows_for_plan": 9,
-                                          "pruned_by_cost": true
-                                        }
-                                      ] /* rest_of_plan */
+                                      "pruned_by_heuristic": true
                                     }
                                   ] /* considered_execution_plans */
                                 }

=== modified file 'mysql-test/suite/parts/t/partition_alter4_myisam.test'
--- a/mysql-test/suite/parts/t/partition_alter4_myisam.test	2010-06-17 17:17:17 +0000
+++ b/mysql-test/suite/parts/t/partition_alter4_myisam.test	2012-02-23 11:51:35 +0000
@@ -45,6 +45,8 @@ let $more_pk_ui_tests= 0;
 
 # Does not work with --embedded
 --source include/not_embedded.inc
+# This test takes long time, so only run it with the --big mtr-flag.
+--source include/big_test.inc
 
 #------------------------------------------------------------------------------#
 # Engine specific settings and requirements

=== modified file 'mysql-test/suite/rpl/t/rpl_row_event_max_size.test'
--- a/mysql-test/suite/rpl/t/rpl_row_event_max_size.test	2012-01-30 15:01:17 +0000
+++ b/mysql-test/suite/rpl/t/rpl_row_event_max_size.test	2012-02-22 21:27:15 +0000
@@ -59,6 +59,9 @@ connection master;
 CREATE TABLE t1 (a int not null auto_increment, data1 LONGBLOB,
                  data2 LONGBLOB, data3 LONGBLOB, PRIMARY KEY(a));
 
+--sync_slave_with_master
+--connection master
+
 INSERT INTO t1 (data1, data2, data3) VALUES (repeat('a',1000000), repeat('a', 1000000), repeat('a', 1000000));
 
 SELECT LENGTH(data1), LENGTH(data2), LENGTH(data3) FROM t1 WHERE a = 1;

=== modified file 'mysql-test/t/disabled.def'
--- a/mysql-test/t/disabled.def	2012-02-22 16:21:18 +0000
+++ b/mysql-test/t/disabled.def	2012-02-23 05:57:45 +0000
@@ -14,3 +14,4 @@ read_many_rows_innodb    : Bug#11748886 
 sum_distinct-big         : Bug#11764126 2010-11-15 mattiasj was not tested
 archive-big              : Bug#11817185 2011-03-10 Anitha Disabled since this leads to timeout on Solaris Sparc
 log_tables-big           : Bug#11756699 2010-11-15 mattiasj report already exists
+events_restart @windows  : Bug#11748899 2012-02-23 hemant The test started failing on windows after the fix for bug#11748899 and followup patch didnot work.

=== modified file 'mysql-test/t/func_str.test'
--- a/mysql-test/t/func_str.test	2012-01-25 15:49:57 +0000
+++ b/mysql-test/t/func_str.test	2012-02-23 10:08:33 +0000
@@ -1688,5 +1688,28 @@ SET NAMES latin2;
 EXECUTE stmt;
 
 --echo #
+--echo # Bug#11829861: SUBSTRING_INDEX() RESULTS IN MISSING CHARACTERS WHEN USED
+--echo # INSIDE LOWER()
+--echo #
+
+SET @user_at_host = 'root@stripped';
+SELECT LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1));
+
+--echo # End of test  BUG#11829861
+
+--echo #
+--echo # Bug#42404: SUBSTRING_INDEX() RESULTS ARE INCONSISTENT
+--echo #
+
+CREATE TABLE t (i INT NOT NULL, c CHAR(255) NOT NULL);
+INSERT INTO t VALUES (0,'.www.mysql.com'),(1,'.wwwmysqlcom');
+SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t WHERE i = 1;
+SELECT i, SUBSTRING_INDEX(c, '.', -2) FROM t;
+
+DROP TABLE t;
+
+--echo # End of test  BUG#42404
+
+--echo #
 --echo # End of 5.6 tests
 --echo #

=== added file 'mysql-test/t/greedy_search.test'
--- a/mysql-test/t/greedy_search.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/greedy_search.test	2012-02-23 11:22:34 +0000
@@ -0,0 +1,423 @@
+--echo #
+--echo # TEST 1
+--echo # Greedy search iteration test for 16-way join: star schema
+--echo #
+--echo # Creation of 16 tables hidden
+--echo #
+
+--disable_result_log
+--disable_query_log
+
+let $brands=7;
+let $models_pr_brand=5;
+let $misc_properties_big=20;
+let $misc_properties_small=10;
+let $vehicles=100;
+
+eval 
+CREATE TABLE brands (
+  id_pk int PRIMARY KEY, 
+  id_nokey int, 
+  name varchar(100)
+);
+
+let $i=0;
+while ($i<$brands)
+{
+  inc $i;
+  eval INSERT INTO brands VALUES ($i, $i, concat('brand',$i));
+}
+
+eval 
+CREATE TABLE models (
+  id_pk int PRIMARY KEY, 
+  id_nokey int, 
+  brand_id int, 
+  name varchar(100), 
+  INDEX(`brand_id`)
+);
+
+let $i=0;
+let $cnt=0;
+while ($i<$brands)
+{
+  inc $i;
+  let $j=0;
+  while ($j<$models_pr_brand)
+  { 
+    inc $cnt;
+    inc $j;
+    eval INSERT INTO models VALUES ($cnt, $cnt, $i, concat('brandmodel',$cnt));  
+  }
+}
+
+eval 
+CREATE TABLE subtypes (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+  inc $i;
+  eval INSERT INTO subtypes VALUES ($i, $i, concat('subtype',$i));
+}
+
+eval 
+CREATE TABLE colors (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_big)
+{
+  inc $i;
+  eval INSERT INTO colors VALUES ($i, $i, concat('color',$i));
+}
+
+eval 
+CREATE TABLE heating (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+  inc $i;
+  eval INSERT INTO heating VALUES ($i, $i, concat('heating',$i));
+}
+
+eval 
+CREATE TABLE windows (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+  inc $i;
+  eval INSERT INTO windows VALUES ($i, $i, concat('window',$i));
+}
+
+eval 
+CREATE TABLE fuels (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+  inc $i;
+  eval INSERT INTO fuels VALUES ($i, $i, concat('fuel',$i));
+}
+
+eval 
+CREATE TABLE transmissions (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+  inc $i;
+  eval INSERT INTO transmissions VALUES ($i, $i, concat('transmission',$i));
+}
+
+eval 
+CREATE TABLE steerings (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_small)
+{
+  inc $i;
+  eval INSERT INTO steerings VALUES ($i, $i, concat('steering',$i));
+}
+
+eval 
+CREATE TABLE interiors (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+
+let $i=0;
+while ($i<$misc_properties_big)
+{
+  inc $i;
+  eval INSERT INTO interiors VALUES ($i, $i, concat('interior',$i));
+}
+
+eval 
+CREATE TABLE drives (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+let $i=0;
+while ($i<$misc_properties_small)
+{
+  inc $i;
+  eval INSERT INTO drives VALUES ($i, $i, concat('drive',$i));
+}
+
+eval 
+CREATE TABLE wheels (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+let $i=0;
+while ($i<$misc_properties_small)
+{
+  inc $i;
+  eval INSERT INTO wheels VALUES ($i, $i, concat('wheel',$i));
+}
+
+eval 
+CREATE TABLE engine (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+let $i=0;
+while ($i<$misc_properties_small)
+{
+  inc $i;
+  eval INSERT INTO engine VALUES ($i, $i, concat('engine',$i));
+}
+
+eval 
+CREATE TABLE price_ranges (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+let $i=0;
+while ($i<$misc_properties_small)
+{
+  inc $i;
+  eval INSERT INTO price_ranges VALUES ($i, $i, concat('price',$i));
+}
+
+eval 
+CREATE TABLE countries (
+  id_pk int primary key, 
+  id_nokey int, 
+  name varchar(100)
+);
+let $i=0;
+while ($i<$misc_properties_big)
+{
+  inc $i;
+  eval INSERT INTO countries VALUES ($i, $i, concat('country',$i));
+}
+
+eval 
+CREATE TABLE vehicles (
+  id int primary key, 
+  model_id int, 
+  subtype_id int, 
+  color_id int, 
+  heating_id int, 
+  window_id int, 
+  fuel_id int, 
+  transmission_id int, 
+  steering_id int, 
+  interior_id int, 
+  drive_id int, 
+  price_range_id int, 
+  assembled_in int, 
+  engine_id int, 
+  wheels_id int
+);
+
+
+let $brands=7;
+let $models_pr_brand=3;
+let $misc_properties_big=20;
+let $misc_properties_small=$misc_properties_big/2;
+
+while ($i<$vehicles)
+{
+  inc $i;
+  eval INSERT INTO vehicles VALUES ($i, $i%2, $i%3, $i%4, $i%5, $i%6, $i%7, 
+                                    $i, $i%2, $i%3, $i%4, $i%5, $i%6, $i%7, 
+                                    $i );
+}
+--enable_result_log
+--enable_query_log
+
+SET SESSION optimizer_search_depth = 25; 
+# print_greedy_search_count will do it's own FLUSH STATUS after
+# executing each query, but we need to reset counters before 
+# the first query is executed as well.
+FLUSH STATUS;
+
+--echo #
+--echo # 16-way join - all 15 fact tables joined on column with key
+--echo #
+
+let $query=
+SELECT *
+FROM vehicles
+  JOIN models        ON vehicles.model_id        = models.id_pk
+  JOIN subtypes      ON vehicles.subtype_id      = subtypes.id_pk
+  JOIN colors        ON vehicles.color_id        = colors.id_pk
+  JOIN heating       ON vehicles.heating_id      = heating.id_pk
+  JOIN windows       ON vehicles.window_id       = windows.id_pk
+  JOIN fuels         ON vehicles.fuel_id         = fuels.id_pk
+  JOIN transmissions ON vehicles.transmission_id = transmissions.id_pk
+  JOIN steerings     ON vehicles.steering_id     = steerings.id_pk
+  JOIN interiors     ON vehicles.interior_id     = interiors.id_pk
+  JOIN drives        ON vehicles.drive_id        = drives.id_pk
+  JOIN wheels        ON vehicles.wheels_id       = wheels.id_pk
+  JOIN engine        ON vehicles.engine_id       = engine.id_pk
+  JOIN price_ranges  ON vehicles.price_range_id  = price_ranges.id_pk
+  JOIN countries     ON vehicles.assembled_in    = countries.id_pk
+  JOIN brands        ON models.brand_id          = brands.id_pk;
+--source include/print_greedy_search_count.inc
+
+--echo #
+--echo # 16-way join - 10 fact tables joined on column with key and
+--echo #                5 fact tables joined on column without key
+--echo #
+
+let $query=
+SELECT *
+FROM vehicles
+  JOIN models        ON vehicles.model_id        = models.id_nokey
+  JOIN subtypes      ON vehicles.subtype_id      = subtypes.id_pk
+  JOIN colors        ON vehicles.color_id        = colors.id_pk
+  JOIN heating       ON vehicles.heating_id      = heating.id_nokey
+  JOIN windows       ON vehicles.window_id       = windows.id_pk
+  JOIN fuels         ON vehicles.fuel_id         = fuels.id_pk
+  JOIN transmissions ON vehicles.transmission_id = transmissions.id_nokey
+  JOIN steerings     ON vehicles.steering_id     = steerings.id_pk
+  JOIN interiors     ON vehicles.interior_id     = interiors.id_pk
+  JOIN drives        ON vehicles.drive_id        = drives.id_pk
+  JOIN wheels        ON vehicles.wheels_id       = wheels.id_nokey
+  JOIN engine        ON vehicles.engine_id       = engine.id_pk
+  JOIN price_ranges  ON vehicles.price_range_id  = price_ranges.id_pk
+  JOIN countries     ON vehicles.assembled_in    = countries.id_pk
+  JOIN brands        ON models.brand_id          = brands.id_nokey;
+--source include/print_greedy_search_count.inc
+
+select @@optimizer_search_depth;
+select @@optimizer_prune_level;
+
+DROP TABLE vehicles, models, subtypes, colors, heating, windows, 
+           fuels, transmissions, steerings, interiors, drives, 
+           price_ranges, countries, brands, wheels, engine;
+
+
+--echo #
+--echo # TEST 2
+--echo # Greedy search iteration test for chain of tables
+--echo #
+
+--source include/greedy_search_load_tables.inc
+
+# Explanation to the test
+#
+# A chain of tables is joined like this:
+#    t1 JOIN t2 ON t1.<some_col>=t2.<some_col> JOIN t3 ON ...
+#
+# Different variants of table sizes and columns in the join conditions
+# are tested. 
+#
+# The column names mean:
+#   'pk'     - The column is primary key
+#   'colidx' - The column is indexed
+#   'col'    - The column is not indexed
+#
+# The table names mean:
+#   tx_y     - table with x rows, y is simply used to get unique table names
+#
+# A comment explains each test. The notation used is
+#    (...,tx_col_next):(ty_col_prev,...)
+# which means that table x is joined with table y with join condition
+# "ON tx.col_next = ty.col_prev" like this:
+#    t1 JOIN t2 ON t1.col_next=t2.col_prev ...
+
+--echo #
+--echo # Chain test a:      colidx):(pk,colidx):(pk,colidx)
+--echo #
+
+let $query= SELECT * FROM t10_1;
+let $i= 1;
+while ($i < 8)
+{
+  let $query= $query JOIN t100_$i ON t10_$i.colidx = t100_$i.pk;
+  let $j=$i;
+  inc $j;
+  let $query= $query JOIN t10_$j ON t100_$i.colidx = t10_$j.pk;
+  inc $i;
+}
+--source include/print_greedy_search_count.inc
+
+--echo #
+--echo # Chain test b: (...,col):(colidx, col):(pk,col):(colidx,col):(pk,...)
+--echo #
+let $query= SELECT * FROM t10_1;
+let $i= 1;
+while ($i < 8)
+{
+  let $query= $query JOIN t100_$i ON t10_$i.col = t100_$i.colidx;
+  let $j=$i;
+  inc $j;
+  let $query= $query JOIN t10_$j ON t100_$i.col = t10_$j.pk;
+  inc $i;
+}
+--source include/print_greedy_search_count.inc
+
+--echo #
+--echo # Chain test c: (...,colidx):(col, pk):(col,colidx):(col,...)
+--echo #
+let $query= SELECT * FROM t10_1;
+let $i= 1;
+while ($i < 8)
+{
+  let $query= $query JOIN t100_$i ON t10_$i.colidx = t100_$i.col;
+  let $j=$i;
+  inc $j;
+  let $query= $query JOIN t10_$j ON t100_$i.pk = t10_$j.col;
+  inc $i;
+}
+--source include/print_greedy_search_count.inc
+
+--echo #
+--echo # Chain test d: (...,colidx):(pk, col):(pk,colidx):(pk,col):(pk,...)
+--echo #
+let $query= SELECT * FROM t10_1;
+let $i= 1;
+while ($i < 8)
+{
+  let $query= $query JOIN t100_$i ON t10_$i.colidx = t100_$i.pk;
+  let $j=$i;
+  inc $j;
+  let $query= $query JOIN t10_$j ON t100_$i.col = t10_$j.pk;
+  inc $i;
+}
+--source include/print_greedy_search_count.inc
+
+--echo #
+--echo # Cleanup after TEST 2
+--echo #
+
+--source include/greedy_search_drop_tables.inc

=== modified file 'mysql-test/t/mysql_client_test_embedded.test'
--- a/mysql-test/t/mysql_client_test_embedded.test	2011-12-05 10:11:58 +0000
+++ b/mysql-test/t/mysql_client_test_embedded.test	2012-02-23 10:39:15 +0000
@@ -2,7 +2,7 @@
 
 --echo # Run the start/stop test 17 times (* 64 = 1088 restarts)
 --echo # --silent to avoid printing out server version etc.
---exec $MYSQL_CLIENT_TEST --silent --count=17 test_embedded_start_stop 2>&1
+--exec $MYSQL_CLIENT_TEST --silent --count=17 test_embedded_start_stop > $MYSQLTEST_VARDIR/log/test_embedded_start_stop.log 2>&1
 # TODO: Test all other tests once.
 #       (Currently not done, since many fail with embedded server).
-#--exec $MYSQL_CLIENT_TEST 2>&1
+#--exec $MYSQL_CLIENT_TEST > $MYSQLTEST_VARDIR/log/mysql_client_test_embedded.log 2>&1

=== modified file 'mysql-test/t/type_timestamp.test'
--- a/mysql-test/t/type_timestamp.test	2012-01-31 15:16:16 +0000
+++ b/mysql-test/t/type_timestamp.test	2012-02-23 16:32:32 +0000
@@ -426,3 +426,26 @@ DROP TABLE t1;
 
 --echo End of 5.5 tests
 
+
+--echo #
+--echo # Start of 5.6 tests
+--echo #
+
+--echo #
+--echo # Bug#13596893 - "ERROR 1690 (22003): BIGINT UNSIGNED VALUE IS OUT OF RANGE" ON DATE OPERATION
+--echo #
+CREATE TABLE t1 (
+  `c1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `c2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
+);
+INSERT INTO t1 VALUES ('2003-05-16 23:53:29','2000-01-27 23:13:41');
+SELECT c2-c1 FROM t1;
+SELECT * FROM t1;
+SELECT TIMESTAMP'2000-01-27 23:13:41' - TIMESTAMP'2003-05-16 23:53:29'; 
+SELECT TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29'); 
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 5.6 tests
+--echo #

=== modified file 'sql/field.cc'
--- a/sql/field.cc	2012-02-13 06:26:52 +0000
+++ b/sql/field.cc	2012-02-23 16:32:32 +0000
@@ -5144,8 +5144,6 @@ Field_temporal_with_date_and_time::conve
 
 void Field_temporal_with_date_and_time::init_timestamp_flags()
 {
-  /* For 4.0 MYD and 4.0 InnoDB compatibility */
-  flags|= ZEROFILL_FLAG | UNSIGNED_FLAG | BINARY_FLAG;
   if (unireg_check != NONE)
   {
     /*
@@ -5243,6 +5241,8 @@ Field_timestamp::Field_timestamp(uchar *
                                      unireg_check_arg, field_name_arg, 0)
 {
   init_timestamp_flags();
+   /* For 4.0 MYD and 4.0 InnoDB compatibility */
+  flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
 }
 
 
@@ -5253,6 +5253,8 @@ Field_timestamp::Field_timestamp(bool ma
                                      NONE, field_name_arg, 0)
 {
   init_timestamp_flags();
+  /* For 4.0 MYD and 4.0 InnoDB compatibility */
+  flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
 }
 
 
@@ -5429,8 +5431,6 @@ Field_timestampf::Field_timestampf(bool 
                                       maybe_null_arg ? (uchar*) "": 0, 0,
                                       NONE, field_name_arg, dec_arg)
 {
-  /* For 4.0 MYD and 4.0 InnoDB compatibility */
-  flags|= ZEROFILL_FLAG | UNSIGNED_FLAG | BINARY_FLAG;
   if (unireg_check != TIMESTAMP_DN_FIELD)
     flags|= ON_UPDATE_NOW_FLAG;
 }
@@ -9742,6 +9742,9 @@ bool Create_field::init(THD *thd, char *
     }
     break;
   case MYSQL_TYPE_TIMESTAMP:
+    /* Add flags for TIMESTAMP for 4.0 MYD and 4.0 InnoDB compatibility */
+    flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
+    /* Fall through */
   case MYSQL_TYPE_TIMESTAMP2:
     if (fld_length == NULL)
     {
@@ -9759,7 +9762,7 @@ bool Create_field::init(THD *thd, char *
         length= ((length+1)/2)*2;
       length= min<ulong>(length, MAX_DATETIME_COMPRESSED_WIDTH);
     }
-    flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
+    
     /*
       Since we silently rewrite down to MAX_DATETIME_COMPRESSED_WIDTH bytes,
       the parser should not raise errors unless bizzarely large. 

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2012-02-16 11:31:37 +0000
+++ b/sql/item.cc	2012-02-23 16:32:32 +0000
@@ -617,6 +617,18 @@ uint Item::decimal_precision() const
                                      unsigned_flag);
     return min<uint>(prec, DECIMAL_MAX_PRECISION);
   }
+  switch (field_type())
+  {
+    case MYSQL_TYPE_TIME:
+      return decimals + TIME_INT_DIGITS;
+    case MYSQL_TYPE_DATETIME:
+    case MYSQL_TYPE_TIMESTAMP:
+      return decimals + DATETIME_INT_DIGITS;
+    case MYSQL_TYPE_DATE:
+      return decimals + DATE_INT_DIGITS;
+    default:
+      break;
+  }
   return min<uint>(max_char_length(), DECIMAL_MAX_PRECISION);
 }
 

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2012-02-17 13:55:18 +0000
+++ b/sql/item_func.cc	2012-02-23 16:32:32 +0000
@@ -596,6 +596,7 @@ void Item_func_numhybrid::fix_num_length
 */
 void Item_func::count_datetime_length(Item **item, uint nitems)
 {
+  unsigned_flag= 0;
   decimals= 0;
   if (field_type() != MYSQL_TYPE_DATE)
   {
@@ -611,16 +612,13 @@ void Item_func::count_datetime_length(It
     case MYSQL_TYPE_DATETIME:
     case MYSQL_TYPE_TIMESTAMP:
       len+= MAX_DATETIME_WIDTH;
-      unsigned_flag= 1;
       break;
     case MYSQL_TYPE_DATE:
     case MYSQL_TYPE_NEWDATE:
       len+= MAX_DATE_WIDTH;
-      unsigned_flag= 1;
       break;
     case MYSQL_TYPE_TIME:
       len+= MAX_TIME_WIDTH;
-      unsigned_flag= 0;
       break;
     default:
       DBUG_ASSERT(0);

=== modified file 'sql/item_strfunc.cc'
--- a/sql/item_strfunc.cc	2012-02-22 08:57:27 +0000
+++ b/sql/item_strfunc.cc	2012-02-23 10:08:33 +0000
@@ -1566,10 +1566,12 @@ void Item_func_substr_index::fix_length_
 String *Item_func_substr_index::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
+  char buff[MAX_FIELD_WIDTH];
+  String tmp(buff,sizeof(buff),system_charset_info);
   String *res= args[0]->val_str(str);
-  String *delimiter= args[1]->val_str(&tmp_value);
+  String *delimiter= args[1]->val_str(&tmp);
   int32 count= (int32) args[2]->val_int();
-  uint offset;
+  int offset;
 
   if (args[0]->null_value || args[1]->null_value || args[2]->null_value)
   {					// string and/or delim are null
@@ -1640,7 +1642,7 @@ String *Item_func_substr_index::val_str(
     {					// start counting from the beginning
       for (offset=0; ; offset+= delimiter_length)
       {
-	if ((int) (offset= res->strstr(*delimiter, offset)) < 0)
+	if ((offset= res->strstr(*delimiter, offset)) < 0)
 	  return res;			// Didn't find, return org string
 	if (!--count)
 	{
@@ -1654,14 +1656,14 @@ String *Item_func_substr_index::val_str(
       /*
         Negative index, start counting at the end
       */
-      for (offset=res->length(); offset ;)
+      for (offset=res->length(); offset; )
       {
         /* 
           this call will result in finding the position pointing to one 
           address space less than where the found substring is located
           in res
         */
-	if ((int) (offset= res->strrstr(*delimiter, offset)) < 0)
+	if ((offset= res->strrstr(*delimiter, offset)) < 0)
 	  return res;			// Didn't find, return org string
         /*
           At this point, we've searched for the substring
@@ -1674,14 +1676,10 @@ String *Item_func_substr_index::val_str(
 	  break;
 	}
       }
+      if (count)
+        return res;			// Didn't find, return org string
     }
   }
-  /*
-    We always mark tmp_value as const so that if val_str() is called again
-    on this object, we don't disrupt the contents of tmp_value when it was
-    derived from another String.
-  */
-  tmp_value.mark_as_const();
   return (&tmp_value);
 }
 

=== modified file 'sql/item_timefunc.cc'
--- a/sql/item_timefunc.cc	2012-02-16 09:51:14 +0000
+++ b/sql/item_timefunc.cc	2012-02-23 16:32:32 +0000
@@ -2235,11 +2235,9 @@ void Item_date_add_interval::fix_length_
     uint8 dec= MY_MAX(args[0]->datetime_precision(), interval_dec);
     fix_length_and_dec_and_charset_datetime(MAX_DATETIME_WIDTH, dec);
     cached_field_type= MYSQL_TYPE_DATETIME;
-    unsigned_flag= 1;
   }
   else if (arg0_field_type == MYSQL_TYPE_DATE)
   {
-    unsigned_flag= 1;
     if (int_type <= INTERVAL_DAY || int_type == INTERVAL_YEAR_MONTH)
     {
       cached_field_type= MYSQL_TYPE_DATE;
@@ -2808,7 +2806,6 @@ void Item_func_add_time::fix_length_and_
   else if (args[0]->is_temporal_with_date_and_time() || is_date)
   {
     cached_field_type= MYSQL_TYPE_DATETIME;
-    unsigned_flag= 1;
     uint8 dec= MY_MAX(args[0]->datetime_precision(), args[1]->time_precision());
     fix_length_and_dec_and_charset_datetime(MAX_DATETIME_WIDTH, dec);
   }
@@ -3327,7 +3324,6 @@ void Item_func_str_to_date::fix_from_for
         */
         cached_timestamp_type= MYSQL_TIMESTAMP_DATETIME;
         cached_field_type= MYSQL_TYPE_DATETIME; 
-        unsigned_flag= 1;
         fix_length_and_dec_and_charset_datetime(MAX_DATETIME_WIDTH,
                                                 DATETIME_MAX_DECIMALS);
         return;
@@ -3345,7 +3341,6 @@ void Item_func_str_to_date::fix_from_for
   }
   else if (time_part_used)
   {
-    unsigned_flag= 1;
     if (date_part_used) /* DATETIME, no microseconds */
     {
       cached_timestamp_type= MYSQL_TIMESTAMP_DATETIME;

=== modified file 'sql/item_timefunc.h'
--- a/sql/item_timefunc.h	2012-01-31 15:16:16 +0000
+++ b/sql/item_timefunc.h	2012-02-23 16:32:32 +0000
@@ -588,11 +588,11 @@ class Item_date_func :public Item_tempor
 {
 public:
   Item_date_func() :Item_temporal_func()
-  { unsigned_flag= 1; }
+  { }
   Item_date_func(Item *a) :Item_temporal_func(a)
-  { unsigned_flag= 1; }
+  { }
   Item_date_func(Item *a, Item *b) :Item_temporal_func(a, b)
-  { unsigned_flag= 1; }
+  { }
   enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
   bool get_time(MYSQL_TIME *ltime)
   {
@@ -636,13 +636,13 @@ class Item_datetime_func :public Item_te
 {
 public:
   Item_datetime_func() :Item_temporal_func()
-  { unsigned_flag= 1; }
+  { }
   Item_datetime_func(Item *a) :Item_temporal_func(a)
-  { unsigned_flag= 1; }
+  { }
   Item_datetime_func(Item *a,Item *b) :Item_temporal_func(a,b)
-  { unsigned_flag= 1; }
+  { }
   Item_datetime_func(Item *a,Item *b, Item *c) :Item_temporal_func(a,b,c)
-  { unsigned_flag= 1; }
+  { }
   enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; }
   double val_real() { return val_real_from_decimal(); }
   String *val_str(String *str)

=== added file 'sql/merge_sort.h'
--- a/sql/merge_sort.h	1970-01-01 00:00:00 +0000
+++ b/sql/merge_sort.h	2012-02-23 11:22:34 +0000
@@ -0,0 +1,145 @@
+/* Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA
+*/
+
+#ifndef MERGE_SORT_INCLUDED
+#define MERGE_SORT_INCLUDED
+
+/**
+  @file
+
+  @brief 
+  Merge sort and insert sort implementations. These sorting functions
+  are primarily intended for sorting of JOIN_TABs before the greedy
+  search algorithm is applied. Since the JOIN_TAB comparison functions
+  (Join_tab_compare*) are not transitive, the resulting order depends
+  on the sorting implementation to a certain degree.
+
+  Since the std::stable_sort and std::sort implementations differ
+  between platforms, the result of sorting JOIN_TABs may also differ.
+  In turn, the query execution plan would differ between platforms and
+  that is a problem with mtr tests (EXPLAIN output would vary).
+
+  If you intend to sort something transitive (which means almost
+  everything except JOIN_TABs) you should most likely use one of the
+  std sorting functions instead of this.
+*/
+
+#include "sql_select.h"
+#include <queue>
+
+/**
+ Sorts the elements in the range [first,last) into ascending order
+ using insertion sort.
+
+ @param first   First element in an array of pointers to be sorted
+ @param last    Element after the last element in an array of pointers 
+                to be sorted
+ @param comp    Comparison function object that, taking two pointers 
+                of the same type as those contained in the range, 
+                returns true if the first argument goes before the 
+                second argument in the specific strict weak ordering
+                it defines, and false otherwise.
+
+ In our case comp should be a function object with an operator:
+ 
+ bool operator()(Element_type*, Element_type*)
+*/
+
+template<typename Element_type, typename Comp_func>
+void insert_sort(Element_type **first, Element_type **last, Comp_func comp)
+{
+  for (Element_type **high_water_mark= first+1;
+       high_water_mark < last;
+       high_water_mark++)
+  {
+    for (Element_type **cur= high_water_mark; cur > first ; cur--)
+    {
+      if (comp(*(cur-1), *cur))
+        break;
+
+      Element_type *tmp= *(cur-1);
+      *(cur-1)= *cur;
+      *cur= tmp;
+    }
+  }
+}
+
+
+/**
+ Sorts the elements in the range [first,last) into ascending order
+ using merge sort.
+
+ @param first   First element in an array of pointers to be sorted
+ @param last    Element after the last element in an array of pointers 
+                to be sorted
+ @param comp    Comparison function object that, taking two pointers 
+                of the same type as those contained in the range, 
+                returns true if the first argument goes before the 
+                second argument in the specific strict weak ordering
+                it defines, and false otherwise.
+
+ In our case comp should be a function object with an operator:
+ 
+ bool operator()(Element_type*, Element_type*)
+*/
+
+template<typename Element_type, typename Comp_func>
+void merge_sort(Element_type **first, Element_type **last, Comp_func comp)
+{
+  const uint elements= last - first;
+
+  /*
+    Tests showed that the value 5 was a good number for JOIN_TAB
+    ordering, which is the primary use case for this function
+  */
+  if (elements < 5)
+  {
+    insert_sort(first, last, comp);
+    return;
+  }
+  Element_type **middle= first + (elements)/2;
+
+  merge_sort (first, middle, comp);
+  merge_sort (middle, last, comp);
+
+  std::queue<Element_type *> merged;
+
+  Element_type **cur1= first;
+  Element_type **cur2= middle;
+
+  for (uint i= 0; i < elements; i++)
+  {
+    DBUG_ASSERT (cur1 < middle || cur2 < last);
+
+    if (cur1 == middle)
+      merged.push(*cur2++);
+    else if (cur2 == last)
+      merged.push(*cur1++);
+    else if (comp(*cur1, *cur2))
+      merged.push(*cur1++);
+    else
+      merged.push(*cur2++);
+  }
+
+  Element_type **result= first;
+  while (!merged.empty())
+  {
+    *result++= merged.front();
+    merged.pop();
+  }
+}
+
+#endif /* MERGE_SORT_INCLUDED */

=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc	2012-02-22 16:44:55 +0000
+++ b/sql/mysqld.cc	2012-02-23 23:47:40 +0000
@@ -7102,6 +7102,7 @@ SHOW_VAR status_vars[]= {
   {"Opened_files",             (char*) &my_file_total_opened, SHOW_LONG_NOFLUSH},
   {"Opened_tables",            (char*) offsetof(STATUS_VAR, opened_tables), SHOW_LONGLONG_STATUS},
   {"Opened_table_definitions", (char*) offsetof(STATUS_VAR, opened_shares), SHOW_LONGLONG_STATUS},
+  {"Opt_partial_plans",        (char*) offsetof(STATUS_VAR, opt_partial_plans), SHOW_LONGLONG_STATUS},
   {"Prepared_stmt_count",      (char*) &show_prepared_stmt_count, SHOW_FUNC},
 #ifdef HAVE_QUERY_CACHE
   {"Qcache_free_blocks",       (char*) &query_cache.free_memory_blocks, SHOW_LONG_NOFLUSH},

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2012-02-22 08:57:27 +0000
+++ b/sql/sql_class.h	2012-02-23 11:22:34 +0000
@@ -836,6 +836,7 @@ typedef struct system_status_var
 {
   ulonglong created_tmp_disk_tables;
   ulonglong created_tmp_tables;
+  ulonglong opt_partial_plans;
   ulonglong ha_commit_count;
   ulonglong ha_delete_count;
   ulonglong ha_read_first_count;

=== modified file 'sql/sql_const.h'
--- a/sql/sql_const.h	2011-11-24 08:02:23 +0000
+++ b/sql/sql_const.h	2012-02-23 16:32:32 +0000
@@ -57,6 +57,10 @@
 #define MAX_DATETIME_WIDTH	19	/* YYYY-MM-DD HH:MM:SS */
 #define MAX_DATETIME_COMPRESSED_WIDTH 14  /* YYYYMMDDHHMMSS */
 
+#define DATE_INT_DIGITS       8         /* YYYYMMDD       */
+#define TIME_INT_DIGITS       7         /* hhhmmss        */
+#define DATETIME_INT_DIGITS  14         /* YYYYMMDDhhmmss */
+
 #define MAX_TABLES	(sizeof(table_map)*8-3)	/* Max tables in join */
 #define PARAM_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-3))
 #define OUTER_REF_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-2))

=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc	2012-02-21 10:31:44 +0000
+++ b/sql/sql_executor.cc	2012-02-23 13:27:28 +0000
@@ -1366,8 +1366,9 @@ return_zero_rows(JOIN *join, List<Item> 
     if (join->send_row_on_empty_set())
     {
       // Mark tables as containing only NULL values
-      for (uint tableno= 0; tableno < join->tables; tableno++)
-        mark_as_null_row((join->join_tab+tableno)->table);
+      for (TABLE_LIST *table= join->select_lex->leaf_tables; table;
+           table= table->next_leaf)
+        mark_as_null_row(table->table);
 
       // Calculate aggregate functions for no rows
       List_iterator_fast<Item> it(fields);

=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc	2012-01-16 12:51:06 +0000
+++ b/sql/sql_planner.cc	2012-02-23 11:22:34 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -29,6 +29,7 @@
 #include "opt_range.h"
 #include "opt_trace.h"
 #include "sql_executor.h"
+#include "merge_sort.h"
 #include <my_bit.h>
 
 #include <algorithm>
@@ -37,106 +38,6 @@ using std::min;
 
 static double prev_record_reads(JOIN *join, uint idx, table_map found_ref);
 
-/**
-  Compare two JOIN_TAB objects based on the number of accessed records.
-
-  @param ptr1 pointer to first JOIN_TAB object
-  @param ptr2 pointer to second JOIN_TAB object
-
-  NOTES
-    The order relation implemented by join_tab_cmp() is not transitive,
-    i.e. it is possible to choose such a, b and c that (a < b) && (b < c)
-    but (c < a). This implies that result of a sort using the relation
-    implemented by join_tab_cmp() depends on the order in which
-    elements are compared, i.e. the result is implementation-specific.
-    Example:
-      a: dependent = 0x0 table->map = 0x1 found_records = 3 ptr = 0x907e6b0
-      b: dependent = 0x0 table->map = 0x2 found_records = 3 ptr = 0x907e838
-      c: dependent = 0x6 table->map = 0x10 found_records = 2 ptr = 0x907ecd0
-     
-  @retval
-    1  if first is bigger
-  @retval
-    -1  if second is bigger
-  @retval
-    0  if equal
-*/
-
-static int
-join_tab_cmp(const void *, const void* ptr1, const void* ptr2)
-{
-  JOIN_TAB *jt1= *(JOIN_TAB**) ptr1;
-  JOIN_TAB *jt2= *(JOIN_TAB**) ptr2;
-
-  if (jt1->dependent & jt2->table->map)
-    return 1;
-  if (jt2->dependent & jt1->table->map)
-    return -1;  
-  if (jt1->found_records > jt2->found_records)
-    return 1;
-  if (jt1->found_records < jt2->found_records)
-    return -1; 
-  return jt1 > jt2 ? 1 : (jt1 < jt2 ? -1 : 0);
-}
-
-
-/**
-  Same as join_tab_cmp, but for use with SELECT_STRAIGHT_JOIN.
-*/
-
-static int
-join_tab_cmp_straight(const void *, const void* ptr1, const void* ptr2)
-{
-  JOIN_TAB *jt1= *(JOIN_TAB**) ptr1;
-  JOIN_TAB *jt2= *(JOIN_TAB**) ptr2;
- 
-  /*
-    We don't do subquery flattening if the parent or child select has
-    STRAIGHT_JOIN modifier. It is complicated to implement and the semantics
-    is hardly useful.
-  */
-  DBUG_ASSERT(!jt1->emb_sj_nest);
-  DBUG_ASSERT(!jt2->emb_sj_nest);
-
-  if (jt1->dependent & jt2->table->map)
-    return 1;
-  if (jt2->dependent & jt1->table->map)
-    return -1;
-  return jt1 > jt2 ? 1 : (jt1 < jt2 ? -1 : 0);
-}
-
-
-/*
-  Same as join_tab_cmp but tables from within the given semi-join nest go 
-  first. Used when optimizing semi-join materialization nests.
-*/
-
-static int
-join_tab_cmp_embedded_first(const void *emb, const void* ptr1, const void* ptr2)
-{
-  const TABLE_LIST *emb_nest= (TABLE_LIST*) emb;
-  JOIN_TAB *jt1= *(JOIN_TAB**) ptr1;
-  JOIN_TAB *jt2= *(JOIN_TAB**) ptr2;
-
-  if (jt1->emb_sj_nest == emb_nest && jt2->emb_sj_nest != emb_nest)
-    return -1;
-  if (jt1->emb_sj_nest != emb_nest && jt2->emb_sj_nest == emb_nest)
-    return 1;
-
-  if (jt1->dependent & jt2->table->map)
-    return 1;
-  if (jt2->dependent & jt1->table->map)
-    return -1;
-
-  if (jt1->found_records > jt2->found_records)
-    return 1;
-  if (jt1->found_records < jt2->found_records)
-    return -1; 
-  
-  return jt1 > jt2 ? 1 : (jt1 < jt2 ? -1 : 0);
-}
-
-
 /*
   This is a class for considering possible loose index scan optimizations.
   It's usage pattern is as follows:
@@ -1149,7 +1050,6 @@ bool Optimize_table_order::choose_table_
   }
 
   reset_nj_counters(join->join_list);
-  qsort2_cmp jtab_sort_func;
 
   const bool straight_join= test(join->select_options & SELECT_STRAIGHT_JOIN);
   table_map join_tables;      ///< The tables involved in order selection
@@ -1159,7 +1059,9 @@ bool Optimize_table_order::choose_table_
     /* We're optimizing semi-join materialization nest, so put the 
        tables from this semi-join as first
     */
-    jtab_sort_func= join_tab_cmp_embedded_first;
+    merge_sort(join->best_ref + join->const_tables,
+               join->best_ref + join->tables,
+               Join_tab_compare_embedded_first(emb_sjm_nest));
     join_tables= emb_sjm_nest->sj_inner_tables;
   }
   else
@@ -1172,12 +1074,17 @@ bool Optimize_table_order::choose_table_
         Apply heuristic: pre-sort all access plans with respect to the number of
         records accessed.
     */
-    jtab_sort_func= straight_join ? join_tab_cmp_straight : join_tab_cmp;
+    if (straight_join)
+      merge_sort(join->best_ref + join->const_tables,
+                 join->best_ref + join->tables,
+                 Join_tab_compare_straight());
+    else 
+      merge_sort(join->best_ref + join->const_tables,
+                 join->best_ref + join->tables,
+                 Join_tab_compare_default());
+
     join_tables= join->all_table_map & ~join->const_table_map;
   }
-  my_qsort2(join->best_ref + join->const_tables,
-            join->tables - join->const_tables, sizeof(JOIN_TAB*),
-            jtab_sort_func, (void*)emb_sjm_nest);
 
   Opt_trace_object wrapper(&join->thd->opt_trace);
   Opt_trace_array
@@ -1882,6 +1789,7 @@ bool Optimize_table_order::best_extensio
 
   for (JOIN_TAB **pos= join->best_ref + idx; *pos; pos++)
   {
+    status_var_increment(thd->status_var.opt_partial_plans);
     JOIN_TAB *const s= *pos;
     const table_map real_table_bit= s->table->map;
 
@@ -2250,6 +2158,7 @@ table_map Optimize_table_order::eq_ref_e
                           added_to_eq_ref_extension);
       if (added_to_eq_ref_extension)
       {
+        status_var_increment(thd->status_var.opt_partial_plans);
         double current_record_count, current_read_time;
 
         /* Add the cost of extending the plan with 's' */

=== modified file 'sql/sql_planner.h'
--- a/sql/sql_planner.h	2011-12-14 12:32:55 +0000
+++ b/sql/sql_planner.h	2012-02-23 11:22:34 +0000
@@ -1,7 +1,7 @@
 #ifndef SQL_PLANNER_INCLUDED
 #define SQL_PLANNER_INCLUDED
 
-/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -140,12 +140,6 @@ private:
                 double *newcount, double *newcost);
 
   static uint determine_search_depth(uint search_depth, uint table_count);
-  /**
-    @todo: Add the static functions join_tab_cmp(), join_tab_cmp_straight() and
-    join_tab_cmp_embedded_first() as static private member functions here.
-    This is currently not possible because they are fed to my_qsort2(),
-    which requires C linkage.
-  */
 };
 
 void get_partial_join_cost(JOIN *join, uint n_tables, double *read_time_arg,

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-01-16 12:51:06 +0000
+++ b/sql/sql_select.h	2012-02-23 11:22:34 +0000
@@ -1,7 +1,7 @@
 #ifndef SQL_SELECT_INCLUDED
 #define SQL_SELECT_INCLUDED
 
-/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -33,6 +33,7 @@
 #include "mem_root_array.h"
 #include "sql_executor.h"
 
+#include <functional>
 /**
    Returns a constant of type 'type' with the 'A' lowest-weight bits set.
    Example: LOWER_BITS(uint, 3) == 7.
@@ -654,6 +655,135 @@ st_join_table::st_join_table()
   memset(&read_record, 0, sizeof(read_record));
 }
 
+/**
+  "Less than" comparison function object used to compare two JOIN_TAB
+  objects based on a number of factors in this order:
+
+   - table before another table that depends on it (straight join, 
+     outer join etc), then
+   - table before another table that depends on it to use a key
+     as access method, then
+   - table with smallest number of records first, then
+   - the table with lowest-value pointer (i.e., the one located 
+     in the lowest memory address) first.
+
+  @param jt1  first JOIN_TAB object
+  @param jt2  second JOIN_TAB object
+
+  @note The order relation implemented by Join_tab_compare_default is not
+    transitive, i.e. it is possible to choose a, b and c such that 
+    (a < b) && (b < c) but (c < a). This is the case in the
+    following example: 
+
+      a: dependent = <none>   found_records = 3
+      b: dependent = <none>   found_records = 4
+      c: dependent = b        found_records = 2
+
+        a < b: because a has fewer records
+        b < c: because c depends on b (e.g outer join dependency)
+        c < a: because c has fewer records
+
+    This implies that the result of a sort using the relation
+    implemented by Join_tab_compare_default () depends on the order in
+    which elements are compared, i.e. the result is
+    implementation-specific.
+
+  @return
+    true if jt1 is smaller than jt2, false otherwise
+*/
+class Join_tab_compare_default :
+  public std::binary_function<const JOIN_TAB*, const JOIN_TAB*, bool>
+{
+public:
+  bool operator()(const JOIN_TAB *jt1, const JOIN_TAB *jt2)
+  {
+    // Sorting distinct tables, so a table should not be compared with itself
+    DBUG_ASSERT(jt1 != jt2);
+
+    if (jt1->dependent & jt2->table->map)
+      return false;
+    if (jt2->dependent & jt1->table->map)
+      return true;
+
+    const bool jt1_keydep_jt2= jt1->key_dependent & jt2->table->map;
+    const bool jt2_keydep_jt1= jt2->key_dependent & jt1->table->map;
+
+    if (jt1_keydep_jt2 && !jt2_keydep_jt1)
+      return false;
+    if (jt2_keydep_jt1 && !jt1_keydep_jt2)
+      return true;
+
+    if (jt1->found_records > jt2->found_records)
+      return false;
+    if (jt1->found_records < jt2->found_records)
+      return true;
+
+    return jt1 < jt2;
+  }
+};
+
+/**
+  "Less than" comparison function object used to compare two JOIN_TAB
+  objects that are joined using STRAIGHT JOIN. For STRAIGHT JOINs, 
+  the join order is dictated by the relative order of the tables in the
+  query which is reflected in JOIN_TAB::dependent. Table size and key
+  dependencies are ignored here.
+*/
+class Join_tab_compare_straight :
+  public std::binary_function<const JOIN_TAB*, const JOIN_TAB*, bool>
+{
+public:
+  bool operator()(const JOIN_TAB *jt1, const JOIN_TAB *jt2)
+  {
+    // Sorting distinct tables, so a table should not be compared with itself
+    DBUG_ASSERT(jt1 != jt2);
+
+    /*
+      We don't do subquery flattening if the parent or child select has
+      STRAIGHT_JOIN modifier. It is complicated to implement and the semantics
+      is hardly useful.
+    */
+    DBUG_ASSERT(!jt1->emb_sj_nest);
+    DBUG_ASSERT(!jt2->emb_sj_nest);
+
+    if (jt1->dependent & jt2->table->map)
+      return false;
+    if (jt2->dependent & jt1->table->map)
+      return true;
+
+    return jt1 < jt2;
+  }
+};
+
+/*
+  Same as Join_tab_compare_default but tables from within the given
+  semi-join nest go first. Used when optimizing semi-join
+  materialization nests.
+*/
+class Join_tab_compare_embedded_first :
+  public std::binary_function<const JOIN_TAB*, const JOIN_TAB*, bool>
+{
+private:
+  const TABLE_LIST *emb_nest;
+public:
+  
+  Join_tab_compare_embedded_first(const TABLE_LIST *nest) : emb_nest(nest){}
+
+  bool operator()(const JOIN_TAB *jt1, const JOIN_TAB *jt2)
+  {
+    // Sorting distinct tables, so a table should not be compared with itself
+    DBUG_ASSERT(jt1 != jt2);
+
+    if (jt1->emb_sj_nest == emb_nest && jt2->emb_sj_nest != emb_nest)
+      return true;
+    if (jt1->emb_sj_nest != emb_nest && jt2->emb_sj_nest == emb_nest)
+      return false;
+
+    Join_tab_compare_default cmp;
+    return cmp(jt1,jt2);
+  }
+};
+
 
 
 /**

=== modified file 'unittest/gunit/CMakeLists.txt'
--- a/unittest/gunit/CMakeLists.txt	2012-02-07 11:56:30 +0000
+++ b/unittest/gunit/CMakeLists.txt	2012-02-23 11:22:34 +0000
@@ -260,6 +260,7 @@ SET(SERVER_TESTS
   sql_table
   get_diagnostics
   segfault
+  join_tab_sort
 )
 
 FOREACH(test ${TESTS})

=== added file 'unittest/gunit/join_tab_sort-t.cc'
--- a/unittest/gunit/join_tab_sort-t.cc	1970-01-01 00:00:00 +0000
+++ b/unittest/gunit/join_tab_sort-t.cc	2012-02-23 13:54:03 +0000
@@ -0,0 +1,291 @@
+/* Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved. 
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
+
+// First include (the generated) my_config.h, to get correct platform defines.
+#include "my_config.h"
+#include <gtest/gtest.h>
+
+#include "test_utils.h"
+
+#include "sql_select.h"
+#include "merge_sort.h"
+
+#include <vector>
+
+namespace {
+
+using my_testing::Server_initializer;
+using my_testing::Mock_error_handler;
+
+class JTSortTest : public ::testing::Test
+{
+protected:
+  static void SetUpTestCase()
+  {
+    Server_initializer::SetUpTestCase();
+  }
+
+  static void TearDownTestCase()
+  {
+    Server_initializer::TearDownTestCase();
+  }
+
+  virtual void SetUp()
+  {
+    initializer.SetUp();
+  }
+
+  virtual void TearDown()
+  {
+    initializer.TearDown();
+  }
+
+  Server_initializer initializer;
+};
+
+
+class MOCK_JOIN_TAB : public JOIN_TAB
+{
+public:
+  MOCK_JOIN_TAB(uint recs, uint table_no) : JOIN_TAB()
+  {
+    found_records= recs;
+    m_table.map= 1ULL << table_no;
+    this->table= &m_table;
+  }
+  
+  TABLE       m_table;
+};
+
+std::ostream &operator<<(std::ostream &s, const MOCK_JOIN_TAB &jt)
+{
+  return s << "{"
+           << jt.found_records << ", "
+           << jt.m_table.map
+           << "}";
+}
+
+
+TEST_F(JTSortTest, SimpleSortTest)
+{
+  MOCK_JOIN_TAB jt1(UINT_MAX, 0);
+  MOCK_JOIN_TAB jt2(2, 0);
+  MOCK_JOIN_TAB jt3(1, 0);
+  MOCK_JOIN_TAB jt4(10, 0);
+  MOCK_JOIN_TAB jt5(5, 0);
+
+  MOCK_JOIN_TAB *arr[5];
+  arr[0]= &jt1;
+  arr[1]= &jt2;
+  arr[2]= &jt3;
+  arr[3]= &jt4;
+  arr[4]= &jt5;
+
+  insert_sort(arr, arr+5, Join_tab_compare_default());
+
+  EXPECT_EQ(1U, arr[0]->found_records);
+  EXPECT_EQ(2U, arr[1]->found_records);
+  EXPECT_EQ(5U, arr[2]->found_records);
+  EXPECT_EQ(10U, arr[3]->found_records);
+  EXPECT_EQ(UINT_MAX, arr[4]->found_records);
+
+}
+
+
+TEST_F(JTSortTest, SortFoundRecordsTest)
+{
+  const int num_tables= 50;
+  MOCK_JOIN_TAB *arr[num_tables];
+
+  for (int i= 0; i < num_tables; i++)
+    arr[i]= new MOCK_JOIN_TAB(i, 0);
+
+  // MERGE SORT
+  std::random_shuffle(arr, arr + 50);
+  merge_sort(arr, arr + num_tables, Join_tab_compare_default());
+  for (int i= 1; i < num_tables; i++)
+    EXPECT_TRUE(arr[i]->found_records > arr[i-1]->found_records);
+
+  // INSERT SORT
+  std::random_shuffle(arr, arr + 50);
+  insert_sort(arr, arr + num_tables, Join_tab_compare_default());
+  for (int i= 1; i < num_tables; i++)
+    EXPECT_TRUE(arr[i]->found_records > arr[i-1]->found_records);
+
+  for (int i= 0; i < num_tables; i++)
+  {
+    delete arr[i];
+  }
+}
+
+
+TEST_F(JTSortTest, SortDependsTest)
+{
+  const int num_tables= 50;
+  MOCK_JOIN_TAB *arr[num_tables];
+
+  /*
+    dependency has higher precedence than found_records, so the tables
+    shall be ordered with decreasing number of records in this test
+  */
+  for (int i= 0; i < num_tables; i++)
+  {
+    arr[i]= new MOCK_JOIN_TAB(i, i);
+    for (int j= i+1; j < num_tables; j++)
+      arr[i]->dependent|= 1ULL << j;
+  }
+
+  // MERGE SORT
+  std::random_shuffle(arr, arr + num_tables);
+  merge_sort(arr, arr + num_tables, Join_tab_compare_default());
+  for (int i= 1; i < num_tables; i++)
+    EXPECT_TRUE(arr[i]->found_records < arr[i-1]->found_records)
+      << "i: " << *(arr[i]) << " "
+      << "i-1: " << *(arr[i-1]);
+
+  // INSERT SORT
+  std::random_shuffle(arr, arr + num_tables);
+  insert_sort(arr, arr + num_tables, Join_tab_compare_default());
+  for (int i= 1; i < num_tables; i++)
+    EXPECT_TRUE(arr[i]->found_records < arr[i-1]->found_records);
+
+  for (int i= 0; i < num_tables; i++)
+  {
+    delete arr[i];
+  }
+}
+
+
+TEST_F(JTSortTest, SortKeyDependsTest)
+{
+  const int num_tables= 50;
+  MOCK_JOIN_TAB *arr[num_tables];
+
+  /*
+    key_dependency has higher precedence than found_records, so the
+    tables shall be ordered with decreasing number of records in this
+    test
+  */
+  for (int i= 0; i < num_tables; i++)
+  {
+    arr[i]= new MOCK_JOIN_TAB(i, i);
+    for (int j= i+1; j < num_tables; j++)
+      arr[i]->key_dependent|= 1ULL << j;
+  }
+
+  // MERGE SORT
+  std::random_shuffle(arr, arr + num_tables);
+  merge_sort(arr, arr + num_tables, Join_tab_compare_default());
+  for (int i= 1; i < num_tables; i++)
+    EXPECT_TRUE(arr[i]->found_records < arr[i-1]->found_records);
+
+  // INSERT SORT
+  std::random_shuffle(arr, arr + num_tables);
+  insert_sort(arr, arr + num_tables, Join_tab_compare_default());
+  for (int i= 1; i < num_tables; i++)
+    EXPECT_TRUE(arr[i]->found_records < arr[i-1]->found_records);
+
+  for (int i= 0; i < num_tables; i++)
+    delete arr[i];
+}
+
+/*
+  Above, sorting for JOIN_TABs were tested. Below we check that the
+  sorting works for ints types as well. 
+*/
+
+class Int_compare_ptr :
+  public std::binary_function<const int*, const int*, bool>
+{
+public:
+  bool operator()(const int *i1, const int *i2) const
+  {
+    return *i1 < *i2;
+  }
+};
+
+
+TEST_F(JTSortTest, SortIntTest)
+{
+  const uint ints_to_sort= 1000;
+
+  std::vector<int> arr;
+  std::vector<int*> arr_ptr;
+
+  arr.reserve(ints_to_sort);
+  arr_ptr.reserve(ints_to_sort);
+
+  for (uint i= 0; i < ints_to_sort; i++)
+  {
+    arr.push_back(i);
+    arr_ptr.push_back(&arr[i]);
+  }
+
+  EXPECT_TRUE(arr.size() == ints_to_sort);
+  EXPECT_TRUE(arr_ptr.size() == ints_to_sort);
+
+  // MERGE SORT
+  std::random_shuffle(&arr_ptr.front(), &arr_ptr.back() + 1);
+  merge_sort(&arr_ptr.front(), &arr_ptr.back() + 1, Int_compare_ptr());
+  for (uint i= 0; i < arr_ptr.size(); i++)
+    EXPECT_TRUE(*arr_ptr[i] == (int)i);
+
+  // INSERT SORT
+  std::random_shuffle(&arr_ptr.front(), &arr_ptr.back() + 1);
+  insert_sort(&arr_ptr.front(), &arr_ptr.back() + 1, Int_compare_ptr());
+  for (uint i= 0; i < arr_ptr.size(); i++)
+    EXPECT_TRUE(*arr_ptr[i] == (int)i);
+}
+
+
+TEST_F(JTSortTest, SortInt2Test)
+{
+  const uint ints_to_sort= 1000;
+
+  std::vector<int> arr;
+  std::vector<int*> arr_ptr;
+
+  arr.reserve(ints_to_sort);
+  arr_ptr.reserve(ints_to_sort);
+
+  for (uint i= 0; i < (ints_to_sort - 2); i++)
+  {
+    arr.push_back((i % 2) ? i : (i * -1));
+    arr_ptr.push_back(&arr[i]);
+  }
+
+  arr.push_back(INT_MAX32);
+  arr_ptr.push_back(&arr.back());
+
+  arr.push_back(INT_MIN32);
+  arr_ptr.push_back(&arr.back());
+
+  EXPECT_TRUE(arr.size() == ints_to_sort);
+  EXPECT_TRUE(arr_ptr.size() == ints_to_sort);
+
+  // MERGE SORT
+  std::random_shuffle(&arr_ptr.front(), &arr_ptr.back() + 1);
+  merge_sort(&arr_ptr.front(), &arr_ptr.back() + 1, Int_compare_ptr());
+  for (uint i= 1; i < arr_ptr.size(); i++)
+    EXPECT_TRUE(*arr_ptr[i-1] < *arr_ptr[i]);
+
+  // INSERT SORT
+  std::random_shuffle(&arr_ptr.front(), &arr_ptr.back() + 1);
+  insert_sort(&arr_ptr.front(), &arr_ptr.back() + 1, Int_compare_ptr());
+  for (uint i= 1; i < arr_ptr.size(); i++)
+    EXPECT_TRUE(*arr_ptr[i-1] < *arr_ptr[i]);
+}
+
+}

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk-wl5259 branch (marc.alff:3389 to 3390) Marc Alff24 Feb