#At file:///work/bzrroot/backporting-next-mr-bugfixing/ based on revid:epotemkin@stripped
3170 Evgeny Potemkin 2010-05-12
Bug#47217: Lost optimization caused slowdown & wrong result.
The "early null-filtering" optimization introduced in the fix for the bug#8877
was lost during 4.1->5.0 merge. The optimization was introduced without test
case thus error during the merge wasn't noticed. Due to this in some cases
impossible WHERE expression wasn't detected allowing optimizer to choose a
wrong plan led to a wrong result. Beside that, absence of early null-filtering
allowed server to do useless scans with NULL value in a null-rejecting
predicate thus slowing the query execution.
The lost optimization is re-merged.
Origianl revid:epotemkin@stripped
@ mysql-test/r/greedy_optimizer.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/group_by.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/join.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/join_cache.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/join_nested.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/join_nested_jcl6.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/join_optimizer.result
A test case is added for the bug#47217.
@ mysql-test/r/join_outer.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/join_outer_jcl6.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/myisam.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/null_key.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/order_by.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/select.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/select_jcl6.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/select_safe.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/subselect.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/subselect3.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/subselect3_jcl6.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/subselect_no_mat.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/subselect_no_opts.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/subselect_no_semijoin.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/subselect_sj.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/subselect_sj2.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/subselect_sj2_jcl6.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/subselect_sj_jcl6.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/r/view.result
A test case result is corrected after fix for the bug#47217.
@ mysql-test/t/join_optimizer.test
A test case is added for the bug#47217.
@ sql/sql_select.cc
Bug#47217: Lost optimization caused slowdown & wrong result.
The optimization introduced in the bug fix#8877 is re-merged.
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 2010-05-02 18:01:00 +0000
+++ b/mysql-test/r/distinct.result 2010-05-12 16:03:52 +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 2010-05-11 16:19:31 +0000
+++ b/mysql-test/r/greedy_optimizer.result 2010-05-12 16:03:52 +0000
@@ -124,8 +124,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 NULL NULL NULL NULL 3
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
@@ -136,8 +136,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 NULL NULL NULL NULL 3
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
@@ -146,7 +146,7 @@ Variable_name Value
Last_query_cost 821.936459
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -158,7 +158,7 @@ Variable_name Value
Last_query_cost 794.968437
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -170,7 +170,7 @@ Variable_name Value
Last_query_cost 794.968437
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -182,7 +182,7 @@ Variable_name Value
Last_query_cost 795.085394
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -204,8 +204,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 NULL NULL NULL NULL 3
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
@@ -216,8 +216,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 NULL NULL NULL NULL 3
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
@@ -226,7 +226,7 @@ Variable_name Value
Last_query_cost 821.936459
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 t6 ALL NULL NULL NULL NULL 18 Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
@@ -238,7 +238,7 @@ Variable_name Value
Last_query_cost 289.430791
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 t6 ALL NULL NULL NULL NULL 18 Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
@@ -250,7 +250,7 @@ Variable_name Value
Last_query_cost 289.430791
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 t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
@@ -262,7 +262,7 @@ Variable_name Value
Last_query_cost 289.469776
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 t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
@@ -302,7 +302,7 @@ Variable_name Value
Last_query_cost 822.001607
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
@@ -314,7 +314,7 @@ Variable_name Value
Last_query_cost 795.014136
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
@@ -326,7 +326,7 @@ Variable_name Value
Last_query_cost 795.014136
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
@@ -338,7 +338,7 @@ Variable_name Value
Last_query_cost 795.340548
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
@@ -356,8 +356,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 NULL NULL NULL NULL 3
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
@@ -368,8 +368,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 NULL NULL NULL NULL 3
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
@@ -378,7 +378,7 @@ Variable_name Value
Last_query_cost 821.936459
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 t6 ALL NULL NULL NULL NULL 18 Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
@@ -390,7 +390,7 @@ Variable_name Value
Last_query_cost 289.430791
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 t6 ALL NULL NULL NULL NULL 18 Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
@@ -402,7 +402,7 @@ Variable_name Value
Last_query_cost 289.430791
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 t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
@@ -414,7 +414,7 @@ Variable_name Value
Last_query_cost 289.469776
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 t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
@@ -436,8 +436,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 NULL NULL NULL NULL 3
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
@@ -448,8 +448,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 NULL NULL NULL NULL 3
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
@@ -458,7 +458,7 @@ Variable_name Value
Last_query_cost 821.936459
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -470,7 +470,7 @@ Variable_name Value
Last_query_cost 794.968437
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -482,7 +482,7 @@ Variable_name Value
Last_query_cost 794.968437
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -494,7 +494,7 @@ Variable_name Value
Last_query_cost 795.085394
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -534,7 +534,7 @@ Variable_name Value
Last_query_cost 822.001607
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
@@ -546,7 +546,7 @@ Variable_name Value
Last_query_cost 795.014136
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
@@ -558,7 +558,7 @@ Variable_name Value
Last_query_cost 795.014136
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
@@ -570,7 +570,7 @@ Variable_name Value
Last_query_cost 795.340548
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
@@ -588,8 +588,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 NULL NULL NULL NULL 3
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
@@ -600,8 +600,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 NULL NULL NULL NULL 3
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
-1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
-1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer
+1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 Using where
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
@@ -610,7 +610,7 @@ Variable_name Value
Last_query_cost 821.936459
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -622,7 +622,7 @@ Variable_name Value
Last_query_cost 794.968437
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -634,7 +634,7 @@ Variable_name Value
Last_query_cost 794.968437
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
@@ -646,7 +646,7 @@ Variable_name Value
Last_query_cost 795.085394
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 t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2010-03-25 11:08:24 +0000
+++ b/mysql-test/r/group_by.result 2010-05-12 16:03:52 +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 2010-05-02 18:01:00 +0000
+++ b/mysql-test/r/join.result 2010-05-12 16:03:52 +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 2010-05-12 15:35:38 +0000
+++ b/mysql-test/r/join_cache.result 2010-05-12 16:03:52 +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,
@@ -3567,7 +3567,7 @@ set optimizer_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)
@@ -3743,7 +3743,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 optimizer_join_cache_level=6;
select t2.f1, t2.f2, t2.f3 from t1,t2
@@ -3756,7 +3756,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 optimizer_join_cache_level=7;
select t2.f1, t2.f2, t2.f3 from t1,t2
@@ -3769,7 +3769,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 optimizer_join_cache_level=8;
select t2.f1, t2.f2, t2.f3 from t1,t2
@@ -3782,7 +3782,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 optimizer_join_cache_level=default;
@@ -3801,7 +3801,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;
@@ -3813,7 +3813,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;
@@ -3857,7 +3857,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
@@ -3870,7 +3870,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
@@ -3921,8 +3921,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 2010-05-02 18:01:00 +0000
+++ b/mysql-test/r/join_nested.result 2010-05-12 16:03:52 +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 2010-05-07 17:44:52 +0000
+++ b/mysql-test/r/join_nested_jcl6.result 2010-05-12 16:03:52 +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 2010-04-19 16:33:25 +0000
+++ b/mysql-test/r/join_optimizer.result 2010-05-12 16:03:52 +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 2010-04-22 15:39:39 +0000
+++ b/mysql-test/r/join_outer.result 2010-05-12 16:03:52 +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 2010-05-07 17:44:52 +0000
+++ b/mysql-test/r/join_outer_jcl6.result 2010-05-12 16:03:52 +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 2010-04-20 07:22:51 +0000
+++ b/mysql-test/r/myisam.result 2010-05-12 16:03:52 +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 2010-05-02 18:01:00 +0000
+++ b/mysql-test/r/null_key.result 2010-05-12 16:03:52 +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 2010-05-02 18:01:00 +0000
+++ b/mysql-test/r/order_by.result 2010-05-12 16:03:52 +0000
@@ -1484,7 +1484,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 2010-05-07 21:14:09 +0000
+++ b/mysql-test/r/select.result 2010-05-12 16:03:52 +0000
@@ -3565,19 +3565,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));
@@ -3612,7 +3612,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
@@ -3620,7 +3620,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
@@ -3628,7 +3628,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
@@ -3636,7 +3636,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 2010-05-07 21:14:09 +0000
+++ b/mysql-test/r/select_jcl6.result 2010-05-12 16:03:52 +0000
@@ -3569,19 +3569,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));
@@ -3616,7 +3616,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
@@ -3624,7 +3624,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
@@ -3632,7 +3632,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
@@ -3640,7 +3640,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 2010-04-20 07:22:51 +0000
+++ b/mysql-test/r/select_safe.result 2010-05-12 16:03:52 +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 2010-05-02 18:01:00 +0000
+++ b/mysql-test/r/subselect.result 2010-05-12 16:03:52 +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))
@@ -4222,7 +4222,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
@@ -4232,7 +4232,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
@@ -4735,7 +4735,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 2010-05-10 09:37:24 +0000
+++ b/mysql-test/r/subselect3.result 2010-05-12 16:03:52 +0000
@@ -1164,7 +1164,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 2010-05-10 09:37:24 +0000
+++ b/mysql-test/r/subselect3_jcl6.result 2010-05-12 16:03:52 +0000
@@ -1169,7 +1169,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 2010-05-07 17:44:52 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2010-05-12 16:03:52 +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))
@@ -4226,7 +4226,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
@@ -4236,7 +4236,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
@@ -4739,7 +4739,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 2010-05-07 17:44:52 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2010-05-12 16:03:52 +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`))))
@@ -4739,7 +4739,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 2010-05-07 17:44:52 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2010-05-12 16:03:52 +0000
@@ -4739,7 +4739,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 2010-05-12 12:36:03 +0000
+++ b/mysql-test/r/subselect_sj.result 2010-05-12 16:03:52 +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`))
@@ -664,7 +664,7 @@ EXPLAIN EXTENDED SELECT vkey FROM t0 WHE
(SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t0 ALL PRIMARY NULL NULL NULL 5 100.00
-1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00 Using where
1 PRIMARY t2 ref vkey vkey 4 test.t1.vnokey 2 100.00 Using index; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t0`.`vkey` AS `vkey` from `test`.`t0` `t1` semi join (`test`.`t0` `t2`) join `test`.`t0` where ((`test`.`t2`.`vkey` = `test`.`t1`.`vnokey`) and (`test`.`t1`.`pk` = `test`.`t0`.`pk`))
=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result 2010-05-12 15:46:10 +0000
+++ b/mysql-test/r/subselect_sj2.result 2010-05-12 16:03:52 +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_icp= @@optimizer_switch;
set optimizer_switch='index_condition_pushdown=off';
@@ -362,7 +362,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 Start temporary
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary
Warnings:
=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result 2010-05-12 15:46:10 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-05-12 16:03:52 +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 Using join buffer
set @save_icp= @@optimizer_switch;
set optimizer_switch='index_condition_pushdown=off';
@@ -366,7 +366,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 Start temporary; Using join buffer
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary; Using join buffer
Warnings:
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result 2010-05-12 12:36:03 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2010-05-12 16:03:52 +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`))
@@ -668,7 +668,7 @@ EXPLAIN EXTENDED SELECT vkey FROM t0 WHE
(SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t0 ALL PRIMARY NULL NULL NULL 5 100.00
-1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00 Using join buffer
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00 Using where; Using join buffer
1 PRIMARY t2 ref vkey vkey 4 test.t1.vnokey 2 100.00 Using index; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t0`.`vkey` AS `vkey` from `test`.`t0` `t1` semi join (`test`.`t0` `t2`) join `test`.`t0` where ((`test`.`t2`.`vkey` = `test`.`t1`.`vnokey`) and (`test`.`t1`.`pk` = `test`.`t0`.`pk`))
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result 2010-05-02 18:01:00 +0000
+++ b/mysql-test/r/view.result 2010-05-12 16:03:52 +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 2010-03-22 15:17:58 +0000
+++ b/mysql-test/t/join_optimizer.test 2010-05-12 16:03:52 +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 2010-05-12 15:41:26 +0000
+++ b/sql/sql_select.cc 2010-05-12 16:03:52 +0000
@@ -8567,6 +8567,8 @@ inline void add_cond_and_fix(Item **e1,
{
if (*e1)
{
+ if (!e2)
+ return;
Item *res;
if ((res= new Item_cond_and(*e1, e2)))
{
@@ -8844,6 +8846,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++)
@@ -8940,6 +8946,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-20100512160352-zzhzqzsj8gcdmgze.bundle
| Thread |
|---|
| • bzr commit into mysql-next-mr-bugfixing branch (epotemkin:3170)Bug#47217 | Evgeny Potemkin | 12 May |
