List:Commits« Previous MessageNext Message »
From:Martin Hansson Date:March 16 2011 2:01pm
Subject:bzr commit into mysql-trunk branch (martin.hansson:3299) WL#3724
View as plain text  
#At file:///data0/martin/bzrroot/wl3724/n-mr-o-t/ based on revid:jorgen.loland@stripped

 3299 Martin Hansson	2011-03-16
      WL#3724: Short-Cutting Join Execution: Speeding up star queries

    added:
      mysql-test/r/shortcut.result
      mysql-test/t/shortcut.test
    modified:
      mysql-test/r/func_in_icp.result
      mysql-test/r/func_in_icp_mrr.result
      mysql-test/r/func_in_mrr.result
      mysql-test/r/func_in_mrr_cost.result
      mysql-test/r/func_in_none.result
      mysql-test/r/greedy_optimizer.result
      mysql-test/r/join.result
      mysql-test/r/join_cache_jcl1.result
      mysql-test/r/join_cache_jcl2.result
      mysql-test/r/join_cache_jcl3.result
      mysql-test/r/join_cache_jcl4.result
      mysql-test/r/join_nested.result
      mysql-test/r/myisam_icp.result
      mysql-test/r/myisam_icp_none.result
      mysql-test/r/order_by_icp_mrr.result
      mysql-test/r/order_by_none.result
      mysql-test/r/subselect_innodb.result
      mysql-test/t/subselect_innodb.test
      sql/item.cc
      sql/item.h
      sql/sql_select.cc
      sql/sql_select.h
=== modified file 'mysql-test/r/func_in_icp.result'
--- a/mysql-test/r/func_in_icp.result	2011-01-14 08:20:08 +0000
+++ b/mysql-test/r/func_in_icp.result	2011-03-16 14:01:21 +0000
@@ -372,7 +372,7 @@ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	Shortcut(t3)
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	7	Range checked for each record (index map: 0x1)
 SELECT STRAIGHT_JOIN * FROM t3 
 JOIN t1 ON t3.a=t1.a 
