revision-id: cabc7b62de11d13b631cd643cacfd7f1e7b379e3 (mariadb-10.4.11-27-gcabc7b62de1) parent(s): 983163209d026bfd979b4298053fcbdb373efa9d author: Varun Gupta committer: Varun Gupta timestamp: 2020-01-11 18:47:16 +0530 message: MDEV-21377: Eq_ref access not picked by query with optimizer_use_condition_selectivity > 1 The issue here is the estimate of the number of distinct ref access made by the optimizer are greater than the records in the join prefix. This is incorrect, we need to make sure that the number of distinct ref accesses are less than the records in the join prefix --- mysql-test/main/join_outer_innodb.result | 28 ++++++++-------- mysql-test/main/selectivity.result | 56 +++++++++++++++++++++++++++++++ mysql-test/main/selectivity.test | 38 +++++++++++++++++++++ mysql-test/main/selectivity_innodb.result | 56 +++++++++++++++++++++++++++++++ mysql-test/main/subselect_mat.result | 16 ++++----- mysql-test/main/subselect_sj2_mat.result | 16 ++++----- mysql-test/main/subselect_sj_mat.result | 16 ++++----- mysql-test/main/subselect_sj_mat.test | 4 +-- sql/sql_select.cc | 3 ++ 9 files changed, 190 insertions(+), 43 deletions(-) diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index a0358094baa..5d0c4f4afde 100644 --- a/mysql-test/main/join_outer_innodb.result +++ b/mysql-test/main/join_outer_innodb.result @@ -434,46 +434,46 @@ where t1.a10 = 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a4,a6,a5,a7 NULL NULL NULL 3 Using where 1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where 1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index +1 SIMPLE t10 eq_ref PRIMARY PRIMARY 1 test.t1.a6 1 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index +1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where +1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index 1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index 1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where -1 SIMPLE t14 ALL PRIMARY NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index +1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index 1 SIMPLE t16 ref PRIMARY PRIMARY 2 test.t15.o1 1 Using where -1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where -1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a4,a6,a5,a7 NULL NULL NULL 3 Using where 1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where 1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index +1 SIMPLE t10 eq_ref PRIMARY PRIMARY 1 test.t1.a6 1 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index +1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where +1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index 1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index 1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where -1 SIMPLE t14 ALL PRIMARY NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index +1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index 1 SIMPLE t16 ref PRIMARY PRIMARY 2 test.t15.o1 1 Using where -1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where -1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index drop view v1; drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16; # diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 4366ef6a564..95069c74391 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1867,4 +1867,60 @@ set optimizer_switch= @save_optimizer_switch; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1,t2; # End of 10.1 tests +# +# MDEV-21377: Eq_ref access not picked by query with +# optimizer_use_condition_selectivity > 1 +# +create table t1(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t1 select seq, seq, seq from seq_1_to_100; +create table t2(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t2 select seq, seq, seq from seq_1_to_1000; +create table t3(a int, b int, primary key(a)); +insert into t3 select seq, seq from seq_1_to_100; +analyze table t1,t2,t3; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='rowid_filter=off'; +set optimizer_use_condition_selectivity=1; +explain SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b,c b 5 NULL 5 Using index condition; Using where +1 SIMPLE t2 ref b,c c 5 test.t1.c 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +a b c a b c a b +1 1 1 1 1 1 1 1 +2 2 2 2 2 2 2 2 +3 3 3 3 3 3 3 3 +4 4 4 4 4 4 4 4 +5 5 5 5 5 5 5 5 +6 6 6 6 6 6 6 6 +7 7 7 7 7 7 7 7 +8 8 8 8 8 8 8 8 +9 9 9 9 9 9 9 9 +set optimizer_use_condition_selectivity=4; +explain SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b,c b 5 NULL 5 Using index condition; Using where +1 SIMPLE t2 ref b,c c 5 test.t1.c 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +a b c a b c a b +1 1 1 1 1 1 1 1 +2 2 2 2 2 2 2 2 +3 3 3 3 3 3 3 3 +4 4 4 4 4 4 4 4 +5 5 5 5 5 5 5 5 +6 6 6 6 6 6 6 6 +7 7 7 7 7 7 7 7 +8 8 8 8 8 8 8 8 +9 9 9 9 9 9 9 9 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set optimizer_switch=@save_optimizer_switch; +drop table t1,t2,t3; +# End of 10.4 tests set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index d0158fb717e..6ed3ae67d83 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1269,6 +1269,44 @@ drop table t1,t2; --echo # End of 10.1 tests +--echo # +--echo # MDEV-21377: Eq_ref access not picked by query with +--echo # optimizer_use_condition_selectivity > 1 +--echo # + +create table t1(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t1 select seq, seq, seq from seq_1_to_100; + +create table t2(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t2 select seq, seq, seq from seq_1_to_1000; + +create table t3(a int, b int, primary key(a)); +insert into t3 select seq, seq from seq_1_to_100; +--disable_result_log +analyze table t1,t2,t3; +--enable_result_log + +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='rowid_filter=off'; +let $q= SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and + t2.b < 10 AND t1.b < 10; + +set optimizer_use_condition_selectivity=1; +eval explain $q; +eval $q; + +set optimizer_use_condition_selectivity=4; +eval explain $q; +eval $q; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set optimizer_switch=@save_optimizer_switch; + +drop table t1,t2,t3; + +--echo # End of 10.4 tests + # # Clean up # diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 062b2da7b4f..d48848a92bf 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1877,6 +1877,62 @@ set optimizer_switch= @save_optimizer_switch; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1,t2; # End of 10.1 tests +# +# MDEV-21377: Eq_ref access not picked by query with +# optimizer_use_condition_selectivity > 1 +# +create table t1(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t1 select seq, seq, seq from seq_1_to_100; +create table t2(a int, b int,c int, primary key(a), key(b), key(c)); +insert into t2 select seq, seq, seq from seq_1_to_1000; +create table t3(a int, b int, primary key(a)); +insert into t3 select seq, seq from seq_1_to_100; +analyze table t1,t2,t3; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='rowid_filter=off'; +set optimizer_use_condition_selectivity=1; +explain SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b,c b 5 NULL 9 Using index condition; Using where +1 SIMPLE t2 ref b,c c 5 test.t1.c 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +a b c a b c a b +1 1 1 1 1 1 1 1 +2 2 2 2 2 2 2 2 +3 3 3 3 3 3 3 3 +4 4 4 4 4 4 4 4 +5 5 5 5 5 5 5 5 +6 6 6 6 6 6 6 6 +7 7 7 7 7 7 7 7 +8 8 8 8 8 8 8 8 +9 9 9 9 9 9 9 9 +set optimizer_use_condition_selectivity=4; +explain SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b,c b 5 NULL 9 Using index condition; Using where +1 SIMPLE t2 ref b,c c 5 test.t1.c 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +SELECT * FROM t1, t2, t3 WHERE t1.c= t2.c and t1.a = t3.a and +t2.b < 10 AND t1.b < 10; +a b c a b c a b +1 1 1 1 1 1 1 1 +2 2 2 2 2 2 2 2 +3 3 3 3 3 3 3 3 +4 4 4 4 4 4 4 4 +5 5 5 5 5 5 5 5 +6 6 6 6 6 6 6 6 +7 7 7 7 7 7 7 7 +8 8 8 8 8 8 8 8 +9 9 9 9 9 9 9 9 +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set optimizer_switch=@save_optimizer_switch; +drop table t1,t2,t3; +# End of 10.4 tests set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 34b58daa50e..c2f78a8453a 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -2368,9 +2368,9 @@ t1.dispatch_group IN FROM t2, t3 t3_i WHERE t2.ugroup = t3_i.sys_id AND t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND -t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +t2.user = '931644d4d773020058c92cf65e61034c'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary +1 PRIMARY t2 ref idx3,idx4 idx4 35 const 1 Using index condition; Using where; Start temporary 1 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where 1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t3_i.sys_id 2 Using index condition; Using where; End temporary 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index @@ -2382,13 +2382,12 @@ t1.dispatch_group IN FROM t2, t3 t3_i WHERE t2.ugroup = t3_i.sys_id AND t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND -t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +t2.user = '931644d4d773020058c92cf65e61034c'); assignment_group df50316637232000158bbfc8bcbe5d23 e08fad2637232000158bbfc8bcbe5d39 ec70316637232000158bbfc8bcbe5d60 7b10fd2637232000158bbfc8bcbe5d30 -ebb4620037332000158bbfc8bcbe5d89 set optimizer_switch='materialization=on'; explain SELECT t1.assignment_group FROM t1, t3 @@ -2398,12 +2397,12 @@ t1.dispatch_group IN FROM t2, t3 t3_i WHERE t2.ugroup = t3_i.sys_id AND t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND -t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +t2.user = '931644d4d773020058c92cf65e61034c'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index -2 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where +2 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 1 Using index condition; Using where 2 MATERIALIZED t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where SELECT t1.assignment_group FROM t1, t3 @@ -2413,13 +2412,12 @@ t1.dispatch_group IN FROM t2, t3 t3_i WHERE t2.ugroup = t3_i.sys_id AND t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND -t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +t2.user = '931644d4d773020058c92cf65e61034c'); assignment_group df50316637232000158bbfc8bcbe5d23 e08fad2637232000158bbfc8bcbe5d39 ec70316637232000158bbfc8bcbe5d60 7b10fd2637232000158bbfc8bcbe5d30 -ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; set optimizer_switch=@local_optimizer_switch; # diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 589144f1238..388b6d5e901 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -1825,16 +1825,15 @@ explain SELECT t2.id FROM t2,t1 WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 30 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using index -2 MATERIALIZED t3 ALL NULL NULL NULL NULL 14 -2 MATERIALIZED t1 eq_ref PRIMARY PRIMARY 4 test.t3.id 1 Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 14 Using where; Start temporary +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.ref_id 1 Using where; Using index +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.id 1 Using index; End temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.ref_id 1 Using where; Using index SELECT t2.id FROM t2,t1 WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; id -10 11 +10 set optimizer_switch='materialization=off'; SELECT t2.id FROM t2,t1 WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; @@ -1934,15 +1933,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 12 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2_2.id_product 1 Using where; Using index 1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where -1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t3.id_product,const 1 Using where; Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t2_5 ref id_t2,id_product id_product 5 test.t3.id_product 44 Using index condition; Using where; Start temporary; End temporary +1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using where; Using index; Using join buffer (flat, BNL join) 3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 Using where 5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where -6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 31 Using index condition; Using where 2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 51 4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 33 Using index condition; Using where set optimizer_switch='rowid_filter=default'; diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index afc75a22962..fe9bf0015bb 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -2404,9 +2404,9 @@ t1.dispatch_group IN FROM t2, t3 t3_i WHERE t2.ugroup = t3_i.sys_id AND t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND -t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +t2.user = '931644d4d773020058c92cf65e61034c'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary +1 PRIMARY t2 ref idx3,idx4 idx4 35 const 1 Using index condition; Using where; Start temporary 1 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where 1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t3_i.sys_id 2 Using index condition; Using where; End temporary 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index @@ -2418,13 +2418,12 @@ t1.dispatch_group IN FROM t2, t3 t3_i WHERE t2.ugroup = t3_i.sys_id AND t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND -t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +t2.user = '931644d4d773020058c92cf65e61034c'); assignment_group df50316637232000158bbfc8bcbe5d23 e08fad2637232000158bbfc8bcbe5d39 ec70316637232000158bbfc8bcbe5d60 7b10fd2637232000158bbfc8bcbe5d30 -ebb4620037332000158bbfc8bcbe5d89 set optimizer_switch='materialization=on'; explain SELECT t1.assignment_group FROM t1, t3 @@ -2434,12 +2433,12 @@ t1.dispatch_group IN FROM t2, t3 t3_i WHERE t2.ugroup = t3_i.sys_id AND t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND -t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +t2.user = '931644d4d773020058c92cf65e61034c'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index -2 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where +2 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 1 Using index condition; Using where 2 MATERIALIZED t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where SELECT t1.assignment_group FROM t1, t3 @@ -2449,13 +2448,12 @@ t1.dispatch_group IN FROM t2, t3 t3_i WHERE t2.ugroup = t3_i.sys_id AND t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND -t2.user = '86826bf03710200044e0bfc8bcbe5d79'); +t2.user = '931644d4d773020058c92cf65e61034c'); assignment_group df50316637232000158bbfc8bcbe5d23 e08fad2637232000158bbfc8bcbe5d39 ec70316637232000158bbfc8bcbe5d60 7b10fd2637232000158bbfc8bcbe5d30 -ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; set optimizer_switch=@local_optimizer_switch; # diff --git a/mysql-test/main/subselect_sj_mat.test b/mysql-test/main/subselect_sj_mat.test index 1de8701ecbb..22f60622756 100644 --- a/mysql-test/main/subselect_sj_mat.test +++ b/mysql-test/main/subselect_sj_mat.test @@ -2047,7 +2047,7 @@ INSERT INTO t1 VALUES INSERT INTO t2 VALUES ('17801ac21b13200050fdfbcd2c0713e8','8e826bf03710200044e0bfc8bcbe5d86', '14c19a061b13200050fdfbcd2c07134b'), -('577ed708d773020058c92cf65e61037a','931644d4d773020058c92cf65e61034c', +('96fb652637232000158bbfc8bcbe5db4','931644d4d773020058c92cf65e61034c', '339888d4d773020058c92cf65e6103aa'), ('df50316637232000158bbfc8bcbe5d23','92826bf03710200044e0bfc8bcbe5da9', '3682f56637232000158bbfc8bcbe5d44'), @@ -2156,7 +2156,7 @@ WHERE t1.assignment_group = t3.sys_id AND FROM t2, t3 t3_i WHERE t2.ugroup = t3_i.sys_id AND t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND - t2.user = '86826bf03710200044e0bfc8bcbe5d79'); + t2.user = '931644d4d773020058c92cf65e61034c'); set optimizer_switch='materialization=off'; eval explain $q; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d9d9c229c2f..f5b08dcdd79 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7370,6 +7370,7 @@ best_access_path(JOIN *join, double tmp2= prev_record_reads(join_positions, idx, (found_ref | keyuse->used_tables)); + tmp2= MY_MIN(tmp2, record_count); if (tmp2 < best_prev_record_reads) { best_part_found_ref= keyuse->used_tables & ~join->const_table_map; @@ -7411,6 +7412,7 @@ best_access_path(JOIN *join, but 1.0 would be probably safer */ tmp= prev_record_reads(join_positions, idx, found_ref); + MY_MIN(tmp, record_count); records= 1.0; type= JT_FT; trace_access_idx.add("access_type", join_type_str[type]) @@ -7440,6 +7442,7 @@ best_access_path(JOIN *join, trace_access_idx.add("access_type", join_type_str[type]) .add("index", keyinfo->name); tmp = prev_record_reads(join_positions, idx, found_ref); + tmp= MY_MIN(tmp, record_count); records=1.0; } else