List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:January 5 2012 10:36am
Subject:bzr push into mysql-trunk branch (tor.didriksen:3440 to 3449)
View as plain text  
 3449 Tor Didriksen	2012-01-05 [merge]
      Merge opt-backporting => opt-team

    modified:
      mysql-test/r/distinct.result
      mysql-test/r/group_by.result
      mysql-test/r/having.result
      mysql-test/r/innodb_icp.result
      mysql-test/r/innodb_icp_none.result
      mysql-test/r/join.result
      mysql-test/r/join_outer.result
      mysql-test/r/join_outer_bka.result
      mysql-test/r/join_outer_bka_nixbnl.result
      mysql-test/r/myisam_icp.result
      mysql-test/r/myisam_icp_all.result
      mysql-test/r/myisam_icp_none.result
      mysql-test/suite/opt_trace/include/general2.inc
      mysql-test/suite/opt_trace/r/general2_no_prot.result
      mysql-test/suite/opt_trace/r/general2_ps_prot.result
      mysql-test/suite/opt_trace/r/temp_table.result
      mysql-test/t/distinct.test
      mysql-test/t/group_by.test
      mysql-test/t/having.test
      mysql-test/t/join.test
      mysql-test/t/join_outer.test
      sql/field.h
      sql/field_conv.cc
      sql/item_sum.cc
      sql/opt_sum.cc
      sql/sql_base.cc
      sql/sql_executor.cc
      sql/sql_lex.cc
      sql/sql_optimizer.cc
      sql/sql_parse.cc
      sql/sql_planner.cc
      sql/sql_select.cc
      sql/sql_tmp_table.cc
      sql/table.cc
      sql/table.h
      storage/myisam/mi_create.c
      unittest/gunit/bounded_queue-t.cc
      unittest/gunit/bounds_checked_array-t.cc
      unittest/gunit/cost_estimate-t.cc
      unittest/gunit/dbug-t.cc
      unittest/gunit/dynarray-t.cc
      unittest/gunit/field-t.cc
      unittest/gunit/filesort_buffer-t.cc
      unittest/gunit/get_diagnostics-t.cc
      unittest/gunit/gunit_test_main.cc
      unittest/gunit/item-t.cc
      unittest/gunit/mdl-t.cc
      unittest/gunit/mdl_mytap-t.cc
      unittest/gunit/my_regex-t.cc
      unittest/gunit/opt_range-t.cc
      unittest/gunit/opt_trace-t.cc
      unittest/gunit/sql_list-t.cc
      unittest/gunit/sql_plist-t.cc
      unittest/gunit/stdcxx-t.cc
      unittest/gunit/tap_event_listener.cc
      unittest/gunit/test_utils.cc
      unittest/gunit/thread_utils-t.cc
      unittest/gunit/thread_utils.cc
 3448 Tor Didriksen	2012-01-05 [merge]
      Empty merge opt-backporting => opt-team

 3447 Tor Didriksen	2012-01-05 [merge]
      Empty merge opt-backporting => opt-team

 3446 Tor Didriksen	2012-01-05 [merge]
      NULL merge opt-backporting => opt-team

 3445 Tor Didriksen	2012-01-05 [merge]
      Automerge opt-backporting => opt-team

    modified:
      mysql-test/r/select_all_bka.result
      mysql-test/r/select_all_bka_nixbnl.result
 3444 Tor Didriksen	2012-01-05 [merge]
      Empty merge opt-backporting => opt-team

 3443 Tor Didriksen	2012-01-05 [merge]
      Empty merge opt-backporting => opt-team

 3442 Tor Didriksen	2012-01-05 [merge]
      Merge opt-backporting => opt-team, reverted, copied result files

    modified:
      mysql-test/r/innodb_explain_non_select_all.result
      mysql-test/r/innodb_icp_all.result
      mysql-test/r/innodb_mrr_all.result
      mysql-test/r/innodb_mrr_cost_all.result
 3441 Tor Didriksen	2012-01-05 [merge]
      Automerge opt-backporting => opt-team

 3440 Tor Didriksen	2011-12-16 [merge]
      merge trunk => opt-team

    modified:
      mysql-test/suite/binlog/t/binlog_index.test
      mysql-test/suite/rpl/t/rpl_binlog_errors.test
=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result	2011-11-02 12:47:31 +0000
+++ b/mysql-test/r/distinct.result	2012-01-04 13:53:33 +0000
@@ -826,3 +826,62 @@ ORDER BY col_time_key
 LIMIT 3;
 col_int_key
 DROP TABLE t1;
+#
+# BUG#13540692: WRONG NULL HANDLING WITH RIGHT JOIN + 
+#               DISTINCT OR ORDER BY
+#
+CREATE TABLE t1 (
+a INT,
+b INT NOT NULL
+);
+INSERT INTO t1 VALUES (1,2), (3,3);
+
+EXPLAIN SELECT DISTINCT subselect.b
+FROM t1 LEFT JOIN 
+(SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect 
+ON t1.a = subselect.b
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+2	DERIVED	it_a	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	it_b	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+SELECT DISTINCT subselect.b
+FROM t1 LEFT JOIN 
+(SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect 
+ON t1.a = subselect.b
+;
+b
+3
+NULL
+DROP TABLE t1;
+#
+# BUG#13538387: WRONG RESULT ON SELECT DISTINCT + LEFT JOIN + 
+#               LIMIT + MIX OF MYISAM AND INNODB
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (2),(3);
+CREATE TABLE t2 (b INT);
+CREATE TABLE t3 (
+a INT,
+b INT,
+PRIMARY KEY (b)
+);
+INSERT INTO t3 VALUES (2001,1), (2007,2);
+EXPLAIN SELECT DISTINCT t3.a AS t3_date
+FROM t1
+LEFT JOIN t2 ON false
+LEFT JOIN t3 ON t2.b = t3.b
+LIMIT 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t3	const	PRIMARY	NULL	NULL	NULL	1	Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Distinct
+SELECT DISTINCT t3.a AS t3_date
+FROM t1
+LEFT JOIN t2 ON false
+LEFT JOIN t3 ON t2.b = t3.b
+LIMIT 1;
+t3_date
+NULL
+DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/group_by.result	2012-01-05 10:17:03 +0000
@@ -2204,3 +2204,65 @@ field1	field2
 1	3
 DROP VIEW v1;
 DROP TABLE t1;
+#
+# Bug#13422961: WRONG RESULTS FROM SELECT WITH AGGREGATES AND
+#               IMPLICIT GROUPING + MYISAM OR MEM
+#
+CREATE TABLE it (
+pk INT NOT NULL,
+col_int_nokey INT NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+CREATE TABLE ot (
+pk int(11) NOT NULL,
+col_int_nokey int(11) NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO ot VALUES (10,8);
+
+SELECT col_int_nokey, MAX( pk ) 
+FROM ot 
+WHERE (8, 1) IN ( SELECT pk, COUNT( col_int_nokey ) FROM it );
+col_int_nokey	MAX( pk )
+NULL	NULL
+
+DROP TABLE it,ot;
+#
+# Bug#13430588: WRONG RESULT FROM IMPLICITLY GROUPED QUERY WITH
+#               CONST TABLE AND NO MATCHING ROWS
+#
+CREATE TABLE t1 (i INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (j INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+
+SELECT i, j, COUNT(i) FROM t1 JOIN t2 WHERE j=3;
+i	j	COUNT(i)
+NULL	NULL	0
+
+DROP TABLE t1,t2;
+#
+# BUG#13541761: WRONG RESULTS ON CORRELATED SUBQUERY + 
+#               AGGREGATE FUNCTION + MYISAM OR MEMORY
+#
+CREATE TABLE t1 (
+a varchar(1)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('a'), ('b');
+CREATE TABLE t2 (
+a varchar(1),
+b int(11)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('a',1);
+
+EXPLAIN SELECT (SELECT MAX(b) FROM t2 WHERE t2.a != t1.a) as MAX 
+FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
+SELECT (SELECT MAX(b) FROM t2 WHERE t2.a != t1.a) as MAX 
+FROM t1;
+MAX
+NULL
+1
+DROP TABLE t1,t2;

=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result	2011-11-25 14:07:13 +0000
+++ b/mysql-test/r/having.result	2011-12-20 12:04:31 +0000
@@ -633,4 +633,24 @@ HAVING x < 2;
 x
 DROP TABLE it,ot;
 DROP FUNCTION f;
+#
+# Bug#11760517: MIN/MAX FAILS TO EVALUATE HAVING CONDITION, 
+#               RETURNS INCORRECT NULL RESULT
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY, i4 INT);
+INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);
+
+SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
+FROM t1 as table1, t1 as table2
+WHERE table1.pk = 1;
+MIN(table1.i4)	min_pk
+NULL	NULL
+
+SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
+FROM t1 as table1, t1 as table2
+WHERE table1.pk = 1
+HAVING min_pk <= 10;
+MIN(table1.i4)	min_pk
+
+DROP TABLE t1;
 End of 5.6 tests

=== modified file 'mysql-test/r/innodb_explain_non_select_all.result'
--- a/mysql-test/r/innodb_explain_non_select_all.result	2011-12-15 12:13:58 +0000
+++ b/mysql-test/r/innodb_explain_non_select_all.result	2012-01-05 10:12:47 +0000
@@ -30,12 +30,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	4
 Handler_update	3
 
@@ -69,13 +69,13 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	3
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	4
 
 DROP TABLE t1;
@@ -108,13 +108,13 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	1
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	4
 
 DROP TABLE t1;
@@ -152,12 +152,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	8
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	8
 Handler_update	1
 
@@ -185,10 +185,6 @@ id	select_type	table	type	possible_keys	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_first	1
-Handler_read_key	2
-Handler_read_rnd_next	4
-Handler_write	3
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12                WHERE t11.a = 1;
@@ -200,20 +196,16 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` where (`test`.`t11`.`a` = 1)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_first	1
-Handler_read_key	2
-Handler_read_rnd_next	4
-Handler_write	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	12
 Handler_write	3
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	12
 Handler_update	1
 Handler_write	3
@@ -253,12 +245,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	5
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	4
-Handler_read_key	8
+Handler_read_key	4
 Handler_read_rnd_next	7
 Handler_update	3
 
@@ -300,12 +292,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	8
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	4
-Handler_read_key	8
+Handler_read_key	4
 Handler_read_rnd_next	11
 Handler_update	2
 
@@ -347,12 +339,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	3
-Handler_read_key	6
+Handler_read_key	3
 Handler_read_rnd_next	12
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	5
-Handler_read_key	10
+Handler_read_key	5
 Handler_read_rnd_next	15
 Handler_update	2
 
@@ -380,10 +372,6 @@ id	select_type	table	type	possible_keys	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_first	1
-Handler_read_key	2
-Handler_read_rnd_next	4
-Handler_write	3
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12;
@@ -395,20 +383,16 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12`
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_first	1
-Handler_read_key	2
-Handler_read_rnd_next	4
-Handler_write	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	12
 Handler_write	3
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	8
+Handler_read_key	5
 Handler_read_rnd	3
 Handler_read_rnd_next	25
 Handler_update	3
@@ -458,13 +442,13 @@ Handler_write	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	5
 Handler_write	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	6
+Handler_read_key	4
 Handler_read_rnd	3
 Handler_read_rnd_next	9
 Handler_update	3
@@ -494,10 +478,6 @@ id	select_type	table	type	possible_keys	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_first	1
-Handler_read_key	2
-Handler_read_rnd_next	4
-Handler_write	3
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12                WHERE t11.a > 1;
@@ -509,20 +489,16 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` where (`test`.`t11`.`a` > 1)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_first	1
-Handler_read_key	2
-Handler_read_rnd_next	4
-Handler_write	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	12
 Handler_write	3
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	16
 Handler_update	2
 Handler_write	3
@@ -557,13 +533,13 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	2
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	1
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	2
 
 DROP TABLE t1;
@@ -648,7 +624,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	range	a,b	a	5	NULL	1	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	2
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 3;
@@ -658,14 +633,14 @@ Warnings:
 Note	1003	/* select#1 */ select '3' AS `a`,'3' AS `b` from `test`.`t1` where 1
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	4
+Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	4
+Handler_read_key	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	1
-Handler_read_key	4
+Handler_read_key	1
 
 DROP TABLE t1;
 #15
@@ -685,7 +660,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	range	a,b	a	5	NULL	1	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	3
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 3;
@@ -695,13 +669,12 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` < 3)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	9
+Handler_read_key	1
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	5
+Handler_read_key	1
 
 DROP TABLE t1;
 #16
@@ -720,7 +693,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	2
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
@@ -730,14 +702,13 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 0) order by `test`.`t1`.`a`
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	5
+Handler_read_key	1
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	4
+Handler_read_key	1
 
 INSERT INTO t1 VALUES (1), (2), (3);
 #
@@ -754,7 +725,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	2
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
@@ -764,16 +734,15 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 0) order by `test`.`t1`.`a`
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	5
+Handler_read_key	1
 Handler_read_next	3
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	3
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_next	3
 
 DROP TABLE t1;
@@ -794,7 +763,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	PRIMARY	PRIMARY	4	NULL	4	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1;
@@ -804,16 +772,15 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (@a:=`test`.`t1`.`a`) order by `test`.`t1`.`a` limit 1
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	3
+Handler_read_key	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	1
 Handler_read_first	1
-Handler_read_key	3
+Handler_read_key	1
 
 DROP TABLE t1;
 #18
@@ -834,7 +801,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	c,b,a	NULL	NULL	NULL	10	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	3
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1;
@@ -844,11 +810,10 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` limit 1
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	6
+Handler_read_key	1
 Handler_read_rnd_next	11
 Sort_rows	1
 Sort_scan	1
@@ -856,7 +821,7 @@ Sort_scan	1
 Variable_name	Value
 Handler_delete	1
 Handler_read_first	1
-Handler_read_key	7
+Handler_read_key	2
 Handler_read_rnd	1
 Handler_read_rnd_next	11
 Sort_rows	10
@@ -888,7 +853,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.b2,test.t1.b1	1	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	2
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT *        FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3;
@@ -900,18 +864,17 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`b3` AS `b3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`b3` = `test`.`t1`.`b1`) and (`test`.`t3`.`a3` = `test`.`t2`.`b2`) and (`test`.`t2`.`a2` = `test`.`t1`.`a1`))
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	2
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	12
+Handler_read_key	7
 Handler_read_next	3
 Handler_read_rnd_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	8
 Handler_read_first	1
-Handler_read_key	19
+Handler_read_key	12
 Handler_read_next	3
 Handler_read_rnd	5
 Handler_read_rnd_next	4
@@ -951,12 +914,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	8
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	4
-Handler_read_key	8
+Handler_read_key	4
 Handler_read_rnd_next	10
 Handler_update	3
 
@@ -996,13 +959,13 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	6
-Handler_read_key	12
+Handler_read_key	6
 Handler_read_rnd_next	30
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	3
 Handler_read_first	6
-Handler_read_key	12
+Handler_read_key	6
 Handler_read_rnd_next	30
 
 SET @@optimizer_switch= @save_optimizer_switch;
@@ -1037,13 +1000,13 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	12
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	3
 Handler_read_first	6
-Handler_read_key	12
+Handler_read_key	6
 Handler_read_rnd_next	30
 
 DROP TABLE t1, t2;
@@ -1076,12 +1039,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	6
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	6
 Handler_update	5
 
@@ -1115,13 +1078,13 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	6
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	5
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	6
 
 DROP TABLE t1;
@@ -1147,7 +1110,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	a	a	15	NULL	X	X	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1157,17 +1119,16 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	3
+Handler_read_key	1
 Handler_read_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	5
 Handler_read_first	1
-Handler_read_key	3
+Handler_read_key	1
 Handler_read_next	4
 
 DROP TABLE t1, t2;
@@ -1201,12 +1162,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	4
 Handler_write	3
 
@@ -1241,12 +1202,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	4
 Handler_write	3
 
@@ -1312,25 +1273,23 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	8	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	2
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	8	100.00	Using index condition
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	8	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where ((`test`.`t1`.`i` > 10) and (`test`.`t1`.`i` <= 18)) order by `test`.`t1`.`i` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	5
+Handler_read_key	1
 Handler_read_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	5
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_next	4
 
 DROP TABLE t1;
@@ -1356,7 +1315,6 @@ Warning	1713	Cannot use range access on 
 Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
@@ -1370,11 +1328,10 @@ Warning	1713	Cannot use range access on 
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where ((`test`.`t1`.`i` > 10) and (`test`.`t1`.`i` <= 18)) order by `test`.`t1`.`i` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_rnd_next	27
 Sort_rows	5
 Sort_scan	1
@@ -1382,7 +1339,7 @@ Sort_scan	1
 Variable_name	Value
 Handler_delete	5
 Handler_read_first	1
-Handler_read_key	9
+Handler_read_key	6
 Handler_read_rnd	5
 Handler_read_rnd_next	27
 Sort_rows	8
@@ -1410,7 +1367,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	100.00	Using where; Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1420,11 +1376,10 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_rnd_next	27
 Sort_rows	1
 Sort_scan	1
@@ -1432,7 +1387,7 @@ Sort_scan	1
 Variable_name	Value
 Handler_delete	1
 Handler_read_first	1
-Handler_read_key	5
+Handler_read_key	2
 Handler_read_rnd	1
 Handler_read_rnd_next	27
 Sort_rows	1
@@ -1461,7 +1416,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	a	a	15	NULL	X	X	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1471,17 +1425,16 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	3
+Handler_read_key	1
 Handler_read_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	5
 Handler_read_first	1
-Handler_read_key	3
+Handler_read_key	1
 Handler_read_next	4
 
 DROP TABLE t1, t2;
@@ -1508,7 +1461,6 @@ Warnings:
 Warning	1713	Cannot use range access on index 'a' due to type or collation conversion on field 'b'
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1518,11 +1470,10 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_rnd_next	27
 Sort_rows	1
 Sort_scan	1
@@ -1530,7 +1481,7 @@ Sort_scan	1
 Variable_name	Value
 Handler_delete	1
 Handler_read_first	1
-Handler_read_key	5
+Handler_read_key	2
 Handler_read_rnd	1
 Handler_read_rnd_next	27
 Sort_rows	1
@@ -1606,7 +1557,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	key1,key2	NULL	NULL	NULL	26	100.00	Using where; Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	4
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
@@ -1616,11 +1566,10 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	4
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	7
+Handler_read_key	1
 Handler_read_rnd_next	27
 Sort_rows	4
 Sort_scan	1
@@ -1628,7 +1577,7 @@ Sort_scan	1
 Variable_name	Value
 Handler_delete	4
 Handler_read_first	1
-Handler_read_key	11
+Handler_read_key	5
 Handler_read_rnd	4
 Handler_read_rnd_next	27
 Sort_rows	4
@@ -1656,25 +1605,23 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	8	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	2
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	8	100.00	Using index condition
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	8	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` desc limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	5
+Handler_read_key	1
 Handler_read_prev	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	5
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_prev	4
 
 DROP TABLE t1, t2;
@@ -1699,7 +1646,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5;
@@ -1709,11 +1655,10 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_rnd_next	27
 Sort_rows	5
 Sort_scan	1
@@ -1721,7 +1666,7 @@ Sort_scan	1
 Variable_name	Value
 Handler_delete	5
 Handler_read_first	1
-Handler_read_key	9
+Handler_read_key	6
 Handler_read_rnd	5
 Handler_read_rnd_next	27
 Sort_rows	26
@@ -1750,7 +1695,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	a	a	6	NULL	X	X	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
@@ -1760,16 +1704,15 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	3
+Handler_read_key	1
 Handler_read_last	1
 Handler_read_prev	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	5
-Handler_read_key	3
+Handler_read_key	1
 Handler_read_last	1
 Handler_read_prev	4
 
@@ -1795,24 +1738,22 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	X	X	Using where; Using temporary
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	2
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM  t2    WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	X	X	Using index condition
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	X	X	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	5
+Handler_read_key	1
 Handler_read_next	4
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	10
+Handler_read_key	6
 Handler_read_next	4
 Handler_read_rnd	5
 Handler_update	5
@@ -1842,7 +1783,6 @@ Warning	1713	Cannot use range access on 
 Warning	1713	Cannot use range access on index 'i' due to type or collation conversion on field 'i'
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2     WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
@@ -1856,18 +1796,17 @@ Warning	1713	Cannot use range access on 
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_rnd_next	27
 Sort_rows	5
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	9
+Handler_read_key	6
 Handler_read_rnd	5
 Handler_read_rnd_next	27
 Handler_update	5
@@ -1896,7 +1835,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	100.00	Using where; Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1906,18 +1844,17 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_rnd_next	27
 Sort_rows	1
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	5
+Handler_read_key	2
 Handler_read_rnd	1
 Handler_read_rnd_next	27
 Handler_update	1
@@ -1947,7 +1884,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	a	a	15	NULL	X	X	Using where; Using temporary
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -1957,16 +1893,15 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	3
+Handler_read_key	1
 Handler_read_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	9
+Handler_read_key	6
 Handler_read_next	4
 Handler_read_rnd	5
 Handler_update	5
@@ -1995,7 +1930,6 @@ Warnings:
 Warning	1713	Cannot use range access on index 'a' due to type or collation conversion on field 'b'
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2     WHERE b = 10 ORDER BY a, c LIMIT 5;
@@ -2005,18 +1939,17 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_rnd_next	27
 Sort_rows	1
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	5
+Handler_read_key	2
 Handler_read_rnd	1
 Handler_read_rnd_next	27
 Handler_update	1
@@ -2092,7 +2025,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	key1,key2	NULL	NULL	NULL	26	100.00	Using where; Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	4
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2      WHERE key1 < 13 or key2 < 14 ORDER BY key1;
@@ -2102,18 +2034,17 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	4
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	7
+Handler_read_key	1
 Handler_read_rnd_next	27
 Sort_rows	4
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	11
+Handler_read_key	5
 Handler_read_rnd	4
 Handler_read_rnd_next	27
 Handler_update	4
@@ -2142,24 +2073,22 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	8	100.00	Using where; Using temporary
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	2
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2     WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	8	100.00	Using index condition
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	8	100.00	Using where
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` desc limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	5
+Handler_read_key	1
 Handler_read_prev	4
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	10
+Handler_read_key	6
 Handler_read_prev	4
 Handler_read_rnd	5
 Handler_update	5
@@ -2186,7 +2115,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	26	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2     ORDER BY a, b DESC LIMIT 5;
@@ -2196,18 +2124,17 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_rnd_next	27
 Sort_rows	5
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	9
+Handler_read_key	6
 Handler_read_rnd	5
 Handler_read_rnd_next	27
 Handler_update	5
@@ -2237,7 +2164,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ALL	a	a	6	NULL	X	X	Using temporary
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t2     ORDER BY a DESC, b DESC LIMIT 5;
@@ -2247,15 +2173,14 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	3
+Handler_read_key	1
 Handler_read_last	1
 Handler_read_prev	4
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	9
+Handler_read_key	6
 Handler_read_last	1
 Handler_read_prev	4
 Handler_read_rnd	5
@@ -2285,7 +2210,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	range	PRIMARY,c1_idx	c1_idx	2	NULL	2	100.00	Using where; Using temporary
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	3
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1     WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
@@ -2295,14 +2219,13 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	5
+Handler_read_key	1
 Handler_read_prev	1
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	8
+Handler_read_key	3
 Handler_read_prev	1
 Handler_read_rnd	2
 Handler_update	2
@@ -2321,7 +2244,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	range	PRIMARY,c1_idx	c1_idx	2	NULL	2	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	3
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
@@ -2331,15 +2253,14 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	3
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	5
+Handler_read_key	1
 Handler_read_prev	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	2
-Handler_read_key	5
+Handler_read_key	1
 Handler_read_prev	1
 
 DROP TABLE t1;
@@ -2360,7 +2281,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using where; Using temporary
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	2
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1     WHERE a > 34;
@@ -2370,14 +2290,13 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 34)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	2
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_next	2
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_key	7
+Handler_read_key	3
 Handler_read_next	2
 Handler_read_rnd	2
 Handler_update	2
