diff options
Diffstat (limited to 'mysql-test')
39 files changed, 385 insertions, 225 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index ee8f9f897ad..6e9150f2a06 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -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; diff --git a/mysql-test/r/greedy_optimizer.result b/mysql-test/r/greedy_optimizer.result index c0012c297d1..b8e5fee8de8 100644 --- a/mysql-test/r/greedy_optimizer.result +++ b/mysql-test/r/greedy_optimizer.result @@ -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 diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 991158970ae..6527b4c220c 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -873,7 +873,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; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index af0a9d5f98d..05afd209788 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -846,7 +846,7 @@ select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b; 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 @@ -1095,7 +1095,7 @@ ON t4.a = t5.a ON t1.a = t3.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t3 ref a a 5 test.t1.a 2 Using index +1 SIMPLE t3 ref a a 5 test.t1.a 2 Using where; Using index 1 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 0 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 0 Using where @@ -1184,4 +1184,34 @@ NULL NULL 1 DROP TABLE t1, t2, mm1; +# +# 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 5.1 tests diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 975df707400..8574b3da666 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -4379,7 +4379,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, @@ -4407,7 +4407,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, @@ -4619,7 +4619,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) @@ -4796,7 +4796,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 @@ -4809,7 +4809,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 @@ -4822,7 +4822,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 @@ -4835,7 +4835,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; @@ -4854,7 +4854,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; @@ -4866,7 +4866,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; @@ -4910,7 +4910,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 @@ -4923,7 +4923,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 @@ -4974,8 +4974,8 @@ FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND 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 @@ -5189,7 +5189,7 @@ INSERT INTO t3 VALUES EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using join buffer 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b; @@ -5238,8 +5238,69 @@ ON t3.carrier = t1.carrier; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.carrier 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.carrier 1 Using where 1 SIMPLE t4 ref carrier_id carrier_id 5 test.t3.id 2 Using index SET join_cache_level=default; DROP TABLE t1,t2,t3,t4; +# +# BUG#52636: allowing JOINs on NULL values w/ join_cache_level = 5-8 +# +CREATE TABLE t1 (b int); +INSERT INTO t1 VALUES (NULL),(3); +CREATE TABLE t2 (a int, b int, KEY (b)); +INSERT INTO t2 VALUES (100,NULL),(150,200); +set join_cache_level = 5; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ref b b 5 test.t1.b 2 Using where; Using join buffer +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +a +NULL +NULL +set join_cache_level = 8; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ref b b 5 test.t1.b 2 Using where; Using join buffer +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +a +NULL +NULL +delete from t1; +INSERT INTO t1 VALUES (NULL),(NULL); +set join_cache_level = 5; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ref b b 5 test.t1.b 2 Using where; Using join buffer +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +a +NULL +NULL +DROP TABLE t1,t2; +CREATE TABLE t1 (b varchar(100)); +INSERT INTO t1 VALUES (NULL),("some varchar"); +CREATE TABLE t2 (a int, b varchar(100), KEY (b)); +INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar"); +set join_cache_level = 5; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ref b b 103 test.t1.b 2 Using where; Using join buffer +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +a +NULL +NULL +set join_cache_level = 8; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ref b b 103 test.t1.b 2 Using where; Using join buffer +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +a +NULL +NULL +set join_cache_level = default; +DROP TABLE t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 6718274184d..31d3bbb0189 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -854,7 +854,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on((((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`)) and (`test`.`t3`.`b` is not null))) where 1 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM (t3,t4) LEFT JOIN @@ -966,7 +966,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t8(b); EXPLAIN EXTENDED SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -1015,7 +1015,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); EXPLAIN EXTENDED @@ -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 @@ -1065,7 +1065,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -1201,7 +1201,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 21 Using index -1 SIMPLE t3 index c c 5 NULL 6 Using index +1 SIMPLE t3 index c c 5 NULL 6 Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c 2 Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c @@ -1272,7 +1272,7 @@ DELETE FROM t3; EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 21 Using index -1 SIMPLE t3 index c c 5 NULL 0 Using index +1 SIMPLE t3 index c c 5 NULL 0 Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c 2 Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c @@ -1445,7 +1445,7 @@ explain select * from t4 join t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X -1 SIMPLE t3 ref a a 5 test.t2.b X +1 SIMPLE t3 ref a a 5 test.t2.b X Using where 1 SIMPLE t5 ref a a 5 test.t3.b X 1 SIMPLE t4 ref a a 5 test.t3.b X Using index condition explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b @@ -1453,17 +1453,17 @@ join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X 1 SIMPLE t3 ref a a 5 test.t2.b X Using index condition -1 SIMPLE t4 ref a a 5 test.t3.b X +1 SIMPLE t4 ref a a 5 test.t3.b X Using where 1 SIMPLE t6 ref a a 5 test.t4.b X -1 SIMPLE t5 ref a a 5 test.t2.b X +1 SIMPLE t5 ref a a 5 test.t2.b X Using where 1 SIMPLE t7 ref a a 5 test.t5.b X explain select * from t2 left join (t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b join t5 on t5.a=t3.b) on t3.a=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X -1 SIMPLE t3 ref a a 5 test.t2.b X -1 SIMPLE t4 ref a a 5 test.t3.b X +1 SIMPLE t3 ref a a 5 test.t2.b X Using where +1 SIMPLE t4 ref a a 5 test.t3.b X Using where 1 SIMPLE t6 ref a a 5 test.t4.b X 1 SIMPLE t5 ref a a 5 test.t3.b X drop table t0, t1, t2, t3, t4, t5, t6, t7; @@ -1478,8 +1478,8 @@ explain select * from t1 left join on (t1.a = t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t2 ref a a 5 test.t1.a 1 -1 SIMPLE t3 ref a a 5 test.t2.a 1 +1 SIMPLE t2 ref a a 5 test.t1.a 1 Using where +1 SIMPLE t3 ref a a 5 test.t2.a 1 Using where drop table t1, t2, t3; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10)); CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10)); @@ -1729,9 +1729,9 @@ 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 t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1 Using where 1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index 1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index SELECT COUNT(*) diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result index 5aea6ba74c3..2c406a8014c 100755..100644 --- a/mysql-test/r/join_nested_jcl6.result +++ b/mysql-test/r/join_nested_jcl6.result @@ -861,7 +861,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 2 100.00 Using where; Using join buffer 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on((((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`)) and (`test`.`t3`.`b` is not null))) where 1 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM (t3,t4) LEFT JOIN @@ -973,7 +973,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t8(b); EXPLAIN EXTENDED SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -1022,7 +1022,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); EXPLAIN EXTENDED @@ -1061,7 +1061,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 @@ -1072,7 +1072,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -1208,7 +1208,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 21 Using index -1 SIMPLE t3 index c c 5 NULL 6 Using index +1 SIMPLE t3 index c c 5 NULL 6 Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c 2 Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c @@ -1279,7 +1279,7 @@ DELETE FROM t3; EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 21 Using index -1 SIMPLE t3 index c c 5 NULL 0 Using index +1 SIMPLE t3 index c c 5 NULL 0 Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c 2 Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c @@ -1452,7 +1452,7 @@ explain select * from t4 join t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X -1 SIMPLE t3 ref a a 5 test.t2.b X Using join buffer +1 SIMPLE t3 ref a a 5 test.t2.b X Using where; Using join buffer 1 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer 1 SIMPLE t4 ref a a 5 test.t3.b X Using index condition(BKA); Using join buffer explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b @@ -1460,17 +1460,17 @@ join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X 1 SIMPLE t3 ref a a 5 test.t2.b X Using index condition(BKA); Using join buffer -1 SIMPLE t4 ref a a 5 test.t3.b X Using join buffer +1 SIMPLE t4 ref a a 5 test.t3.b X Using where; Using join buffer 1 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer -1 SIMPLE t5 ref a a 5 test.t2.b X Using join buffer +1 SIMPLE t5 ref a a 5 test.t2.b X Using where; Using join buffer 1 SIMPLE t7 ref a a 5 test.t5.b X Using join buffer explain select * from t2 left join (t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b join t5 on t5.a=t3.b) on t3.a=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X -1 SIMPLE t3 ref a a 5 test.t2.b X Using join buffer -1 SIMPLE t4 ref a a 5 test.t3.b X Using join buffer +1 SIMPLE t3 ref a a 5 test.t2.b X Using where; Using join buffer +1 SIMPLE t4 ref a a 5 test.t3.b X Using where; Using join buffer 1 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer 1 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer drop table t0, t1, t2, t3, t4, t5, t6, t7; @@ -1485,8 +1485,8 @@ explain select * from t1 left join on (t1.a = t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t2 ref a a 5 test.t1.a 1 Using join buffer -1 SIMPLE t3 ref a a 5 test.t2.a 1 Using join buffer +1 SIMPLE t2 ref a a 5 test.t1.a 1 Using where; Using join buffer +1 SIMPLE t3 ref a a 5 test.t2.a 1 Using where; Using join buffer drop table t1, t2, t3; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10)); CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10)); @@ -1736,9 +1736,9 @@ 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 t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1 Using where 1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index 1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index SELECT COUNT(*) @@ -1773,7 +1773,7 @@ ON t6.b >= 2 AND t5.b=t7.b AND (t8.a > 0 OR t8.c IS NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 -1 SIMPLE t7 ref b_i b_i 5 test.t5.b 2 Using join buffer +1 SIMPLE t7 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer 1 SIMPLE t6 ALL b_i NULL NULL NULL 3 Using where; Using join buffer 1 SIMPLE t8 ref b_i b_i 5 test.t7.b 2 Using where; Using join buffer SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index c448bddd3c9..c7bfa7797bd 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1126,7 +1126,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 diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 1347866745b..d3008632cf8 100755..100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1133,7 +1133,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 diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result index a87ade614b8..f424c5ac297 100644 --- a/mysql-test/r/key_cache.result +++ b/mysql-test/r/key_cache.result @@ -642,7 +642,7 @@ i 3 explain select count(*) from t1, t2 where t1.p = t2.i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index k1 k1 5 NULL 1024 Using index +1 SIMPLE t2 index k1 k1 5 NULL 1024 Using where; Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.i 1 Using index select count(*) from t1, t2 where t1.p = t2.i; count(*) diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index a4b201754c0..6faf12b0277 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -357,7 +357,7 @@ id select_type table type possible_keys key key_len ref rows Extra 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 diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index d59383f3037..027d1d2b93c 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -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 @@ -407,8 +407,8 @@ EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.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 t2 ref idx idx 5 test.t1.a 1 -1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using index +1 SIMPLE t2 ref idx idx 5 test.t1.a 1 Using where +1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using where; Using index FLUSH STATUS ; SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b; @@ -423,8 +423,8 @@ FOUND_ROWS() SHOW STATUS LIKE "handler_read%"; Variable_name Value Handler_read_first 0 -Handler_read_key 6 -Handler_read_next 2 +Handler_read_key 8 +Handler_read_next 31942 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 5 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index dcd40f66365..fc1cd57dd28 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1489,7 +1489,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' diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 5e9a336ca9e..a9745599615 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -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)); @@ -3608,7 +3608,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 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 @@ -3616,7 +3616,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 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 @@ -3624,7 +3624,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND 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 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 @@ -3632,7 +3632,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND 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 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); diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 7ea2744d69b..b6ee346f83c 100755..100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -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)); @@ -3615,7 +3615,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 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 @@ -3623,7 +3623,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 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 @@ -3631,7 +3631,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND 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 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 @@ -3639,7 +3639,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND 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 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); diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 5e9a336ca9e..a9745599615 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -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)); @@ -3608,7 +3608,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 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 @@ -3616,7 +3616,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 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 @@ -3624,7 +3624,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND 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 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 @@ -3632,7 +3632,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND 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 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); diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index 9419f256472..26fb008c2a0 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -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; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 94e5c843b8b..45e28d30ca3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1338,7 +1338,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`) @@ -1348,7 +1348,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)) @@ -1358,8 +1358,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.61 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`)) @@ -1375,7 +1375,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)) @@ -4202,7 +4202,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 @@ -4212,7 +4212,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 diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index a947aecb326..bcaa056adc9 100755..100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1131,7 +1131,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; diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index ad6ef5dc325..a708642e320 100755..100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1139,7 +1139,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; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 84d02f2ff35..c8502e55a0d 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -1342,7 +1342,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`) @@ -1352,7 +1352,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)) @@ -1362,8 +1362,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.61 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`)) @@ -1379,7 +1379,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)) @@ -4206,7 +4206,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 @@ -4216,7 +4216,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 diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 1b04c836560..b959b8032a8 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -1317,7 +1317,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 from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 066d29d941f..d6de0263df4 100755..100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -13,7 +13,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 @@ -40,7 +40,7 @@ select * from t1 where a in (select a from t11); 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); @@ -51,8 +51,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 @@ -62,8 +62,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`)) @@ -508,7 +508,7 @@ EXPLAIN EXTENDED SELECT vkey FROM t0 WHERE pk IN (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`)) diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index f17edf3be6c..9d335a0cb30 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -34,7 +34,7 @@ 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 subselect2 ALL unique_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where select * from t2 where b in (select a from t1); a b 1 1 @@ -76,7 +76,7 @@ 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 subselect2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 -2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 +2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); @@ -273,7 +273,7 @@ from t0 where a in id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary 1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer -1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index +1 PRIMARY t2 ref a a 5 test.t1.a 1 Using where; Using index 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary drop table t0, t1,t2,t3; CREATE TABLE t1 ( @@ -425,7 +425,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: diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index e497263ed4a..ff6a341a799 100755..100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -41,7 +41,7 @@ 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 subselect2 ALL unique_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where select * from t2 where b in (select a from t1); a b 1 1 @@ -83,7 +83,7 @@ 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 subselect2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer -2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 +2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); @@ -280,7 +280,7 @@ from t0 where a in id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary 1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer -1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index +1 PRIMARY t2 ref a a 5 test.t1.a 1 Using where; Using index 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary drop table t0, t1,t2,t3; CREATE TABLE t1 ( @@ -432,7 +432,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 Start temporary +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary 1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 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: diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 37c9da25aeb..a4b05d6570e 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -20,7 +20,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 @@ -47,7 +47,7 @@ select * from t1 where a in (select a from t11); 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); @@ -58,8 +58,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 @@ -69,8 +69,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`)) @@ -515,7 +515,7 @@ EXPLAIN EXTENDED SELECT vkey FROM t0 WHERE pk IN (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`)) @@ -1103,7 +1103,7 @@ explain 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 Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Start temporary +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary 1 PRIMARY t1 ref a a 5 test.t0.a 1 Using join buffer 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary; Using join buffer SELECT * FROM t0 WHERE t0.a IN diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index a1b8ba0018d..282ae4cb55d 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -26,17 +26,17 @@ a explain select * from t1 left join t2 on t2.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where # This will not be eliminated as t2.b is in in order list: explain select t1.a from t1 left join t2 on t2.a=t1.a order by t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where # This will not be eliminated as t2.b is in group list: explain select t1.a from t1 left join t2 on t2.a=t1.a group by t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where # This will not be eliminated as t2.b is in the WHERE explain select t1.a from t1 left join t2 on t2.a=t1.a where t2.b < 3 or t2.b is null; id select_type table type possible_keys key key_len ref rows Extra @@ -75,7 +75,7 @@ This must not use elimination: explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index drop table t0, t1, t2, t3; create table t0 ( id integer, primary key (id)); create table t1 ( @@ -128,7 +128,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3 This should use facts and a1 tables: explain extended select id from v1 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR +1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using where; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1 @@ -156,7 +156,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3 This should use facts and a1 tables: explain extended select id from v2 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR +1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using where; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1 @@ -164,7 +164,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` whe This should use facts, a2 and its subquery: explain extended select id from v2 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using MRR +1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index Warnings: @@ -217,7 +217,7 @@ explain select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 Using where explain select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) @@ -232,7 +232,7 @@ t1 left join on t2.pk=t1.col or t2.pk=t1.col; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 Using where drop table t1, t2, t3; # # Check things that look like functional dependencies but really are not diff --git a/mysql-test/r/table_elim_debug.result b/mysql-test/r/table_elim_debug.result index b059baffa89..ae49b1433fd 100644 --- a/mysql-test/r/table_elim_debug.result +++ b/mysql-test/r/table_elim_debug.result @@ -10,7 +10,7 @@ set optimizer_switch='table_elimination=off'; explain select t1.a from t1 left join t2 on t2.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index set optimizer_switch='table_elimination=on'; explain select t1.a from t1 left join t2 on t2.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 1d9f8a84e24..178901f5774 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2341,11 +2341,11 @@ CREATE VIEW v1 AS SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; 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 diff --git a/mysql-test/suite/pbxt/r/distinct.result b/mysql-test/suite/pbxt/r/distinct.result index d95a2bb3232..3153845279a 100644 --- a/mysql-test/suite/pbxt/r/distinct.result +++ b/mysql-test/suite/pbxt/r/distinct.result @@ -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 t2 ref a a 4 test.t1.a 1 Using index 1 SIMPLE t3 ref a a 5 test.t1.b 1 Using index SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; diff --git a/mysql-test/suite/pbxt/r/greedy_optimizer.result b/mysql-test/suite/pbxt/r/greedy_optimizer.result index 1d3e42b70fb..8c634935894 100644 --- a/mysql-test/suite/pbxt/r/greedy_optimizer.result +++ b/mysql-test/suite/pbxt/r/greedy_optimizer.result @@ -144,7 +144,7 @@ Variable_name Value Last_query_cost 822.625316 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 795.625316 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 795.625316 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 795.625316 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 822.625316 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 290.146368 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 290.146368 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 290.146368 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 822.625316 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 795.625316 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 795.625316 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 795.625316 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 822.625316 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 290.146368 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 290.146368 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 290.146368 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 822.625316 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 795.625316 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 795.625316 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 795.625316 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 822.625316 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 795.625316 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 795.625316 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 795.625316 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 822.625316 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 795.625316 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 795.625316 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 795.625316 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 diff --git a/mysql-test/suite/pbxt/r/group_by.result b/mysql-test/suite/pbxt/r/group_by.result index c5c9099af0d..7f6a35989b5 100644 --- a/mysql-test/suite/pbxt/r/group_by.result +++ b/mysql-test/suite/pbxt/r/group_by.result @@ -856,7 +856,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; diff --git a/mysql-test/suite/pbxt/r/join.result b/mysql-test/suite/pbxt/r/join.result index 6b6dba69b32..2e49a7f02bf 100644 --- a/mysql-test/suite/pbxt/r/join.result +++ b/mysql-test/suite/pbxt/r/join.result @@ -794,8 +794,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 diff --git a/mysql-test/suite/pbxt/r/join_nested.result b/mysql-test/suite/pbxt/r/join_nested.result index 1b46807a159..739407169d9 100644 --- a/mysql-test/suite/pbxt/r/join_nested.result +++ b/mysql-test/suite/pbxt/r/join_nested.result @@ -854,7 +854,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 1 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on((((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`)) and (`test`.`t3`.`b` is not null))) where 1 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM (t3,t4) LEFT JOIN @@ -966,7 +966,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) +Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t8(b); EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -1209,7 +1209,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL # Using index -1 SIMPLE t3 index c c 5 NULL # Using index +1 SIMPLE t3 index c c 5 NULL # Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c # Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c @@ -1280,7 +1280,7 @@ DELETE FROM t3; EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL # Using index -1 SIMPLE t3 index c c 5 NULL # Using index +1 SIMPLE t3 index c c 5 NULL # Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c # Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c @@ -1453,7 +1453,7 @@ explain select * from t4 join t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X -1 SIMPLE t3 ref a a 5 test.t2.b X +1 SIMPLE t3 ref a a 5 test.t2.b X Using where 1 SIMPLE t5 ref a a 5 test.t3.b X 1 SIMPLE t4 ref a a 5 test.t3.b X Using where explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b @@ -1461,17 +1461,17 @@ join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X 1 SIMPLE t3 ref a a 5 test.t2.b X Using where -1 SIMPLE t4 ref a a 5 test.t3.b X +1 SIMPLE t4 ref a a 5 test.t3.b X Using where 1 SIMPLE t6 ref a a 5 test.t4.b X -1 SIMPLE t5 ref a a 5 test.t2.b X +1 SIMPLE t5 ref a a 5 test.t2.b X Using where 1 SIMPLE t7 ref a a 5 test.t5.b X explain select * from t2 left join (t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b join t5 on t5.a=t3.b) on t3.a=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL X -1 SIMPLE t3 ref a a 5 test.t2.b X -1 SIMPLE t4 ref a a 5 test.t3.b X +1 SIMPLE t3 ref a a 5 test.t2.b X Using where +1 SIMPLE t4 ref a a 5 test.t3.b X Using where 1 SIMPLE t6 ref a a 5 test.t4.b X 1 SIMPLE t5 ref a a 5 test.t3.b X drop table t0, t1, t2, t3, t4, t5, t6, t7; @@ -1486,8 +1486,8 @@ explain select * from t1 left join on (t1.a = t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t2 ref a a 5 test.t1.a 1 -1 SIMPLE t3 ref a a 5 test.t2.a 1 +1 SIMPLE t2 ref a a 5 test.t1.a 1 Using where +1 SIMPLE t3 ref a a 5 test.t2.a 1 Using where drop table t1, t2, t3; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10)); CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10)); diff --git a/mysql-test/suite/pbxt/r/join_outer.result b/mysql-test/suite/pbxt/r/join_outer.result index e6ae055d811..adf316f7e6c 100644 --- a/mysql-test/suite/pbxt/r/join_outer.result +++ b/mysql-test/suite/pbxt/r/join_outer.result @@ -1126,7 +1126,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 diff --git a/mysql-test/suite/pbxt/r/null_key.result b/mysql-test/suite/pbxt/r/null_key.result index 272120e46a4..48ea4f6dbd2 100644 --- a/mysql-test/suite/pbxt/r/null_key.result +++ b/mysql-test/suite/pbxt/r/null_key.result @@ -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 @@ -407,8 +407,8 @@ EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.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 t2 ref idx idx 5 test.t1.a 1 -1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using index +1 SIMPLE t2 ref idx idx 5 test.t1.a 1 Using where +1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using where; Using index FLUSH STATUS ; SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b; @@ -423,8 +423,8 @@ FOUND_ROWS() SHOW STATUS LIKE "handler_read%"; Variable_name Value Handler_read_first 0 -Handler_read_key 6 -Handler_read_next 2 +Handler_read_key 8 +Handler_read_next 31942 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 5 diff --git a/mysql-test/suite/pbxt/r/select.result b/mysql-test/suite/pbxt/r/select.result index fbb84444bd0..7a5803d654d 100644 --- a/mysql-test/suite/pbxt/r/select.result +++ b/mysql-test/suite/pbxt/r/select.result @@ -2368,7 +2368,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2); insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 1 +1 SIMPLE t2 ref c,d d 5 const 1 Using where 1 SIMPLE t1 ref a a 5 test.t2.c 1 select * from t1 left join t2 on a=c where d in (4); a b c d @@ -2376,7 +2376,7 @@ a b c d 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 1 +1 SIMPLE t2 ref c,d d 5 const 1 Using where 1 SIMPLE t1 ref a a 5 test.t2.c 1 select * from t1 left join t2 on a=c where d = 4; a b c d @@ -3632,7 +3632,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND 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 2 Using where +1 SIMPLE t2 range si,ai ai 5 NULL 1 Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where DROP TABLE t1,t2,t3; DROP TABLE IF EXISTS t1; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 43b373c9703..cb865528aa2 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -851,4 +851,23 @@ ENGINE=MERGE UNION=(t1,t2); SELECT t1.a FROM mm1,t1; DROP TABLE t1, t2, mm1; +--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 5.1 tests diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index f65f521f21a..a71db1081a1 100755 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -2026,5 +2026,55 @@ SET join_cache_level=default; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # BUG#52636: allowing JOINs on NULL values w/ join_cache_level = 5-8 +--echo # + +CREATE TABLE t1 (b int); +INSERT INTO t1 VALUES (NULL),(3); + +CREATE TABLE t2 (a int, b int, KEY (b)); +INSERT INTO t2 VALUES (100,NULL),(150,200); + +set join_cache_level = 5; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +--sorted_result +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; + +set join_cache_level = 8; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +--sorted_result +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; + +# test crash when no key is worth collecting by BKA for t2's ref +delete from t1; +INSERT INTO t1 VALUES (NULL),(NULL); +set join_cache_level = 5; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +--sorted_result +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; + +DROP TABLE t1,t2; + +# test varchar keys +CREATE TABLE t1 (b varchar(100)); +INSERT INTO t1 VALUES (NULL),("some varchar"); + +CREATE TABLE t2 (a int, b varchar(100), KEY (b)); +INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar"); + +set join_cache_level = 5; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +--sorted_result +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; + +set join_cache_level = 8; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +--sorted_result +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; + +set join_cache_level = default; +DROP TABLE t1,t2; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; |