diff options
38 files changed, 240 insertions, 227 deletions
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 5cc12aac77f..4b7b7cb8088 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -3260,10 +3260,10 @@ ORDER BY path; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 Using filesort 2 DERIVED t2 ALL NULL NULL NULL NULL 15 Using where -3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 Using where -3 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 -4 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 Using where -4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 +3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 15 Using where; Using join buffer (flat, BNL join) +4 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 +4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 15 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL DROP TABLE t1,t2; set tmp_memory_table_size=default; @@ -4466,8 +4466,8 @@ NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL 3 DERIVED v ALL NULL NULL NULL NULL 12 Using where 3 DERIVED h ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 3 DERIVED w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) -2 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 2 -2 RECURSIVE UNION h ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 RECURSIVE UNION h ALL NULL NULL NULL NULL 12 +2 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 2 RECURSIVE UNION w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL prepare stmt from "with recursive @@ -4565,8 +4565,8 @@ id select_type table type possible_keys key key_len ref rows Extra 5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL 2 DERIVED h ALL NULL NULL NULL NULL 12 -2 DERIVED w ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join) -2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 DERIVED w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) prepare stmt from "with recursive ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, w_id, w_name, w_dob, w_father, w_mother) diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result index ed3683d52f9..5bca2bd5ec2 100644 --- a/mysql-test/main/delete.result +++ b/mysql-test/main/delete.result @@ -205,13 +205,13 @@ insert into t3 values (1,1),(2,1),(1,3); select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; a b a b a b 1 1 1 1 1 1 -2 1 2 2 2 1 1 3 1 1 1 3 +2 1 2 2 2 1 explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using index -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b,test.t1.b 1 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 8 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE t3 index PRIMARY PRIMARY 8 NULL 3 Using where; Using index; Using join buffer (incremental, BNL join) delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; select * from t3; a b diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index c73bc43a2a4..6bd055add1c 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -2044,16 +2044,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) -1 SIMPLE t3 ref e e 3 test.t2.d 1 Using index 1 SIMPLE t4 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 index e e 7 NULL 8 Using where; Using index; Using join buffer (flat, BNL join) SELECT t2.d FROM t1,t2,v3 WHERE v3.e = t2.d AND v3.i < 3; d -w -w -w -w -w -w d d d @@ -2072,6 +2066,12 @@ e e e e +w +w +w +w +w +w SET SESSION join_cache_level = @exit_join_cache_level; SET SESSION join_buffer_size = @exit_join_buffer_size; DROP VIEW v3; @@ -2459,6 +2459,8 @@ SELECT * FROM t1; a 1 1 +1 +1 drop table t1,t2; set optimizer_switch=@save968720_optimizer_switch; # @@ -2595,8 +2597,8 @@ CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; EXPLAIN EXTENDED SELECT v1.c1, v1.c2 FROM v1, t2 WHERE v1.c1=t2.c1 AND v1.c2=t2.c2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c2 2 100.00 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 75.00 Using where; Using join buffer (flat, BNL join) 2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 /* select#1 */ select `v1`.`c1` AS `c1`,`v1`.`c2` AS `c2` from `test`.`v1` join `test`.`t2` where `v1`.`c1` = `test`.`t2`.`c1` and `v1`.`c2` = `test`.`t2`.`c2` @@ -2608,8 +2610,8 @@ EXPLAIN EXTENDED SELECT t2.c1, t2.c2 FROM (SELECT c1 g, MAX(c2) m FROM t1 GROUP BY c1) t, t2 WHERE t.g=t2.c1 AND t.m=t2.c2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c2 2 100.00 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 75.00 Using where; Using join buffer (flat, BNL join) 2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from (/* select#2 */ select `test`.`t1`.`c1` AS `g`,max(`test`.`t1`.`c2`) AS `m` from `test`.`t1` group by `test`.`t1`.`c1`) `t` join `test`.`t2` where `t`.`g` = `test`.`t2`.`c1` and `t`.`m` = `test`.`t2`.`c2` diff --git a/mysql-test/main/distinct.result b/mysql-test/main/distinct.result index cad56fdc92a..e02c5c2a7c7 100644 --- a/mysql-test/main/distinct.result +++ b/mysql-test/main/distinct.result @@ -173,9 +173,9 @@ 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 t2 index a a 4 NULL 5 Using index; Using temporary -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using where +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; Using join buffer (flat, BNL join) SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; a 1 @@ -523,7 +523,7 @@ EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2 WHERE t1_1.a = t1_2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1_1 ALL PRIMARY NULL NULL NULL 3 Using temporary -1 SIMPLE t1_2 eq_ref PRIMARY PRIMARY 4 test.t1_1.a 1 Using index; Distinct +1 SIMPLE t1_2 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) EXPLAIN SELECT a FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 Using index diff --git a/mysql-test/main/greedy_optimizer.result b/mysql-test/main/greedy_optimizer.result index 55812cb6ff9..5a5d24c973e 100644 --- a/mysql-test/main/greedy_optimizer.result +++ b/mysql-test/main/greedy_optimizer.result @@ -142,52 +142,52 @@ Variable_name Value Last_query_cost 1693.637037 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 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 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 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 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 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 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 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 set optimizer_prune_level=0; select @@optimizer_prune_level; @@optimizer_prune_level @@ -454,52 +454,52 @@ Variable_name Value Last_query_cost 1693.637037 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 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 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 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +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 (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 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 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 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 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +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 (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 set optimizer_search_depth=1; select @@optimizer_search_depth; @@optimizer_search_depth @@ -606,52 +606,52 @@ Variable_name Value Last_query_cost 1693.637037 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 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 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 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +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 (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 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 Using where -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 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 Using where -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +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 (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 844.037037 +Last_query_cost 642.737037 drop table t1,t2,t3,t4,t5,t6,t7; CREATE TABLE t1 (a int, b int, d int, i int); INSERT INTO t1 VALUES (1,1,1,1); diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index 994805303f2..7f1e40bd314 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -552,12 +552,12 @@ a b 3 1 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a NULL NULL NULL 4 Using temporary; Using filesort -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a NULL NULL NULL 4 Using temporary -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary +1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) drop table t1,t2; SET @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity,@@optimizer_switch=@save_optimizer_switch; create table t1 (a int, b int); diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index 65dccc6b9a3..94d782abd05 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -258,7 +258,7 @@ name varchar(255) default NULL, PRIMARY KEY (id) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5'); -select t1.*, t2.* from t1, t2 where t2.id=t1.t2_id limit 2; +select straight_join t1.*, t2.* from t2, t1 where t2.id=t1.t2_id limit 2; t1_id t2_id type cost_unit min_value max_value t3_id item_id id name 12 5 Percent Cost -1 0 -1 -1 5 s5 14 4 Percent Cost -1 0 -1 -1 4 s4 diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test index e929854251a..1948ccd3a36 100644 --- a/mysql-test/main/join.test +++ b/mysql-test/main/join.test @@ -253,7 +253,7 @@ CREATE TABLE t2 ( PRIMARY KEY (id) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5'); -select t1.*, t2.* from t1, t2 where t2.id=t1.t2_id limit 2; +select straight_join t1.*, t2.* from t2, t1 where t2.id=t1.t2_id limit 2; drop table t1,t2; # diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 1580a4a931a..2435b3f7485 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -3071,11 +3071,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index 1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaidformatid 4 test.t1.metaid 1 Using index condition; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t4 eq_ref PRIMARY,t4_formatclassid,t4_formats_idx PRIMARY 4 test.t3.formatid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (incremental, BNL join) 1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaidformatid 4 test.t1.metaid 1 Using index condition; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 eq_ref PRIMARY,t4_formatclassid,t4_formats_idx PRIMARY 4 test.t3.formatid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t1.uniquekey, t1.xml AS affiliateXml, t8.name AS artistName, t8.artistid, t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, @@ -4507,8 +4507,8 @@ SET SESSION join_buffer_size = 256; EXPLAIN SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL idx NULL NULL NULL 15 Using where -1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t2.a 36 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 36 +1 SIMPLE t2 ALL idx NULL NULL NULL 15 Using where; Using join buffer (flat, BNL join) SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; a c SET SESSION join_cache_level=@save_join_cache_level; diff --git a/mysql-test/main/join_nested.result b/mysql-test/main/join_nested.result index eee3a01c078..05a8fc3e8e3 100644 --- a/mysql-test/main/join_nested.result +++ b/mysql-test/main/join_nested.result @@ -1064,7 +1064,7 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ref idx_a idx_a 5 const 2 100.00 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t1 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL idx_b NULL NULL NULL 7 75.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result index ab5618bd2f6..01b4c38902f 100644 --- a/mysql-test/main/join_nested_jcl6.result +++ b/mysql-test/main/join_nested_jcl6.result @@ -1073,7 +1073,7 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ref idx_a idx_a 5 const 2 100.00 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t1 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t1 ALL idx_b NULL NULL NULL 7 75.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t5 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) @@ -2068,9 +2068,9 @@ FROM t5 LEFT JOIN ON (t5.b=t8.b); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 -1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join) 1 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t5 LEFT JOIN (t6 LEFT JOIN t7 ON t7.a=1, t8) @@ -2085,9 +2085,9 @@ FROM t5 LEFT JOIN ON (t5.b=t8.b); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 -1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join) 1 SIMPLE t7 ref b_i b_i 5 const 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t5 LEFT JOIN (t6 LEFT JOIN t7 ON t7.b=2, t8) diff --git a/mysql-test/main/limit_rows_examined.result b/mysql-test/main/limit_rows_examined.result index f0a22b8f3f2..abed46ff235 100644 --- a/mysql-test/main/limit_rows_examined.result +++ b/mysql-test/main/limit_rows_examined.result @@ -49,11 +49,10 @@ explain select * from t1i, t2i where c1 = c2 LIMIT ROWS EXAMINED 6; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1i index PRIMARY PRIMARY 2 NULL 4 Using index -1 SIMPLE t2i eq_ref PRIMARY PRIMARY 2 test.t1i.c1 1 Using index +1 SIMPLE t2i index PRIMARY PRIMARY 2 NULL 4 Using where; Using index; Using join buffer (flat, BNL join) select * from t1i, t2i where c1 = c2 LIMIT ROWS EXAMINED 6; c1 c2 bb bb -cc cc Warnings: Warning 1931 Query execution was interrupted. The query examined at least 7 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete set @@join_cache_level=6; @@ -70,11 +69,10 @@ explain select * from t1i, t2i where c1 = c2 LIMIT ROWS EXAMINED 6; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1i index PRIMARY PRIMARY 2 NULL 4 Using index -1 SIMPLE t2i eq_ref PRIMARY PRIMARY 2 test.t1i.c1 1 Using index +1 SIMPLE t2i index PRIMARY PRIMARY 2 NULL 4 Using where; Using index; Using join buffer (flat, BNL join) select * from t1i, t2i where c1 = c2 LIMIT ROWS EXAMINED 6; c1 c2 bb bb -cc cc Warnings: Warning 1931 Query execution was interrupted. The query examined at least 7 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete Mix LIMIT ROWS EXAMINED with LIMIT diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index 674dc79fe12..1af632a19c8 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -1092,8 +1092,8 @@ a b c a b c set optimizer_switch='firstmatch=off'; explain update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c< t2.c) order by t2.c, t1.c limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using temporary; Using filesort -1 PRIMARY t1 ref a a 5 test.t2.a 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort +1 PRIMARY t1 ALL a NULL NULL NULL 10 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Start temporary; End temporary update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; select * from t2; diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index 2ff966fdfd3..1c2deb32224 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -932,15 +932,15 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be EXPLAIN DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 3 Using where +1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 3 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 100.00 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 100.00 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 3 75.00 Using where +1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 3 75.00 Using where # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 13 @@ -949,8 +949,8 @@ FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 100.00 Using index -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 100.00 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 8 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE t3 index PRIMARY PRIMARY 8 NULL 3 75.00 Using where; Using index; Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`b3` AS `b3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a2` = `test`.`t1`.`a1` and `test`.`t3`.`a3` = `test`.`t2`.`b2` and `test`.`t3`.`b3` = `test`.`t1`.`b1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -960,16 +960,16 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead # Status of "equivalent" SELECT query execution: Variable_name Value -Handler_read_key 19 -Handler_read_next 3 +Handler_read_first 2 +Handler_read_key 13 +Handler_read_next 6 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value Handler_delete 8 -Handler_read_key 19 -Handler_read_next 3 +Handler_read_key 13 Handler_read_rnd 5 -Handler_read_rnd_next 4 +Handler_read_rnd_next 28 DROP TABLE t1, t2, t3; #20 diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 70499311396..71084fa3f4c 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -1113,8 +1113,8 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { }, { "access_type": "scan", - "resulting_rows": 100, - "cost": 2.317382812, + "resulting_rows": 75, + "cost": 7.317382812, "chosen": false } ], @@ -1160,8 +1160,8 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { }, { "access_type": "scan", - "resulting_rows": 100, - "cost": 2.317382812, + "resulting_rows": 75, + "cost": 7.317382812, "chosen": false } ], @@ -2685,8 +2685,8 @@ explain select * from t1 left join t2 on t2.a=t1.a { }, { "access_type": "scan", - "resulting_rows": 2, - "cost": 8.017578125, + "resulting_rows": 1.5, + "cost": 8.417578125, "chosen": false } ], @@ -9586,8 +9586,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", - "resulting_rows": 800, - "cost": 44.19726562, + "resulting_rows": 600, + "cost": 84.19726562, "chosen": false } ], @@ -9844,8 +9844,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", - "resulting_rows": 100, - "cost": 2.219726562, + "resulting_rows": 75, + "cost": 7.219726562, "chosen": false } ], @@ -9911,16 +9911,16 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", - "resulting_rows": 10, - "cost": 2.021972656, + "resulting_rows": 7.5, + "cost": 2.521972656, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records": 10, - "cost": 2.021972656, + "records": 7.5, + "cost": 2.521972656, "uses_join_buffering": true } } @@ -9934,10 +9934,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "t2" ], "table": "t1", - "rows_for_plan": 1000, - "cost_for_plan": 224.2416992, + "rows_for_plan": 750, + "cost_for_plan": 174.7416992, "pruned_by_cost": true, - "current_cost": 224.2416992, + "current_cost": 174.7416992, "best_cost": 36.0278306 } ] @@ -10353,9 +10353,9 @@ create table t3 (a int, b int, c int); insert into t3 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4); explain select * from t2,t1,t3 where t2.b= t1.b and t1.a=t3.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t1 ref PRIMARY PRIMARY 8 test.t3.a,test.t2.b 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 index PRIMARY PRIMARY 12 NULL 5 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (incremental, BNL join) set @trace=(select trace from information_schema.optimizer_trace); set @path= (select json_search(@trace, 'one', 'no predicate for first keypart')); set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2); diff --git a/mysql-test/main/opt_trace_selectivity.result b/mysql-test/main/opt_trace_selectivity.result index 54f05885049..00a01def168 100644 --- a/mysql-test/main/opt_trace_selectivity.result +++ b/mysql-test/main/opt_trace_selectivity.result @@ -103,8 +103,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) ], "table": "t1", "rows_for_plan": 7, - "cost_for_plan": 3.573416331, - "estimated_join_cardinality": 7 + "cost_for_plan": 3.573416331 } ] ] @@ -206,8 +205,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) ], "table": "t1", "rows_for_plan": 0.6, - "cost_for_plan": 2.292957403, - "estimated_join_cardinality": 0.6 + "cost_for_plan": 2.292957403 } ] ] diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 7fbdbe1b9ed..b002abede5b 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2975,7 +2975,7 @@ id y x explain extended select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 Using index -1 SIMPLE t2 ref x,y y 5 const 2 83.33 Using where +1 SIMPLE t2 ref x,y y 5 const 2 91.67 Using where Warnings: Note 1003 select 1 AS `id`,`test`.`t2`.`y` AS `y`,`test`.`t2`.`x` AS `x` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`y` = 2 and `test`.`t2`.`x` = 1 drop table t1, t2; diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 691ba1319dd..5474c05628d 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -114,17 +114,17 @@ order by s_acctbal desc, n_name, s_name, p_partkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where; Using temporary; Using filesort -1 PRIMARY nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 100.00 -1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 2.08 Using where; Using join buffer (flat, BNL join) -1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 100.00 Using where +1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where +1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where +1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where 2 DEPENDENT SUBQUERY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where 2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 2 DEPENDENT SUBQUERY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where 2 DEPENDENT SUBQUERY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.part.p_partkey' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `dbt3_s001`.`supplier`.`s_acctbal` AS `s_acctbal`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`nation`.`n_name` AS `n_name`,`dbt3_s001`.`part`.`p_partkey` AS `p_partkey`,`dbt3_s001`.`part`.`p_mfgr` AS `p_mfgr`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`dbt3_s001`.`supplier`.`s_comment` AS `s_comment` from `dbt3_s001`.`part` join `dbt3_s001`.`supplier` join `dbt3_s001`.`partsupp` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`part`.`p_size` = 9 and `dbt3_s001`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey` and `dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey` and `dbt3_s001`.`region`.`r_name` = 'ASIA' and `dbt3_s001`.`part`.`p_type` like '%TIN' and `dbt3_s001`.`partsupp`.`ps_supplycost` = <expr_cache><`dbt3_s001`.`part`.`p_partkey`>((/* select#2 */ select min(`dbt3_s001`.`partsupp`.`ps_supplycost`) from `dbt3_s001`.`partsupp` join `dbt3_s001`.`supplier` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where `dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey` and `dbt3_s001`.`region`.`r_name` = 'ASIA' and `dbt3_s001`.`part`.`p_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey`)) order by `dbt3_s001`.`supplier`.`s_acctbal` desc,`dbt3_s001`.`nation`.`n_name`,`dbt3_s001`.`supplier`.`s_name`,`dbt3_s001`.`part`.`p_partkey` +Note 1003 /* select#1 */ select `dbt3_s001`.`supplier`.`s_acctbal` AS `s_acctbal`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`nation`.`n_name` AS `n_name`,`dbt3_s001`.`part`.`p_partkey` AS `p_partkey`,`dbt3_s001`.`part`.`p_mfgr` AS `p_mfgr`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`dbt3_s001`.`supplier`.`s_comment` AS `s_comment` from `dbt3_s001`.`part` join `dbt3_s001`.`supplier` join `dbt3_s001`.`partsupp` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`part`.`p_size` = 9 and `dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey` and `dbt3_s001`.`region`.`r_name` = 'ASIA' and `dbt3_s001`.`part`.`p_type` like '%TIN' and `dbt3_s001`.`partsupp`.`ps_supplycost` = <expr_cache><`dbt3_s001`.`part`.`p_partkey`>((/* select#2 */ select min(`dbt3_s001`.`partsupp`.`ps_supplycost`) from `dbt3_s001`.`partsupp` join `dbt3_s001`.`supplier` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where `dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey` and `dbt3_s001`.`region`.`r_name` = 'ASIA' and `dbt3_s001`.`part`.`p_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey`)) order by `dbt3_s001`.`supplier`.`s_acctbal` desc,`dbt3_s001`.`nation`.`n_name`,`dbt3_s001`.`supplier`.`s_name`,`dbt3_s001`.`part`.`p_partkey` === Q15 === create view revenue0 (supplier_no, total_revenue) as select l_suppkey, sum(l_extendedprice * (1 - l_discount)) diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index 126fda6ddc7..f4e931c3236 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -71,10 +71,10 @@ order by revenue desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using temporary; Using filesort 1 SIMPLE nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 -1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using rowid filter 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey @@ -85,7 +85,7 @@ and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01' group by n_name order by revenue desc; n_name revenue -PERU 321915.87150000007 +PERU 321915.8715 ARGENTINA 69817.1451 set optimizer_switch=@save_optimizer_switch; delete from mysql.index_stats; @@ -178,10 +178,10 @@ order by revenue desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using temporary; Using filesort 1 SIMPLE nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 -1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using rowid filter 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey @@ -192,7 +192,7 @@ and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01' group by n_name order by revenue desc; n_name revenue -PERU 321915.87150000007 +PERU 321915.8715 ARGENTINA 69817.1451 set optimizer_switch=@save_optimizer_switch; EXPLAIN select o_year, diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 4209e2bc529..668ccdf773f 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -345,7 +345,7 @@ patient_uq clinic_uq explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t7 index PRIMARY PRIMARY 4 NULL 2 100.00 Using index -1 PRIMARY t6 ALL i1 NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t6 ALL i1 NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t7` join `test`.`t6` where `test`.`t6`.`clinic_uq` = `test`.`t7`.`uq` @@ -1440,7 +1440,7 @@ a 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` select * from t2 where t2.a in (select a from t1 where t1.b <> 30); @@ -1450,7 +1450,7 @@ a 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using where +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`b` <> 30 select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); @@ -1459,11 +1459,11 @@ 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 -1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 +1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t1`.`a` = `test`.`t2`.`a` +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t2`.`a` = `test`.`t1`.`a` drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index 28187e0ffdd..2820775cb4f 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -166,7 +166,7 @@ from t3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key -2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL a NULL NULL NULL 10 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index 9df821e07dc..8be02ad5c2a 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -169,7 +169,7 @@ from t3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key -2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +2 DEPENDENT SUBQUERY t2 ALL a NULL NULL NULL 10 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` diff --git a/mysql-test/main/subselect_exists2in.result b/mysql-test/main/subselect_exists2in.result index 6ff518b5a29..d03edf522eb 100644 --- a/mysql-test/main/subselect_exists2in.result +++ b/mysql-test/main/subselect_exists2in.result @@ -52,7 +52,7 @@ explain extended SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index aa aa 4 NULL 2 100.00 Using index -1 PRIMARY t3 ALL bb NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL bb NULL NULL NULL 2 75.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t3`.`b` = `test`.`t1`.`a` diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 05f36d6fceb..a73b79fe697 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -1889,8 +1889,8 @@ WHERE alias4.c = alias3.b ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 MATERIALIZED alias3 ALL NULL NULL NULL NULL 2 Using where -3 MATERIALIZED alias4 ref c c 11 test.alias3.b 2 Using where; Using index +3 MATERIALIZED alias3 ALL NULL NULL NULL NULL 2 +3 MATERIALIZED alias4 index c c 11 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) DROP TABLE t1,t2; # # BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result index 1889291398c..67a4feafeee 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.result +++ b/mysql-test/main/subselect_mat_cost_bugs.result @@ -97,7 +97,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 100.00 Using where 2 DEPENDENT SUBQUERY t1b ALL NULL NULL NULL NULL 9 100.00 2 DEPENDENT SUBQUERY t1a ref c2 c2 5 test.t1b.pk 1 100.00 Using where -2 DEPENDENT SUBQUERY t2 index c3 c3 9 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2 index c3 c3 9 NULL 2 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on(`test`.`t1a`.`c2` = `test`.`t1b`.`pk` and 2) where `test`.`t2`.`c3` = `test`.`t1b`.`c4` and `test`.`t1`.`pk` <> 0 and <cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`))) diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index e32e6007328..4a8a9ffc322 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -1444,7 +1444,7 @@ a 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` select * from t2 where t2.a in (select a from t1 where t1.b <> 30); @@ -1454,7 +1454,7 @@ a 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using where +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`b` <> 30 select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); @@ -1463,11 +1463,11 @@ 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 -1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 +1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t1`.`a` = `test`.`t2`.`a` +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t2`.`a` = `test`.`t1`.`a` drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 07755a5144a..42b767c505e 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -352,7 +352,7 @@ patient_uq clinic_uq explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t7 index PRIMARY PRIMARY 4 NULL 2 100.00 Using index -1 PRIMARY t6 ALL i1 NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t6 ALL i1 NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t7` join `test`.`t6` where `test`.`t6`.`clinic_uq` = `test`.`t7`.`uq` @@ -1447,7 +1447,7 @@ a 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` select * from t2 where t2.a in (select a from t1 where t1.b <> 30); @@ -1457,7 +1457,7 @@ a 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using where +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`b` <> 30 select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); @@ -1466,11 +1466,11 @@ 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 -1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 +1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t1`.`a` = `test`.`t2`.`a` +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t2`.`a` = `test`.`t1`.`a` drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 15688fc1717..51386376d80 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -1506,10 +1506,10 @@ 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 a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a 1167 100.00 Using index +2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using index +2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t3` where `test`.`t1`.`b` = `test`.`t3`.`a` and <cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t1`.`b` and <cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index e3bdddbf84b..3b4f0aa368f 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -351,7 +351,7 @@ patient_uq clinic_uq explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t7 index PRIMARY PRIMARY 4 NULL 2 100.00 Using index -1 PRIMARY t6 ALL i1 NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t6 ALL i1 NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t7` join `test`.`t6` where `test`.`t6`.`clinic_uq` = `test`.`t7`.`uq` @@ -1446,7 +1446,7 @@ a 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` select * from t2 where t2.a in (select a from t1 where t1.b <> 30); @@ -1456,7 +1456,7 @@ a 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using where +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`b` <> 30 select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); @@ -1465,11 +1465,11 @@ 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 PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 -1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 +1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t1`.`a` = `test`.`t2`.`a` +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t2`.`a` = `test`.`t1`.`a` drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 88f8f78019e..a66886cfc21 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -925,10 +925,10 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a) explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 MATERIALIZED t2 index a a 5 NULL 3 100.00 Using index -2 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 100.00 +2 MATERIALIZED t2 index a a 5 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t2`.`a` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t3`.`a` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; # check correct NULL Processing for normal IN/ALL/ANY # and 2 ways of max/min optimization @@ -1463,10 +1463,10 @@ 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 MATERIALIZED t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using index -2 MATERIALIZED t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t1 ALL PRIMARY NULL NULL NULL 4 100.00 +2 MATERIALIZED t3 index PRIMARY PRIMARY 4 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t3` where `test`.`t1`.`b` = `test`.`t3`.`a` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`a`)))) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t1`.`b` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`a`)))) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1506,10 +1506,10 @@ 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 a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a 1167 100.00 Using index +2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using index +2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t3` where `test`.`t1`.`b` = `test`.`t3`.`a` and <cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t1`.`b` and <cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index b69471edce3..1da51e76b4d 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -1249,8 +1249,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); EXPLAIN SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary; End temporary SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); i 1 @@ -1610,9 +1610,9 @@ A.t1field IN (SELECT C.t2field FROM t2 C WHERE C.t2field IN (SELECT D.t2field FROM t2 D)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A index PRIMARY PRIMARY 4 NULL 3 Using index -1 PRIMARY C eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index -1 PRIMARY D eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index 1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; Start temporary; End temporary +1 PRIMARY C index PRIMARY PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) +1 PRIMARY D eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index SELECT * FROM t1 A WHERE A.t1field IN (SELECT A.t1field FROM t2 B) AND @@ -2154,8 +2154,8 @@ explain SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 2 Using index; Using join buffer (flat, BNL join) -1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; Start temporary; End temporary +1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; Start temporary +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); b a 5 6 @@ -2350,8 +2350,8 @@ SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t3 ALL NULL NULL NULL NULL 13 Using where -2 MATERIALIZED t2 ref b b 4 test.t3.a 1 Using index +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 13 +2 MATERIALIZED t2 index b b 8 NULL 7 Using where; Using index SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); a 19 diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 6efa3fc12b1..d1f8080bfaa 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -1260,8 +1260,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); EXPLAIN SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); i 1 @@ -1621,9 +1621,9 @@ A.t1field IN (SELECT C.t2field FROM t2 C WHERE C.t2field IN (SELECT D.t2field FROM t2 D)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A index PRIMARY PRIMARY 4 NULL 3 Using index -1 PRIMARY C eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index +1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; FirstMatch(A); Using join buffer (flat, BNL join) +1 PRIMARY C index PRIMARY PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (incremental, BNL join) 1 PRIMARY D eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index -1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; FirstMatch(D); Using join buffer (flat, BNL join) SELECT * FROM t1 A WHERE A.t1field IN (SELECT A.t1field FROM t2 B) AND @@ -2165,8 +2165,8 @@ explain SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 2 Using index; Using join buffer (flat, BNL join) -1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; Start temporary; End temporary; Using join buffer (incremental, BNL join) +1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index; End temporary; Using join buffer (incremental, BNL join) SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); b a 5 6 @@ -2361,8 +2361,8 @@ SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t3 ALL NULL NULL NULL NULL 13 Using where -2 MATERIALIZED t2 ref b b 4 test.t3.a 1 Using index +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 13 +2 MATERIALIZED t2 index b b 8 NULL 7 Using where; Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); a 19 diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index c68b740b022..94832e725cd 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -380,7 +380,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where 5 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where -5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index +5 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 75.00 Using where; Using index; Using join buffer (flat, BNL join) 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: @@ -405,7 +405,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 5 MATERIALIZED t3c ALL NULL NULL NULL NULL 4 100.00 Using where -5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3c.c1,test.t3c.c2 1 100.00 Using index +5 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 75.00 Using where; Using index; Using join buffer (flat, BNL join) 4 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: @@ -439,7 +439,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery5> eq_ref distinct_key # # # 1 100.00 # 2 MATERIALIZED t2 ALL NULL # # # 5 100.00 # 5 MATERIALIZED t3 ALL NULL # # # 4 100.00 # -5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # +5 MATERIALIZED t2i index it2i1,it2i2,it2i3 # # # 5 75.00 # 4 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 3 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 7 UNION t1i range it1i1,it1i2,it1i3 # # # 3 100.00 # @@ -475,7 +475,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where -4 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index +4 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 75.00 Using where; Using index; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL @@ -499,7 +499,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) 1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where -4 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index +4 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 75.00 Using where; Using index; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index dcd2f4389c2..6b18a99ecbf 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -566,7 +566,7 @@ explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index -2 UNION t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index +2 UNION t2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index; Using join buffer (flat, BNL join) NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity,@@optimizer_switch=@save_optimizer_switch; explain (select * from t1 where a=1) union (select * from t1 where b=1); diff --git a/mysql-test/suite/maria/mrr.result b/mysql-test/suite/maria/mrr.result index 066f1a50aab..cc42f81e3c4 100644 --- a/mysql-test/suite/maria/mrr.result +++ b/mysql-test/suite/maria/mrr.result @@ -404,8 +404,8 @@ FROM t1 AS table1, t2 AS table2 WHERE table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE table2 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE table1 ref PRIMARY,col_varchar_1024_latin1_key col_varchar_1024_latin1_key 1027 test.table2.col_varchar_10_latin1 2 Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE table2 ALL NULL NULL NULL NULL 2 +1 SIMPLE table1 ALL PRIMARY,col_varchar_1024_latin1_key NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) SELECT count(*) FROM t1 AS table1, t2 AS table2 WHERE diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3b4e386500f..a3811c4d085 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7701,7 +7701,7 @@ void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key) @param s Current JOIN_TAB @param with_found_constraint There is a filtering condition on the - current table. See more below + current table of type. See below. @param use_cond_selectivity Value of optimizer_use_condition_selectivity. If > 1 then use table->cond_selecitivity. @@ -7715,16 +7715,27 @@ void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key) - apply the part of WHERE that refers only to this table. - The result cannot be bigger than table records + 'with_found_constraint' is true if the WHERE has a top level expression of + type: + current_table.key_part=expression_that_contains_fields_from_previous_tables + Examples (assuming join order t1,t2): + WHERE t2.keypart1=t1.some_field + WHERE t2.keypart1=t1.some_field+t1.other_field + + TODO: + Extend with_found_constraint' to be set for a top level expression of type + X=Y where X and Y has fields from current table and at least one field from + one o more previous tables. + @see also table_after_join_selectivity() produces selectivity of condition that is checked after joining rows from this table to rows from preceding tables. */ -inline -double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint, - uint use_cond_selectivity) +static double matching_candidates_in_table(JOIN_TAB *s, + bool with_found_constraint, + uint use_cond_selectivity) { - ha_rows records; double dbl_records; if (use_cond_selectivity > 1) @@ -7742,10 +7753,19 @@ double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint, sel <= s->table->opt_range_condition_rows / table_records); dbl_records= table_records * sel; - return dbl_records; } - - records = s->found_records; + else + { + /* + This is only taking into considering constant key parts used with + this table! + If no such conditions existed the following should hold: + s->table->opt_range_condition_rows == s->found_rows == + s->records. + */ + DBUG_ASSERT(s->table->opt_range_condition_rows <= s->found_records); + dbl_records= rows2double(s->table->opt_range_condition_rows); + } /* If there is a filtering condition on the table (i.e. ref analyzer found @@ -7756,17 +7776,9 @@ double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint, This heuristic is supposed to force tables used in exprZ to be before this table in join order. */ - if (with_found_constraint) - records-= records/4; - - /* - If applicable, get a more accurate estimate. Don't use the two - heuristics at once. - */ - if (s->table->opt_range_condition_rows != s->found_records) - records= s->table->opt_range_condition_rows; - dbl_records= (double)records; + if (with_found_constraint) + dbl_records-= dbl_records/4; return dbl_records; } @@ -7990,6 +8002,9 @@ best_access_path(JOIN *join, { found_part|= keyuse->keypart_map; key_parts_dependent= 0; + found_constraint|= (keyuse->used_tables & + ~(remaining_tables | + join->const_table_map)); if (!(keyuse->used_tables & ~join->const_table_map)) const_part|= keyuse->keypart_map; @@ -8024,6 +8039,7 @@ best_access_path(JOIN *join, if (all_parts & 1) key_dependent|= key_parts_dependent; found_ref|= best_part_found_ref; + /* Remember if the key expression used previous non const tables */ } while (keyuse->table == table && keyuse->key == key); /* @@ -8053,7 +8069,6 @@ best_access_path(JOIN *join, } else { - found_constraint= MY_TEST(found_part); loose_scan_opt.check_ref_access_part1(s, key, start_key, found_part); /* Check if we found full key */ diff --git a/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result index a6bd3a7c1a1..438f54f6ce6 100644 --- a/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result @@ -87,7 +87,7 @@ NULL NULL NULL 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r` t0) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`) and (t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r` t0) on ((t2.`b` = t3.`b`) and (t1.`c` = t2.`c`) and (t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result index f6c808be973..67d688e1383 100644 --- a/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result @@ -87,7 +87,7 @@ NULL c 2000-01-03 00:00:00 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null))) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null))) on ((t2.`b` = t3.`b`) and (t1.`c` = t2.`c`)) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') |