@@ -2416,12 +2335,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	3
-Handler_read_key	6
+Handler_read_key	3
 Handler_read_rnd_next	5
 
 #
@@ -2453,12 +2372,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	4
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	4
 
 DROP TABLE t1, t2;
@@ -2486,7 +2405,6 @@ Warnings:
 Note	1276	Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT              (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1;
@@ -2498,17 +2416,16 @@ Note	1276	Field or reference 'test.t1.f1
 Note	1003	/* select#1 */ select (/* select#2 */ select max(`test`.`t2`.`f4`) from `test`.`t2` where (`test`.`t2`.`f3` = `test`.`t1`.`f1`)) AS `(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1)` from `test`.`t1`
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	7
+Handler_read_key	3
 Handler_read_next	2
 Handler_read_rnd_next	3
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	7
+Handler_read_key	3
 Handler_read_next	2
 Handler_read_rnd_next	3
 Handler_update	2
@@ -2582,12 +2499,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	4
+Handler_read_key	2
 Handler_read_rnd_next	6
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	6
+Handler_read_key	3
 Handler_read_rnd	1
 Handler_read_rnd_next	9
 Handler_write	2
@@ -2624,12 +2541,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	3
-Handler_read_key	6
+Handler_read_key	3
 Handler_read_rnd_next	9
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	5
-Handler_read_key	13
+Handler_read_key	7
 Handler_read_rnd	2
 Handler_read_rnd_next	19
 Handler_update	1
@@ -2655,7 +2572,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	2
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM v1 WHERE a < 4;
@@ -2665,17 +2581,16 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 4)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	2
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_next	3
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	3
 Handler_read_first	1
-Handler_read_key	4
+Handler_read_key	1
 Handler_read_next	3
 
 DROP TABLE t1;
@@ -2702,7 +2617,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.x	1	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT *  FROM t2, v1 WHERE t2.x = v1.a;
@@ -2713,17 +2627,16 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,(`test`.`t1`.`b` + 1) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`x`)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	5
+Handler_read_key	2
 Handler_read_rnd_next	11
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	4
 Handler_read_first	1
-Handler_read_key	13
+Handler_read_key	9
 Handler_read_rnd	4
 Handler_read_rnd_next	5
 
@@ -2751,7 +2664,6 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.x	1	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_key	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT *  FROM t2, v1 WHERE t2.x = v1.a;
@@ -2762,17 +2674,16 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,(`test`.`t1`.`b` + 1) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`x`)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_key	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
-Handler_read_key	5
+Handler_read_key	2
 Handler_read_rnd_next	11
 # Status of testing query execution:
 Variable_name	Value
 Handler_delete	4
 Handler_read_first	1
-Handler_read_key	13
+Handler_read_key	9
 Handler_read_rnd	4
 Handler_read_rnd_next	5
 
@@ -2843,12 +2754,12 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_first	1
-Handler_read_key	2
+Handler_read_key	1
 Handler_read_rnd_next	1
 
 DROP TABLE t1, t2;
@@ -2871,53 +2782,41 @@ INSERT INTO t2 VALUES (1), (2), (3);
 EXPLAIN UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	<derived3>	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	<derived3>	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_first	2
-Handler_read_key	3
-Handler_read_rnd_next	5
-Handler_write	1
-Sort_rows	3
-Sort_scan	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1            WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	1	100.00	Materialize; Scan
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	5	test.t1.a	2	100.00	Using index; FirstMatch(t1)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where (`test`.`t1`.`a` = `x`.`b`)
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where (`x`.`b` = `test`.`t1`.`a`)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_first	1
-Handler_read_key	3
-Handler_read_rnd_next	4
-Handler_write	1
-Sort_rows	3
-Sort_scan	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
 Handler_read_key	5
-Handler_read_rnd_next	12
-Handler_write	2
+Handler_read_rnd_next	8
+Handler_write	1
 Sort_rows	3
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_first	3
-Handler_read_key	5
-Handler_read_rnd_next	9
+Handler_read_first	2
+Handler_read_key	2
+Handler_read_rnd_next	13
 Handler_update	1
 Handler_write	1
 Sort_rows	3
@@ -2929,49 +2828,37 @@ Sort_scan	1
 #
 EXPLAIN UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	1	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	5	test.t1.a	2	Using index; FirstMatch(t1)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (Block Nested Loop)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	1	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	5	test.t1.a	2	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (Block Nested Loop)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_first	1
-Handler_read_key	3
-Handler_read_rnd_next	4
-Handler_write	1
-Sort_rows	3
-Sort_scan	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1, t2            WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	1	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	5	test.t1.a	2	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (Block Nested Loop)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where (`test`.`t1`.`a` = `x`.`b`)
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where (`x`.`b` = `test`.`t1`.`a`)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
-Handler_read_first	1
-Handler_read_key	3
-Handler_read_rnd_next	4
-Handler_write	1
-Sort_rows	3
-Sort_scan	1
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_first	2
 Handler_read_key	5
-Handler_read_rnd_next	10
+Handler_read_rnd_next	8
 Handler_write	1
 Sort_rows	3
 Sort_scan	1
@@ -2979,7 +2866,7 @@ Sort_scan	1
 Variable_name	Value
 Handler_read_first	2
 Handler_read_key	5
-Handler_read_rnd_next	10
+Handler_read_rnd_next	8
 Handler_write	1
 Sort_rows	3
 Sort_scan	1
@@ -2990,61 +2877,49 @@ Sort_scan	1
 #
 EXPLAIN UPDATE        t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	1	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived4>	ref	auto_key0	auto_key0	5	test.t1.a	2	Using index; FirstMatch(t1)
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (Block Nested Loop)
 4	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	1	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<derived4>	ref	auto_key0	auto_key0	5	test.t1.a	2	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (Block Nested Loop)
 4	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
