revision-id: e093ddb70dc93a5c0934656b201b881de185df8c (mariadb-10.3.21-26-ge093ddb70dc) parent(s): 9be5c19c3458963ccf82450b198567976275d599 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-01-20 00:06:51 +0300 message: MDEV-21383: Possible range plan is not used under certain conditions [Variant 2 of the fix: collect the attached conditions] Problem: 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 infer more range restrictions. The fix: - Do range optimization using a condition that is an AND of this table's condition and all of the previous tables' conditions. - Also, fix the range optimizer to prefer SEL_ARGs with type=KEY_RANGE over SEL_ARGS with type=MAYBE_KEY, regardless of the key part. Computing key_and( SEL_ARG(type=MAYBE_KEY key_part=1), SEL_ARG(type=KEY_RANGE, key_part=2) ) will now produce the SEL_ARG with type=KEY_RANGE. --- mysql-test/main/join.result | 56 +++++++++++++++++++++++++++++++++++ mysql-test/main/join.test | 65 ++++++++++++++++++++++++++++++++++++++++ sql/opt_range.cc | 2 ++ sql/sql_select.cc | 72 ++++++++++++++++++++++++++++++++++++++++++++- 4 files changed, 194 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index fb4f35ed555..fe6d18f7807 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -3339,3 +3339,59 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 1 SIMPLE t1 ref a a 5 test.t0.a 1 drop table t0,t1; +# +# MDEV-21383: Possible range plan is not used under certain conditions +# +drop table if exists t10, t1000, t03; +create table t10(a int); +insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1000(a int); +insert into t1000 select A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C; +create table t03(a int); +insert into t03 values (0),(1),(2); +create table t1 ( +stationid int +); +insert into t1 select a from t10; +CREATE TABLE t2 ( +stationId int, +startTime int, +filler char(100), +key1 int, +key2 int, +key(key1), +key(key2), +PRIMARY KEY (`stationId`,`startTime`) +); +insert into t2 select +A.a, +B.a, +repeat('filler=data-', 4), +B.a, +1 +from +t03 A, +t1000 B; +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +create table t3(a int, filler char(100), key(a)); +insert into t3 select A.a+1000*B.a, 'filler-data' from t1000 A, t10 B; +# This should produce a join order of t1,t2,t3 +# t2 should have type=range, key=PRIMARY key_len=8 (not type=ALL or key_len<8) +explain +SELECT * +FROM +t1,t2,t3 +WHERE +t2.startTime <= 100 and +t2.stationId = t1.stationId and +(t1.stationid = 1 or t1.stationid = 2 or t1.stationid = 3) and +key1 >0 and +t2.key2=t3.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t2 range PRIMARY,key1,key2 PRIMARY 8 NULL 219 Using index condition; Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ref a a 5 test.t2.key2 1 +drop table t1,t2,t3; +drop table t1000,t10,t03; diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test index c5d62e213d8..c72ff0e1a8c 100644 --- a/mysql-test/main/join.test +++ b/mysql-test/main/join.test @@ -1748,3 +1748,68 @@ show keys from t1; explain select * from t0,t1 where t0.a=t1.a; drop table t0,t1; + +--echo # +--echo # MDEV-21383: Possible range plan is not used under certain conditions +--echo # + +--disable_warnings +drop table if exists t10, t1000, t03; +--enable_warnings + +create table t10(a int); +insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1000(a int); +insert into t1000 select A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C; + +create table t03(a int); +insert into t03 values (0),(1),(2); + + +create table t1 ( + stationid int +); +insert into t1 select a from t10; + +CREATE TABLE t2 ( + stationId int, + startTime int, + filler char(100), + key1 int, + key2 int, + key(key1), + key(key2), + PRIMARY KEY (`stationId`,`startTime`) +); + +insert into t2 select + A.a, + B.a, + repeat('filler=data-', 4), + B.a, + 1 +from + t03 A, + t1000 B; +analyze table t2; + +create table t3(a int, filler char(100), key(a)); +insert into t3 select A.a+1000*B.a, 'filler-data' from t1000 A, t10 B; + +--echo # This should produce a join order of t1,t2,t3 +--echo # t2 should have type=range, key=PRIMARY key_len=8 (not type=ALL or key_len<8) +explain +SELECT * +FROM + t1,t2,t3 +WHERE + t2.startTime <= 100 and + t2.stationId = t1.stationId and + (t1.stationid = 1 or t1.stationid = 2 or t1.stationid = 3) and + key1 >0 and + t2.key2=t3.a; + +drop table t1,t2,t3; +drop table t1000,t10,t03; + diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 32e6a767f15..bd5f25d67c2 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -8999,6 +8999,8 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, } if (key1->type == SEL_ARG::MAYBE_KEY) { + if (key2->type == SEL_ARG::KEY_RANGE) + return key2; key1->right= key1->left= &null_element; key1->next= key1->prev= 0; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 87a45a1baed..ae26458d451 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10543,6 +10543,74 @@ make_outerjoin_info(JOIN *join) } +/* + @brief + Build a temporary join prefix condition for JOIN_TABs up to the last tab + + @param ret OUT the condition is returned here + + @return + false OK + true Out of memory + + @detail + Walk through the join prefix (from the first table to the last_tab) and + build a condition: + + join_tab_1_cond AND join_tab_2_cond AND ... AND last_tab_conds + + The condition is only intended to be used by the range optimizer, so: + - it is not normalized (can have Item_cond_and inside another + Item_cond_and) + - it does not include join->exec_const_cond and other similar conditions. +*/ + +bool build_tmp_join_prefix_cond(JOIN *join, JOIN_TAB *last_tab, Item **ret) +{ + THD *const thd= join->thd; + Item_cond_and *all_conds= NULL; + + Item *res= NULL; + + // Pick the ON-expression. Use the same logic as in get_sargable_cond(): + if (last_tab->on_expr_ref) + res= *last_tab->on_expr_ref; + else if (last_tab->table->pos_in_table_list && + last_tab->table->pos_in_table_list->embedding && + !last_tab->table->pos_in_table_list->embedding->sj_on_expr) + { + res= last_tab->table->pos_in_table_list->embedding->on_expr; + } + + for (JOIN_TAB *tab= first_depth_first_tab(join); + tab; + tab= next_depth_first_tab(join, tab)) + { + if (tab->select_cond) + { + if (!res) + res= tab->select_cond; + else + { + if (!all_conds) + { + if (!(all_conds= new (thd->mem_root)Item_cond_and(thd, res, + tab->select_cond))) + return true; + res= all_conds; + } + else + all_conds->add(tab->select_cond, thd->mem_root); + } + } + if (tab == last_tab) + break; + } + *ret= all_conds? all_conds: res; + return false; +} + + static bool make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { @@ -10890,7 +10958,9 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { /* Join with outer join condition */ COND *orig_cond=sel->cond; - sel->cond= and_conds(thd, sel->cond, *tab->on_expr_ref); + + if (build_tmp_join_prefix_cond(join, tab, &sel->cond)) + return true; /* We can't call sel->cond->fix_fields,