#At file:///work/bzrroot/mysql-6.0-codebase-bugfixing/ based on revid:alik@stripped
3728 Evgeny Potemkin 2009-11-24 [merge]
Auto-merged fix for the bug#47217.
modified:
mysql-test/r/distinct.result
mysql-test/r/greedy_optimizer.result
mysql-test/r/group_by.result
mysql-test/r/join.result
mysql-test/r/join_cache.result
mysql-test/r/join_nested.result
mysql-test/r/join_nested_jcl6.result
mysql-test/r/join_optimizer.result
mysql-test/r/join_outer.result
mysql-test/r/join_outer_jcl6.result
mysql-test/r/myisam.result
mysql-test/r/null_key.result
mysql-test/r/order_by.result
mysql-test/r/select.result
mysql-test/r/select_jcl6.result
mysql-test/r/select_safe.result
mysql-test/r/subselect.result
mysql-test/r/subselect3.result
mysql-test/r/subselect3_jcl6.result
mysql-test/r/subselect_no_mat.result
mysql-test/r/subselect_no_opts.result
mysql-test/r/subselect_no_semijoin.result
mysql-test/r/subselect_sj.result
mysql-test/r/subselect_sj2.result
mysql-test/r/subselect_sj2_jcl6.result
mysql-test/r/subselect_sj_jcl6.result
mysql-test/r/view.result
mysql-test/t/join_optimizer.test
sql/sql_select.cc
=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result 2009-09-08 08:33:46 +0000
+++ b/mysql-test/r/distinct.result 2009-11-24 11:24:39 +0000
@@ -173,7 +173,7 @@ INSERT INTO t2 values (1),(2),(3);
INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using temporary
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using where; Using temporary
1 SIMPLE t3 ref a a 5 test.t1.b 2 Using index
1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct; Using join buffer
SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
=== modified file 'mysql-test/r/greedy_optimizer.result'
--- a/mysql-test/r/greedy_optimizer.result 2009-01-16 10:45:17 +0000
+++ b/mysql-test/r/greedy_optimizer.result 2009-11-24 11:24:39 +0000
@@ -144,7 +144,7 @@ Variable_name Value
Last_query_cost 821.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -156,7 +156,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -168,7 +168,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -180,7 +180,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -224,7 +224,7 @@ Variable_name Value
Last_query_cost 821.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
@@ -236,7 +236,7 @@ Variable_name Value
Last_query_cost 289.418727
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
@@ -248,7 +248,7 @@ Variable_name Value
Last_query_cost 289.418727
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
@@ -260,7 +260,7 @@ Variable_name Value
Last_query_cost 289.418727
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
@@ -300,7 +300,7 @@ Variable_name Value
Last_query_cost 821.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+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
@@ -312,7 +312,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+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
@@ -324,7 +324,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+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
@@ -336,7 +336,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+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
@@ -376,7 +376,7 @@ Variable_name Value
Last_query_cost 821.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
@@ -388,7 +388,7 @@ Variable_name Value
Last_query_cost 289.418727
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
@@ -400,7 +400,7 @@ Variable_name Value
Last_query_cost 289.418727
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
@@ -412,7 +412,7 @@ Variable_name Value
Last_query_cost 289.418727
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 6
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
@@ -456,7 +456,7 @@ Variable_name Value
Last_query_cost 821.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -468,7 +468,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -480,7 +480,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -492,7 +492,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -532,7 +532,7 @@ Variable_name Value
Last_query_cost 821.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+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
@@ -544,7 +544,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+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
@@ -556,7 +556,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+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
@@ -568,7 +568,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+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
@@ -608,7 +608,7 @@ Variable_name Value
Last_query_cost 821.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -620,7 +620,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -632,7 +632,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
@@ -644,7 +644,7 @@ Variable_name Value
Last_query_cost 794.837037
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2009-06-02 13:04:43 +0000
+++ b/mysql-test/r/group_by.result 2009-11-24 11:24:39 +0000
@@ -874,7 +874,7 @@ explain
SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1
where t2.b=v1.a GROUP BY t2.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index b b 2 NULL 10 Using index
+1 SIMPLE t2 index b b 2 NULL 10 Using where; Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 1 test.t2.b 1
SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1
where t2.b=v1.a GROUP BY t2.b;
=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result 2009-11-09 10:27:46 +0000
+++ b/mysql-test/r/join.result 2009-11-24 11:24:39 +0000
@@ -846,7 +846,7 @@ select * from t1, t2, t3 where t3.a=t1.a
a b a a
explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+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
We expect rnd_next=5, and read_key must be 0 because of short-cutting:
@@ -889,8 +889,8 @@ Z
vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv
explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=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 A eq_ref PRIMARY PRIMARY 4 test.t1.a 1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE A eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
1 SIMPLE B eq_ref PRIMARY PRIMARY 4 test.A.b 1
show status like '%cost%';
Variable_name Value
@@ -910,7 +910,7 @@ INSERT INTO t1 SELECT a + 64, b FROM t1;
INSERT INTO t2 SELECT a, b FROM t1;
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 2
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
@@ -924,7 +924,7 @@ a b c d
2 NULL 2 NULL
EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using where; Using filesort
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1
EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result 2009-10-12 09:08:34 +0000
+++ b/mysql-test/r/join_cache.result 2009-11-24 11:24:39 +0000
@@ -3328,7 +3328,7 @@ SELECT a1<>a2, a1, a2, b2, b3, c3,
SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 9
+1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using where
1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index
1 SIMPLE t3 ref idx idx 5 test.t2.b2 5 Using where
SELECT a1<>a2, a1, a2, b2, b3, c3,
@@ -3356,7 +3356,7 @@ SELECT a1<>a2, a1, a2, b2, b3, c3,
SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 9
+1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using where
1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index
1 SIMPLE t3 ref idx idx 5 test.t2.b2 5 Using where; Using join buffer
SELECT a1<>a2, a1, a2, b2, b3, c3,
@@ -3568,7 +3568,7 @@ set join_cache_level=6;
set join_buffer_size=1024;
EXPLAIN SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 2050
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2050 Using where
1 SIMPLE t2 ref idx idx 5 test.t1.a 640 Using join buffer
SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b;
AVG(c)
@@ -3744,7 +3744,7 @@ f1 f2 f3
explain select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer
set join_cache_level=6;
select t2.f1, t2.f2, t2.f3 from t1,t2
@@ -3757,7 +3757,7 @@ f1 f2 f3
explain select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer
set join_cache_level=7;
select t2.f1, t2.f2, t2.f3 from t1,t2
@@ -3770,7 +3770,7 @@ f1 f2 f3
explain select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer
set join_cache_level=8;
select t2.f1, t2.f2, t2.f3 from t1,t2
@@ -3783,7 +3783,7 @@ f1 f2 f3
explain select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer
drop table t1,t2;
set join_cache_level=default;
@@ -3802,7 +3802,7 @@ explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using index; Using temporary; Using filesort
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
@@ -3814,7 +3814,7 @@ explain
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using index; Using temporary; Using filesort
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
@@ -3858,7 +3858,7 @@ explain
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;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 7
+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
@@ -3871,7 +3871,7 @@ explain
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;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 7
+1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
1 SIMPLE t2 ref idx idx 5 test.t1.b 1 Using join buffer
1 SIMPLE t3 ref idx idx 5 test.t1.d 1 Using join buffer
1 SIMPLE t4 ref idx idx 5 test.t1.c 1 Using join buffer
@@ -3922,8 +3922,8 @@ FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN
WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND
t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 349
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.id3 1 Using join buffer
+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; Using join buffer
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.id4 1 Using where; Using join buffer
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1 Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where; Using join buffer
=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result 2008-03-30 05:59:02 +0000
+++ b/mysql-test/r/join_nested.result 2009-11-24 11:24:39 +0000
@@ -1054,7 +1054,7 @@ t0.b=t1.b AND
(t8.b=t9.b OR t8.c IS NULL) AND
(t9.a=1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00
+1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where
1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where
@@ -1729,7 +1729,7 @@ LEFT JOIN
(t5 JOIN t4 ON t5.carrier_id = t4.id)
ON t4.carrier = t1.carrier;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index package_id package_id 5 NULL 45 Using index
+1 SIMPLE t2 index package_id package_id 5 NULL 45 Using where; Using index
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
=== modified file 'mysql-test/r/join_nested_jcl6.result'
--- a/mysql-test/r/join_nested_jcl6.result 2009-01-14 10:29:36 +0000
+++ b/mysql-test/r/join_nested_jcl6.result 2009-11-24 11:24:39 +0000
@@ -1058,7 +1058,7 @@ t0.b=t1.b AND
(t8.b=t9.b OR t8.c IS NULL) AND
(t9.a=1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00
+1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where
1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using join buffer
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer
@@ -1733,7 +1733,7 @@ LEFT JOIN
(t5 JOIN t4 ON t5.carrier_id = t4.id)
ON t4.carrier = t1.carrier;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index package_id package_id 5 NULL 45 Using index
+1 SIMPLE t2 index package_id package_id 5 NULL 45 Using where; Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1 Using join buffer
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
=== modified file 'mysql-test/r/join_optimizer.result'
--- a/mysql-test/r/join_optimizer.result 2009-01-26 19:42:59 +0000
+++ b/mysql-test/r/join_optimizer.result 2009-11-24 11:24:39 +0000
@@ -32,6 +32,37 @@ explain
SELECT STRAIGHT_JOIN g.id FROM t2 a, t3 g USE INDEX(groups_dt)
WHERE g.domain = 'queue' AND g.type = a.type;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE a ALL NULL NULL NULL NULL 2
+1 SIMPLE a ALL NULL NULL NULL NULL 2 Using where
1 SIMPLE g ref groups_dt groups_dt 70 const,test.a.type 13 Using index condition
drop table t0,t1,t2,t3;
+#
+# BUG#47217 Lost optimization caused slowdown & wrong result.
+#
+CREATE TABLE t1 (pk INT, v VARCHAR(2), PRIMARY KEY(pk));
+CREATE INDEX ix1 ON t1(v);
+CREATE TABLE t2 (pk INT, v VARCHAR(2), PRIMARY KEY(pk));
+CREATE INDEX ix2 ON t2(v);
+INSERT INTO t1 VALUES (1,'a'),(2,NULL);
+INSERT INTO t2 VALUES (1,NULL);
+EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v ORDER BY 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+INSERT INTO t1 VALUES (3,'b'),(4,NULL),(5,'c'),(6,'cc'),(7,'d'),
+(8,'dd'),(9,'e'),(10,'ee');
+INSERT INTO t2 VALUES (2,NULL);
+FLUSH STATUS;
+SELECT * FROM t1 JOIN t2 ON t1.v = t2.v WHERE t2.v IS NULL ORDER BY 1;
+pk v pk v
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 1
+Handler_read_next 0
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 1
+DROP TABLE t1, t2;
+# End of test for bug#47217
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result 2009-09-25 14:15:30 +0000
+++ b/mysql-test/r/join_outer.result 2009-11-24 11:24:39 +0000
@@ -1130,7 +1130,7 @@ a b a b
7 8 7 5
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result 2009-03-10 01:34:11 +0000
+++ b/mysql-test/r/join_outer_jcl6.result 2009-11-24 11:24:39 +0000
@@ -1134,7 +1134,7 @@ a b a b
7 8 7 5
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using join buffer
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/r/myisam.result'
--- a/mysql-test/r/myisam.result 2009-10-30 15:36:45 +0000
+++ b/mysql-test/r/myisam.result 2009-11-24 11:24:39 +0000
@@ -357,7 +357,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref a a 4 test.t2.a 3
explain select * from t1,t2 where t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL b NULL NULL NULL 2
+1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where
1 SIMPLE t1 ref b b 5 test.t2.b 1
explain select * from t1,t2 force index(c) where t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/r/null_key.result'
--- a/mysql-test/r/null_key.result 2009-01-25 16:59:07 +0000
+++ b/mysql-test/r/null_key.result 2009-11-24 11:24:39 +0000
@@ -178,12 +178,12 @@ create table t2 (a int);
insert into t2 values (7),(8);
explain select * from t2 straight_join t1 where t1.a=t2.a and b is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
1 SIMPLE t1 ref a,b a 10 test.t2.a,const 2 Using where; Using index
drop index b on t1;
explain select * from t2,t1 where t1.a=t2.a and b is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
1 SIMPLE t1 ref a a 10 test.t2.a,const 2 Using where; Using index
select * from t2,t1 where t1.a=t2.a and b is null;
a a b
@@ -191,7 +191,7 @@ a a b
8 8 NULL
explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index
select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
a a b
=== modified file 'mysql-test/r/order_by.result'
--- a/mysql-test/r/order_by.result 2009-10-14 14:47:26 +0000
+++ b/mysql-test/r/order_by.result 2009-11-24 11:24:39 +0000
@@ -1454,7 +1454,7 @@ SELECT d FROM t1, t2
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using temporary; Using filesort
+1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using temporary; Using filesort
1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition
SELECT d FROM t1, t2
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result 2009-11-18 06:14:20 +0000
+++ b/mysql-test/r/select.result 2009-11-24 11:26:13 +0000
@@ -3562,19 +3562,19 @@ EXPLAIN SELECT t2.*
FROM t1 JOIN t2 ON t2.fk=t1.pk
WHERE t2.fk < 'c' AND t2.pk=t1.fk;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using MRR
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using where; Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
EXPLAIN SELECT t2.*
FROM t1 JOIN t2 ON t2.fk=t1.pk
WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
EXPLAIN SELECT t2.*
FROM t1 JOIN t2 ON t2.fk=t1.pk
WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where
DROP TABLE t1,t2;
CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
@@ -3609,7 +3609,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AN
t3.a=t2.a AND t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using MRR
+1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
EXPLAIN
SELECT t3.a FROM t1,t2,t3
@@ -3617,7 +3617,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AN
t3.a=t2.a AND t3.c IN ('bb','ee') ;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using MRR
+1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
EXPLAIN
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
@@ -3625,7 +3625,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t
t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR
+1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
EXPLAIN
SELECT t3.a FROM t1,t2,t3
@@ -3633,7 +3633,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t
t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using MRR
+1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result 2009-11-18 06:14:20 +0000
+++ b/mysql-test/r/select_jcl6.result 2009-11-24 11:26:13 +0000
@@ -3566,19 +3566,19 @@ EXPLAIN SELECT t2.*
FROM t1 JOIN t2 ON t2.fk=t1.pk
WHERE t2.fk < 'c' AND t2.pk=t1.fk;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using MRR
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using where; Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer
EXPLAIN SELECT t2.*
FROM t1 JOIN t2 ON t2.fk=t1.pk
WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer
EXPLAIN SELECT t2.*
FROM t1 JOIN t2 ON t2.fk=t1.pk
WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR
+1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer
DROP TABLE t1,t2;
CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
@@ -3613,7 +3613,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AN
t3.a=t2.a AND t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using MRR
+1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer
EXPLAIN
SELECT t3.a FROM t1,t2,t3
@@ -3621,7 +3621,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AN
t3.a=t2.a AND t3.c IN ('bb','ee') ;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using MRR
+1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer
EXPLAIN
SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
@@ -3629,7 +3629,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t
t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR
+1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer
EXPLAIN
SELECT t3.a FROM t1,t2,t3
@@ -3637,7 +3637,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t
t3.c IN ('bb','ee');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
-1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using MRR
+1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Using MRR
1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer
DROP TABLE t1,t2,t3;
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
=== modified file 'mysql-test/r/select_safe.result'
--- a/mysql-test/r/select_safe.result 2007-03-09 21:08:24 +0000
+++ b/mysql-test/r/select_safe.result 2009-11-24 11:24:39 +0000
@@ -66,12 +66,12 @@ test.t1 analyze status OK
insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL b NULL NULL NULL 21
+1 SIMPLE t1 ALL b NULL NULL NULL 21 Using where
1 SIMPLE t2 ref b b 21 test.t1.b 6
set MAX_SEEKS_FOR_KEY=1;
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL b NULL NULL NULL 21
+1 SIMPLE t1 ALL b NULL NULL NULL 21 Using where
1 SIMPLE t2 ref b b 21 test.t1.b 6
SET MAX_SEEKS_FOR_KEY=DEFAULT;
drop table t1;
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2009-11-23 12:36:34 +0000
+++ b/mysql-test/r/subselect.result 2009-11-24 11:26:13 +0000
@@ -1336,7 +1336,7 @@ a
4
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)
@@ -1346,7 +1346,7 @@ a
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
@@ -1356,8 +1356,8 @@ a
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
-1 PRIMARY t3 index a a 5 NULL 3 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
+1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index
1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` = `test`.`t3`.`a`))
@@ -1373,7 +1373,7 @@ a
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
@@ -4224,7 +4224,7 @@ CREATE INDEX I1 ON t1 (a);
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan
+1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
@@ -4234,7 +4234,7 @@ CREATE INDEX I1 ON t2 (a);
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
+1 PRIMARY t2 index I1 I1 4 NULL 2 Using where; Using index; LooseScan
1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
a b
@@ -4711,7 +4711,7 @@ INSERT INTO t3 VALUES (1,1,1), (2,32,1);
explain
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using index
+1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using where; Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 Using index
2 DEPENDENT SUBQUERY t2 index b,b_2,c d 5 NULL 1 Using where
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result 2009-11-18 06:14:20 +0000
+++ b/mysql-test/r/subselect3.result 2009-11-24 11:26:13 +0000
@@ -1117,7 +1117,7 @@ insert into t4 select a from t3;
explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using MRR; LooseScan
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
drop table t1, t3, t4;
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result 2009-11-18 06:14:20 +0000
+++ b/mysql-test/r/subselect3_jcl6.result 2009-11-24 11:26:13 +0000
@@ -1122,7 +1122,7 @@ insert into t4 select a from t3;
explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using MRR; LooseScan
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
drop table t1, t3, t4;
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2009-11-20 09:16:27 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2009-11-24 11:26:13 +0000
@@ -1340,7 +1340,7 @@ a
4
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)
@@ -1350,7 +1350,7 @@ a
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
@@ -1360,8 +1360,8 @@ a
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
-1 PRIMARY t3 index a a 5 NULL 3 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
+1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index
1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` = `test`.`t3`.`a`))
@@ -1377,7 +1377,7 @@ a
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
@@ -4228,7 +4228,7 @@ CREATE INDEX I1 ON t1 (a);
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan
+1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
@@ -4238,7 +4238,7 @@ CREATE INDEX I1 ON t2 (a);
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
+1 PRIMARY t2 index I1 I1 4 NULL 2 Using where; Using index; LooseScan
1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
a b
@@ -4715,7 +4715,7 @@ INSERT INTO t3 VALUES (1,1,1), (2,32,1);
explain
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using index
+1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using where; Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 Using index
2 DEPENDENT SUBQUERY t2 index b,b_2,c d 5 NULL 1 Using where
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result 2009-11-20 09:16:27 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2009-11-24 11:26:13 +0000
@@ -1318,7 +1318,7 @@ a
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00
+2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
@@ -4715,7 +4715,7 @@ INSERT INTO t3 VALUES (1,1,1), (2,32,1);
explain
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using index
+1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using where; Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 Using index
2 DEPENDENT SUBQUERY t2 index b,b_2,c d 5 NULL 1 Using where
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2009-11-20 09:16:27 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2009-11-24 11:26:13 +0000
@@ -4715,7 +4715,7 @@ INSERT INTO t3 VALUES (1,1,1), (2,32,1);
explain
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using index
+1 PRIMARY t3 index b,b_2 b 10 NULL 2 Using where; Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 Using index
2 DEPENDENT SUBQUERY t2 index b,b_2,c d 5 NULL 1 Using where
SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2009-11-23 10:27:34 +0000
+++ b/mysql-test/r/subselect_sj.result 2009-11-24 11:26:13 +0000
@@ -12,7 +12,7 @@ insert into t12 select * from t10;
Flattened because of dependency, t10=func(t1)
explain select * from t1 where a in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
select * from t1 where a in (select pk from t10);
a b
@@ -39,7 +39,7 @@ select * from t1 where a in (select a fr
a b
explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
select * from t1 where a in (select pk from t10) and b in (select pk from t10);
@@ -50,8 +50,8 @@ a b
flattening a nested subquery
explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 Using index
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
a b
@@ -61,8 +61,8 @@ a b
flattening subquery w/ several tables
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using where
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t12` join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result 2009-11-12 14:31:12 +0000
+++ b/mysql-test/r/subselect_sj2.result 2009-11-24 11:24:39 +0000
@@ -32,7 +32,7 @@ a b
9 5
explain select * from t2 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Materialize; Scan
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Materialize; Scan
1 PRIMARY t2 ref b b 5 test.t1.a 2
select * from t2 where b in (select a from t1);
a b
@@ -73,7 +73,7 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
from t0 A, t0 B where B.a <5;
explain select * from t3 where b in (select a from t0);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Materialize; Scan
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
1 PRIMARY t3 ref b b 5 test.t0.a 1
set @save_ecp= @@engine_condition_pushdown;
set engine_condition_pushdown=0;
@@ -417,7 +417,7 @@ explain extended select * from t0
where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
t1.b=t2.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00
+1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 FirstMatch(t0)
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where
Warnings:
=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result 2009-11-12 14:31:12 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result 2009-11-24 11:24:39 +0000
@@ -36,7 +36,7 @@ a b
9 5
explain select * from t2 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Materialize; Scan
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Materialize; Scan
1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer
select * from t2 where b in (select a from t1);
a b
@@ -77,7 +77,7 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
from t0 A, t0 B where B.a <5;
explain select * from t3 where b in (select a from t0);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Materialize; Scan
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
1 PRIMARY t3 ref b b 5 test.t0.a 1
set @save_ecp= @@engine_condition_pushdown;
set engine_condition_pushdown=0;
@@ -421,7 +421,7 @@ explain extended select * from t0
where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
t1.b=t2.b);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00
+1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 FirstMatch(t0); Using join buffer
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; Using join buffer
Warnings:
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result 2009-11-23 10:27:34 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2009-11-24 11:26:13 +0000
@@ -16,7 +16,7 @@ insert into t12 select * from t10;
Flattened because of dependency, t10=func(t1)
explain select * from t1 where a in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
select * from t1 where a in (select pk from t10);
a b
@@ -43,7 +43,7 @@ select * from t1 where a in (select a fr
a b
explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
select * from t1 where a in (select pk from t10) and b in (select pk from t10);
@@ -54,8 +54,8 @@ a b
flattening a nested subquery
explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 Using index
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
a b
@@ -65,8 +65,8 @@ a b
flattening subquery w/ several tables
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using where; Using join buffer
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t12` join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result 2009-11-10 08:27:24 +0000
+++ b/mysql-test/r/view.result 2009-11-24 11:24:39 +0000
@@ -2342,11 +2342,11 @@ CREATE VIEW v1 AS SELECT t1.* FROM t1,t2
CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a;
EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 5 const 1 Using index
+1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
1 SIMPLE t2 ref a a 10 const,test.t1.b 1 Using index
EXPLAIN SELECT * FROM v1 WHERE a=1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a a 5 const 1 Using index
+1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
1 SIMPLE t2 ref a a 10 const,test.t1.b 1 Using index
EXPLAIN SELECT * FROM v2 WHERE a=1;
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/t/join_optimizer.test'
--- a/mysql-test/t/join_optimizer.test 2009-01-26 19:42:59 +0000
+++ b/mysql-test/t/join_optimizer.test 2009-11-24 11:24:39 +0000
@@ -43,3 +43,23 @@ SELECT STRAIGHT_JOIN g.id FROM t2 a, t3
WHERE g.domain = 'queue' AND g.type = a.type;
drop table t0,t1,t2,t3;
+
+--echo #
+--echo # BUG#47217 Lost optimization caused slowdown & wrong result.
+--echo #
+CREATE TABLE t1 (pk INT, v VARCHAR(2), PRIMARY KEY(pk));
+CREATE INDEX ix1 ON t1(v);
+CREATE TABLE t2 (pk INT, v VARCHAR(2), PRIMARY KEY(pk));
+CREATE INDEX ix2 ON t2(v);
+INSERT INTO t1 VALUES (1,'a'),(2,NULL);
+INSERT INTO t2 VALUES (1,NULL);
+EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v ORDER BY 1;
+EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v;
+INSERT INTO t1 VALUES (3,'b'),(4,NULL),(5,'c'),(6,'cc'),(7,'d'),
+ (8,'dd'),(9,'e'),(10,'ee');
+INSERT INTO t2 VALUES (2,NULL);
+FLUSH STATUS;
+SELECT * FROM t1 JOIN t2 ON t1.v = t2.v WHERE t2.v IS NULL ORDER BY 1;
+SHOW STATUS LIKE 'Handler_read_%';
+DROP TABLE t1, t2;
+--echo # End of test for bug#47217
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-11-23 13:08:01 +0000
+++ b/sql/sql_select.cc 2009-11-24 11:26:13 +0000
@@ -8476,6 +8476,8 @@ inline void add_cond_and_fix(Item **e1,
{
if (*e1)
{
+ if (!e2)
+ return;
Item *res;
if ((res= new Item_cond_and(*e1, e2)))
{
@@ -8753,6 +8755,10 @@ static bool make_join_select(JOIN *join,
make_cond_for_table(cond,
join->const_table_map,
(table_map) 0, 1);
+ /* Add conditions added by add_not_null_conds(). */
+ for (uint i= 0 ; i < join->const_tables ; i++)
+ add_cond_and_fix(&const_cond, join->join_tab[i].select_cond);
+
DBUG_EXECUTE("where",print_where(const_cond,"constants", QT_ORDINARY););
for (JOIN_TAB *tab= join->join_tab+join->const_tables;
tab < join->join_tab+join->tables ; tab++)
@@ -8849,6 +8855,10 @@ static bool make_join_select(JOIN *join,
tmp= NULL;
if (cond)
tmp= make_cond_for_table(cond,used_tables,current_map, 0);
+ /* Add conditions added by add_not_null_conds(). */
+ if (tab->select_cond)
+ add_cond_and_fix(&tmp, tab->select_cond);
+
if (cond && !tmp && tab->quick)
{ // Outer join
if (tab->type != JT_ALL)
Attachment: [text/bzr-bundle] bzr/epotemkin@mysql.com-20091124112613-zuq35r2odhziynn8.bundle
| Thread |
|---|
| • bzr commit into mysql-6.0-codebase-bugfixing branch (epotemkin:3728)Bug#47217 | Evgeny Potemkin | 24 Nov |