-Handler_read_first	2
-Handler_read_key	5
-Handler_read_rnd_next	8
-Handler_write	4
-Sort_rows	3
-Sort_scan	1
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2) y            WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	1	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	End temporary; Using join buffer (Block Nested Loop)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	<derived4>	ref	auto_key0	auto_key0	5	test.t1.a	2	100.00	Using index; FirstMatch(t1)
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (Block Nested Loop)
 4	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`y`.`b` AS `b` from `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `y` where (`test`.`t1`.`a` = `x`.`b`)
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`y`.`b` AS `b` from `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `y` where (`x`.`b` = `test`.`t1`.`a`)
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
+# Status of "equivalent" SELECT query execution:
+Variable_name	Value
 Handler_read_first	2
 Handler_read_key	5
 Handler_read_rnd_next	8
-Handler_write	4
-Sort_rows	3
-Sort_scan	1
-# Status of "equivalent" SELECT query execution:
-Variable_name	Value
-Handler_read_first	3
-Handler_read_key	7
-Handler_read_rnd_next	14
-Handler_write	4
+Handler_write	1
 Sort_rows	3
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
-Handler_read_first	3
-Handler_read_key	7
-Handler_read_rnd_next	14
-Handler_write	4
+Handler_read_first	2
+Handler_read_key	5
+Handler_read_rnd_next	8
+Handler_write	1
 Sort_rows	3
 Sort_scan	1
 
@@ -3063,7 +2938,7 @@ JOIN t1 AS a12 ON a12.c1 = a11.c1
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	100.00	
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	DERIVED	a11	index	PRIMARY	PRIMARY	4	NULL	1	100.00	Using index
 3	DERIVED	a21	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (Block Nested Loop)
 3	DERIVED	a12	eq_ref	PRIMARY	PRIMARY	4	test.a11.c1	1	100.00	Using index

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2011-12-01 14:12:10 +0000
+++ b/mysql-test/r/innodb_icp.result	2012-01-03 11:04:14 +0000
@@ -511,9 +511,9 @@ ON table3.col_varchar_key = table2.col_v
 ON table3.col_int_nokey = table1.col_int_key
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table2	index	col_varchar_key	col_varchar_key	9	NULL	19	Using where; Using index
+1	PRIMARY	table1	ALL	col_int_key	NULL	NULL	NULL	19	
+1	PRIMARY	table2	range	col_varchar_key	col_varchar_key	4	NULL	18	Using where; Using index; Using join buffer (Block Nested Loop)
 1	PRIMARY	table3	ref	col_varchar_key	col_varchar_key	4	test.table2.col_varchar_key	1	Using where
-1	PRIMARY	table1	ref	col_int_key	col_int_key	5	test.table3.col_int_nokey	1	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 SELECT table1.col_int_nokey
 FROM t1 AS table1 STRAIGHT_JOIN (

=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result	2011-10-13 11:00:58 +0000
+++ b/mysql-test/r/innodb_icp_all.result	2012-01-05 10:17:03 +0000
@@ -349,7 +349,7 @@ WHERE ts BETWEEN '0000-00-00' AND '2010-
 ORDER BY ts DESC
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using index condition
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where
 
 DROP TABLE t1;
 #
@@ -511,9 +511,9 @@ ON table3.col_varchar_key = table2.col_v
 ON table3.col_int_nokey = table1.col_int_key
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table2	index	col_varchar_key	col_varchar_key	9	NULL	19	Using where; Using index
+1	PRIMARY	table1	ALL	col_int_key	NULL	NULL	NULL	19	
+1	PRIMARY	table2	range	col_varchar_key	col_varchar_key	4	NULL	18	Using where; Using index; Using join buffer (Block Nested Loop)
 1	PRIMARY	table3	ref	col_varchar_key	col_varchar_key	4	test.table2.col_varchar_key	1	Using where
-1	PRIMARY	table1	ref	col_int_key	col_int_key	5	test.table3.col_int_nokey	1	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 SELECT table1.col_int_nokey
 FROM t1 AS table1 STRAIGHT_JOIN (
@@ -543,7 +543,7 @@ WHERE t1.pk < 317 AND 2 IN (SELECT COUNT
 FROM t2)
 ORDER BY t1.c2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Using where; Using filesort
+1	PRIMARY	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where; Using filesort
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
 SELECT t1.c1
 FROM t1
@@ -628,7 +628,7 @@ PRIMARY KEY (pk)
 INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
 EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using index condition
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where
 SET SESSION optimizer_switch='index_condition_pushdown=off';
 SELECT pk, c1 FROM t1 WHERE pk <> 3;
 pk	c1
@@ -709,8 +709,8 @@ EXPLAIN SELECT c2 FROM t1 JOIN t2 ON t1.
 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
 (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition
-1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using join buffer (Block Nested Loop)
 SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
 (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);

=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result	2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2012-01-03 11:04:14 +0000
@@ -510,9 +510,9 @@ ON table3.col_varchar_key = table2.col_v
 ON table3.col_int_nokey = table1.col_int_key
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table2	index	col_varchar_key	col_varchar_key	9	NULL	19	Using where; Using index
+1	PRIMARY	table1	ALL	col_int_key	NULL	NULL	NULL	19	
+1	PRIMARY	table2	range	col_varchar_key	col_varchar_key	4	NULL	18	Using where; Using index; Using join buffer (Block Nested Loop)
 1	PRIMARY	table3	ref	col_varchar_key	col_varchar_key	4	test.table2.col_varchar_key	1	Using where
-1	PRIMARY	table1	ref	col_int_key	col_int_key	5	test.table3.col_int_nokey	1	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 SELECT table1.col_int_nokey
 FROM t1 AS table1 STRAIGHT_JOIN (

=== modified file 'mysql-test/r/innodb_mrr_all.result'
--- a/mysql-test/r/innodb_mrr_all.result	2011-12-01 11:30:35 +0000
+++ b/mysql-test/r/innodb_mrr_all.result	2011-12-16 09:47:20 +0000
@@ -567,7 +567,7 @@ FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t
 WHERE t1.pk > 176;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Using where
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where
 SELECT STRAIGHT_JOIN t1.c1
 FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
 WHERE t1.pk > 176;

=== modified file 'mysql-test/r/innodb_mrr_cost_all.result'
--- a/mysql-test/r/innodb_mrr_cost_all.result	2011-12-01 11:30:35 +0000
+++ b/mysql-test/r/innodb_mrr_cost_all.result	2011-12-16 09:47:20 +0000
@@ -567,7 +567,7 @@ FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t
 WHERE t1.pk > 176;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Using where
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where
 SELECT STRAIGHT_JOIN t1.c1
 FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
 WHERE t1.pk > 176;

=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result	2011-11-22 10:48:41 +0000
+++ b/mysql-test/r/join.result	2012-01-03 11:04:14 +0000
@@ -1306,3 +1306,42 @@ pk
 8
 DROP VIEW v1;
 DROP TABLE t1;
+#
+# BUG#11752239 - 43368: STRAIGHT_JOIN DOESN'T WORK FOR NESTED JOINS 
+#
+create table t1(c1 int primary key, c2 char(10)) engine=myisam;
+create table t2(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+create table t3(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+create table t4(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+insert into t1 values(1,'a');
+insert into t2 values(1,'a', 1);
+insert into t3 values(1,'a', 1);
+insert into t3 values(2,'b',2);
+insert into t4 values(1,'a', 1);
+insert into t4 values(2,'a', 2);
+insert into t4 values(3,'a', 3);
+insert into t4 values(4,'a', 4);
+insert into t1 values(2,'b');
+insert into t1 values(3,'c');
+EXPLAIN SELECT * FROM t4 JOIN (t1 join t3 on t3.ref_t1 =
+t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t4 JOIN (t1 join t3 on t3.ref_t1 =
+t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+EXPLAIN SELECT * FROM t4 STRAIGHT_JOIN (t1 join t3 on t3.ref_t1 =
+t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t4.ref_t1	1	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+drop table t1,t2,t3,t4;

=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2011-10-17 13:52:27 +0000
+++ b/mysql-test/r/join_outer.result	2012-01-03 11:04:14 +0000
@@ -1807,3 +1807,192 @@ field1
 20
 
 drop table t1,t2,t3;
+# Bug#11766384 - 59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
+CREATE TABLE t1 (
+pk int(11) NOT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (1,'1');
+CREATE TABLE t2 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+col_int int(11) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t4 VALUES (1,1,1,'1');
+CREATE TABLE t5 (
+col_int int(11) DEFAULT NULL,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t5 VALUES (1,'1');
+CREATE TABLE t6 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t6 VALUES (1,'1',1);
+SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk	pk
+1	NULL
+EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6a	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (Block Nested Loop)
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t6b	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk	pk
+1	NULL
+EXPLAIN SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6a	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (Block Nested Loop)
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t6b	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+drop table t1,t2,t3,t4,t5,t6;

=== modified file 'mysql-test/r/join_outer_bka.result'
--- a/mysql-test/r/join_outer_bka.result	2011-10-17 13:52:27 +0000
+++ b/mysql-test/r/join_outer_bka.result	2012-01-03 11:04:14 +0000
@@ -1808,4 +1808,193 @@ field1
 20
 
 drop table t1,t2,t3;
+# Bug#11766384 - 59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
+CREATE TABLE t1 (
+pk int(11) NOT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (1,'1');
+CREATE TABLE t2 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+col_int int(11) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t4 VALUES (1,1,1,'1');
+CREATE TABLE t5 (
+col_int int(11) DEFAULT NULL,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t5 VALUES (1,'1');
+CREATE TABLE t6 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t6 VALUES (1,'1',1);
+SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk	pk
+1	NULL
+EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6a	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (Block Nested Loop)
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t6b	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk	pk
+1	NULL
+EXPLAIN SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6a	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (Block Nested Loop)
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t6b	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
+drop table t1,t2,t3,t4,t5,t6;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/join_outer_bka_nixbnl.result'
--- a/mysql-test/r/join_outer_bka_nixbnl.result	2011-10-17 13:52:27 +0000
+++ b/mysql-test/r/join_outer_bka_nixbnl.result	2012-01-03 11:04:14 +0000
@@ -1808,4 +1808,193 @@ field1
 20
 
 drop table t1,t2,t3;
+# Bug#11766384 - 59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
+CREATE TABLE t1 (
+pk int(11) NOT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (1,'1');
+CREATE TABLE t2 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+col_int int(11) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t4 VALUES (1,1,1,'1');
+CREATE TABLE t5 (
+col_int int(11) DEFAULT NULL,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t5 VALUES (1,'1');
+CREATE TABLE t6 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t6 VALUES (1,'1',1);
+SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk	pk
+1	NULL
+EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6a	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t6b	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+pk	pk
+1	NULL
+EXPLAIN SELECT t6a.pk, t2.pk
+FROM
+t6 AS t6a
+LEFT JOIN
+(
+t2
+RIGHT JOIN
+(
+(
+t1
+LEFT JOIN
+(
+t4
+JOIN
+t3
+ON t4.col_int
+)
+ON t4.col_int_key = t1.pk
+)
+LEFT JOIN
+(
+t5
+JOIN
+t6 AS t6b
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6a	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t6b	ALL	NULL	NULL	NULL	NULL	1	Using where
+drop table t1,t2,t3,t4,t5,t6;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/myisam_icp.result	2012-01-03 11:04:14 +0000
@@ -509,9 +509,9 @@ ON table3.col_varchar_key = table2.col_v
 ON table3.col_int_nokey = table1.col_int_key
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table3	ALL	col_varchar_key	NULL	NULL	NULL	19	Using where
+1	PRIMARY	table1	ALL	col_int_key	NULL	NULL	NULL	19	
+1	PRIMARY	table3	ALL	col_varchar_key	NULL	NULL	NULL	19	Using where; Using join buffer (Block Nested Loop)
 1	PRIMARY	table2	ref	col_varchar_key	col_varchar_key	4	test.table3.col_varchar_key	2	Using index
-1	PRIMARY	table1	ref	col_int_key	col_int_key	5	test.table3.col_int_nokey	2	
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT table1.col_int_nokey
 FROM t1 AS table1 STRAIGHT_JOIN (

=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result	2011-10-13 11:00:58 +0000
+++ b/mysql-test/r/myisam_icp_all.result	2012-01-03 11:04:14 +0000
@@ -509,9 +509,9 @@ ON table3.col_varchar_key = table2.col_v
 ON table3.col_int_nokey = table1.col_int_key
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table3	ALL	col_varchar_key	NULL	NULL	NULL	19	Using where
+1	PRIMARY	table1	ALL	col_int_key	NULL	NULL	NULL	19	
+1	PRIMARY	table3	ALL	col_varchar_key	NULL	NULL	NULL	19	Using where; Using join buffer (Block Nested Loop)
 1	PRIMARY	table2	ref	col_varchar_key	col_varchar_key	4	test.table3.col_varchar_key	2	Using index
-1	PRIMARY	table1	ref	col_int_key	col_int_key	5	test.table3.col_int_nokey	2	
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT table1.col_int_nokey
 FROM t1 AS table1 STRAIGHT_JOIN (

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2012-01-03 11:04:14 +0000
@@ -508,9 +508,9 @@ ON table3.col_varchar_key = table2.col_v
 ON table3.col_int_nokey = table1.col_int_key
 WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table3	ALL	col_varchar_key	NULL	NULL	NULL	19	Using where
+1	PRIMARY	table1	ALL	col_int_key	NULL	NULL	NULL	19	
+1	PRIMARY	table3	ALL	col_varchar_key	NULL	NULL	NULL	19	Using where; Using join buffer (Block Nested Loop)
 1	PRIMARY	table2	ref	col_varchar_key	col_varchar_key	4	test.table3.col_varchar_key	2	Using index
-1	PRIMARY	table1	ref	col_int_key	col_int_key	5	test.table3.col_int_nokey	2	
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT table1.col_int_nokey
 FROM t1 AS table1 STRAIGHT_JOIN (

=== modified file 'mysql-test/r/select_all_bka.result'
--- a/mysql-test/r/select_all_bka.result	2011-09-07 10:21:50 +0000
+++ b/mysql-test/r/select_all_bka.result	2011-12-16 10:57:51 +0000
@@ -5009,5 +5009,23 @@ avg(distinct(t1.a))
 0
 DROP TABLE t1;
 # End of test BUG#57203
+#
+# Bug#63020: Function "format"'s 'locale' argument is not considered
+#	     when creating a "view'
+#
+CREATE TABLE t1 (f1 DECIMAL(10,2));
+INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92);
+CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1;
+SHOW CREATE VIEW view_t1;
+View	Create View	character_set_client	collation_connection
+view_t1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t1` AS select format(`t1`.`f1`,1,'sk_SK') AS `f1` from `t1`	latin1	latin1_swedish_ci
+SELECT * FROM view_t1;
+f1
+11,7
+17 865,3
+12 345 678,9
+DROP TABLE t1;
+DROP VIEW view_t1;
+# End of test  BUG#63020
 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-10-05 13:16:38 +0000
+++ b/mysql-test/r/select_all_bka_nixbnl.result	2011-12-16 10:57:51 +0000
@@ -5009,5 +5009,23 @@ avg(distinct(t1.a))
 0
 DROP TABLE t1;
 # End of test BUG#57203
+#
+# Bug#63020: Function "format"'s 'locale' argument is not considered
+#	     when creating a "view'
+#
+CREATE TABLE t1 (f1 DECIMAL(10,2));
+INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92);
+CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1;
+SHOW CREATE VIEW view_t1;
+View	Create View	character_set_client	collation_connection
+view_t1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t1` AS select format(`t1`.`f1`,1,'sk_SK') AS `f1` from `t1`	latin1	latin1_swedish_ci
+SELECT * FROM view_t1;
+f1
+11,7
+17 865,3
+12 345 678,9
+DROP TABLE t1;
+DROP VIEW view_t1;
+# End of test  BUG#63020
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/suite/opt_trace/include/general2.inc'
--- a/mysql-test/suite/opt_trace/include/general2.inc	2011-11-08 07:51:49 +0000
+++ b/mysql-test/suite/opt_trace/include/general2.inc	2012-01-03 11:04:14 +0000
@@ -156,9 +156,6 @@ SELECT t1.f2 FROM t1
 STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2  = t2.f2  ) ON t3 .f2  = t2 .f2
 HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
 ORDER BY f2;
---echo Maps should say that t2 and t3 depend on t1
---echo (because of straight_join), they don't, this is
---echo bug#11766858 and bug#11752239
 --replace_regex /("sort_buffer_size":) [0-9]+/\1 NNN/
 select TRACE from information_schema.OPTIMIZER_TRACE;
 

=== modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_no_prot.result	2011-11-30 11:36:14 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result	2012-01-03 11:04:14 +0000
@@ -675,9 +675,6 @@ f
 f
 f
 f
-Maps should say that t2 and t3 depend on t1
-(because of straight_join), they don't, this is
-bug#11766858 and bug#11752239
 select TRACE from information_schema.OPTIMIZER_TRACE;
 TRACE
 {
@@ -797,6 +794,7 @@ TRACE
                 "row_may_be_null": false,
                 "map_bit": 1,
                 "depends_on_map_bits": [
+                  0
                 ] /* depends_on_map_bits */
               },
               {
@@ -805,6 +803,7 @@ TRACE
                 "row_may_be_null": false,
                 "map_bit": 2,
                 "depends_on_map_bits": [
+                  0
                 ] /* depends_on_map_bits */
               }
             ] /* table_dependencies */
@@ -834,9 +833,10 @@ TRACE
               {
                 "database": "test",
                 "table": "t3",
-                "rows": 1,
-                "cost": 1,
-                "table_type": "system"
+                "table_scan": {
+                  "rows": 1,
+                  "cost": 2
+                } /* table_scan */
               }
             ] /* rows_estimation */
           },
@@ -861,6 +861,45 @@ TRACE
                 "rest_of_plan": [
                   {
                     "database": "test",
+                    "table": "t3",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "scan",
+                          "using_join_cache": true,
+                          "rows": 1,
+                          "cost": 2.005,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 4.8147,
+                    "rows_for_plan": 2,
+                    "rest_of_plan": [
+                      {
+                        "database": "test",
+                        "table": "t2",
+                        "best_access_path": {
+                          "considered_access_paths": [
+                            {
+                              "access_type": "scan",
+                              "using_join_cache": true,
+                              "rows": 2,
+                              "cost": 2.0099,
+                              "chosen": true
+                            }
+                          ] /* considered_access_paths */
+                        } /* best_access_path */,
+                        "cost_for_plan": 7.6247,
+                        "rows_for_plan": 4,
+                        "sort_cost": 4,
+                        "new_cost_for_plan": 11.625,
+                        "chosen": true
+                      }
+                    ] /* rest_of_plan */
+                  },
+                  {
+                    "database": "test",
                     "table": "t2",
                     "best_access_path": {
                       "considered_access_paths": [
@@ -875,34 +914,15 @@ TRACE
                     } /* best_access_path */,
                     "cost_for_plan": 5.2196,
                     "rows_for_plan": 4,
-                    "sort_cost": 4,
-                    "new_cost_for_plan": 9.2196,
-                    "chosen": true
+                    "pruned_by_heuristic": true
                   }
                 ] /* rest_of_plan */
-              },
-              {
-                "database": "test",
-                "table": "t2",
-                "best_access_path": {
-                  "considered_access_paths": [
-                    {
-                      "access_type": "scan",
-                      "rows": 2,
-                      "cost": 2.0098,
-                      "chosen": true
-                    }
-                  ] /* considered_access_paths */
-                } /* best_access_path */,
-                "cost_for_plan": 2.4098,
-                "rows_for_plan": 2,
-                "pruned_by_heuristic": true
               }
             ] /* considered_execution_plans */
           },
           {
             "attaching_conditions_to_tables": {
-              "original_condition": "(`test`.`t2`.`f2` = 'f')",
+              "original_condition": "(`test`.`t2`.`f2` = `test`.`t3`.`f2`)",
               "attached_conditions_computation": [
               ] /* attached_conditions_computation */,
               "attached_conditions_summary": [
@@ -913,8 +933,13 @@ TRACE
                 },
                 {
                   "database": "test",
+                  "table": "t3",
+                  "attached": null
+                },
+                {
+                  "database": "test",
                   "table": "t2",
-                  "attached": "(`test`.`t2`.`f2` = 'f')"
+                  "attached": "(`test`.`t2`.`f2` = `test`.`t3`.`f2`)"
                 }
               ] /* attached_conditions_summary */
             } /* attaching_conditions_to_tables */
@@ -941,6 +966,11 @@ TRACE
               },
               {
                 "database": "test",
+                "table": "t3",
+                "access_type": "table_scan"
+              },
+              {
+                "database": "test",
                 "table": "t2",
                 "access_type": "table_scan"
               }

=== modified file 'mysql-test/suite/opt_trace/r/general2_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_ps_prot.result	2011-11-30 11:36:14 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_ps_prot.result	2012-01-03 11:04:14 +0000
@@ -703,9 +703,6 @@ f
 f
 f
 f
-Maps should say that t2 and t3 depend on t1
-(because of straight_join), they don't, this is
-bug#11766858 and bug#11752239
 select TRACE from information_schema.OPTIMIZER_TRACE;
 TRACE
 {
@@ -815,6 +812,7 @@ TRACE
                 "row_may_be_null": false,
                 "map_bit": 1,
                 "depends_on_map_bits": [
+                  0
                 ] /* depends_on_map_bits */
               },
               {
@@ -823,6 +821,7 @@ TRACE
                 "row_may_be_null": false,
                 "map_bit": 2,
                 "depends_on_map_bits": [
+                  0
                 ] /* depends_on_map_bits */
               }
             ] /* table_dependencies */
@@ -852,9 +851,10 @@ TRACE
               {
                 "database": "test",
                 "table": "t3",
-                "rows": 1,
-                "cost": 1,
-                "table_type": "system"
+                "table_scan": {
+                  "rows": 1,
+                  "cost": 2
+                } /* table_scan */
               }
             ] /* rows_estimation */
           },
@@ -879,6 +879,45 @@ TRACE
                 "rest_of_plan": [
                   {
                     "database": "test",
+                    "table": "t3",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "scan",
+                          "using_join_cache": true,
+                          "rows": 1,
+                          "cost": 2.005,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 4.8147,
+                    "rows_for_plan": 2,
+                    "rest_of_plan": [
+                      {
+                        "database": "test",
+                        "table": "t2",
+                        "best_access_path": {
+                          "considered_access_paths": [
+                            {
+                              "access_type": "scan",
+                              "using_join_cache": true,
+                              "rows": 2,
+                              "cost": 2.0099,
+                              "chosen": true
+                            }
+                          ] /* considered_access_paths */
+                        } /* best_access_path */,
+                        "cost_for_plan": 7.6247,
+                        "rows_for_plan": 4,
+                        "sort_cost": 4,
+                        "new_cost_for_plan": 11.625,
+                        "chosen": true
+                      }
+                    ] /* rest_of_plan */
+                  },
+                  {
+                    "database": "test",
                     "table": "t2",
                     "best_access_path": {
                       "considered_access_paths": [
@@ -893,34 +932,15 @@ TRACE
                     } /* best_access_path */,
                     "cost_for_plan": 5.2196,
                     "rows_for_plan": 4,
-                    "sort_cost": 4,
-                    "new_cost_for_plan": 9.2196,
-                    "chosen": true
+                    "pruned_by_heuristic": true
                   }
                 ] /* rest_of_plan */
-              },
-              {
-                "database": "test",
-                "table": "t2",
-                "best_access_path": {
-                  "considered_access_paths": [
-                    {
-                      "access_type": "scan",
-                      "rows": 2,
-                      "cost": 2.0098,
-                      "chosen": true
-                    }
-                  ] /* considered_access_paths */
-                } /* best_access_path */,
-                "cost_for_plan": 2.4098,
-                "rows_for_plan": 2,
-                "pruned_by_heuristic": true
               }
             ] /* considered_execution_plans */
           },
           {
             "attaching_conditions_to_tables": {
-              "original_condition": "(`test`.`t2`.`f2` = 'f')",
+              "original_condition": "(`test`.`t2`.`f2` = `test`.`t3`.`f2`)",
               "attached_conditions_computation": [
               ] /* attached_conditions_computation */,
               "attached_conditions_summary": [
@@ -931,8 +951,13 @@ TRACE
                 },
                 {
                   "database": "test",
+                  "table": "t3",
+                  "attached": null
+                },
+                {
+                  "database": "test",
                   "table": "t2",
-                  "attached": "(`test`.`t2`.`f2` = 'f')"
+                  "attached": "(`test`.`t2`.`f2` = `test`.`t3`.`f2`)"
                 }
               ] /* attached_conditions_summary */
             } /* attaching_conditions_to_tables */
@@ -959,6 +984,11 @@ TRACE
               },
               {
                 "database": "test",
+                "table": "t3",
+                "access_type": "table_scan"
+              },
+              {
+                "database": "test",
                 "table": "t2",
                 "access_type": "table_scan"
               }

=== modified file 'mysql-test/suite/opt_trace/r/temp_table.result'
--- a/mysql-test/suite/opt_trace/r/temp_table.result	2011-11-21 08:51:57 +0000
+++ b/mysql-test/suite/opt_trace/r/temp_table.result	2012-01-04 09:51:39 +0000
@@ -133,7 +133,7 @@ SELECT uniq, col1 FROM t1 GROUP BY col2,
                 "key_length": 1040,
                 "unique_constraint": true,
                 "location": "disk (MyISAM)",
-                "record_format": "fixed"
+                "record_format": "packed"
               } /* tmp_table_info */
             } /* creating_tmp_table */
           },
@@ -153,7 +153,7 @@ SELECT uniq, col1 FROM t1 GROUP BY col2,
             "filesort_priority_queue_optimization": {
               "limit": 3,
               "rows_estimate": 18,
-              "row_size": 1043,
+              "row_size": 1042,
               "memory_available": 262144,
               "chosen": true
             } /* filesort_priority_queue_optimization */,
@@ -163,7 +163,7 @@ SELECT uniq, col1 FROM t1 GROUP BY col2,
               "rows": 4,
               "examined_rows": 8,
               "number_of_tmp_files": 0,
-              "sort_buffer_size": 4204,
+              "sort_buffer_size": 4200,
               "sort_mode": "<sort_key, rowid>"
             } /* filesort_summary */
           }
@@ -484,7 +484,7 @@ SELECT uniq, col1, col2 FROM t1 GROUP BY
                 "key_length": 13,
                 "unique_constraint": false,
                 "location": "disk (MyISAM)",
-                "record_format": "fixed"
+                "record_format": "packed"
               } /* tmp_table_info */
             } /* converting_tmp_table_to_myisam */
           },
