revision-id: a49376056f9eeeb76f1818c0d8b9f9e006d4dfe3 (mariadb-10.3.10-2-ga49376056f9) parent(s): ea9a7ef6ec0e3e24927fb1fc39e00cb526eec89c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-12-23 01:13:38 +0300 message: MDEV-21383: Possible range plan is not used under certain conditions (10.3's variant of the fix) make_join_select() has a section of code which starts with "We plan to scan all rows. Check again if we should use an index." the code in that section will [unnecessarily] re-run the range optimizer using this condition: condition_attached_to_current_table AND current_table's_ON_expr Note that the original invocation of range optimizer in make_join_statistics was done using the whole select's WHERE condition. taking the whole select's WHERE condition and using multiple-equalities allowed the range optimizer to do more inferences. This fix uses a very conservative approach: we still re-run the range optimizer, but we save the quick select we already have, and take the best of the two quick selects. --- mysql-test/main/distinct.result | 2 +- mysql-test/main/group_min_max.result | 2 +- sql/sql_select.cc | 15 +++++++++++++++ 3 files changed, 17 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/distinct.result b/mysql-test/main/distinct.result index 70bce519ad2..9091d78d9fb 100644 --- a/mysql-test/main/distinct.result +++ b/mysql-test/main/distinct.result @@ -212,7 +212,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index explain SELECT distinct a from t3 order by a desc limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL a 5 NULL 40 Using index +1 SIMPLE t3 range NULL a 5 NULL 10 Using index for group-by; Using temporary; Using filesort explain SELECT distinct a,b from t3 order by a+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index cfdf9ef9865..80de24e350c 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -2429,7 +2429,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 index NULL a 10 NULL 15 Using index +2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 491e684f7a0..be0f85e9aa2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10670,6 +10670,11 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) if (sel->cond && !sel->cond->fixed) sel->cond->quick_fix_field(); + // Move away the quick select (the first thing test_quick_select() + // will do is delete it anyway) + SQL_SELECT save_sel= *sel; + tab->select->quick=NULL; + if (sel->test_quick_select(thd, tab->keys, ((used_tables & ~ current_map) | OUTER_REF_TABLE_BIT), @@ -10697,6 +10702,16 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) else sel->cond=orig_cond; + // If save_sel_copy has a better quick select than sel, put it + // back. + if (save_sel.quick && + (!sel->quick || sel->quick->read_time > save_sel.read_time)) + { + delete sel->quick; + *sel= save_sel; + save_sel.quick= NULL; + } + /* Fix for EXPLAIN */ if (sel->quick) join->best_positions[i].records_read= (double)sel->quick->records;