diff options
| author | Sergei Petrunia <psergey@askmonty.org> | 2019-12-23 01:13:38 +0300 |
|---|---|---|
| committer | Sergei Petrunia <psergey@askmonty.org> | 2019-12-23 01:13:38 +0300 |
| commit | a49376056f9eeeb76f1818c0d8b9f9e006d4dfe3 (patch) | |
| tree | 253857a3089856ae5589de1019301ab43686d9ee | |
| parent | ea9a7ef6ec0e3e24927fb1fc39e00cb526eec89c (diff) | |
| download | mariadb-git-bb-10.3.10-mdev21243.tar.gz | |
MDEV-21383: Possible range plan is not used under certain conditionsbb-10.3.10-mdev21243
(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.
| -rw-r--r-- | mysql-test/main/distinct.result | 2 | ||||
| -rw-r--r-- | mysql-test/main/group_min_max.result | 2 | ||||
| -rw-r--r-- | 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; |