@@ -506,7 +506,7 @@ SELECT uniq, col1, col2 FROM t1 GROUP BY
               "rows": 16,
               "examined_rows": 16,
               "number_of_tmp_files": 0,
-              "sort_buffer_size": 676,
+              "sort_buffer_size": 650,
               "sort_mode": "<sort_key, rowid>"
             } /* filesort_summary */
           }

=== modified file 'mysql-test/t/distinct.test'
--- a/mysql-test/t/distinct.test	2011-11-02 12:47:31 +0000
+++ b/mysql-test/t/distinct.test	2012-01-04 13:53:33 +0000
@@ -652,3 +652,56 @@ ORDER BY col_time_key
 LIMIT 3;
 
 DROP TABLE t1;
+
+--echo #
+--echo # BUG#13540692: WRONG NULL HANDLING WITH RIGHT JOIN + 
+--echo #               DISTINCT OR ORDER BY
+--echo #
+
+CREATE TABLE t1 (
+  a INT,
+  b INT NOT NULL
+);
+INSERT INTO t1 VALUES (1,2), (3,3);
+
+let $query=
+SELECT DISTINCT subselect.b
+FROM t1 LEFT JOIN 
+     (SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect 
+     ON t1.a = subselect.b
+;
+
+--echo
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1;
+
+--echo #
+--echo # BUG#13538387: WRONG RESULT ON SELECT DISTINCT + LEFT JOIN + 
+--echo #               LIMIT + MIX OF MYISAM AND INNODB
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (2),(3);
+
+CREATE TABLE t2 (b INT);
+
+CREATE TABLE t3 (
+  a INT,
+  b INT,
+  PRIMARY KEY (b)
+);
+INSERT INTO t3 VALUES (2001,1), (2007,2);
+
+let $query=
+SELECT DISTINCT t3.a AS t3_date
+FROM t1
+     LEFT JOIN t2 ON false
+     LEFT JOIN t3 ON t2.b = t3.b
+LIMIT 1;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2011-11-17 10:09:13 +0000
+++ b/mysql-test/t/group_by.test	2012-01-04 13:53:33 +0000
@@ -1538,3 +1538,74 @@ LIMIT 3;
 
 DROP VIEW v1;
 DROP TABLE t1;
+
+--echo #
+--echo # Bug#13422961: WRONG RESULTS FROM SELECT WITH AGGREGATES AND
+--echo #               IMPLICIT GROUPING + MYISAM OR MEM
+--echo #
+
+CREATE TABLE it (
+  pk INT NOT NULL,
+  col_int_nokey INT NOT NULL,
+  PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+
+CREATE TABLE ot (
+  pk int(11) NOT NULL,
+  col_int_nokey int(11) NOT NULL,
+  PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO ot VALUES (10,8);
+
+--echo
+SELECT col_int_nokey, MAX( pk ) 
+FROM ot 
+WHERE (8, 1) IN ( SELECT pk, COUNT( col_int_nokey ) FROM it );
+
+--echo
+DROP TABLE it,ot;
+
+--echo #
+--echo # Bug#13430588: WRONG RESULT FROM IMPLICITLY GROUPED QUERY WITH
+--echo #               CONST TABLE AND NO MATCHING ROWS
+--echo #
+CREATE TABLE t1 (i INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (j INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+
+--echo
+SELECT i, j, COUNT(i) FROM t1 JOIN t2 WHERE j=3;
+
+--echo
+DROP TABLE t1,t2;
+
+--echo #
+--echo # BUG#13541761: WRONG RESULTS ON CORRELATED SUBQUERY + 
+--echo #               AGGREGATE FUNCTION + MYISAM OR MEMORY
+--echo #
+
+CREATE TABLE t1 (
+  a varchar(1)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES ('a'), ('b');
+
+CREATE TABLE t2 (
+  a varchar(1),
+  b int(11)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES ('a',1);
+
+let $query= 
+SELECT (SELECT MAX(b) FROM t2 WHERE t2.a != t1.a) as MAX 
+FROM t1;
+
+--echo
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1,t2;
+

=== modified file 'mysql-test/t/having.test'
--- a/mysql-test/t/having.test	2011-11-25 14:07:13 +0000
+++ b/mysql-test/t/having.test	2011-12-20 12:04:31 +0000
@@ -667,4 +667,25 @@ HAVING x < 2;
 DROP TABLE it,ot;
 DROP FUNCTION f;
 
+--echo #
+--echo # Bug#11760517: MIN/MAX FAILS TO EVALUATE HAVING CONDITION, 
+--echo #               RETURNS INCORRECT NULL RESULT
+--echo #
+CREATE TABLE t1 (pk INT PRIMARY KEY, i4 INT);
+INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);
+
+--echo
+SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
+FROM t1 as table1, t1 as table2
+WHERE table1.pk = 1;
+
+--echo
+SELECT MIN(table1.i4), MIN(table2.pk) as min_pk
+FROM t1 as table1, t1 as table2
+WHERE table1.pk = 1
+HAVING min_pk <= 10;
+
+--echo
+DROP TABLE t1;
+
 --echo End of 5.6 tests

=== modified file 'mysql-test/t/join.test'
--- a/mysql-test/t/join.test	2011-11-22 10:48:41 +0000
+++ b/mysql-test/t/join.test	2012-01-03 11:04:14 +0000
@@ -1015,3 +1015,30 @@ eval $query;
 
 DROP VIEW v1;
 DROP TABLE t1;
+
+--echo #
+--echo # BUG#11752239 - 43368: STRAIGHT_JOIN DOESN'T WORK FOR NESTED JOINS 
+--echo #
+create table t1(c1 int primary key, c2 char(10)) engine=myisam;
+create table t2(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+create table t3(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+create table t4(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+insert into t1 values(1,'a');
+insert into t2 values(1,'a', 1);
+insert into t3 values(1,'a', 1);
+insert into t3 values(2,'b',2);
+insert into t4 values(1,'a', 1);
+insert into t4 values(2,'a', 2);
+insert into t4 values(3,'a', 3);
+insert into t4 values(4,'a', 4);
+insert into t1 values(2,'b');
+insert into t1 values(3,'c');
+
+let $rest_of_query=(t1 join t3 on t3.ref_t1 =
+t1.c1 join t2 on t2.ref_t1 = t1.c1) on t4.ref_t1 = t1.c1;
+
+eval EXPLAIN SELECT * FROM t4 JOIN $rest_of_query;
+eval EXPLAIN SELECT STRAIGHT_JOIN * FROM t4 JOIN $rest_of_query;
+eval EXPLAIN SELECT * FROM t4 STRAIGHT_JOIN $rest_of_query;
+
+drop table t1,t2,t3,t4;

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2011-10-17 13:52:27 +0000
+++ b/mysql-test/t/join_outer.test	2012-01-03 11:04:14 +0000
@@ -1408,3 +1408,84 @@ FROM t1;
 
 --echo
 drop table t1,t2,t3;
+
+--echo # Bug#11766384 - 59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
+
+CREATE TABLE t1 (
+  pk int(11) NOT NULL,
+  col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (1,'1');
+CREATE TABLE t2 (
+  pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+  pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (
+  pk int(11) NOT NULL,
+  col_int int(11) DEFAULT NULL,
+  col_int_key int(11) DEFAULT NULL,
+  col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t4 VALUES (1,1,1,'1');
+CREATE TABLE t5 (
+  col_int int(11) DEFAULT NULL,
+  col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t5 VALUES (1,'1');
+CREATE TABLE t6 (
+  col_int_key int(11) DEFAULT NULL,
+  col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+  pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t6 VALUES (1,'1',1);
+
+# EXPLAIN of query above (t2 is before t5 in plan)
+
+let $rest_of_query=t6a.pk, t2.pk
+FROM
+  t6 AS t6a
+  LEFT JOIN
+  (
+    t2
+    RIGHT JOIN
+    (
+      (
+        t1
+        LEFT JOIN
+        (
+          t4
+          JOIN
+          t3
+          ON t4.col_int
+        )
+        ON t4.col_int_key = t1.pk
+      )
+      LEFT JOIN
+      (
+        t5
+        JOIN
+        t6 AS t6b
+        ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
+      )
+      ON t1.pk = t5.col_int
+    )
+    ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+       AND t5.col_varchar_10_utf8_key = 0
+  )
+  ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE
+;
+
+eval SELECT STRAIGHT_JOIN $rest_of_query;
+eval EXPLAIN SELECT STRAIGHT_JOIN $rest_of_query;
+
+# right result (same query, just remove STRAIGHT_JOIN):
+
+eval SELECT $rest_of_query;
+eval EXPLAIN SELECT $rest_of_query;
+
+drop table t1,t2,t3,t4,t5,t6;

=== modified file 'sql/field.h'
--- a/sql/field.h	2011-12-15 12:12:14 +0000
+++ b/sql/field.h	2011-12-20 12:04:31 +0000
@@ -642,14 +642,24 @@ public:
   inline bool is_null(my_ptrdiff_t row_offset= 0)
   {
     /*
-      If the field is NULLable, it has a valid null_ptr pointer, and its
-      NULLity is recorded in the "null_bit" bit of null_ptr[row_offset].
-      Otherwise, it can still be NULL, if it belongs to the inner table of an
-      outer join and the row is NULL-complemented: that case is recorded in
-      TABLE::null_row.
+      The table may have been marked as containing only NULL values
+      for all fields if it is a NULL-complemented row of an OUTER JOIN
+      or if the query is an implicitly grouped query (has aggregate
+      functions but no GROUP BY clause) with no qualifying rows. If
+      this is the case (in which TABLE::null_row is true), the field
+      is considered to be NULL.
+
+      Otherwise, if the field is NULLable, it has a valid null_ptr
+      pointer, and its NULLity is recorded in the "null_bit" bit of
+      null_ptr[row_offset].
     */
-    return null_ptr ? (null_ptr[row_offset] & null_bit ? 1 : 0) :
-      table->null_row;
+    if (table->null_row)
+      return true;
+
+    if (null_ptr)
+      return (null_ptr[row_offset] & null_bit);
+
+    return false;
   }
   inline bool is_real_null(my_ptrdiff_t row_offset= 0)
     { return null_ptr ? (null_ptr[row_offset] & null_bit ? 1 : 0) : 0; }

=== modified file 'sql/field_conv.cc'
--- a/sql/field_conv.cc	2011-12-15 12:12:14 +0000
+++ b/sql/field_conv.cc	2012-01-04 13:53:33 +0000
@@ -82,24 +82,8 @@ static void do_field_8(Copy_field *copy)
   copy->to_ptr[7]=copy->from_ptr[7];
 }
 
-
 static void do_field_to_null_str(Copy_field *copy)
 {
-  if (*copy->from_null_ptr & copy->from_bit)
-  {
-    memset(copy->to_ptr, 0, copy->from_length);
-    copy->to_null_ptr[0]=1;			// Always bit 1
-  }
-  else
-  {
-    copy->to_null_ptr[0]=0;
-    memcpy(copy->to_ptr,copy->from_ptr,copy->from_length);
-  }
-}
-
-
-static void do_outer_field_to_null_str(Copy_field *copy)
-{
   if (*copy->null_row ||
       (copy->from_null_ptr && (*copy->from_null_ptr & copy->from_bit)))
   {
@@ -211,21 +195,6 @@ static void do_skip(Copy_field *copy __a
 
 static void do_copy_null(Copy_field *copy)
 {
-  if (*copy->from_null_ptr & copy->from_bit)
-  {
-    *copy->to_null_ptr|=copy->to_bit;
-    copy->to_field->reset();
-  }
-  else
-  {
-    *copy->to_null_ptr&= ~copy->to_bit;
-    (copy->do_copy2)(copy);
-  }
-}
-
-
-static void do_outer_field_null(Copy_field *copy)
-{
   if (*copy->null_row ||
       (copy->from_null_ptr && (*copy->from_null_ptr & copy->from_bit)))
   {
@@ -242,7 +211,7 @@ static void do_outer_field_null(Copy_fie
 
 static void do_copy_not_null(Copy_field *copy)
 {
-  if (*copy->from_null_ptr & copy->from_bit)
+  if (*copy->null_row || (*copy->from_null_ptr & copy->from_bit))
   {
     copy->to_field->set_warning(Sql_condition::WARN_LEVEL_WARN,
                                 WARN_DATA_TRUNCATED, 1);
@@ -263,7 +232,7 @@ static void do_copy_maybe_null(Copy_fiel
 
 static void do_copy_timestamp(Copy_field *copy)
 {
-  if (*copy->from_null_ptr & copy->from_bit)
+  if (*copy->null_row || (*copy->from_null_ptr & copy->from_bit))
   {
     /* Same as in set_field_to_null_with_conversions() */
     copy->to_field->set_time();
@@ -275,7 +244,7 @@ static void do_copy_timestamp(Copy_field
 
 static void do_copy_next_number(Copy_field *copy)
 {
-  if (*copy->from_null_ptr & copy->from_bit)
+  if (*copy->null_row || (*copy->from_null_ptr & copy->from_bit))
   {
     /* Same as in set_field_to_null_with_conversions() */
     copy->to_field->table->auto_increment_field_not_null= FALSE;
@@ -574,6 +543,7 @@ void Copy_field::set(uchar *to,Field *fr
   from_ptr=from->ptr;
   to_ptr=to;
   from_length=from->pack_length();
+  null_row= &from->table->null_row;
   if (from->maybe_null())
   {
     from_null_ptr=from->null_ptr;
@@ -581,13 +551,7 @@ void Copy_field::set(uchar *to,Field *fr
     to_ptr[0]=	  1;				// Null as default value
     to_null_ptr=  (uchar*) to_ptr++;
     to_bit=	  1;
-    if (from->table->maybe_null)
-    {
-      null_row=   &from->table->null_row;
-      do_copy=	  do_outer_field_to_null_str;
-    }
-    else
-      do_copy=	  do_field_to_null_str;
+    do_copy=	  do_field_to_null_str;
   }
   else
   {
@@ -630,6 +594,7 @@ void Copy_field::set(Field *to,Field *fr
 
   // set up null handling
   from_null_ptr=to_null_ptr=0;
+  null_row= &from->table->null_row;
   if (from->maybe_null())
   {
     from_null_ptr=	from->null_ptr;
@@ -638,13 +603,7 @@ void Copy_field::set(Field *to,Field *fr
     {
       to_null_ptr=	to->null_ptr;
       to_bit=		to->null_bit;
-      if (from_null_ptr)
-	do_copy=	do_copy_null;
-      else
-      {
-	null_row=	&from->table->null_row;
-	do_copy=	do_outer_field_null;
-      }
+      do_copy=	do_copy_null;
     }
     else
     {

=== modified file 'sql/item_sum.cc'
--- a/sql/item_sum.cc	2011-12-15 15:15:37 +0000
+++ b/sql/item_sum.cc	2011-12-20 12:04:31 +0000
@@ -1057,7 +1057,7 @@ void Aggregator_distinct::endup()
   {
     /*
       We don't have a tree only if 'setup()' hasn't been called;
-      this is the case of sql_select.cc:return_zero_rows.
+      this is the case of sql_executor.cc:return_zero_rows.
     */
     if (tree)
       table->field[0]->set_notnull();

=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc	2011-07-21 17:51:38 +0000
+++ b/sql/opt_sum.cc	2011-12-16 14:39:02 +0000
@@ -268,7 +268,7 @@ int opt_sum_query(THD *thd,
     TABLE_LIST *embedded;
     for (embedded= tl ; embedded; embedded= embedded->embedding)
     {
-      if (embedded->on_expr)
+      if (embedded->join_cond())
         break;
     }
     if (embedded)

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2011-12-14 12:32:55 +0000
+++ b/sql/sql_base.cc	2011-12-16 14:39:02 +0000
@@ -7914,8 +7914,8 @@ store_top_level_join_columns(THD *thd, T
 
     /* Add a TRUE condition to outer joins that have no common columns. */
     if (table_ref_2->outer_join &&
-        !table_ref_1->on_expr && !table_ref_2->on_expr)
-      table_ref_2->on_expr= new Item_int((longlong) 1,1);   /* Always true. */
+        !table_ref_1->join_cond() && !table_ref_2->join_cond())
+      table_ref_2->set_join_cond(new Item_int((longlong) 1,1)); // Always true.
 
     /* Change this table reference to become a leaf for name resolution. */
     if (left_neighbor)
@@ -8732,15 +8732,15 @@ int setup_conds(THD *thd, TABLE_LIST *ta
     do
     {
       embedded= embedding;
-      if (embedded->on_expr)
+      if (embedded->join_cond())
       {
         /* Make a join an a expression */
         select_lex->resolve_place= st_select_lex::RESOLVE_JOIN_NEST;
         select_lex->resolve_nest= embedded;
         thd->where="on clause";
-        if ((!embedded->on_expr->fixed &&
-            embedded->on_expr->fix_fields(thd, &embedded->on_expr)) ||
-	    embedded->on_expr->check_cols(1))
+        if ((!embedded->join_cond()->fixed &&
+           embedded->join_cond()->fix_fields(thd, embedded->join_cond_ref())) ||
+	   embedded->join_cond()->check_cols(1))
 	  goto err_no_arena;
         select_lex->cond_count++;
         select_lex->resolve_place= st_select_lex::RESOLVE_NONE;

=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc	2011-12-15 09:00:42 +0000
+++ b/sql/sql_executor.cc	2012-01-04 13:53:33 +0000
@@ -43,10 +43,9 @@ using std::max;
 using std::min;
 
 static void disable_sorted_access(JOIN_TAB* join_tab);
-static int return_zero_rows(JOIN *join, select_result *res,TABLE_LIST *tables,
-                            List<Item> &fields, bool send_row,
-                            ulonglong select_options, const char *info,
-                            Item *having);
+static void return_zero_rows(JOIN *join, List<Item> &fields);
+static void save_const_null_info(JOIN *join, table_map *save_nullinfo);
+static void restore_const_null_info(JOIN *join, table_map save_nullinfo);
 static int do_select(JOIN *join,List<Item> *fields,TABLE *tmp_table,
 		     Procedure *proc);
 
@@ -197,12 +196,7 @@ JOIN::exec()
 
   if (zero_result_cause)
   {
-    (void) return_zero_rows(this, result, select_lex->leaf_tables,
-                            *columns_list,
-			    send_row_on_empty_set(),
-			    select_options,
-			    zero_result_cause,
-			    having);
+    return_zero_rows(this, *columns_list);
     DBUG_VOID_RETURN;
   }
   
@@ -1354,42 +1348,51 @@ static void update_const_equal_items(Ite
   }
 }
 
+/**
+  For some reason (impossible WHERE clause etc), the tables cannot
+  possibly contain any rows that will be in the result. This function
+  is used to return with a result based on no matching rows (i.e., an
+  empty result or one row with aggregates calculated without using
+  rows in the case of implicit grouping) before the execution of
+  nested loop join.
 
-static int
-return_zero_rows(JOIN *join, select_result *result,TABLE_LIST *tables,
-		 List<Item> &fields, bool send_row, ulonglong select_options,
-		 const char *info, Item *having)
+  @param join    The join that does not produce a row
+  @param fields  Fields in result
+*/
+static void
+return_zero_rows(JOIN *join, List<Item> &fields)
 {
   DBUG_ENTER("return_zero_rows");
 
   join->join_free();
 
-  if (send_row)
-  {
-    for (TABLE_LIST *table= tables; table; table= table->next_leaf)
-      mark_as_null_row(table->table);		// All fields are NULL
-    if (having && having->val_int() == 0)
-      send_row=0;
-  }
-  if (!(result->send_result_set_metadata(fields,
-                              Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)))
+  if (!(join->result->send_result_set_metadata(fields,
+                                               Protocol::SEND_NUM_ROWS | 
+                                               Protocol::SEND_EOF)))
   {
     bool send_error= FALSE;
-    if (send_row)
+    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);
+
+      // Calculate aggregate functions for no rows
       List_iterator_fast<Item> it(fields);
       Item *item;
       while ((item= it++))
-	item->no_rows_in_result();
-      send_error= result->send_data(fields);
+        item->no_rows_in_result();
+
+      if (!join->having || join->having->val_int())
+        send_error= join->result->send_data(fields);
     }
     if (!send_error)
-      result->send_eof();				// Should be safe
+      join->result->send_eof();                 // Should be safe
   }
   /* Update results for FOUND_ROWS */
   join->thd->set_examined_row_count(0);
   join->thd->limit_found_rows= 0;
-  DBUG_RETURN(0);
+  DBUG_VOID_RETURN;
 }
 
 /**
@@ -1542,12 +1545,33 @@ do_select(JOIN *join,List<Item> *fields,
     }
     else if (join->send_row_on_empty_set())
     {
+      table_map save_nullinfo= 0;
+      /*
+        If this is a subquery, we need to save and later restore
+        the const table NULL info before clearing the tables
+        because the following executions of the subquery do not
+        reevaluate constant fields. @see save_const_null_info
+        and restore_const_null_info
+      */
+      if (join->select_lex->master_unit()->item && join->const_tables)
+        save_const_null_info(join, &save_nullinfo);
+
+      // Mark tables as containing only NULL values
+      join->clear();
+
+      // Calculate aggregate functions for no rows
+      List<Item> *columns_list= (procedure ? &join->procedure_fields_list :
+                                 fields);
+      List_iterator_fast<Item> it(*columns_list);
+      Item *item;
+      while ((item= it++))
+        item->no_rows_in_result();
+
       if (!join->having || join->having->val_int())
-      {
-        List<Item> *columns_list= (procedure ? &join->procedure_fields_list :
-                                   fields);
         rc= join->result->send_data(*columns_list);
-      }
+
+      if (save_nullinfo)
+        restore_const_null_info(join, save_nullinfo);
     }
     /*
       An error can happen when evaluating the conds 
@@ -2640,8 +2664,8 @@ join_read_const_table(JOIN_TAB *tab, POS
     do
     {
       embedded= embedding;
-      if (embedded->on_expr)
-         update_const_equal_items(embedded->on_expr, tab);
+      if (embedded->join_cond())
+        update_const_equal_items(embedded->join_cond(), tab);
       embedding= embedded->embedding;
     }
     while (embedding &&
@@ -3388,12 +3412,25 @@ end_send_group(JOIN *join, JOIN_TAB *joi
 	}
 	else
 	{
-	  if (!join->first_record)
-	  {
+          table_map save_nullinfo= 0;
+          if (!join->first_record)
+          {
+            /*
+              If this is a subquery, we need to save and later restore
+              the const table NULL info before clearing the tables
+              because the following executions of the subquery do not
+              reevaluate constant fields. @see save_const_null_info
+              and restore_const_null_info
+            */
+            if (join->select_lex->master_unit()->item && join->const_tables)
+              save_const_null_info(join, &save_nullinfo);
+
+            // Mark tables as containing only NULL values
+            join->clear();
+
+            // Calculate aggregate functions for no rows
             List_iterator_fast<Item> it(*join->fields);
             Item *item;
-	    /* No matching rows for group function */
-	    join->clear();
 
             while ((item= it++))
               item->no_rows_in_result();
@@ -3411,6 +3448,9 @@ end_send_group(JOIN *join, JOIN_TAB *joi
 	    if (join->rollup_send_data((uint) (idx+1)))
 	      error= 1;
 	  }
+          if (save_nullinfo)
+            restore_const_null_info(join, save_nullinfo);
+
 	}
 	if (error > 0)
           DBUG_RETURN(NESTED_LOOP_ERROR);        /* purecov: inspected */
@@ -3669,11 +3709,34 @@ end_write_group(JOIN *join, JOIN_TAB *jo
       int send_group_parts= join->send_group_parts;
       if (idx < send_group_parts)
       {
-	if (!join->first_record)
-	{
-	  /* No matching rows for group function */
-	  join->clear();
-	}
+        table_map save_nullinfo= 0;
+        if (!join->first_record)
+        {
+          // Dead code or we need a test case for this branch
+          DBUG_ASSERT(false);
+          /*
+            If this is a subquery, we need to save and later restore
+            the const table NULL info before clearing the tables
+            because the following executions of the subquery do not
+            reevaluate constant fields. @see save_const_null_info
+            and restore_const_null_info
+          */
+          if (join->select_lex->master_unit()->item && join->const_tables)
+            save_const_null_info(join, &save_nullinfo);
+
+          // Mark tables as containing only NULL values
+          join->clear();
+
+          // Calculate aggregate functions for no rows
+          List<Item> *columns_list= (join->procedure ?
+                                     &join->procedure_fields_list :
+                                     join->fields);
+          List_iterator_fast<Item> it(*columns_list);
+          Item *item;
+          while ((item= it++))
+            item->no_rows_in_result();
+
+        }
         copy_sum_funcs(join->sum_funcs,
                        join->sum_funcs_end[send_group_parts]);
 	if (!join->having || join->having->val_int())
@@ -3691,6 +3754,9 @@ end_write_group(JOIN *join, JOIN_TAB *jo
 	  if (join->rollup_write_data((uint) (idx+1), table))
 	    DBUG_RETURN(NESTED_LOOP_ERROR);
 	}
+        if (save_nullinfo)
+          restore_const_null_info(join, save_nullinfo);
+
 	if (end_of_records)
 	  DBUG_RETURN(NESTED_LOOP_OK);
       }
@@ -4705,6 +4771,79 @@ change_refs_to_tmp_fields(THD *thd, Ref_
 
 
 /**
+  Save NULL-row info for constant tables. Used in conjunction with
+  restore_const_null_info() to restore constant table null_row and
+  status values after temporarily marking rows as NULL. This is only
+  done for const tables in subqueries because these values are not
+  recalculated on next execution of the subquery.
+
+  @param join               The join for which const tables are about to be
+                            marked as containing only NULL values
+  @param[out] save_nullinfo Const tables that have null_row=false and
+                            STATUS_NULL_ROW set are tagged in this
+                            table_map so that the value can be
+                            restored by restore_const_null_info()
+
+  @see mark_as_null_row
+  @see restore_const_null_info
+*/
+static void save_const_null_info(JOIN *join, table_map *save_nullinfo)
+{
+  DBUG_ASSERT(join->const_tables);
+
+  for (uint tableno= 0; tableno < join->const_tables; tableno++)
+  {
+    TABLE *tbl= (join->join_tab+tableno)->table;
+    /*
+      tbl->status and tbl->null_row must be in sync: either both set
+      or none set. Otherwise, an additional table_map parameter is
+      needed to save/restore_const_null_info() these separately
+    */
+    DBUG_ASSERT(tbl->null_row ? (tbl->status & STATUS_NULL_ROW) :
+                               !(tbl->status & STATUS_NULL_ROW));
+
+    if (!tbl->null_row)
+      *save_nullinfo|= tbl->map;
+  }
+}
+
+/**
+  Restore NULL-row info for constant tables. Used in conjunction with
+  save_const_null_info() to restore constant table null_row and status
+  values after temporarily marking rows as NULL. This is only done for
+  const tables in subqueries because these values are not recalculated
+  on next execution of the subquery.
+
+  @param join            The join for which const tables have been
+                         marked as containing only NULL values
+  @param save_nullinfo   Const tables that had null_row=false and
+                         STATUS_NULL_ROW set when
+                         save_const_null_info() was called
+
+  @see mark_as_null_row
+  @see save_const_null_info
+*/
+static void restore_const_null_info(JOIN *join, table_map save_nullinfo)
+{
+  DBUG_ASSERT(join->const_tables && save_nullinfo);
+
+  for (uint tableno= 0; tableno < join->const_tables; tableno++)
+  {
+    TABLE *tbl= (join->join_tab+tableno)->table;
+    if ((save_nullinfo & tbl->map))
+    {
+      /*
+        The table had null_row=false and STATUS_NULL_ROW set when
+        save_const_null_info was called
+      */
+      tbl->null_row= false;
+      tbl->status&= ~STATUS_NULL_ROW;
+    }
+  }
+}
+
+
+/**
   @} (end of group Query_Executor)
 */
 

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2011-12-16 11:20:13 +0000
+++ b/sql/sql_lex.cc	2012-01-05 10:17:03 +0000
@@ -2361,10 +2361,10 @@ static void print_table_array(THD *thd, 
     else
       str->append(STRING_WITH_LEN(" join "));
     curr->print(thd, str, query_type);          // Print table
-    if (curr->on_expr)                          // Print join condition
+    if (curr->join_cond())                      // Print join condition
     {
       str->append(STRING_WITH_LEN(" on("));
-      curr->on_expr->print(str, query_type);
+      curr->join_cond()->print(str, query_type);
       str->append(')');
     }
   }
@@ -3525,10 +3525,10 @@ static void fix_prepare_info_in_table_li
 {
   for (; tbl; tbl= tbl->next_local)
   {
-    if (tbl->on_expr)
+    if (tbl->join_cond())
     {
-      tbl->prep_on_expr= tbl->on_expr;
-      tbl->on_expr= tbl->on_expr->copy_andor_structure(thd);
+      tbl->prep_join_cond= tbl->join_cond();
+      tbl->set_join_cond(tbl->join_cond()->copy_andor_structure(thd));
     }
     fix_prepare_info_in_table_list(thd, tbl->merge_underlying_list);
   }

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2011-12-16 11:20:13 +0000
+++ b/sql/sql_optimizer.cc	2012-01-05 10:17:03 +0000
@@ -266,7 +266,7 @@ JOIN::optimize()
       */
       if (!tbl->embedding)
       {
-        Item *prune_cond= tbl->on_expr? tbl->on_expr : conds;
+        Item *prune_cond= tbl->join_cond()? tbl->join_cond() : conds;
         tbl->table->no_partitions_used= prune_partitions(thd, tbl->table,
 	                                                 prune_cond);
       }
@@ -1660,17 +1660,18 @@ static Item *build_equal_items(THD *thd,
 
     while ((table= li++))
     {
-      if (table->on_expr)
+      if (table->join_cond())
       {
         List<TABLE_LIST> *nested_join_list= table->nested_join ?
           &table->nested_join->join_list : NULL;
         /*
-          We can modify table->on_expr because its old value will
+          We can modify table->join_cond() because its old value will
           be restored before re-execution of PS/SP.
         */
-        table->on_expr= build_equal_items(thd, table->on_expr, inherited,
-                                          nested_join_list,
-                                          &table->cond_equal);
+        table->set_join_cond(build_equal_items(thd, table->join_cond(),
+                                               inherited,
+                                               nested_join_list,
+                                               &table->cond_equal));
       }
     }
   }
@@ -2292,9 +2293,9 @@ simplify_joins(JOIN *join, List<TABLE_LI
          If the element of join_list is a nested join apply
          the procedure to its nested join list first.
       */
-      if (table->on_expr)
+      if (table->join_cond())
       {
-        Item *expr= table->on_expr;
+        Item *expr= table->join_cond();
         /* 
            If an on expression E is attached to the table, 
            check all null rejected predicates in this expression.
@@ -2308,12 +2309,12 @@ simplify_joins(JOIN *join, List<TABLE_LI
                            &expr, changelog))
           DBUG_RETURN(true);
 
-        if (!table->prep_on_expr || expr != table->on_expr)
+        if (!table->prep_join_cond || expr != table->join_cond())
         {
           DBUG_ASSERT(expr);
 
-          table->on_expr= expr;
-          if (!(table->prep_on_expr= expr->copy_andor_structure(join->thd)))
+          table->set_join_cond(expr);
+          if (!(table->prep_join_cond= expr->copy_andor_structure(join->thd)))
             DBUG_RETURN(true);
         }
       }
@@ -2327,8 +2328,8 @@ simplify_joins(JOIN *join, List<TABLE_LI
     }
     else
     {
-      if (!table->prep_on_expr)
-        table->prep_on_expr= table->on_expr;
+      if (!table->prep_join_cond)
+        table->prep_join_cond= table->join_cond();
       used_tables= table->table->map;
       if (conds)
         not_null_tables= conds->not_null_tables();
@@ -2351,14 +2352,14 @@ simplify_joins(JOIN *join, List<TABLE_LI
         *changelog|= OUTER_JOIN_TO_INNER;
         table->outer_join= 0;
       }
-      if (table->on_expr)
+      if (table->join_cond())
       {
         *changelog|= JOIN_COND_TO_WHERE;
         /* Add ON expression to the WHERE or upper-level ON condition. */
         if (conds)
         {
           Item_cond_and *new_cond=
-            static_cast<Item_cond_and*>(and_conds(conds, table->on_expr));
+            static_cast<Item_cond_and*>(and_conds(conds, table->join_cond()));
           if (!new_cond)
             DBUG_RETURN(true);
           conds= new_cond;
@@ -2377,18 +2378,19 @@ simplify_joins(JOIN *join, List<TABLE_LI
               on_expr isn't necessarily the second argument anymore,
               since fix_fields may have merged it into an existing AND expr.
             */
-            if (arg == table->on_expr)
+            if (arg == table->join_cond())
               join->thd->
-                change_item_tree_place(&table->on_expr, lit.ref());
+                change_item_tree_place(table->join_cond_ref(), lit.ref());
           }
         }
         else
         {
-          conds= table->on_expr; 
+          conds= table->join_cond(); 
           /* In case on_expr has a pending rollback in THD::change_list */
-          join->thd->change_item_tree_place(&table->on_expr, &conds);
+          join->thd->change_item_tree_place(table->join_cond_ref(), &conds);
         }
-        table->prep_on_expr= table->on_expr= 0;
+        table->prep_join_cond= NULL;
+        table->set_join_cond(NULL);
       }
     }
     
@@ -2399,9 +2401,9 @@ simplify_joins(JOIN *join, List<TABLE_LI
       Only inner tables of non-convertible outer joins
       remain with on_expr.
     */ 
-    if (table->on_expr)
+    if (table->join_cond())
     {
-      table->dep_tables|= table->on_expr->used_tables(); 
+      table->dep_tables|= table->join_cond()->used_tables(); 
       if (table->embedding)
       {
         table->dep_tables&= ~table->embedding->nested_join->used_tables;   
@@ -2409,7 +2411,8 @@ simplify_joins(JOIN *join, List<TABLE_LI
            Embedding table depends on tables used
            in embedded on expressions. 
         */
-        table->embedding->on_expr_dep_tables|= table->on_expr->used_tables();
+        table->embedding->on_expr_dep_tables|=
+          table->join_cond()->used_tables();
       }
       else
         table->dep_tables&= ~table->table->map;
@@ -2420,7 +2423,7 @@ simplify_joins(JOIN *join, List<TABLE_LI
       /* The order of tables is reverse: prev_table follows table */
       if (prev_table->straight || straight_join)
         prev_table->dep_tables|= used_tables;
-      if (prev_table->on_expr)
+      if (prev_table->join_cond())
       {
         prev_table->dep_tables|= table->on_expr_dep_tables;
         table_map prev_used_tables= prev_table->nested_join ?
@@ -2437,7 +2440,7 @@ simplify_joins(JOIN *join, List<TABLE_LI
           For example it might happen if RAND() function
           is used in JOIN ON clause.
 	*/  
-        if (!((prev_table->on_expr->used_tables() & ~RAND_TABLE_BIT) &
+        if (!((prev_table->join_cond()->used_tables() & ~RAND_TABLE_BIT) &
               ~prev_used_tables))
           prev_table->dep_tables|= used_tables;
       }
@@ -2463,7 +2466,7 @@ simplify_joins(JOIN *join, List<TABLE_LI
         DBUG_RETURN(true);
       *changelog|= SEMIJOIN;
     }
-    else if (nested_join && !table->on_expr)
+    else if (nested_join && !table->join_cond())
     {
       *changelog|= PAREN_REMOVAL;
       TABLE_LIST *tbl;
@@ -2472,6 +2475,7 @@ simplify_joins(JOIN *join, List<TABLE_LI
       {
         tbl->embedding= table->embedding;
         tbl->join_list= table->join_list;
+        tbl->dep_tables|= table->dep_tables;
       }
       li.replace(nested_join->join_list);
     }
@@ -2550,7 +2554,7 @@ static uint build_bitmap_for_nested_join
       if (nested_join->join_list.elements != 1)
       {
         /* Don't assign bits to sj-nests */
-        if (table->on_expr)
+        if (table->join_cond())
           nested_join->nj_map= (nested_join_map) 1 << first_unused++;
         first_unused= build_bitmap_for_nested_joins(&nested_join->join_list,
                                                     first_unused);
@@ -2738,7 +2742,7 @@ make_join_statistics(JOIN *join, TABLE_L
       table->file->stats.records= 2;
     table->quick_condition_rows= table->file->stats.records;
 
-    s->on_expr_ref= &tables->on_expr;
+    s->on_expr_ref= tables->join_cond_ref();
 
     if (tables->in_outer_join_nest())
     {
@@ -5036,7 +5040,7 @@ static void add_key_fields_for_nj(JOIN *
   {
     if (table->nested_join)
     {
-      if (!table->on_expr)
+      if (!table->join_cond())
       {
         /* It's a semi-join nest. Walk into it as if it wasn't a nest */
         have_another= TRUE;
@@ -5047,11 +5051,11 @@ static void add_key_fields_for_nj(JOIN *
         add_key_fields_for_nj(join, table, end, and_level, sargables);
     }
     else
-      if (!table->on_expr)
+      if (!table->join_cond())
         tables |= table->table->map;
   }
-  if (nested_join_table->on_expr)
-    add_key_fields(join, end, and_level, nested_join_table->on_expr, tables,
+  if (nested_join_table->join_cond())
+    add_key_fields(join, end, and_level, nested_join_table->join_cond(), tables,
                    sargables);
 }
 
@@ -5251,7 +5255,7 @@ add_group_and_distinct_keys(JOIN *join, 
   @param       join_tab       Array in tablenr_order
   @param       tables         Number of tables in join
   @param       cond           WHERE condition (note that the function analyzes
-                              join_tab[i]->on_expr too)
+                              join_tab[i]->join_cond() too)
   @param       normal_tables  Tables not inner w.r.t some outer join (ones
                               for which we can make ref access based the WHERE
                               clause)
@@ -5528,7 +5532,7 @@ make_outerjoin_info(JOIN *join)
         is in the query above.)
       */
       tab->last_inner= tab->first_inner= tab;
-      tab->on_expr_ref= &tbl->on_expr;
+      tab->on_expr_ref= tbl->join_cond_ref();
       tab->cond_equal= tbl->cond_equal;
       if (embedding)
         tab->first_upper= embedding->nested_join->first_nested;
@@ -5536,7 +5540,7 @@ make_outerjoin_info(JOIN *join)
     for ( ; embedding ; embedding= embedding->embedding)
     {
       /* Ignore sj-nests: */
-      if (!embedding->on_expr)
+      if (!embedding->join_cond())
         continue;
       NESTED_JOIN *nested_join= embedding->nested_join;
       if (!nested_join->counter_)
@@ -5546,7 +5550,7 @@ make_outerjoin_info(JOIN *join)
           Save reference to it in the nested join structure.
         */ 
         nested_join->first_nested= tab;
-        tab->on_expr_ref= &embedding->on_expr;
+        tab->on_expr_ref= embedding->join_cond_ref();
         tab->cond_equal= tbl->cond_equal;
         if (embedding->embedding)
           tab->first_upper= embedding->embedding->nested_join->first_nested;
@@ -5918,9 +5922,9 @@ static void fix_list_after_tbl_changes(s
   TABLE_LIST *table;
   while ((table= it++))
   {
-    if (table->on_expr)
-      table->on_expr->fix_after_pullout(parent_select, removed_select,
-                                        &table->on_expr);
+    if (table->join_cond())
+      table->join_cond()->fix_after_pullout(parent_select, removed_select,
+                                            table->join_cond_ref());
     if (table->nested_join)
       fix_list_after_tbl_changes(parent_select, removed_select,
                                  &table->nested_join->join_list);
@@ -6062,8 +6066,8 @@ static bool convert_subquery_to_semijoin
       wrap_nest->outer_join= outer_tbl->outer_join;
       outer_tbl->outer_join= 0;
 
-      wrap_nest->on_expr= outer_tbl->on_expr;
-      outer_tbl->on_expr= NULL;
+      wrap_nest->set_join_cond(outer_tbl->join_cond());
+      outer_tbl->set_join_cond(NULL);
 
       List_iterator<TABLE_LIST> li(*wrap_nest->join_list);
       TABLE_LIST *tbl;
@@ -6257,11 +6261,12 @@ static bool convert_subquery_to_semijoin
   if (emb_tbl_nest)
   {
     /* Inject sj_on_expr into the parent's ON condition */
-    emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr, 
-                                     sj_nest->sj_on_expr);
-    if (emb_tbl_nest->on_expr == NULL)
+    emb_tbl_nest->set_join_cond(and_items(emb_tbl_nest->join_cond(), 
+                                          sj_nest->sj_on_expr));
+    if (emb_tbl_nest->join_cond() == NULL)
       DBUG_RETURN(TRUE);
-    emb_tbl_nest->on_expr->fix_fields(parent_join->thd, &emb_tbl_nest->on_expr);
+    emb_tbl_nest->join_cond()->fix_fields(parent_join->thd,
+                                          emb_tbl_nest->join_cond_ref());
   }
   else
   {
@@ -6376,7 +6381,7 @@ bool JOIN::flatten_subqueries()
   // joins.
   for (TABLE_LIST *tbl= select_lex->leaf_tables; tbl; tbl=tbl->next_leaf)
   {
-    if (tbl->on_expr || tbl->in_outer_join_nest())
+    if (tbl->join_cond() || tbl->in_outer_join_nest())
     {
       subq= sj_subselects.begin();
       arena= thd->activate_stmt_arena_if_needed(&backup);
@@ -6405,7 +6410,7 @@ bool JOIN::flatten_subqueries()
        subq++)
   {
     Item **tree= ((*subq)->embedding_join_nest == NULL) ?
-                   &conds : &((*subq)->embedding_join_nest->on_expr);
+                   &conds : ((*subq)->embedding_join_nest->join_cond_ref());
     if (replace_subcondition(this, tree, *subq, new Item_int(1), FALSE))
       DBUG_RETURN(TRUE); /* purecov: inspected */
   }
@@ -6460,8 +6465,7 @@ skip_conversion:
     const bool subquery_in_join_clause= (*subq)->embedding_join_nest != NULL;
 
     Item **tree= subquery_in_join_clause ?
-                   &((*subq)->embedding_join_nest->on_expr) :
-                   &conds;
+      ((*subq)->embedding_join_nest->join_cond_ref()) : &conds;
     if (replace_subcondition(this, tree, *subq, substitute, do_fix_fields))
       DBUG_RETURN(TRUE);
     (*subq)->substitution= NULL;
@@ -6470,10 +6474,10 @@ skip_conversion:
     {
       if (subquery_in_join_clause)
       {
-        tree= &((*subq)->embedding_join_nest->prep_on_expr);
+        tree= &((*subq)->embedding_join_nest->prep_join_cond);
         /*
           Some precaution is needed when dealing with PS/SP:
-          fix_prepare_info_in_table_list() sets prep_on_expr, but only for
+          fix_prepare_info_in_table_list() sets prep_join_cond, but only for
           tables, not for join nest objects. This is instead populated in
           simplify_joins(), which is called after this function. The case
           where *tree is NULL is handled by this procedure.
@@ -7334,7 +7338,7 @@ static bool make_join_select(JOIN *join,
 
 	    /*
               We can't call sel->cond->fix_fields,
-              as it will break tab->on_expr if it's AND condition
+              as it will break tab->join_cond() if it's AND condition
               (fix_fields currently removes extra AND/OR levels).
               Yet attributes of the just built condition are not needed.
               Thus we call sel->cond->quick_fix_field for safety.

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2011-12-09 08:59:22 +0000
+++ b/sql/sql_parse.cc	2011-12-16 14:39:02 +0000
@@ -6644,19 +6644,14 @@ push_new_name_resolution_context(THD *th
 
   @param b     the second operand of a JOIN ... ON
   @param expr  the condition to be added to the ON clause
-
-  @retval
-    FALSE  if there was some error
-  @retval
-    TRUE   if all is OK
 */
 
 void add_join_on(TABLE_LIST *b, Item *expr)
 {
   if (expr)
   {
-    if (!b->on_expr)
-      b->on_expr= expr;
+    if (!b->join_cond())
+      b->set_join_cond(expr);
     else
     {
       /*
@@ -6664,9 +6659,9 @@ void add_join_on(TABLE_LIST *b, Item *ex
         right and left join. If called later, it happens if we add more
         than one condition to the ON clause.
       */
-      b->on_expr= new Item_cond_and(b->on_expr,expr);
+      b->set_join_cond(new Item_cond_and(b->join_cond(), expr));
     }
-    b->on_expr->top_level_item();
+    b->join_cond()->top_level_item();
   }
 }
 
@@ -6681,7 +6676,7 @@ void add_join_on(TABLE_LIST *b, Item *ex
     setup_conds() creates a list of equal condition between all fields
     of the same name for NATURAL JOIN or the fields in 'using_fields'
     for JOIN ... USING. The list of equality conditions is stored
-    either in b->on_expr, or in JOIN::conds, depending on whether there
+    either in b->join_cond(), or in JOIN::conds, depending on whether there
     was an outer join.
 
   EXAMPLE

=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc	2011-12-14 12:32:55 +0000
+++ b/sql/sql_planner.cc	2011-12-16 14:39:02 +0000
@@ -3763,7 +3763,7 @@ void Optimize_table_order::backout_nj_sj
 
   for (;last_emb != NULL; last_emb= last_emb->embedding)
   {
-    if (last_emb->on_expr)
+    if (last_emb->join_cond())
     {
       NESTED_JOIN *nest= last_emb->nested_join;
       DBUG_ASSERT(nest->counter_ > 0);

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-12-16 11:20:13 +0000
+++ b/sql/sql_select.cc	2012-01-05 10:17:03 +0000
@@ -3209,18 +3209,6 @@ ORDER *simple_remove_const(ORDER *order,
 }
 
 
-/*
-  used only in JOIN::clear
-*/
-static void clear_tables(JOIN *join)
-{
-  /* 
-    must clear only the non-const tables, as const tables
-    are not re-calculated.
-  */
-  for (uint i=join->const_tables ; i < join->tables ; i++)
-    mark_as_null_row(join->all_tables[i]);		// All fields are NULL
-}
 
 
 /* 
@@ -4593,7 +4581,9 @@ bool JOIN::rollup_make_fields(List<Item>
 
 void JOIN::clear()
 {
-  clear_tables(this);
+  for (uint tableno= 0; tableno < this->tables; tableno++)
+    mark_as_null_row((join_tab+tableno)->table);
+
   copy_fields(&tmp_table_param);
 
   if (sum_funcs)

=== modified file 'sql/sql_tmp_table.cc'
--- a/sql/sql_tmp_table.cc	2011-12-15 15:15:37 +0000
+++ b/sql/sql_tmp_table.cc	2012-01-04 09:51:39 +0000
@@ -464,8 +464,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
   uint  temp_pool_slot=MY_BIT_NONE;
   uint fieldnr= 0;
   ulong reclength, string_total_length;
-  bool  using_unique_constraint= 0;
-  bool  use_packed_rows= 0;
+  bool  using_unique_constraint= false;
+  bool  use_packed_rows= false;
   bool  not_all_columns= !(select_options & TMP_TABLE_ALL_COLUMNS);
   char  *tmpname,path[FN_REFLEN];
   uchar	*pos, *group_buff, *bitmaps;
@@ -527,10 +527,10 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
       */
       (*tmp->item)->marker= 4;
       if ((*tmp->item)->max_length >= CONVERT_IF_BIGGER_TO_BLOB)
-	using_unique_constraint=1;
+        using_unique_constraint= true;
     }
     if (param->group_length >= MAX_BLOB_WIDTH)
-      using_unique_constraint=1;
+      using_unique_constraint= true;
     if (group)
       distinct=0;				// Can't use distinct
   }
@@ -750,6 +750,14 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
         *blob_field++= fieldnr;
 	blob_count++;
       }
+
+      if (new_field->real_type() == MYSQL_TYPE_STRING ||
+          new_field->real_type() == MYSQL_TYPE_VARCHAR)
+      {
+        string_count++;
+        string_total_length+= new_field->pack_length();
+      }
+
       if (item->marker == 4 && item->maybe_null)
       {
 	group_null_items++;
@@ -800,7 +808,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
     if (group &&
 	(param->group_parts > table->file->max_key_parts() ||
 	 param->group_length > table->file->max_key_length()))
-      using_unique_constraint=1;
+      using_unique_constraint= true;
   }
   else
   {
@@ -836,7 +844,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
       (string_total_length >= STRING_TOTAL_LENGTH_TO_PACK_ROWS &&
       (reclength / string_total_length <= RATIO_TO_PACK_ROWS ||
        string_total_length / string_count >= AVG_STRING_LENGTH_TO_PACK_ROWS)))
-    use_packed_rows= 1;
+    use_packed_rows= true;
 
   if (!use_packed_rows)
     share->db_create_options&= ~HA_OPTION_PACK_RECORD;
@@ -956,6 +964,10 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
              field->real_type() == MYSQL_TYPE_STRING &&
 	     length >= MIN_STRING_LENGTH_TO_PACK_ROWS)
       recinfo->type=FIELD_SKIP_ENDSPACE;
+    else if (use_packed_rows && 
+             field->real_type() == MYSQL_TYPE_VARCHAR &&
+             length >= MIN_STRING_LENGTH_TO_PACK_ROWS)
+      recinfo->type= FIELD_VARCHAR;
     else
       recinfo->type=FIELD_NORMAL;
     if (!--hidden_field_count)
@@ -1732,7 +1744,10 @@ bool create_myisam_tmp_table(TABLE *tabl
                        start_recinfo,
                        share->uniques, &uniquedef,
                        &create_info,
-                       HA_CREATE_TMP_TABLE)))
+                       HA_CREATE_TMP_TABLE | 
+                       ((share->db_create_options & HA_OPTION_PACK_RECORD) ?
+                        HA_PACK_RECORD : 0)
+                       )))
   {
     table->file->print_error(error,MYF(0));	/* purecov: inspected */
     table->db_stat=0;

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2011-11-28 07:42:18 +0000
+++ b/sql/table.cc	2011-12-16 14:39:02 +0000
@@ -3741,8 +3741,8 @@ bool TABLE_LIST::prep_where(THD *thd, It
             this expression will not be moved to WHERE condition (i.e. will
             be clean correctly for PS/SP)
           */
-          tbl->on_expr= and_conds(tbl->on_expr,
-                                  where->copy_andor_structure(thd));
+          tbl->set_join_cond(and_conds(tbl->join_cond(),
+                                       where->copy_andor_structure(thd)));
           break;
         }
       }
@@ -3784,8 +3784,8 @@ merge_on_conds(THD *thd, TABLE_LIST *tab
 
   Item *cond= NULL;
   DBUG_PRINT("info", ("alias: %s", table->alias));
-  if (table->on_expr)
-    cond= table->on_expr->copy_andor_structure(thd);
+  if (table->join_cond())
+    cond= table->join_cond()->copy_andor_structure(thd);
   if (!table->nested_join)
     DBUG_RETURN(cond);
   List_iterator<TABLE_LIST> li(table->nested_join->join_list);
@@ -5446,8 +5446,9 @@ void TABLE_LIST::reinit_before_use(THD *
   do
   {
     embedded= parent_embedding;
-    if (embedded->prep_on_expr)
-      embedded->on_expr= embedded->prep_on_expr->copy_andor_structure(thd);
+    if (embedded->prep_join_cond)
+      embedded->
+        set_join_cond(embedded->prep_join_cond->copy_andor_structure(thd));
     parent_embedding= embedded->embedding;
   }
   while (parent_embedding &&

=== modified file 'sql/table.h'
--- a/sql/table.h	2011-12-15 12:12:14 +0000
+++ b/sql/table.h	2011-12-20 12:04:31 +0000
@@ -1514,7 +1514,24 @@ struct TABLE_LIST
   TABLE_LIST *next_global, **prev_global;
   char		*db, *alias, *table_name, *schema_table_name;
   char          *option;                /* Used by cache index  */
-  Item		*on_expr;		/* Used with outer join */
+
+private:
+  Item		*m_join_cond;           /* Used with outer join */
+public:
+  Item         **join_cond_ref() { return &m_join_cond; }
+  Item          *join_cond() { return m_join_cond; }
+  Item          *set_join_cond(Item *val)
+                 { return m_join_cond= val; }
+  /*
+    The structure of the join condition presented in the member above
+    can be changed during certain optimizations. This member
+    contains a snapshot of AND-OR structure of the join condition
+    made after permanent transformations of the parse tree, and is
+    used to restore the join condition before every reexecution of a prepared
+    statement or stored procedure.
+  */
+  Item          *prep_join_cond;
+
   Item          *sj_on_expr;            /* Synthesized semijoin condition */
   /*
     (Valid only for semi-join nests) Bitmap of tables that are within the
@@ -1526,15 +1543,6 @@ struct TABLE_LIST
   Item_exists_subselect  *sj_subq_pred;
   Semijoin_mat_exec *sj_mat_exec;
 
-  /*
-    The structure of ON expression presented in the member above
-    can be changed during certain optimizations. This member
-    contains a snapshot of AND-OR structure of the ON expression
-    made after permanent transformations of the parse tree, and is
-    used to restore ON clause before every reexecution of a prepared
-    statement or stored procedure.
-  */
-  Item          *prep_on_expr;
   COND_EQUAL    *cond_equal;            /* Used with outer join */
   /*
     During parsing - left operand of NATURAL/USING join where 'this' is
@@ -2327,7 +2335,6 @@ inline void mark_as_null_row(TABLE *tabl
 {
   table->null_row=1;
   table->status|=STATUS_NULL_ROW;
-  memset(table->null_flags, 255, table->s->null_bytes);
 }
 
 bool is_simple_order(ORDER *order);

=== modified file 'storage/myisam/mi_create.c'
--- a/storage/myisam/mi_create.c	2011-08-29 12:08:58 +0000
+++ b/storage/myisam/mi_create.c	2012-01-04 09:51:39 +0000
@@ -462,7 +462,6 @@ int mi_create(const char *name,uint keys
     key_del[i]=HA_OFFSET_ERROR;
 
   unique_key_parts=0;
-  offset=reclength-uniques*MI_UNIQUE_HASH_LENGTH;
   for (i=0, uniquedef=uniquedefs ; i < uniques ; i++ , uniquedef++)
   {
     uniquedef->key=keys+i;
@@ -727,7 +726,7 @@ int mi_create(const char *name,uint keys
 #endif
   }
   /* Create extra keys for unique definitions */
-  offset=reclength-uniques*MI_UNIQUE_HASH_LENGTH;
+  offset= real_reclength - uniques * MI_UNIQUE_HASH_LENGTH;
   memset(&tmp_keydef, 0, sizeof(tmp_keydef));
   memset(&tmp_keyseg, 0, sizeof(tmp_keyseg));
   for (i=0; i < uniques ; i++)

=== modified file 'unittest/gunit/bounded_queue-t.cc'
--- a/unittest/gunit/bounded_queue-t.cc	2010-12-23 11:03:09 +0000
+++ b/unittest/gunit/bounded_queue-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    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,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 #include <algorithm>

=== modified file 'unittest/gunit/bounds_checked_array-t.cc'
--- a/unittest/gunit/bounds_checked_array-t.cc	2011-08-26 08:16:16 +0000
+++ b/unittest/gunit/bounds_checked_array-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    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,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/cost_estimate-t.cc'
--- a/unittest/gunit/cost_estimate-t.cc	2011-11-08 11:37:54 +0000
+++ b/unittest/gunit/cost_estimate-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    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,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/dbug-t.cc'
--- a/unittest/gunit/dbug-t.cc	2010-10-18 11:31:18 +0000
+++ b/unittest/gunit/dbug-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    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,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/dynarray-t.cc'
--- a/unittest/gunit/dynarray-t.cc	2011-05-18 08:29:46 +0000
+++ b/unittest/gunit/dynarray-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    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,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/field-t.cc'
--- a/unittest/gunit/field-t.cc	2011-11-18 11:49:13 +0000
+++ b/unittest/gunit/field-t.cc	2011-12-20 12:04:31 +0000
@@ -13,8 +13,7 @@
    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,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 
@@ -70,6 +69,17 @@ static void compareMysqlTime(const MYSQL
   EXPECT_EQ(first.time_type, second.time_type);
 }
 
+class Mock_table : public TABLE
+{
+public:
+  Mock_table(THD *thd)
+  {
+    null_row= false;
+    read_set= 0;
+    in_use= thd;
+  }
+};
+
 // A mock Protocol class to be able to test Field::send_binary
 // It just verifies that store_time has been passed what is expected
 class Mock_protocol : public Protocol
@@ -257,6 +267,8 @@ TEST_F(FieldTest, FieldTimef)
   // Not testing store(const char, uint, const CHARSET_INFO *, enum_check_fields)
   // it requires a mock table
   
+  Mock_table m_table(thd());
+  f->table= &m_table;
   struct timeval tv;
   int warnings= 0;
   EXPECT_EQ(0, f->get_timestamp(&tv, &warnings));

=== modified file 'unittest/gunit/filesort_buffer-t.cc'
--- a/unittest/gunit/filesort_buffer-t.cc	2011-11-07 15:32:36 +0000
+++ b/unittest/gunit/filesort_buffer-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02111-1307  USA */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/get_diagnostics-t.cc'
--- a/unittest/gunit/get_diagnostics-t.cc	2011-11-23 09:14:10 +0000
+++ b/unittest/gunit/get_diagnostics-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02111-1307  USA */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/gunit_test_main.cc'
--- a/unittest/gunit/gunit_test_main.cc	2011-10-11 04:27:52 +0000
+++ b/unittest/gunit/gunit_test_main.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/item-t.cc'
--- a/unittest/gunit/item-t.cc	2011-11-17 13:41:28 +0000
+++ b/unittest/gunit/item-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    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,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/mdl-t.cc'
--- a/unittest/gunit/mdl-t.cc	2011-05-19 09:47:59 +0000
+++ b/unittest/gunit/mdl-t.cc	2011-12-20 09:51:05 +0000
@@ -22,8 +22,7 @@
    The code below should hopefully be (mostly) self-explanatory.
  */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/mdl_mytap-t.cc'
--- a/unittest/gunit/mdl_mytap-t.cc	2011-05-19 09:47:59 +0000
+++ b/unittest/gunit/mdl_mytap-t.cc	2011-12-20 09:51:05 +0000
@@ -21,8 +21,7 @@
    numbers in case of failures.
  */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <string>
 #include <iostream>

=== modified file 'unittest/gunit/my_regex-t.cc'
--- a/unittest/gunit/my_regex-t.cc	2010-12-23 11:03:09 +0000
+++ b/unittest/gunit/my_regex-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    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,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/opt_range-t.cc'
--- a/unittest/gunit/opt_range-t.cc	2011-05-13 09:36:13 +0000
+++ b/unittest/gunit/opt_range-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    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,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/opt_trace-t.cc'
--- a/unittest/gunit/opt_trace-t.cc	2011-10-05 13:16:38 +0000
+++ b/unittest/gunit/opt_trace-t.cc	2011-12-20 09:51:05 +0000
@@ -18,6 +18,7 @@
    Unit test of the Optimizer trace API (WL#5257)
 */
 
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 
@@ -80,10 +81,15 @@ void do_check_json_compliance(const char
 #endif
 }
 
+extern "C"
+void my_error_handler(uint error, const char *str, myf MyFlags);
+
+
 class TraceContentTest : public ::testing::Test
 {
 public:
   Opt_trace_context trace;
+  static bool oom; ///< whether we got an OOM error from opt trace
 protected:
   static void SetUpTestCase()
   {
@@ -96,18 +102,20 @@ protected:
     // Setting debug flags triggers enter/exit trace, so redirect to /dev/null
     DBUG_SET("o," IF_WIN("NUL", "/dev/null"));
   }
-  static bool oom; ///< whether we got an OOM error from opt trace
-  static void my_error_handler(uint error, const char *str, myf MyFlags)
-  {
-    const uint EE= static_cast<uint>(EE_OUTOFMEMORY);
-    EXPECT_EQ(EE, error);
-    if (error == EE)
-      oom= true;
-  }
+
 };
+bool TraceContentTest::oom;
+
+
+void my_error_handler(uint error, const char *str, myf MyFlags)
+{
+  const uint EE= static_cast<uint>(EE_OUTOFMEMORY);
+  EXPECT_EQ(EE, error);
+  if (error == EE)
+    TraceContentTest::oom= true;
+}
 
 
-bool TraceContentTest::oom;
 
 TEST_F(TraceContentTest, ConstructAndDestruct)
 {

=== modified file 'unittest/gunit/sql_list-t.cc'
--- a/unittest/gunit/sql_list-t.cc	2011-10-06 11:06:34 +0000
+++ b/unittest/gunit/sql_list-t.cc	2011-12-20 09:51:05 +0000
@@ -20,8 +20,7 @@
   http://code.google.com/p/googletest/wiki/GoogleTestPrimer
 */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/sql_plist-t.cc'
--- a/unittest/gunit/sql_plist-t.cc	2011-04-13 11:31:44 +0000
+++ b/unittest/gunit/sql_plist-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/stdcxx-t.cc'
--- a/unittest/gunit/stdcxx-t.cc	2011-11-23 09:14:10 +0000
+++ b/unittest/gunit/stdcxx-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02111-1307  USA */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/tap_event_listener.cc'
--- a/unittest/gunit/tap_event_listener.cc	2011-06-30 15:50:45 +0000
+++ b/unittest/gunit/tap_event_listener.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/test_utils.cc'
--- a/unittest/gunit/test_utils.cc	2011-07-28 10:54:44 +0000
+++ b/unittest/gunit/test_utils.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    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,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/thread_utils-t.cc'
--- a/unittest/gunit/thread_utils-t.cc	2011-06-30 15:50:45 +0000
+++ b/unittest/gunit/thread_utils-t.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

=== modified file 'unittest/gunit/thread_utils.cc'
--- a/unittest/gunit/thread_utils.cc	2011-06-30 15:50:45 +0000
+++ b/unittest/gunit/thread_utils.cc	2011-12-20 09:51:05 +0000
@@ -13,8 +13,7 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA */
 
-// First include (the generated) my_config.h, to get correct platform defines,
-// then gtest.h (before any other MySQL headers), to avoid min() macros etc ...
+// First include (the generated) my_config.h, to get correct platform defines.
 #include "my_config.h"
 #include <gtest/gtest.h>
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (tor.didriksen:3440 to 3449) Tor Didriksen9 Jan