@@ -390,7 +390,7 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
-1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	Shortcut(t3)
 2	DEPENDENT SUBQUERY	t4	index	NULL	PRIMARY	4	NULL	7	Using where; Using index
 SELECT STRAIGHT_JOIN 
 (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 

=== modified file 'mysql-test/r/func_in_icp_mrr.result'
--- a/mysql-test/r/func_in_icp_mrr.result	2011-01-14 08:20:08 +0000
+++ b/mysql-test/r/func_in_icp_mrr.result	2011-03-16 14:01:21 +0000
@@ -372,7 +372,7 @@ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	Shortcut(t3)
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	7	Range checked for each record (index map: 0x1)
 SELECT STRAIGHT_JOIN * FROM t3 
 JOIN t1 ON t3.a=t1.a 
@@ -390,7 +390,7 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
-1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	Shortcut(t3)
 2	DEPENDENT SUBQUERY	t4	index	NULL	PRIMARY	4	NULL	7	Using where; Using index
 SELECT STRAIGHT_JOIN 
 (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 

=== modified file 'mysql-test/r/func_in_mrr.result'
--- a/mysql-test/r/func_in_mrr.result	2011-01-14 08:20:08 +0000
+++ b/mysql-test/r/func_in_mrr.result	2011-03-16 14:01:21 +0000
@@ -372,7 +372,7 @@ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	Shortcut(t3)
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	7	Range checked for each record (index map: 0x1)
 SELECT STRAIGHT_JOIN * FROM t3 
 JOIN t1 ON t3.a=t1.a 
@@ -390,7 +390,7 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
-1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	Shortcut(t3)
 2	DEPENDENT SUBQUERY	t4	index	NULL	PRIMARY	4	NULL	7	Using where; Using index
 SELECT STRAIGHT_JOIN 
 (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 

=== modified file 'mysql-test/r/func_in_mrr_cost.result'
--- a/mysql-test/r/func_in_mrr_cost.result	2011-01-14 08:20:08 +0000
+++ b/mysql-test/r/func_in_mrr_cost.result	2011-03-16 14:01:21 +0000
@@ -372,7 +372,7 @@ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	Shortcut(t3)
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	7	Range checked for each record (index map: 0x1)
 SELECT STRAIGHT_JOIN * FROM t3 
 JOIN t1 ON t3.a=t1.a 
@@ -390,7 +390,7 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
-1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	Shortcut(t3)
 2	DEPENDENT SUBQUERY	t4	index	NULL	PRIMARY	4	NULL	7	Using where; Using index
 SELECT STRAIGHT_JOIN 
 (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 

=== modified file 'mysql-test/r/func_in_none.result'
--- a/mysql-test/r/func_in_none.result	2011-01-14 08:20:08 +0000
+++ b/mysql-test/r/func_in_none.result	2011-03-16 14:01:21 +0000
@@ -371,7 +371,7 @@ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	Shortcut(t3)
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	7	Range checked for each record (index map: 0x1)
 SELECT STRAIGHT_JOIN * FROM t3 
 JOIN t1 ON t3.a=t1.a 
@@ -389,7 +389,7 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
-1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	Shortcut(t3)
 2	DEPENDENT SUBQUERY	t4	index	NULL	PRIMARY	4	NULL	7	Using where; Using index
 SELECT STRAIGHT_JOIN 
 (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 

=== modified file 'mysql-test/r/greedy_optimizer.result'
--- a/mysql-test/r/greedy_optimizer.result	2011-02-17 11:38:07 +0000
+++ b/mysql-test/r/greedy_optimizer.result	2011-03-16 14:01:21 +0000
@@ -175,8 +175,8 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index; Shortcut(t1)
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Shortcut(t1)
 SHOW STATUS LIKE 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	289.430791
@@ -198,8 +198,8 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index; Shortcut(t1)
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Shortcut(t1)
 SHOW STATUS LIKE 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	289.430791
@@ -308,8 +308,8 @@ EXPLAIN select t1.c11 from t1, t2, t3, t
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Shortcut(t1)
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Shortcut(t1)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (BNL, incremental buffers)
@@ -331,8 +331,8 @@ EXPLAIN select t1.c11 from t7, t6, t5, t
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Shortcut(t1)
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Shortcut(t1)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (BNL, incremental buffers)
@@ -355,7 +355,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where; Shortcut(t3)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (BNL, incremental buffers)
@@ -375,7 +375,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where; Shortcut(t3)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (BNL, incremental buffers)
@@ -447,8 +447,8 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index; Shortcut(t1)
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Shortcut(t1)
 SHOW STATUS LIKE 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	289.430791
@@ -470,8 +470,8 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.c21	1	Using where
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index; Shortcut(t1)
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Shortcut(t1)
 SHOW STATUS LIKE 'Last_query_cost';
 Variable_name	Value
 Last_query_cost	289.430791
@@ -720,8 +720,8 @@ EXPLAIN select t1.c11 from t1, t2, t3, t
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Shortcut(t1)
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Shortcut(t1)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (BNL, incremental buffers)
@@ -743,8 +743,8 @@ EXPLAIN select t1.c11 from t7, t6, t5, t
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	Using index
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using index; Shortcut(t1)
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using index; Shortcut(t1)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (BNL, incremental buffers)
@@ -767,7 +767,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where; Shortcut(t3)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (BNL, incremental buffers)
@@ -787,7 +787,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.c12	1	
 1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t1.c14	1	Using where
-1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	4	test.t1.c16	1	Using where; Shortcut(t3)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (BNL, incremental buffers)

=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result	2010-12-16 17:38:26 +0000
+++ b/mysql-test/r/join.result	2011-03-16 14:01:21 +0000
@@ -848,7 +848,7 @@ explain select * from t1, t2, t3 where t
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using index
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index; Shortcut(t1)
 We expect rnd_next=5, and read_key must be 0 because of short-cutting:
 show status like 'Handler_read%';
 Variable_name	Value

=== modified file 'mysql-test/r/join_cache_jcl1.result'
--- a/mysql-test/r/join_cache_jcl1.result	2011-03-02 09:28:23 +0000
+++ b/mysql-test/r/join_cache_jcl1.result	2011-03-16 14:01:21 +0000
@@ -1309,10 +1309,10 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ref	t1_affiliateid,t1_metaid	t1_affiliateid	4	const	1	
 1	SIMPLE	t4	ref	PRIMARY,t4_formatclassid,t4_formats_idx	t4_formats_idx	1	const	1	Using index condition; Using where
 1	SIMPLE	t5	eq_ref	PRIMARY,t5_formattypeid	PRIMARY	4	test.t4.formatclassid	1	Using where
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	
-1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
-1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where
-1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Shortcut(t1)
+1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index; Shortcut(t1)
+1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where; Shortcut(t4)
+1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	Shortcut(t7)
 1	SIMPLE	t9	index	PRIMARY,t9_subgenreid,t9_metaid	PRIMARY	8	NULL	2	Using where; Using index; Using join buffer (BNL, regular buffers)
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	
@@ -1614,7 +1614,7 @@ t1.b IS NULL AND t2.b IS NULL AND t3.b I
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16384	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Shortcut(t1)
 SELECT COUNT(*) FROM t1,t2,t3
 WHERE t1.a=t2.a AND t2.a=t3.a AND
 t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
@@ -1816,8 +1816,8 @@ where t2.b=t1.b and t3.d=t1.d and t4.c=t
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
 1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	1	
-1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	
-1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	
+1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	Shortcut(t1)
+1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	Shortcut(t1)
 select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
 a	b	c	d	e	f	g
@@ -1866,8 +1866,8 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	349	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.id3	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.id4	1	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	Shortcut(t1)
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where; Shortcut(t1)
 SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
 FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
 WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND

=== modified file 'mysql-test/r/join_cache_jcl2.result'
--- a/mysql-test/r/join_cache_jcl2.result	2011-03-02 09:28:23 +0000
+++ b/mysql-test/r/join_cache_jcl2.result	2011-03-16 14:01:21 +0000
@@ -1309,10 +1309,10 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ref	t1_affiliateid,t1_metaid	t1_affiliateid	4	const	1	
 1	SIMPLE	t4	ref	PRIMARY,t4_formatclassid,t4_formats_idx	t4_formats_idx	1	const	1	Using index condition; Using where
 1	SIMPLE	t5	eq_ref	PRIMARY,t5_formattypeid	PRIMARY	4	test.t4.formatclassid	1	Using where
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	
-1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
-1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where
-1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Shortcut(t1)
+1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index; Shortcut(t1)
+1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where; Shortcut(t4)
+1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	Shortcut(t7)
 1	SIMPLE	t9	index	PRIMARY,t9_subgenreid,t9_metaid	PRIMARY	8	NULL	2	Using where; Using index; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	
@@ -1614,7 +1614,7 @@ t1.b IS NULL AND t2.b IS NULL AND t3.b I
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16384	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Shortcut(t1)
 SELECT COUNT(*) FROM t1,t2,t3
 WHERE t1.a=t2.a AND t2.a=t3.a AND
 t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
@@ -1816,8 +1816,8 @@ where t2.b=t1.b and t3.d=t1.d and t4.c=t
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
 1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	1	
-1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	
-1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	
+1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	Shortcut(t1)
+1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	Shortcut(t1)
 select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
 a	b	c	d	e	f	g
@@ -1866,8 +1866,8 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	349	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.id3	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.id4	1	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	Shortcut(t1)
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where; Shortcut(t1)
 SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
 FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
 WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND

=== modified file 'mysql-test/r/join_cache_jcl3.result'
--- a/mysql-test/r/join_cache_jcl3.result	2011-03-02 09:28:23 +0000
+++ b/mysql-test/r/join_cache_jcl3.result	2011-03-16 14:01:21 +0000
@@ -1309,10 +1309,10 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ref	t1_affiliateid,t1_metaid	t1_affiliateid	4	const	1	
 1	SIMPLE	t4	ref	PRIMARY,t4_formatclassid,t4_formats_idx	t4_formats_idx	1	const	1	Using index condition; Using where
 1	SIMPLE	t5	eq_ref	PRIMARY,t5_formattypeid	PRIMARY	4	test.t4.formatclassid	1	Using where
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	
-1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
-1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where
-1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Shortcut(t1)
+1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index; Shortcut(t1)
+1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where; Shortcut(t4)
+1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	Shortcut(t7)
 1	SIMPLE	t9	index	PRIMARY,t9_subgenreid,t9_metaid	PRIMARY	8	NULL	2	Using where; Using index; Using join buffer (BNL, regular buffers)
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	
@@ -1614,7 +1614,7 @@ t1.b IS NULL AND t2.b IS NULL AND t3.b I
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16384	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Shortcut(t1)
 SELECT COUNT(*) FROM t1,t2,t3
 WHERE t1.a=t2.a AND t2.a=t3.a AND
 t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
@@ -1816,8 +1816,8 @@ where t2.b=t1.b and t3.d=t1.d and t4.c=t
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
 1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	1	
-1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	
-1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	
+1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	Shortcut(t1)
+1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	Shortcut(t1)
 select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
 a	b	c	d	e	f	g
@@ -1866,8 +1866,8 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	349	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.id3	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.id4	1	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	Shortcut(t1)
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where; Shortcut(t1)
 SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
 FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
 WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND

=== modified file 'mysql-test/r/join_cache_jcl4.result'
--- a/mysql-test/r/join_cache_jcl4.result	2011-03-02 09:28:23 +0000
+++ b/mysql-test/r/join_cache_jcl4.result	2011-03-16 14:01:21 +0000
@@ -1309,10 +1309,10 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ref	t1_affiliateid,t1_metaid	t1_affiliateid	4	const	1	
 1	SIMPLE	t4	ref	PRIMARY,t4_formatclassid,t4_formats_idx	t4_formats_idx	1	const	1	Using index condition; Using where
 1	SIMPLE	t5	eq_ref	PRIMARY,t5_formattypeid	PRIMARY	4	test.t4.formatclassid	1	Using where
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	
-1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
-1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where
-1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Shortcut(t1)
+1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index; Shortcut(t1)
+1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where; Shortcut(t4)
+1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	Shortcut(t7)
 1	SIMPLE	t9	index	PRIMARY,t9_subgenreid,t9_metaid	PRIMARY	8	NULL	2	Using where; Using index; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	
@@ -1614,7 +1614,7 @@ t1.b IS NULL AND t2.b IS NULL AND t3.b I
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16384	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Shortcut(t1)
 SELECT COUNT(*) FROM t1,t2,t3
 WHERE t1.a=t2.a AND t2.a=t3.a AND
 t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
@@ -1816,8 +1816,8 @@ where t2.b=t1.b and t3.d=t1.d and t4.c=t
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
 1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	1	
-1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	
-1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	
+1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	Shortcut(t1)
+1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	Shortcut(t1)
 select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
 a	b	c	d	e	f	g
@@ -1866,8 +1866,8 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	349	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.id3	1	Using where
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.id4	1	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	
-1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	Shortcut(t1)
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where; Shortcut(t1)
 SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
 FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
 WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND

=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result	2010-12-06 13:12:51 +0000
+++ b/mysql-test/r/join_nested.result	2011-03-16 14:01:21 +0000
@@ -1465,7 +1465,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t3	ref	a	a	5	test.t2.b	X	
 1	SIMPLE	t4	ref	a	a	5	test.t3.b	X	
 1	SIMPLE	t6	ref	a	a	5	test.t4.b	X	
-1	SIMPLE	t5	ref	a	a	5	test.t3.b	X	
+1	SIMPLE	t5	ref	a	a	5	test.t3.b	X	Shortcut(t3)
 drop table t0, t1, t2, t3, t4, t5, t6, t7;
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -1733,7 +1733,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.package_id	1	
 1	SIMPLE	t4	eq_ref	PRIMARY,id	PRIMARY	2	test.t1.carrier	1	
 1	SIMPLE	t5	ref	carrier_id	carrier_id	5	test.t4.id	22	Using index
-1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index
+1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index; Shortcut(t1)
 SELECT COUNT(*) 
 FROM ((t2 JOIN t1 ON t2.package_id = t1.id) 
 JOIN t3 ON t3.package_id = t1.id)

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	2011-01-31 11:56:15 +0000
+++ b/mysql-test/r/myisam_icp.result	2011-03-16 14:01:21 +0000
@@ -511,7 +511,7 @@ WHERE table3.col_int_key != SOME ( SELEC
 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	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	
+1	PRIMARY	table1	ref	col_int_key	col_int_key	5	test.table3.col_int_nokey	2	Shortcut(table3)
 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-01-31 11:56:15 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2011-03-16 14:01:21 +0000
@@ -510,7 +510,7 @@ WHERE table3.col_int_key != SOME ( SELEC
 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	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	
+1	PRIMARY	table1	ref	col_int_key	col_int_key	5	test.table3.col_int_nokey	2	Shortcut(table3)
 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/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result	2011-02-07 09:46:53 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result	2011-03-16 14:01:21 +0000
@@ -498,7 +498,7 @@ EXPLAIN select t1.gid, t2.sid, t3.uid fr
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	PRIMARY,uid	NULL	NULL	NULL	6	Using temporary; Using filesort
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t2.uid	1	Using where; Using index
-1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.gid	1	Using index
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.gid	1	Using index; Shortcut(t2)
 EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using temporary; Using filesort
@@ -507,7 +507,7 @@ EXPLAIN SELECT t1.gid, t2.sid, t3.uid fr
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	PRIMARY,uid	NULL	NULL	NULL	6	Using temporary; Using filesort
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.gid	1	Using index
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t2.uid	1	Using where; Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t2.uid	1	Using where; Using index; Shortcut(t2)
 EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using temporary; Using filesort

=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result	2011-02-07 09:46:53 +0000
+++ b/mysql-test/r/order_by_none.result	2011-03-16 14:01:21 +0000
@@ -497,7 +497,7 @@ EXPLAIN select t1.gid, t2.sid, t3.uid fr
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	PRIMARY,uid	NULL	NULL	NULL	6	Using temporary; Using filesort
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t2.uid	1	Using where; Using index
-1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.gid	1	Using index
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.gid	1	Using index; Shortcut(t2)
 EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using temporary; Using filesort
@@ -506,7 +506,7 @@ EXPLAIN SELECT t1.gid, t2.sid, t3.uid fr
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	PRIMARY,uid	NULL	NULL	NULL	6	Using temporary; Using filesort
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.gid	1	Using index
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t2.uid	1	Using where; Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t2.uid	1	Using where; Using index; Shortcut(t2)
 EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using temporary; Using filesort

=== added file 'mysql-test/r/shortcut.result'
--- a/mysql-test/r/shortcut.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/shortcut.result	2011-03-16 14:01:21 +0000
@@ -0,0 +1,854 @@
+#
+# wl3724: Short-Cutting Join Execution: Speeding up star queries
+#
+#
+# Test 1. Basic tests.
+#
+# Test 1a. Tests of EXPLAIN.
+#
+CREATE TABLE t1 (
+a INT,
+b INT
+);
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+CREATE TABLE t2 (
+a INT,
+KEY( a )
+);
+INSERT INTO t2 VALUES (1), (1), (1), (1), (1);
+CREATE TABLE t3 (
+b INT,
+KEY( b )
+);
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1), (1);
+EXPLAIN SELECT * FROM t1 JOIN t2 USING( a ) JOIN t3 USING( b );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	b	b	5	test.t1.b	2	Using index; Shortcut(t1)
+SELECT * FROM t1 JOIN t2 USING( a ) JOIN t3 USING( b );
+b	a
+# Test that the short-cut does not cross an outer join boundary
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( t2, t3 ) USING( b );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t3	ref	b	b	5	test.t1.b	2	Using index
+1	SIMPLE	t2	index	NULL	a	5	NULL	5	Using index
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( t2 JOIN t3 ON a = b ) USING( b );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t3	ref	b	b	5	test.t1.b	2	Using index
+1	SIMPLE	t2	ref	a	a	5	test.t3.b	2	Using index
+# Test that the optimization is robust wrt reordering of tables
+EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t1.b = t3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	b	b	5	test.t1.b	2	Using index; Shortcut(t1)
+EXPLAIN SELECT * FROM t1, t3, t2 WHERE t1.a = t2.a AND t1.b = t3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	b	b	5	test.t1.b	2	Using index; Shortcut(t1)
+EXPLAIN SELECT * FROM t2, t1, t3 WHERE t1.a = t2.a AND t1.b = t3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	b	b	5	test.t1.b	2	Using index; Shortcut(t1)
+EXPLAIN SELECT * FROM t2, t3, t1 WHERE t1.a = t2.a AND t1.b = t3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	b	b	5	test.t1.b	2	Using index; Shortcut(t1)
+EXPLAIN SELECT * FROM t3, t1, t2 WHERE t1.a = t2.a AND t1.b = t3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	b	b	5	test.t1.b	2	Using index; Shortcut(t1)
+EXPLAIN SELECT * FROM t3, t2, t1 WHERE t1.a = t2.a AND t1.b = t3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	b	b	5	test.t1.b	2	Using index; Shortcut(t1)
+DROP TABLE t1, t2, t3;
+# Test that short-cuts show up in EXPLAIN properly with "Using index" 
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 ( b INT KEY );
+INSERT INTO t2 VALUES (1), (2), (3);
+CREATE TABLE t3 ( c INT KEY );
+INSERT INTO t3 VALUES (1), (2), (3);
+EXPLAIN SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index; Shortcut(t1)
+# Test that we don't take a short-cut if we have successfully read a 
+# row already.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE c > 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE c > 1;
+a	b	c
+2	2	2
+3	3	3
+DROP TABLE t1, t2, t3;
+# 
+# Test 1b.
+# Tests that short-cuts work properly with join buffers.
+# 
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 ( b INT );
+INSERT INTO t2 VALUES (1), (2), (3);
+CREATE TABLE t2key ( b INT KEY );
+INSERT INTO t2key VALUES (1), (2), (3);
+CREATE TABLE t3 ( c INT, d INT );
+INSERT INTO t3 VALUES (1, -1), (2, 1), (3, 1);
+# Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+a	b	c	d
+2	2	2	1
+3	3	3	1
+SET @@optimizer_join_cache_level = 0;
+# Should now use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Shortcut(t1)
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+a	b	c	d
+2	2	2	1
+3	3	3	1
+SET @@optimizer_join_cache_level = DEFAULT;
+# Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2key	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+a	b	c	d
+2	2	2	1
+3	3	3	1
+SET @@optimizer_join_cache_level = 0;
+# Should now use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2key	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Shortcut(t1)
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+a	b	c	d
+2	2	2	1
+3	3	3	1
+SET @@optimizer_join_cache_level = DEFAULT;
+DROP TABLE t1, t2, t2key, t3;
+# Test that we reject a short-cut properly. We have a cartesian product
+# of a table and the result of an outer join. The optimizer places the
+# table between the outer and inner table of the outer join and hence 
+# there is a short-cut from the inner table to the outer table. This
+# short-cut should be rejected.
+CREATE TABLE t1 ( a INT PRIMARY KEY );
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 ( a INT );
+INSERT INTO t2 VALUES (1), (2), (3);
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (1), (2), (3);
+# Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t2, t1 LEFT OUTER JOIN t3 ON (t3.a = t1.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	2	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t2, t1 LEFT OUTER JOIN t3 ON (t3.a = t1.a);
+a	a	a
+1	1	1
+2	1	1
+3	1	1
+1	2	2
+2	2	2
+3	2	2
+DROP TABLE t1, t2, t3;
+# Test 2 
+# That the optimization actually works, i.e. that unneccesary reads are
+# indeed short-cut.
+#
+# Test 2a: We have two tables between giving and receiving end of short-cut:
+# t2 and t3.
+#
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+CREATE TABLE t2 ( a INT, b INT, KEY( a ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+INSERT INTO t2 SELECT * FROM t2;
+CREATE TABLE t3 ( b INT, KEY( b ) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t3 SELECT * FROM t3;
+CREATE TABLE t4 ( b INT, KEY( b ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+FLUSH STATUS;
+EXPLAIN
+SELECT * FROM t1 JOIN t2 USING(a) JOIN t3 ON t2.a = t3.b JOIN t4 ON t1.b = t4.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using index condition
+1	SIMPLE	t3	ref	b	b	5	test.t2.a	2	Using where; Using index
+1	SIMPLE	t4	ref	b	b	5	test.t1.b	4	Using index; Shortcut(t1)
+SELECT * FROM t1 JOIN t2 USING(a) JOIN t3 ON t2.a = t3.b JOIN t4 ON t1.b = t4.b;
+a	b	b	b	b
+SHOW STATUS LIKE 'handler_read_%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	4
+Handler_read_last	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	3
+DROP TABLE t1, t2, t3, t4;
+# Test 2b
+# Short-cuts followed by successful keys. This tests that we perform the
+# optimal number of reads after the short-cut is taken without missing 
+# rows.
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE TABLE t2 ( a INT, KEY (a) );
+INSERT INTO t2 VALUES (1), (2), (2), (2), (2), (2), (3), (4), (4), (4), (5);
+CREATE TABLE t3 ( a INT, KEY (a) );
+INSERT INTO t3 VALUES (1), (3), (5), (-1), (-1), (-1), (-1), (-1), (-1);
+FLUSH STATUS;
+EXPLAIN
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	a	a	5	test.t1.a	2	Using index; Shortcut(t1)
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+a
+1
+3
+5
+SHOW STATUS LIKE 'handler_read_%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	10
+Handler_read_last	0
+Handler_read_next	6
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	6
+DROP TABLE t1, t2, t3;
+# Test 2c
+# Test that we don't erroneously take a short-cut at the end of a table
+# scan.
+set optimizer_join_cache_level = 0;
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 ( a INT, KEY (a) );
+INSERT INTO t2 VALUES (1), (1), (2);
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (1), (3);
+EXPLAIN
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Shortcut(t1)
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+a
+1
+1
+DROP TABLE t1, t2, t3;
+SET @@optimizer_join_cache_level = DEFAULT;
+# 
+# Test 3.
+# Test for outer joins. We may not take short-cuts across outer join
+# boundaries. An outer join boundary goes before the first inner table for an
+# outer join.
+#
+# Test 3a.
+#
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+CREATE TABLE t2 ( a INT, b INT, KEY( a ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+INSERT INTO t2 SELECT * FROM t2;
+CREATE TABLE t3 ( b INT, KEY( b ) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t3 SELECT * FROM t3;
+CREATE TABLE t4 ( b INT, KEY( b ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+CREATE TABLE t5 ( b INT, KEY( b ) );
+INSERT INTO t5 VALUES (1), (1), (1), (1), (1), (1), (1), (1);
+INSERT INTO t5 SELECT * FROM t5;
+# Test of two outer join boundaries in one join.
+EXPLAIN
+SELECT *
+FROM t1 LEFT JOIN (t2 JOIN t3 ON t2.a = t3.b) USING ( a )
+LEFT JOIN t4 ON t1.a = t4.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	
+1	SIMPLE	t3	ref	b	b	5	test.t2.a	2	Using index
+1	SIMPLE	t4	ref	b	b	5	test.t1.a	4	Using index
+EXPLAIN
+SELECT * 
+FROM t1 LEFT JOIN (t2 JOIN t3 ON t2.a = t3.b) USING(a)
+LEFT JOIN (t4 JOIN t5 USING( b )) ON t1.a = t4.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	
+1	SIMPLE	t3	ref	b	b	5	test.t2.a	2	Using index
+1	SIMPLE	t4	ref	b	b	5	test.t1.a	4	Using index
+1	SIMPLE	t5	ref	b	b	5	test.t4.b	2	Using index
+DROP TABLE t1, t2, t3, t4, t5;
+# Test 3b. 
+# Tests that we do not miss any data due to short-cutting.
+CREATE TABLE t1 ( a INT, b INT );
+CREATE TABLE t2 ( a INT, b INT, PRIMARY KEY (a,b) );
+CREATE TABLE t3 ( a INT, b INT, PRIMARY KEY (a,b) );
+INSERT INTO t1 VALUES ( 1, 1 ), ( 2, 1 ), ( 1, 3 );
+INSERT INTO t2 VALUES ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
+INSERT INTO t3 VALUES ( 1, 1 ), ( 2, 1 ), ( 1, 3 );
+# Test of the short-cut detection algorithm. When traversing the 
+# push-down conditions the algorithm will first find that the 
+# prospective short-cut t3->t1, but this must be invalidated due to the 
+# later found dependency t3->t2.
+EXPLAIN 
+SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.b = t3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.b,test.t1.b	1	Using index
+SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.b = t3.b;
+a	b	a	b	a	b
+1	1	1	1	1	1
+2	1	2	2	2	1
+1	3	1	1	1	3
+# Test of internal representation of pushdown conditions.
+# The join conditions will be encoded in ref access, while the WHERE
+# condition remains in the pushdown condition.
+EXPLAIN 
+SELECT * 
+FROM t1 JOIN t2 ON t1.a = t2.a JOIN t3 ON t1.b = t3.a 
+WHERE t3.b + 1 = t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using where; Using index
+# Short-cut is actually applicable in this case, thanks to equality 
+# propagation. The WHERE condition t3.a + 1 = t2.a is rewritten into 
+# t1.b + 1 = t1.a, and hence we have an optimizable star query.
+EXPLAIN 
+SELECT * 
+FROM t1 JOIN t2 ON t1.a = t2.a JOIN t3 ON t1.b = t3.a 
+WHERE t3.a + 1 = t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using index; Shortcut(t1)
+DROP TABLE t1, t2, t3;
+# Test 3c
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (1), (1), (1);
+CREATE TABLE t2 ( a INT, KEY (a), b INT, c INT );
+INSERT INTO t2 VALUES (1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1);
+INSERT INTO t2 SELECT * FROM t2;
+INSERT INTO t2 SELECT * FROM t2;
+CREATE TABLE t3 ( a INT, KEY (a) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (3);
+CREATE TABLE t4 ( a INT, KEY (a), b INT );
+INSERT INTO t4 VALUES (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1);
+INSERT INTO t4 SELECT * FROM t4;
+#
+# Test that the short-cut t4->t1 is rejected in favor of t4->t2.
+#
+EXPLAIN
+SELECT STRAIGHT_JOIN * 
+FROM t1 JOIN t2 ON t1.a = t2.a 
+JOIN t3 ON t2.b = t3.a
+JOIN t4 ON t1.a = t4.a AND t2.c = t4.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	Using where
+1	SIMPLE	t3	ref	a	a	5	test.t2.b	2	Using index
+1	SIMPLE	t4	ref	a	a	5	test.t1.a	2	Using where; Shortcut(t2)
+DROP TABLE t1, t2, t3, t4;
+#
+# Test 4. More tests of reordering of tables.
+#
+CREATE TABLE t1( a INT, b INT );
+INSERT INTO t1 VALUES ( 2, 2 );
+INSERT INTO t1 VALUES ( 2, 2 );
+CREATE TABLE t2( a INT PRIMARY KEY );
+INSERT INTO t2 VALUES ( 1 );
+INSERT INTO t2 VALUES ( 2 );
+CREATE TABLE t3( a INT, INDEX( a ) );
+INSERT INTO t3 VALUES ( 6 );
+INSERT INTO t3 VALUES ( 5 );
+INSERT INTO t3 VALUES ( 2 );
+EXPLAIN
+SELECT t2.* FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	ref	a	a	5	test.t1.b	2	Using index; Shortcut(t1)
+SELECT t2.* FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+a
+2
+2
+EXPLAIN
+SELECT * FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	ref	a	a	5	test.t1.b	2	Using index; Shortcut(t1)
+SELECT * FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+a	a	b	a
+2	2	2	2
+2	2	2	2
+DROP TABLE t1, t2, t3;
+# 
+# Test 5.
+# Tests that a short-cut does not cross a plan node that is using join 
+# buffering.
+# 
+# Test 5a.
+# 
+CREATE TABLE t1 ( a INT, c INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+CREATE TABLE t2 ( a INT, b INT, KEY( a ), KEY( b ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+CREATE TABLE t3 ( a INT, KEY( a ) );
+INSERT INTO t3 VALUES (2), (2), (2), (2), (2);
+INSERT INTO t3 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t3;
+CREATE TABLE t4 ( a INT, KEY( a ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+FLUSH STATUS;
+# Should not use short-cuts.
+EXPLAIN 
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ALL	a,b	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t3	ref	a	a	5	test.t2.b	4	Using index
+1	SIMPLE	t4	ref	a	a	5	test.t1.c	9	Using index
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+a	c	a	b	a	a
+# Should not use short-cuts.
+EXPLAIN 
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ALL	a,b	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t3	ref	a	a	5	test.t2.b	4	Using index
+1	SIMPLE	t4	ref	a	a	5	test.t1.c	9	Using index
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+a	c	a	b	a	a
+SHOW STATUS LIKE 'handler_read_%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	410
+Handler_read_last	0
+Handler_read_next	400
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	18
+DROP TABLE t1, t2, t3, t4;
+# 
+# Test 5b.
+# 
+CREATE TABLE t1 ( a INT, c INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+CREATE TABLE t2 ( a INT, b INT, KEY( a, b ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (2), (2), (2), (2), (2);
+INSERT INTO t3 SELECT * FROM t3;
+CREATE TABLE t4 ( a INT );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+FLUSH STATUS;
+# Should not use short-cuts.
+EXPLAIN 
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	1	Using index
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+a	c	a	b	a	a
+SHOW STATUS LIKE 'handler_read_%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	2
+Handler_read_last	0
+Handler_read_next	5
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	35
+DROP TABLE t1, t2, t3, t4;
+#
+# Test 6. Test of short-cuts in conjunction with outer join.
+#
+# Test 6a. Test of outer join detection.
+# In this case the execution order is t1, t2, t3, t4.
+# t2, t3 and t4 will form an 'outer join nest' with which t1 is left 
+# outer joined. But (t3, t2) is left joined with t4. Since t4 is just one
+# table, however, the 'outer join nest' of t4 is only implicitly 
+# represented in the query plan. The only way to detect it is by looking 
+# at t4's first_inner pointer, which would be null for the last inner table
+# in an outer join nest. But since it points to itseld, t4 is in its own
+# nest. But there is no NESTED_JOIN to represent it, since it follows the
+# 'normal' left-deep tree representation.
+#
+CREATE TABLE t1 (a INT, b INT, c INT);
+CREATE TABLE t2 (d INT, e INT, f INT);
+CREATE TABLE t3 (g INT, h INT, i INT);
+CREATE TABLE t4 (j INT, k INT, l INT);
+INSERT INTO t1 VALUES (3,1,0), (2,2,0), (3,3,0);
+INSERT INTO t2 VALUES (1,1,0), (2,2,0);
+INSERT INTO t3 VALUES (3,2,0), (6,2,0), (6,1,0);
+INSERT INTO t4 VALUES (0,2,0), (1,2,0);
+EXPLAIN
+SELECT a, b, c, d, e, f, g, h, i, j, k, l
+FROM t1 LEFT JOIN ( (t3, t2) LEFT JOIN t4 ON t2.e = t4.k AND t3.h < 10 ) ON t1.b = t2.e;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+SELECT *
+FROM t1 LEFT JOIN ( (t3, t2) LEFT JOIN t4 ON t2.e = t4.k AND t3.h < 10 ) ON t1.b = t2.e;
+a	b	c	g	h	i	d	e	f	j	k	l
+2	2	0	3	2	0	2	2	0	0	2	0
+2	2	0	6	2	0	2	2	0	0	2	0
+2	2	0	6	1	0	2	2	0	0	2	0
+2	2	0	3	2	0	2	2	0	1	2	0
+2	2	0	6	2	0	2	2	0	1	2	0
+2	2	0	6	1	0	2	2	0	1	2	0
+3	1	0	3	2	0	1	1	0	NULL	NULL	NULL
+3	1	0	6	2	0	1	1	0	NULL	NULL	NULL
+3	1	0	6	1	0	1	1	0	NULL	NULL	NULL
+3	3	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+DROP TABLE t1, t2, t3, t4;
+#
+# Test 6b.
+# We may take short-cuts across or inside an outer join, as long as we 
+# don't fail due to an outer join predicate. It goes as follows.
+#
+# - We may take a short-cut across a nested outer join.
+# - We may take short-cuts inside a nested inner join sequence. 
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2 ( b INT, KEY    ( b ) );
+INSERT INTO t2 VALUES (1), (2),      (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3 ( c INT, KEY( c ) );
+INSERT INTO t3 VALUES (1), (2), (3), (4),      (6), (7), (8), (9), (10);
+CREATE TABLE t4 ( d INT, KEY( d ) );
+INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6), (7), (8),      (10);
+INSERT INTO t4 VALUES (10), (10), (10), (10);
+CREATE TABLE t5 ( e INT, KEY( e ) );
+INSERT INTO t5 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+# Outer join followed by a table that is inner joined with the first.
+EXPLAIN 
+SELECT * FROM t1 LEFT JOIN t2 ON a = b JOIN t3 ON a = c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	c	c	5	test.t1.a	2	Using index; Shortcut(t1)
+SELECT * FROM t1 LEFT JOIN t2 ON a = b JOIN t3 ON a = c;
+a	b	c
+1	1	1
+2	2	2
+3	NULL	3
+4	4	4
+6	6	6
+7	7	7
+8	8	8
+9	9	9
+10	10	10
+# Short-cut across a nested outer join
+EXPLAIN
+SELECT * FROM t1 JOIN (t2 LEFT JOIN t3 ON b = c) ON a = b JOIN t4 ON a = d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	c	c	5	test.t1.a	2	Using index
+1	SIMPLE	t4	ref	d	d	5	test.t1.a	2	Using index; Shortcut(t1)
+SELECT * FROM t1 JOIN (t2 LEFT JOIN t3 ON b = c) ON a = b JOIN t4 ON a = d;
+a	b	c	d
+1	1	1	1
+2	2	2	2
+4	4	4	4
+5	5	NULL	5
+6	6	6	6
+7	7	7	7
+8	8	8	8
+10	10	10	10
+10	10	10	10
+10	10	10	10
+10	10	10	10
+10	10	10	10
+# Short-cut across a nested outer join with a nested inner join
+EXPLAIN
+SELECT * FROM t1
+JOIN (t2 LEFT JOIN (t3 JOIN t4 ON c = d) ON b = d) ON a = b
+JOIN t5 ON a = e;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	2	Using where; Using index
+1	SIMPLE	t3	ref	c	c	5	test.t2.b	2	Using where; Using index
+1	SIMPLE	t4	ref	d	d	5	test.t3.c	2	Using where; Using index
+1	SIMPLE	t5	ref	e	e	5	test.t2.b	2	Using where; Using index; Shortcut(t2)
+SELECT * FROM t1
+JOIN (t2 LEFT JOIN (t3 JOIN t4 ON c = d) ON b = d) ON a = b
+JOIN t5 ON a = e;
+a	b	c	d	e
+1	1	1	1	1
+2	2	2	2	2
+4	4	4	4	4
+5	5	NULL	NULL	5
+6	6	6	6	6
+7	7	7	7	7
+8	8	8	8	8
+9	9	NULL	NULL	9
+10	10	10	10	10
+10	10	10	10	10
+10	10	10	10	10
+10	10	10	10	10
+10	10	10	10	10
+# Short-cut from last inner table inside an inner join that is nested 
+# within an outer join
+EXPLAIN 
+SELECT * FROM t1 LEFT JOIN (t2 JOIN t3 ON b = c JOIN t4 ON b = d) on a = b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	c	c	5	test.t2.b	2	Using index
+1	SIMPLE	t4	ref	d	d	5	test.t2.b	2	Using index; Shortcut(t2)
+SELECT * FROM t1 LEFT JOIN (t2 JOIN t3 ON b = c JOIN t4 ON b = d) on a = b;
+a	b	c	d
+1	1	1	1
+2	2	2	2
+3	NULL	NULL	NULL
+4	4	4	4
+5	NULL	NULL	NULL
+6	6	6	6
+7	7	7	7
+8	8	8	8
+9	NULL	NULL	NULL
+10	10	10	10
+10	10	10	10
+10	10	10	10
+10	10	10	10
+10	10	10	10
+# Short-cut from not last inner table inside an inner join that is nested
+# within an outer join
+EXPLAIN 
+SELECT * FROM t1
+LEFT JOIN 
+(t2 JOIN t3 ON b = c JOIN t4 ON b = d JOIN t5 ON d = e)
+ON a = b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	c	c	5	test.t2.b	2	Using index
+1	SIMPLE	t4	ref	d	d	5	test.t2.b	2	Using index; Shortcut(t2)
+1	SIMPLE	t5	ref	e	e	5	test.t4.d	2	Using index
+SELECT * FROM t1
+LEFT JOIN 
+(t2 JOIN t3 ON b = c JOIN t4 ON b = d JOIN t5 ON d = e)
+ON a = b;
+a	b	c	d	e
+1	1	1	1	1
+2	2	2	2	2
+3	NULL	NULL	NULL	NULL
+4	4	4	4	4
+5	NULL	NULL	NULL	NULL
+6	6	6	6	6
+7	7	7	7	7
+8	8	8	8	8
+9	NULL	NULL	NULL	NULL
+10	10	10	10	10
+10	10	10	10	10
+10	10	10	10	10
+10	10	10	10	10
+10	10	10	10	10
+# Illegal scenario for short-cut. Should not be taken.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN t3 ON a = c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	c	c	5	test.t1.a	2	Using index
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN t3 ON a = c;
+a	b	c
+1	1	1
+2	2	2
+4	4	4
+5	5	NULL
+6	6	6
+7	7	7
+8	8	8
+9	9	9
+10	10	10
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN (t3 JOIN t4 ON c = d) ON a = c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t2	ref	b	b	5	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	c	c	5	test.t1.a	2	Using index
+1	SIMPLE	t4	ref	d	d	5	test.t1.a	2	Using index
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN (t3 JOIN t4 ON c = d) ON a = c;
+a	b	c	d
+1	1	1	1
+2	2	2	2
+4	4	4	4
+5	5	NULL	NULL
+6	6	6	6
+7	7	7	7
+8	8	8	8
+9	9	NULL	NULL
+10	10	10	10
+10	10	10	10
+10	10	10	10
+10	10	10	10
+10	10	10	10
+DROP TABLE t1, t2, t3, t4, t5;
+#
+# Test 6c.
+# Tests that short-cuts work even if a row was read and then rejected.
+# This happens for table scan without join buffering.
+#
+set optimizer_join_cache_level = 0;;
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 ( b INT );
+INSERT INTO t2 VALUES (1), (2), (2), (2), (2), (3);
+CREATE TABLE t3 ( c INT, d INT );
+INSERT INTO t3(c) VALUES (1), (3), (4), (4), (4), (4);
+FLUSH STATUS;
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	6	Using where; Shortcut(t1)
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+a	b	c	d
+1	1	1	NULL
+3	3	3	NULL
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	0
+Handler_read_last	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	41
+FLUSH STATUS;
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	6	Using where; Shortcut(t1)
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+a	b	c	d
+SHOW STATUS LIKE 'handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	0
+Handler_read_last	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	34
+SET @@optimizer_join_cache_level = DEFAULT;
+DROP TABLE t1, t2, t3;
+#
+# Test 7.
+# Combination test. This test contains multiple nesting levels, outer 
+# join, table reordering, and NULL comparison. Short-cut should not be 
+# used.
+#
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES ( 1,1 ), ( 2,2 ), ( 3,3 );
+CREATE TABLE t2( a INT, b INT, filler CHAR( 200 ), KEY( a ) );
+INSERT INTO t2( a, b ) VALUES ( 0, 0 ), ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
+CREATE TABLE t3 LIKE t2;
+INSERT INTO t3 SELECT * FROM t2;
+INSERT INTO t3 SELECT * FROM t2;
+CREATE TABLE t4 LIKE t3;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t4 SELECT * FROM t3;
+EXPLAIN
+SELECT *
+FROM t3 JOIN t1 LEFT JOIN ( t2 JOIN t4 ON t4.a = t2.b ) ON t2.a = t1.b
+WHERE t3.a <=> t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	a	a	5	test.t1.b	2	
+1	SIMPLE	t4	ref	a	a	5	test.t2.b	2	
+1	SIMPLE	t3	ref	a	a	5	test.t2.b	1	Using index condition
+SELECT *
+FROM t3 JOIN t1 LEFT JOIN ( t2 JOIN t4 ON t4.a = t2.b ) ON t2.a = t1.b
+WHERE t3.a <=> t2.b;
+a	b	filler	a	b	a	b	filler	a	b	filler
+1	1	NULL	1	1	1	1	NULL	1	1	NULL
+1	1	NULL	1	1	1	1	NULL	1	1	NULL
+1	1	NULL	1	1	1	1	NULL	1	1	NULL
+1	1	NULL	1	1	1	1	NULL	1	1	NULL
+1	1	NULL	1	1	1	1	NULL	1	1	NULL
+1	1	NULL	1	1	1	1	NULL	1	1	NULL
+1	1	NULL	1	1	1	1	NULL	1	1	NULL
+1	1	NULL	1	1	1	1	NULL	1	1	NULL
+2	2	NULL	2	2	2	2	NULL	2	2	NULL
+2	2	NULL	2	2	2	2	NULL	2	2	NULL
+2	2	NULL	2	2	2	2	NULL	2	2	NULL
+2	2	NULL	2	2	2	2	NULL	2	2	NULL
+2	2	NULL	2	2	2	2	NULL	2	2	NULL
+2	2	NULL	2	2	2	2	NULL	2	2	NULL
+2	2	NULL	2	2	2	2	NULL	2	2	NULL
+2	2	NULL	2	2	2	2	NULL	2	2	NULL
+3	3	NULL	3	3	3	3	NULL	3	3	NULL
+3	3	NULL	3	3	3	3	NULL	3	3	NULL
+3	3	NULL	3	3	3	3	NULL	3	3	NULL
+3	3	NULL	3	3	3	3	NULL	3	3	NULL
+3	3	NULL	3	3	3	3	NULL	3	3	NULL
+3	3	NULL	3	3	3	3	NULL	3	3	NULL
+3	3	NULL	3	3	3	3	NULL	3	3	NULL
+3	3	NULL	3	3	3	3	NULL	3	3	NULL
+DROP TABLE t1, t2, t3, t4;

=== modified file 'mysql-test/r/subselect_innodb.result'
--- a/mysql-test/r/subselect_innodb.result	2011-03-02 09:28:23 +0000
+++ b/mysql-test/r/subselect_innodb.result	2011-03-16 14:01:21 +0000
@@ -278,7 +278,7 @@ EXPLAIN EXTENDED SELECT t2.*, t4.DOCTYPE
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	DDOCTYPEID_IDX	NULL	NULL	NULL	9	100.00	Using where
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCID	1	100.00	
-1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCTYPEID	1	100.00	
+1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCTYPEID	1	100.00	Shortcut(t2)
 2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	100.00	Using where
 3	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	100.00	Using where
 4	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	34	func	1	100.00	Using where
@@ -286,6 +286,9 @@ id	select_type	table	type	possible_keys	
 6	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	func	1	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTYPEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and <in_optimizer>(`test`.`t2`.`FOLDERID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`FOLDERID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level3') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level2') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`))))))
+SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
+DOCID	DOCNAME	DOCTYPEID	FOLDERID	AUTHOR	CREATED	TITLE	SUBTITLE	DOCABSTRACT	PUBLISHDATE	EXPIRATIONDATE	LOCKEDBY	STATUS	PARENTDOCID	REPID	MODIFIED	MODIFIER	PUBLISHSTATUS	ORIGINATOR	DOCTYPENAME	CONTENTSIZE	MIMETYPE
+c373e9f5ad07993f3859444553544200	Last Discussion	c373e9f5ad079174ff17444553544200	c373e9f5ad0796c0eca4444553544200	Goldilocks	2003-06-09 11:21:06	Title: Last Discussion	NULL	Setting new abstract and keeping doc checked out	2003-06-09 10:51:26	2003-06-09 10:51:26	NULL	NULL	NULL	03eea05112b845949f3fd03278b5fe43	2003-06-09 11:21:06	admin	0	NULL	Discussion	NULL	NULL
 drop table t1, t2, t3, t4;
 CREATE TABLE t1 (
 school_name varchar(45) NOT NULL,

=== added file 'mysql-test/t/shortcut.test'
--- a/mysql-test/t/shortcut.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/shortcut.test	2011-03-16 14:01:21 +0000
@@ -0,0 +1,580 @@
+--echo #
+--echo # wl3724: Short-Cutting Join Execution: Speeding up star queries
+--echo #
+
+--echo #
+--echo # Test 1. Basic tests.
+--echo #
+--echo # Test 1a. Tests of EXPLAIN.
+--echo #
+CREATE TABLE t1 (
+  a INT,
+  b INT
+);
+
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+
+CREATE TABLE t2 (
+  a INT,
+  KEY( a )
+);
+INSERT INTO t2 VALUES (1), (1), (1), (1), (1);
+
+CREATE TABLE t3 (
+  b INT,
+  KEY( b )
+);
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1), (1);
+
+EXPLAIN SELECT * FROM t1 JOIN t2 USING( a ) JOIN t3 USING( b );
+
+SELECT * FROM t1 JOIN t2 USING( a ) JOIN t3 USING( b );
+
+--echo # Test that the short-cut does not cross an outer join boundary
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( t2, t3 ) USING( b );
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( t2 JOIN t3 ON a = b ) USING( b );
+
+--echo # Test that the optimization is robust wrt reordering of tables
+EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t1.b = t3.b;
+EXPLAIN SELECT * FROM t1, t3, t2 WHERE t1.a = t2.a AND t1.b = t3.b;
+
+EXPLAIN SELECT * FROM t2, t1, t3 WHERE t1.a = t2.a AND t1.b = t3.b;
+EXPLAIN SELECT * FROM t2, t3, t1 WHERE t1.a = t2.a AND t1.b = t3.b;
+
+EXPLAIN SELECT * FROM t3, t1, t2 WHERE t1.a = t2.a AND t1.b = t3.b;
+EXPLAIN SELECT * FROM t3, t2, t1 WHERE t1.a = t2.a AND t1.b = t3.b;
+
+DROP TABLE t1, t2, t3;
+
+--echo # Test that short-cuts show up in EXPLAIN properly with "Using index" 
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+
+CREATE TABLE t2 ( b INT KEY );
+INSERT INTO t2 VALUES (1), (2), (3);
+
+CREATE TABLE t3 ( c INT KEY );
+INSERT INTO t3 VALUES (1), (2), (3);
+
+EXPLAIN SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+
+--echo # Test that we don't take a short-cut if we have successfully read a 
+--echo # row already.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE c > 1;
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE c > 1;
+
+DROP TABLE t1, t2, t3;
+--echo # 
+--echo # Test 1b.
+--echo # Tests that short-cuts work properly with join buffers.
+--echo # 
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+
+CREATE TABLE t2 ( b INT );
+INSERT INTO t2 VALUES (1), (2), (3);
+
+CREATE TABLE t2key ( b INT KEY );
+INSERT INTO t2key VALUES (1), (2), (3);
+
+CREATE TABLE t3 ( c INT, d INT );
+INSERT INTO t3 VALUES (1, -1), (2, 1), (3, 1);
+
+--echo # Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+
+SET @@optimizer_join_cache_level = 0;
+
+--echo # Should now use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+
+SET @@optimizer_join_cache_level = DEFAULT;
+
+--echo # Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+
+SET @@optimizer_join_cache_level = 0;
+
+--echo # Should now use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+
+SET @@optimizer_join_cache_level = DEFAULT;
+
+DROP TABLE t1, t2, t2key, t3;
+
+--echo # Test that we reject a short-cut properly. We have a cartesian product
+--echo # of a table and the result of an outer join. The optimizer places the
+--echo # table between the outer and inner table of the outer join and hence 
+--echo # there is a short-cut from the inner table to the outer table. This
+--echo # short-cut should be rejected.
+CREATE TABLE t1 ( a INT PRIMARY KEY );
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 ( a INT );
+INSERT INTO t2 VALUES (1), (2), (3);
+
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (1), (2), (3);
+
+--echo # Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t2, t1 LEFT OUTER JOIN t3 ON (t3.a = t1.a);
+SELECT * FROM t2, t1 LEFT OUTER JOIN t3 ON (t3.a = t1.a);
+DROP TABLE t1, t2, t3;
+--echo # Test 2 
+--echo # That the optimization actually works, i.e. that unneccesary reads are
+--echo # indeed short-cut.
+--echo #
+--echo # Test 2a: We have two tables between giving and receiving end of short-cut:
+--echo # t2 and t3.
+--echo #
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+
+CREATE TABLE t2 ( a INT, b INT, KEY( a ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+INSERT INTO t2 SELECT * FROM t2;
+
+CREATE TABLE t3 ( b INT, KEY( b ) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t3 SELECT * FROM t3;
+
+CREATE TABLE t4 ( b INT, KEY( b ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+
+FLUSH STATUS;
+
+EXPLAIN
+SELECT * FROM t1 JOIN t2 USING(a) JOIN t3 ON t2.a = t3.b JOIN t4 ON t1.b = t4.b;
+SELECT * FROM t1 JOIN t2 USING(a) JOIN t3 ON t2.a = t3.b JOIN t4 ON t1.b = t4.b;
+
+SHOW STATUS LIKE 'handler_read_%';
+DROP TABLE t1, t2, t3, t4;
+--echo # Test 2b
+--echo # Short-cuts followed by successful keys. This tests that we perform the
+--echo # optimal number of reads after the short-cut is taken without missing 
+--echo # rows.
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+
+CREATE TABLE t2 ( a INT, KEY (a) );
+INSERT INTO t2 VALUES (1), (2), (2), (2), (2), (2), (3), (4), (4), (4), (5);
+
+CREATE TABLE t3 ( a INT, KEY (a) );
+INSERT INTO t3 VALUES (1), (3), (5), (-1), (-1), (-1), (-1), (-1), (-1);
+
+FLUSH STATUS;
+
+EXPLAIN
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+
+SHOW STATUS LIKE 'handler_read_%';
+
+DROP TABLE t1, t2, t3;
+--echo # Test 2c
+--echo # Test that we don't erroneously take a short-cut at the end of a table
+--echo # scan.
+let $requires_join_cache_level = 0;
+--source include/have_join_cache_level.inc
+--eval set optimizer_join_cache_level = $requires_join_cache_level
+
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2);
+
+CREATE TABLE t2 ( a INT, KEY (a) );
+INSERT INTO t2 VALUES (1), (1), (2);
+
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (1), (3);
+
+EXPLAIN
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+
+DROP TABLE t1, t2, t3;
+
+SET @@optimizer_join_cache_level = DEFAULT;
+--echo # 
+--echo # Test 3.
+--echo # Test for outer joins. We may not take short-cuts across outer join
+--echo # boundaries. An outer join boundary goes before the first inner table for an
+--echo # outer join.
+--echo #
+--echo # Test 3a.
+--echo #
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+
+CREATE TABLE t2 ( a INT, b INT, KEY( a ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+INSERT INTO t2 SELECT * FROM t2;
+
+CREATE TABLE t3 ( b INT, KEY( b ) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t3 SELECT * FROM t3;
+
+CREATE TABLE t4 ( b INT, KEY( b ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+
+CREATE TABLE t5 ( b INT, KEY( b ) );
+INSERT INTO t5 VALUES (1), (1), (1), (1), (1), (1), (1), (1);
+INSERT INTO t5 SELECT * FROM t5;
+
+--echo # Test of two outer join boundaries in one join.
+EXPLAIN
+SELECT *
+FROM t1 LEFT JOIN (t2 JOIN t3 ON t2.a = t3.b) USING ( a )
+        LEFT JOIN t4 ON t1.a = t4.b;
+
+EXPLAIN
+SELECT * 
+FROM t1 LEFT JOIN (t2 JOIN t3 ON t2.a = t3.b) USING(a)
+        LEFT JOIN (t4 JOIN t5 USING( b )) ON t1.a = t4.b;
+
+DROP TABLE t1, t2, t3, t4, t5;
+--echo # Test 3b. 
+--echo # Tests that we do not miss any data due to short-cutting.
+
+CREATE TABLE t1 ( a INT, b INT );
+CREATE TABLE t2 ( a INT, b INT, PRIMARY KEY (a,b) );
+CREATE TABLE t3 ( a INT, b INT, PRIMARY KEY (a,b) );
+
+INSERT INTO t1 VALUES ( 1, 1 ), ( 2, 1 ), ( 1, 3 );
+INSERT INTO t2 VALUES ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
+INSERT INTO t3 VALUES ( 1, 1 ), ( 2, 1 ), ( 1, 3 );
+
+--echo # Test of the short-cut detection algorithm. When traversing the 
+--echo # push-down conditions the algorithm will first find that the 
+--echo # prospective short-cut t3->t1, but this must be invalidated due to the 
+--echo # later found dependency t3->t2.
+EXPLAIN 
+SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.b = t3.b;
+SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.b = t3.b;
+
+--echo # Test of internal representation of pushdown conditions.
+--echo # The join conditions will be encoded in ref access, while the WHERE
+--echo # condition remains in the pushdown condition.
+EXPLAIN 
+SELECT * 
+FROM t1 JOIN t2 ON t1.a = t2.a JOIN t3 ON t1.b = t3.a 
+WHERE t3.b + 1 = t2.b;
+
+--echo # Short-cut is actually applicable in this case, thanks to equality 
+--echo # propagation. The WHERE condition t3.a + 1 = t2.a is rewritten into 
+--echo # t1.b + 1 = t1.a, and hence we have an optimizable star query.
+EXPLAIN 
+SELECT * 
+FROM t1 JOIN t2 ON t1.a = t2.a JOIN t3 ON t1.b = t3.a 
+WHERE t3.a + 1 = t2.a;
+
+DROP TABLE t1, t2, t3;
+--echo # Test 3c
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (1), (1), (1);
+
+CREATE TABLE t2 ( a INT, KEY (a), b INT, c INT );
+INSERT INTO t2 VALUES (1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1);
+INSERT INTO t2 SELECT * FROM t2;
+INSERT INTO t2 SELECT * FROM t2;
+
+CREATE TABLE t3 ( a INT, KEY (a) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (3);
+
+CREATE TABLE t4 ( a INT, KEY (a), b INT );
+INSERT INTO t4 VALUES (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1);
+INSERT INTO t4 SELECT * FROM t4;
+
+--echo #
+--echo # Test that the short-cut t4->t1 is rejected in favor of t4->t2.
+--echo #
+EXPLAIN
+SELECT STRAIGHT_JOIN * 
+FROM t1 JOIN t2 ON t1.a = t2.a 
+        JOIN t3 ON t2.b = t3.a
+        JOIN t4 ON t1.a = t4.a AND t2.c = t4.b;
+
+DROP TABLE t1, t2, t3, t4;
+
+--echo #
+--echo # Test 4. More tests of reordering of tables.
+--echo #
+CREATE TABLE t1( a INT, b INT );
+
+INSERT INTO t1 VALUES ( 2, 2 );
+INSERT INTO t1 VALUES ( 2, 2 );
+
+CREATE TABLE t2( a INT PRIMARY KEY );
+
+INSERT INTO t2 VALUES ( 1 );
+INSERT INTO t2 VALUES ( 2 );
+
+CREATE TABLE t3( a INT, INDEX( a ) );
+
+INSERT INTO t3 VALUES ( 6 );
+INSERT INTO t3 VALUES ( 5 );
+INSERT INTO t3 VALUES ( 2 );
+
+EXPLAIN
+SELECT t2.* FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+SELECT t2.* FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+
+EXPLAIN
+SELECT * FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+SELECT * FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+
+DROP TABLE t1, t2, t3;
+
+
+--echo # 
+--echo # Test 5.
+--echo # Tests that a short-cut does not cross a plan node that is using join 
+--echo # buffering.
+--echo # 
+--echo # Test 5a.
+--echo # 
+CREATE TABLE t1 ( a INT, c INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+
+CREATE TABLE t2 ( a INT, b INT, KEY( a ), KEY( b ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+
+CREATE TABLE t3 ( a INT, KEY( a ) );
+INSERT INTO t3 VALUES (2), (2), (2), (2), (2);
+INSERT INTO t3 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t3;
+
+CREATE TABLE t4 ( a INT, KEY( a ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+
+FLUSH STATUS;
+
+--echo # Should not use short-cuts.
+EXPLAIN 
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+
+--echo # Should not use short-cuts.
+EXPLAIN 
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+
+SHOW STATUS LIKE 'handler_read_%';
+
+DROP TABLE t1, t2, t3, t4;
+--echo # 
+--echo # Test 5b.
+--echo # 
+CREATE TABLE t1 ( a INT, c INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+
+CREATE TABLE t2 ( a INT, b INT, KEY( a, b ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (2), (2), (2), (2), (2);
+INSERT INTO t3 SELECT * FROM t3;
+
+CREATE TABLE t4 ( a INT );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+
+FLUSH STATUS;
+
+--echo # Should not use short-cuts.
+EXPLAIN 
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+
+SHOW STATUS LIKE 'handler_read_%';
+
+DROP TABLE t1, t2, t3, t4;
+--echo #
+--echo # Test 6. Test of short-cuts in conjunction with outer join.
+--echo #
+--echo # Test 6a. Test of outer join detection.
+--echo # In this case the execution order is t1, t2, t3, t4.
+--echo # t2, t3 and t4 will form an 'outer join nest' with which t1 is left 
+--echo # outer joined. But (t3, t2) is left joined with t4. Since t4 is just one
+--echo # table, however, the 'outer join nest' of t4 is only implicitly 
+--echo # represented in the query plan. The only way to detect it is by looking 
+--echo # at t4's first_inner pointer, which would be null for the last inner table
+--echo # in an outer join nest. But since it points to itseld, t4 is in its own
+--echo # nest. But there is no NESTED_JOIN to represent it, since it follows the
+--echo # 'normal' left-deep tree representation.
+--echo #
+CREATE TABLE t1 (a INT, b INT, c INT);
+CREATE TABLE t2 (d INT, e INT, f INT);
+CREATE TABLE t3 (g INT, h INT, i INT);
+CREATE TABLE t4 (j INT, k INT, l INT);
+
+INSERT INTO t1 VALUES (3,1,0), (2,2,0), (3,3,0);
+INSERT INTO t2 VALUES (1,1,0), (2,2,0);
+INSERT INTO t3 VALUES (3,2,0), (6,2,0), (6,1,0);
+INSERT INTO t4 VALUES (0,2,0), (1,2,0);
+
+EXPLAIN
+SELECT a, b, c, d, e, f, g, h, i, j, k, l
+FROM t1 LEFT JOIN ( (t3, t2) LEFT JOIN t4 ON t2.e = t4.k AND t3.h < 10 ) ON t1.b = t2.e;
+SELECT *
+FROM t1 LEFT JOIN ( (t3, t2) LEFT JOIN t4 ON t2.e = t4.k AND t3.h < 10 ) ON t1.b = t2.e;
+
+DROP TABLE t1, t2, t3, t4;
+--echo #
+--echo # Test 6b.
+--echo # We may take short-cuts across or inside an outer join, as long as we 
+--echo # don't fail due to an outer join predicate. It goes as follows.
+--echo #
+--echo # - We may take a short-cut across a nested outer join.
+--echo # - We may take short-cuts inside a nested inner join sequence. 
+--echo #
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+CREATE TABLE t2 ( b INT, KEY    ( b ) );
+INSERT INTO t2 VALUES (1), (2),      (4), (5), (6), (7), (8), (9), (10);
+
+CREATE TABLE t3 ( c INT, KEY( c ) );
+INSERT INTO t3 VALUES (1), (2), (3), (4),      (6), (7), (8), (9), (10);
+
+CREATE TABLE t4 ( d INT, KEY( d ) );
+INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6), (7), (8),      (10);
+INSERT INTO t4 VALUES (10), (10), (10), (10);
+
+CREATE TABLE t5 ( e INT, KEY( e ) );
+INSERT INTO t5 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+--echo # Outer join followed by a table that is inner joined with the first.
+EXPLAIN 
+SELECT * FROM t1 LEFT JOIN t2 ON a = b JOIN t3 ON a = c;
+SELECT * FROM t1 LEFT JOIN t2 ON a = b JOIN t3 ON a = c;
+
+--echo # Short-cut across a nested outer join
+EXPLAIN
+SELECT * FROM t1 JOIN (t2 LEFT JOIN t3 ON b = c) ON a = b JOIN t4 ON a = d;
+SELECT * FROM t1 JOIN (t2 LEFT JOIN t3 ON b = c) ON a = b JOIN t4 ON a = d;
+
+--echo # Short-cut across a nested outer join with a nested inner join
+EXPLAIN
+SELECT * FROM t1
+         JOIN (t2 LEFT JOIN (t3 JOIN t4 ON c = d) ON b = d) ON a = b
+         JOIN t5 ON a = e;
+SELECT * FROM t1
+         JOIN (t2 LEFT JOIN (t3 JOIN t4 ON c = d) ON b = d) ON a = b
+         JOIN t5 ON a = e;
+
+--echo # Short-cut from last inner table inside an inner join that is nested 
+--echo # within an outer join
+EXPLAIN 
+SELECT * FROM t1 LEFT JOIN (t2 JOIN t3 ON b = c JOIN t4 ON b = d) on a = b;
+SELECT * FROM t1 LEFT JOIN (t2 JOIN t3 ON b = c JOIN t4 ON b = d) on a = b;
+
+--echo # Short-cut from not last inner table inside an inner join that is nested
+--echo # within an outer join
+EXPLAIN 
+SELECT * FROM t1
+         LEFT JOIN 
+         (t2 JOIN t3 ON b = c JOIN t4 ON b = d JOIN t5 ON d = e)
+         ON a = b;
+SELECT * FROM t1
+         LEFT JOIN 
+         (t2 JOIN t3 ON b = c JOIN t4 ON b = d JOIN t5 ON d = e)
+         ON a = b;
+
+--echo # Illegal scenario for short-cut. Should not be taken.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN t3 ON a = c;
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN t3 ON a = c;
+
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN (t3 JOIN t4 ON c = d) ON a = c;
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN (t3 JOIN t4 ON c = d) ON a = c;
+
+DROP TABLE t1, t2, t3, t4, t5;
+--echo #
+--echo # Test 6c.
+--echo # Tests that short-cuts work even if a row was read and then rejected.
+--echo # This happens for table scan without join buffering.
+--echo #
+let $requires_join_cache_level = 0;
+--source include/have_join_cache_level.inc
+--eval set optimizer_join_cache_level = $requires_join_cache_level;
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+
+CREATE TABLE t2 ( b INT );
+INSERT INTO t2 VALUES (1), (2), (2), (2), (2), (3);
+
+CREATE TABLE t3 ( c INT, d INT );
+INSERT INTO t3(c) VALUES (1), (3), (4), (4), (4), (4);
+
+FLUSH STATUS;
+
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+
+SHOW STATUS LIKE 'handler_read%';
+FLUSH STATUS;
+
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+
+SHOW STATUS LIKE 'handler_read%';
+
+SET @@optimizer_join_cache_level = DEFAULT;
+
+DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # Test 7.
+--echo # Combination test. This test contains multiple nesting levels, outer 
+--echo # join, table reordering, and NULL comparison. Short-cut should not be 
+--echo # used.
+--echo #
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES ( 1,1 ), ( 2,2 ), ( 3,3 );
+
+CREATE TABLE t2( a INT, b INT, filler CHAR( 200 ), KEY( a ) );
+INSERT INTO t2( a, b ) VALUES ( 0, 0 ), ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
+
+CREATE TABLE t3 LIKE t2;
+INSERT INTO t3 SELECT * FROM t2;
+INSERT INTO t3 SELECT * FROM t2;
+
+CREATE TABLE t4 LIKE t3;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t4 SELECT * FROM t3;
+
+EXPLAIN
+SELECT *
+FROM t3 JOIN t1 LEFT JOIN ( t2 JOIN t4 ON t4.a = t2.b ) ON t2.a = t1.b
+WHERE t3.a <=> t2.b;
+
+SELECT *
+FROM t3 JOIN t1 LEFT JOIN ( t2 JOIN t4 ON t4.a = t2.b ) ON t2.a = t1.b
+WHERE t3.a <=> t2.b;
+
+DROP TABLE t1, t2, t3, t4;

=== modified file 'mysql-test/t/subselect_innodb.test'
--- a/mysql-test/t/subselect_innodb.test	2010-12-17 09:41:21 +0000
+++ b/mysql-test/t/subselect_innodb.test	2011-03-16 14:01:21 +0000
@@ -302,7 +302,7 @@ ALTER TABLE t3 ADD  FOREIGN KEY FK_FLDRS
 SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
 
 EXPLAIN EXTENDED SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
-
+SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
 drop table t1, t2, t3, t4;
 # End of 4.1 tests
 

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2011-01-31 10:37:16 +0000
+++ b/sql/item.cc	2011-03-16 14:01:21 +0000
@@ -2294,6 +2294,9 @@ bool Item_field::eq(const Item *item, bo
 }
 
 
+/**
+   Returns simply the table to which the field belongs.
+*/
 table_map Item_field::used_tables() const
 {
   if (field->table->const_table)

=== modified file 'sql/item.h'
--- a/sql/item.h	2011-02-16 09:12:52 +0000
+++ b/sql/item.h	2011-03-16 14:01:21 +0000
@@ -868,7 +868,14 @@ public:
   virtual bool val_bool_result() { return val_bool(); }
   virtual bool is_null_result() { return is_null(); }
 
-  /* bit map of tables used by item */
+  /**
+     This is the set of tables referenced by this Item.
+     
+     Returns the set of tables referenced by this item. An item can be
+     recursively defined in terms of other items, and the set of tables is
+     generally calculated by forming the union of the set of tables referenced
+     by the embedded items.
+  */
   virtual table_map used_tables() const { return (table_map) 0L; }
   /*
     Return table map of tables that can't be NULL tables (tables that are

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-03-01 14:57:53 +0000
+++ b/sql/sql_select.cc	2011-03-16 14:01:21 +0000
@@ -1731,6 +1731,98 @@ static int clear_sj_tmp_tables(JOIN *joi
 }
 
 
+/**
+   @defgroup Shortcut Short-cut
+   @{ 
+*/
+
+
+/**
+   Sets the short-cut from one plan node to another, if one is found.
+   
+   @param join The execution plan.
+   
+   @param join_tab The plan node from which a short-cut will potentially be
+   taken.
+*/
+static void set_star_dependency(const JOIN *join, JOIN_TAB *join_tab)
+{
+  if (join_tab->use_join_cache != JOIN_CACHE::ALG_NONE)
+    return;
+
+  table_map where_clause_tables;
+  if (join_tab->select_cond != NULL)
+  {
+    join_tab->select_cond->update_used_tables();
+    where_clause_tables= join_tab->select_cond->used_tables();
+  }
+  else
+    where_clause_tables= (table_map)0L;
+  
+  table_map tables_depended_upon= 
+    (where_clause_tables | join_tab->ref.depend_map) & ~PSEUDO_TABLE_BITS;
+
+  if (tables_depended_upon == (table_map)0)
+    return;
+  
+  for (JOIN_TAB *prospect= join_tab - 1; prospect >= join->join_tab; --prospect)
+  {
+    if (prospect->use_join_cache != JOIN_CACHE::ALG_NONE)
+      return;
+
+    if ((prospect->table->map & tables_depended_upon) != (table_map)0)
+    {
+      if (join->are_inner_joined(prospect, join_tab) && prospect < join_tab - 1)
+      {
+        DBUG_PRINT("info", ("Found a join short-cut from %s to %s", 
+                            join_tab->table->alias,
+                            prospect->table->alias));
+        
+        join_tab->shortcut= prospect;
+      }
+      return;
+    }
+  }
+}
+
+
+/**
+   Pre-compute short-cuts for the join short-cut optimization.
+   
+   This function adorns the query exeution plan with star join short-cuts
+   where applicable. Short-cuts are triggered by the executioner. The
+   algorithm is implemented as a nested loop, where the outer loop walks the
+   nodes (JOIN_TAB's) in the query execution plan, and the inner loop iterates
+   through all tables that this table depends upon. Valid short-cuts have the
+   following properties.
+   
+   - The short-cut has a minimum length of 2. If a table is dependent on the
+   table directly preceding it, the short-cut would be of length 1, and it
+   would indicate that there are no valid short-cuts from this table.
+   
+   - The short-cut does not cross an outer join boundary. In this case the
+   optimization is not applicable, because a valid result would be missed.
+   
+   - The short-cut does not cross a node using join buffers. Short-cuts are
+   not compatible with join buffering.
+   
+   - If there are several applicable shortcuts by the above requirements, the
+   shortest one is chosen.
+   
+   @param join The query execution plan. The plan is assumed to be complete
+   and ready for execution when this method is invoked.
+*/
+static void setup_shortcuts(JOIN *join)
+{
+  if (join->join_tab == NULL)
+    return;
+  
+  for (uint i= join->const_tables + 2; i < join->tables; ++i)
+    set_star_dependency(join, &join->join_tab[i]);
+}
+
+/* @} */
+
 
 /**
   global select optimisation.
@@ -2470,6 +2562,9 @@ JOIN::optimize()
   }
 
   tmp_having= having;
+
+  setup_shortcuts(this);
+
   if (select_options & SELECT_DESCRIBE)
   {
     error= 0;
@@ -17272,6 +17367,7 @@ sub_select_cache(JOIN *join, JOIN_TAB *j
   DBUG_ASSERT(cache != NULL);
 
   cache->reset_join(join);
+  join->return_tab= join_tab;
 
   DBUG_ENTER("sub_select_cache");
 
@@ -17444,6 +17540,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
   DBUG_ENTER("sub_select");
 
   join_tab->table->null_row=0;
+  join->return_tab= join_tab;
   if (end_of_records)
   {
     enum_nested_loop_state nls=
@@ -17459,7 +17556,6 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
     do_sj_reset(join_tab->flush_weedout_table);
   }
 
-  join->return_tab= join_tab;
   join_tab->not_null_compl= TRUE;
 
   if (join_tab->last_inner)
@@ -17474,6 +17570,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
   }
   join->thd->warning_info->reset_current_row_for_warning();
 
+  join_tab->number_produced_rows= 0;
   error= (*join_tab->read_first_record)(join_tab);
 
   if (join_tab->keep_current_rowid)
@@ -17650,7 +17747,11 @@ evaluate_join_record(JOIN *join, JOIN_TA
   if (error > 0 || (join->thd->is_error()))     // Fatal error
     DBUG_RETURN(NESTED_LOOP_ERROR);
   if (error < 0)
+  {
+    if (join_tab->shortcut != NULL && join_tab->number_produced_rows == 0)
+      join->return_tab= join_tab->shortcut;
     DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
+  }
   if (join->thd->killed)			// Aborted by user
   {
     join->thd->send_kill_message();
@@ -17796,6 +17897,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
 
     if (found)
     {
+      ++join_tab->number_produced_rows;
       enum enum_nested_loop_state rc;
       /* A match from join_tab is found for the current partial join. */
       rc= (*join_tab->next_select)(join, join_tab+1, 0);
@@ -23256,6 +23358,13 @@ void select_describe(JOIN *join, bool ne
             extra.append(STRING_WITH_LEN(", incremental buffers)"));
         }
 
+        if (tab->shortcut != NULL)
+        {
+          extra.append(STRING_WITH_LEN("; Shortcut("));
+          extra.append(tab->shortcut->table->alias);
+          extra.append(STRING_WITH_LEN(")")); 
+        }
+
         /* Skip initial "; "*/
         const char *str= extra.ptr();
         uint32 len= extra.length();

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-03-01 14:57:53 +0000
+++ b/sql/sql_select.h	2011-03-16 14:01:21 +0000
@@ -237,6 +237,17 @@ typedef struct st_join_table : public Sq
   TABLE		*table;
   Key_use	*keyuse;			/**< pointer to first used key */
   SQL_SELECT	*select;
+  /**
+     The condition to be evaluated when a row from this table has been made
+     available inside query execution. It may reference a row from all tables
+     in the current join prefix (ie this table and all tables preceeding it in
+     the join plan). Generally, a condition consists of a set of predicates
+     combined using AND, OR and XOR.
+     
+     @note Predicates that can be fully evaluated using a ref access condition
+     or that have been pushed down to the storage engine are excluded from this
+     condition.
+  */
   Item		*select_cond;
   QUICK_SELECT_I *quick;
   Item	       **on_expr_ref;   /**< pointer to the associated on expression   */
@@ -378,6 +389,28 @@ typedef struct st_join_table : public Sq
   /* NestedOuterJoins: Bitmap of nested joins this table is part of */
   nested_join_map embedding_map;
 
+  /**
+     The number of rows produced at this plan node with a common prefix
+     produced by the preceding plan nodes.
+ 
+     The counter is reset each time a partial row has been produced for which
+     pushdown conditions are true. The partial row is comprised of the
+     concatenation of partial rows from all previous tables in the join.
+  */
+  ha_rows number_produced_rows;
+
+  /**
+     Destination for join short-cuts. Short-cuts are set up by
+     setup_shortcuts() and enable skipping of read operations if it is
+     discovered that none of the rows produced by a certain plan node can
+     satisfy the search condition. In this case execution needs to
+     immediatedly revert back to a previous plan node and commence execution
+     from there.
+
+     @see setup_shortcuts(), evaluate_join_record(), set_star_dependency()
+   */
+  st_join_table *shortcut;
+
   void cleanup();
   inline bool is_using_loose_index_scan()
   {
@@ -506,7 +539,8 @@ st_join_table::st_join_table()
     found_match(FALSE),
 
     keep_current_rowid(0),
-    embedding_map(0)
+    embedding_map(0),
+    shortcut(NULL)
 {
   /**
     @todo Add constructor to READ_RECORD.
@@ -1808,12 +1842,15 @@ public:
   TABLE_LIST *tables_list;           ///<hold 'tables' parameter of mysql_select
   List<TABLE_LIST> *join_list;       ///< list of joined tables in reverse order
   COND_EQUAL *cond_equal;
-  /*
-    Join tab to return to. Points to an element of join->join_tab array, or to
-    join->join_tab[-1].
-    This is used at execution stage to shortcut join enumeration. Currently
-    shortcutting is done to handle outer joins or handle semi-joins with
-    FirstMatch strategy.
+  /**
+    Interface for taking short-cuts through join execution. Join tab to return
+    to. Points to an element of join->join_tab array, or to
+    join->join_tab[-1]. This is used at execution stage to shortcut join
+    enumeration. Shortcutting is done to
+
+    - handle outer joins, and
+    - handle semi-joins with FirstMatch strategy, and
+    - optimize execution of star joins. 
   */
   JOIN_TAB *return_tab;
   Item **ref_pointer_array; ///<used pointer reference for this select
@@ -1981,6 +2018,26 @@ public:
             ((group || tmp_table_param.sum_func_count) && !group_list)) ?
               NULL : join_tab+const_tables;
   }
+
+
+  /**
+     Returns true if JOIN_TAB's a and b participate on the same nesting level
+     within a chain of inner joined tables.
+
+     @note The optimizer execution plan is considered, and outer joins may
+     have been rewritten into inner joins.
+  */
+  static bool are_inner_joined(const JOIN_TAB *a, const JOIN_TAB *b)
+  {
+    DBUG_ASSERT(a < b);
+    DBUG_ASSERT(b->on_expr_ref != NULL);
+    return
+      a->table->pos_in_table_list->embedding ==
+      b->table->pos_in_table_list->embedding &&
+      *b->on_expr_ref == NULL;
+  }
+
+
 private:
   /**
     TRUE if the query contains an aggregate function but has no GROUP


Attachment: [text/bzr-bundle] bzr/martin.hansson@oracle.com-20110316140121-i1bgrzp3x19cswyj.bundle
Thread
bzr commit into mysql-trunk branch (martin.hansson:3299) WL#3724Martin Hansson16 Mar