From 0ccba62db385139caae514f70b31187bdce0de88 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 5 Sep 2018 19:47:37 +0200 Subject: MDEV-16465 Invalid (old?) table or database name or hang in ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys lowercase db and table names before prelocking. Post-fix for 9180e8666b8 This fixes failures on main.lowercase_table4 on Windows --- sql/sql_select.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0461b9725c5..b49a95e61a8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -24577,7 +24577,7 @@ void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str, const char *t_alias= alias; str->append(' '); - if (lower_case_table_names== 1) + if (lower_case_table_names == 1) { if (alias && alias[0]) { -- cgit v1.2.1 From e63b84b9167f334605dfe1f1b46555cdd2e13a8d Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Sun, 9 Sep 2018 21:07:46 +0300 Subject: MDEV-17155: Incorrect ORDER BY optimization: full index scan is used instead of range The bug was this scenario: 1. Join optimizer picks a range plan on index IDX1 (This index doesn't match the ORDER BY clause, so sorting will be needed) 2. Index Condition Pushdown pushes a part of WHERE down. The pushed condition is removed from SQL_SELECT::cond 3. test_if_skip_sort_order() figures that it's better to use IDX2 (as it will match ORDER BY ... LIMIT and so will execute faster) 3.1 It sees that there was a possible range access on IDX2. It tries to construct it by calling SQL_SELECT::test_quick_select(), but alas, SQL_SELECT::cond doesn't have all parts of WHERE anymore. So it uses full index scan which is slow. (The execution works fine because there's code further in test_if_skip_sort_order() which "Unpushes" the index condition and restores the original WHERE clause. It was just the test_quick_select call that suffered). --- sql/sql_select.cc | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0885316c5f7..fa0be81ff3d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -21244,11 +21244,30 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, tmp_map.clear_all(); // Force the creation of quick select tmp_map.set_bit(best_key); // only best_key. select->quick= 0; + + bool cond_saved= false; + Item *saved_cond; + + /* + Index Condition Pushdown may have removed parts of the condition for + this table. Temporarily put them back because we want the whole + condition for the range analysis. + */ + if (select->pre_idx_push_select_cond) + { + saved_cond= select->cond; + select->cond= select->pre_idx_push_select_cond; + cond_saved= true; + } + select->test_quick_select(join->thd, tmp_map, 0, join->select_options & OPTION_FOUND_ROWS ? HA_POS_ERROR : join->unit->select_limit_cnt, TRUE, FALSE, FALSE); + + if (cond_saved) + select->cond= saved_cond; } order_direction= best_key_direction; /* -- cgit v1.2.1 From c5a9a63293f868f074def37c985fe23a9c01c276 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 15 Sep 2018 14:28:19 -0700 Subject: MDEV-16917 Index affects query results The optimizer erroneously allowed to use join cache when joining a splittable materialized table together with splitting optimization. As a consequence in some rare cases the server returned wrong result sets for queries with materialized derived. This patch allows to use either join cache without usage of splitting technique for materialization of a splittable derived table or splitting without usage of join cache when joining such table. The costs the these alternatives are compared and the best variant is chosen. --- sql/sql_select.cc | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 7ff29bb081c..e97ea680d87 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7254,7 +7254,11 @@ best_access_path(JOIN *join, } } - tmp += s->startup_cost; + /* Splitting technique cannot be used with join cache */ + if (s->table->is_splittable()) + tmp+= s->table->get_materialization_cost(); + else + tmp+= s->startup_cost; /* We estimate the cost of evaluating WHERE clause for found records as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus @@ -7276,6 +7280,7 @@ best_access_path(JOIN *join, best_ref_depends_map= 0; best_uses_jbuf= MY_TEST(!disable_jbuf && !((s->table->map & join->outer_join))); + spl_plan= 0; } } -- cgit v1.2.1 From bd21904357d95631fbbb15defe4b023dce6a24a2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 7 Oct 2018 10:19:19 -0700 Subject: MDEV-17382 Hash join algorithm should not be used to join materialized derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table. --- sql/sql_select.cc | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b49a95e61a8..ca9a6a46fda 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10785,7 +10785,15 @@ uint check_join_cache_usage(JOIN_TAB *tab, effort now. */ if (tab->table->pos_in_table_list->is_materialized_derived()) + { no_bka_cache= true; + /* + Don't use hash join algorithm if the temporary table for the rows + of the derived table will be created with an equi-join key. + */ + if (tab->table->s->keys) + no_hashed_cache= true; + } /* Don't use join buffering if we're dictated not to by no_jbuf_after -- cgit v1.2.1 From 1eca49577e979220f3ab663a7e46e0eb70d728c4 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 7 Oct 2018 10:19:19 -0700 Subject: MDEV-17382 Hash join algorithm should not be used to join materialized derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table. --- sql/sql_select.cc | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fa0be81ff3d..62f40eeb99c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -11005,7 +11005,15 @@ uint check_join_cache_usage(JOIN_TAB *tab, effort now. */ if (tab->table->pos_in_table_list->is_materialized_derived()) + { no_bka_cache= true; + /* + Don't use hash join algorithm if the temporary table for the rows + of the derived table will be created with an equi-join key. + */ + if (tab->table->s->keys) + no_hashed_cache= true; + } /* Don't use join buffering if we're dictated not to by no_jbuf_after -- cgit v1.2.1 From 1ebe841fb85725c2a6aaecb3bca41ec098a5193d Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 7 Oct 2018 10:19:19 -0700 Subject: MDEV-17382 Hash join algorithm should not be used to join materialized derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table. --- sql/sql_select.cc | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a27053314bd..6f98bab32d3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -11138,7 +11138,15 @@ uint check_join_cache_usage(JOIN_TAB *tab, effort now. */ if (tab->table->pos_in_table_list->is_materialized_derived()) + { no_bka_cache= true; + /* + Don't use hash join algorithm if the temporary table for the rows + of the derived table will be created with an equi-join key. + */ + if (tab->table->s->keys) + no_hashed_cache= true; + } /* Don't use join buffering if we're dictated not to by no_jbuf_after -- cgit v1.2.1 From e2535dcc04b5ecc15575b878ebeb0cc589cd23fe Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 8 Oct 2018 06:19:27 -0700 Subject: MDEV-17382 Hash join algorithm should not be used to join materialized derived table / view by equality Now rows of a materialized derived table are always put into a temporary table before join operation. If BNLH is used to join this table with the result of a partial join then both operands of the join are actually put into main memory. In most cases this is not efficient. We could avoid this by sending the rows of the derived table directly to the join operation. However this kind of data flow is not supported yet. Fixed by not allowing usage of hash join algorithm to join a materialized derived table if it's joined by an equality predicate of the form f=e where f is a field of the derived table. Change for the test case in 10.3: splitting must be turned off to preserve the explain. --- sql/sql_select.cc | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cd8f3d07824..5b08522aa38 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -11618,7 +11618,15 @@ uint check_join_cache_usage(JOIN_TAB *tab, effort now. */ if (tab->table->pos_in_table_list->is_materialized_derived()) + { no_bka_cache= true; + /* + Don't use hash join algorithm if the temporary table for the rows + of the derived table will be created with an equi-join key. + */ + if (tab->table->s->keys) + no_hashed_cache= true; + } /* Don't use join buffering if we're dictated not to by no_jbuf_after -- cgit v1.2.1 From af6077b5358198384eb873ce26f88e7a7ecfe106 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Sun, 14 Oct 2018 10:44:00 -0700 Subject: MDEV-16990:server crashes in base_list_iterator::next When we have a query which has implicit_grouping then we are sure that we would end up with only one row so there is no point to do DISTINCT computation --- sql/sql_select.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6f98bab32d3..a9adbd168a6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2731,7 +2731,7 @@ bool JOIN::make_aggr_tables_info() remove_duplicates() assumes there is a preceding computation step (and in the degenerate join, there's none) */ - if (top_join_tab_count) + if (top_join_tab_count && tables_list) curr_tab->distinct= true; having= NULL; -- cgit v1.2.1 From 76318d55aa0f5cb2ef5282538d8997b308c48204 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Wed, 24 Oct 2018 09:03:47 +0200 Subject: MDEV-17525: Window functions not working in ONLY_FULL_GROUP_BY mode The ONLY_FULL_GROUP_BY mode states that for SELECT ... GROUP BY queries, disallow SELECTing columns which are not referred to in the GROUP BY clause, unless they are passed to an aggregate function like COUNT() or MAX(). This holds only for the GROUP BY clause of the query. The code also checks this for the partition clause of the window function which is incorrect. --- sql/sql_select.cc | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a9adbd168a6..1fa80da85a6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -22611,7 +22611,8 @@ setup_group(THD *thd, Ref_ptr_array ref_pointer_array, TABLE_LIST *tables, return 1; } } - if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY) + if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && + context_analysis_place == IN_GROUP_BY) { /* Don't allow one to use fields that is not used in GROUP BY -- cgit v1.2.1 From 1c6b982e02eeaa75bb6c2f2a3c2b64491dd6d3c8 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Vicen=C8=9Biu=20Ciorbaru?= Date: Thu, 1 Nov 2018 08:55:16 +0200 Subject: MDEV-12779 Oracle/DB2 Compatibility Implicit Ordering for ROW_NUMBER OVER Users expect window functions to produce a certain ordering of rows in the final result set. Although the standard does not require this, we already have the filesort result done for when we computed the window function. If there is no ORDER BY attached to the query, just keep it till the SELECT is completely evaluated and use that to print the result. Update test cases as many did not take care to guarantee a stable result. --- sql/sql_select.cc | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1fa80da85a6..0cdecf1bf2e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26649,9 +26649,10 @@ AGGR_OP::end_send() // Update ref array join_tab->join->set_items_ref_array(*join_tab->ref_array); + bool keep_last_filesort_result = join_tab->filesort ? false : true; if (join_tab->window_funcs_step) { - if (join_tab->window_funcs_step->exec(join)) + if (join_tab->window_funcs_step->exec(join, keep_last_filesort_result)) return NESTED_LOOP_ERROR; } @@ -26705,6 +26706,12 @@ AGGR_OP::end_send() } } + if (keep_last_filesort_result) + { + delete join_tab->filesort_result; + join_tab->filesort_result= NULL; + } + // Finish rnd scn after sending records if (join_tab->table->file->inited) join_tab->table->file->ha_rnd_end(); -- cgit v1.2.1 From 03680a9b4fda9fa15675e137d46521628553c0eb Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Mon, 22 Oct 2018 15:14:43 +0200 Subject: MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins --- sql/sql_select.cc | 151 ++++++++++++++++++++++++++++++++++++++++++------------ 1 file changed, 118 insertions(+), 33 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a3003285f87..91d9326854b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4300,6 +4300,40 @@ struct SARGABLE_PARAM }; +/* + Mark all tables inside a join nest as constant. + + @detail This is called when there is a local "Impossible WHERE" inside + a multi-table LEFT JOIN. +*/ + +void mark_join_nest_as_const(JOIN *join, + TABLE_LIST *join_nest, + table_map *found_const_table_map, + uint *const_count) +{ + List_iterator it(join_nest->nested_join->join_list); + TABLE_LIST *tbl; + while ((tbl= it++)) + { + if (tbl->nested_join) + { + mark_join_nest_as_const(join, tbl, found_const_table_map, const_count); + continue; + } + JOIN_TAB *tab= tbl->table->reginfo.join_tab; + + tab->type= JT_CONST; + tab->info= ET_IMPOSSIBLE_ON_CONDITION; + tab->table->const_table= 1; + + join->const_table_map|= tab->table->map; + *found_const_table_map|= tab->table->map; + set_position(join,(*const_count)++,tab,(KEYUSE*) 0); + mark_as_null_row(tab->table); // All fields are NULL + } +} + /** Calculate the best possible join and initialize the join structure. @@ -4871,39 +4905,80 @@ make_join_statistics(JOIN *join, List &tables_list, /* Perform range analysis if there are keys it could use (1). - Don't do range analysis if we're on the inner side of an outer join (2). - Do range analysis if we're on the inner side of a semi-join (3). - Don't do range analysis for materialized subqueries (4). - Don't do range analysis for materialized derived tables (5) + Don't do range analysis for materialized subqueries (2). + Don't do range analysis for materialized derived tables (3) */ if ((!s->const_keys.is_clear_all() || !bitmap_is_clear_all(&s->table->cond_set)) && // (1) - (!s->table->pos_in_table_list->embedding || // (2) - (s->table->pos_in_table_list->embedding && // (3) - s->table->pos_in_table_list->embedding->sj_on_expr)) && // (3) - !s->table->is_filled_at_execution() && // (4) - !(s->table->pos_in_table_list->derived && // (5) - s->table->pos_in_table_list->is_materialized_derived())) // (5) + !s->table->is_filled_at_execution() && // (2) + !(s->table->pos_in_table_list->derived && // (3) + s->table->pos_in_table_list->is_materialized_derived())) // (3) { bool impossible_range= FALSE; ha_rows records= HA_POS_ERROR; SQL_SELECT *select= 0; if (!s->const_keys.is_clear_all()) { + Item *sargable_cond; + int cond_source; + /* + Figure out which condition we should use for range analysis. For + INNER JOIN, we use the WHERE, for inner side of LEFT JOIN we should + use the ON expression. + */ + if (*s->on_expr_ref) + { + /* + This is an inner table from a single-table LEFT JOIN, "t1 LEFT JOIN + t2 ON cond". Use the condition cond. + */ + cond_source= 0; + sargable_cond= *s->on_expr_ref; + } + else if (s->table->pos_in_table_list->embedding && + !s->table->pos_in_table_list->embedding->sj_on_expr) + { + /* + This is the inner side of a multi-table outer join. Use the + appropriate ON expression. + */ + cond_source= 1; + sargable_cond= s->table->pos_in_table_list->embedding->on_expr; + } + else + { + /* The table is not inner wrt some LEFT JOIN. Use the WHERE clause */ + cond_source= 2; + sargable_cond= join->conds; + } + select= make_select(s->table, found_const_table_map, found_const_table_map, - *s->on_expr_ref ? *s->on_expr_ref : join->conds, + sargable_cond, (SORT_INFO*) 0, 1, &error); if (!select) goto error; records= get_quick_record_count(join->thd, select, s->table, &s->const_keys, join->row_limit); - /* Range analyzer could modify the condition. */ - if (*s->on_expr_ref) - *s->on_expr_ref= select->cond; - else - join->conds= select->cond; + + /* + Range analyzer might have modified the condition. Put it the new + condition to where we got it from. + */ + switch (cond_source) { + case 0: + *s->on_expr_ref= select->cond; + break; + case 1: + s->table->pos_in_table_list->embedding->on_expr= select->cond; + break; + case 2: + join->conds= select->cond; + break; + default: + DBUG_ASSERT(0); + } s->quick=select->quick; s->needed_reg=select->needed_reg; @@ -4924,23 +4999,33 @@ make_join_statistics(JOIN *join, List &tables_list, } if (impossible_range) { - /* - Impossible WHERE or ON expression - In case of ON, we mark that the we match one empty NULL row. - In case of WHERE, don't set found_const_table_map to get the - caller to abort with a zero row result. - */ - join->const_table_map|= s->table->map; - set_position(join,const_count++,s,(KEYUSE*) 0); - s->type= JT_CONST; - s->table->const_table= 1; - if (*s->on_expr_ref) - { - /* Generate empty row */ - s->info= ET_IMPOSSIBLE_ON_CONDITION; - found_const_table_map|= s->table->map; - mark_as_null_row(s->table); // All fields are NULL - } + /* + Impossible WHERE or ON expression + In case of ON, we mark that the we match one empty NULL row. + In case of WHERE, don't set found_const_table_map to get the + caller to abort with a zero row result. + */ + TABLE_LIST *emb= s->table->pos_in_table_list->embedding; + if (emb && !emb->sj_on_expr) + { + /* Mark all tables in a multi-table join nest as const */ + mark_join_nest_as_const(join, emb, &found_const_table_map, + &const_count); + } + else + { + join->const_table_map|= s->table->map; + set_position(join,const_count++,s,(KEYUSE*) 0); + s->type= JT_CONST; + s->table->const_table= 1; + if (*s->on_expr_ref) + { + /* Generate empty row */ + s->info= ET_IMPOSSIBLE_ON_CONDITION; + found_const_table_map|= s->table->map; + mark_as_null_row(s->table); // All fields are NULL + } + } } if (records != HA_POS_ERROR) { -- cgit v1.2.1 From 3b6d90385230be911e15b9aa93a76e26367bc4bc Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Tue, 23 Oct 2018 11:23:34 +0200 Subject: MDEV-17493: Partition pruning doesn't work for nested outer joins Reuse the fix for MDEV-17518 here, too. --- sql/sql_select.cc | 109 +++++++++++++++++++++++++----------------------------- 1 file changed, 50 insertions(+), 59 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 91d9326854b..08e9b8daf73 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -292,6 +292,9 @@ static bool find_order_in_list(THD *, Ref_ptr_array, TABLE_LIST *, ORDER *, static double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, table_map rem_tables); void set_postjoin_aggr_write_func(JOIN_TAB *tab); + +static Item **get_sargable_cond(JOIN *join, TABLE *table); + #ifndef DBUG_OFF /* @@ -1770,19 +1773,9 @@ JOIN::optimize_inner() List_iterator_fast li(select_lex->leaf_tables); while ((tbl= li++)) { - /* - If tbl->embedding!=NULL that means that this table is in the inner - part of the nested outer join, and we can't do partition pruning - (TODO: check if this limitation can be lifted) - */ - if (!tbl->embedding || - (tbl->embedding && tbl->embedding->sj_on_expr)) - { - Item *prune_cond= tbl->on_expr? tbl->on_expr : conds; - tbl->table->all_partitions_pruned_away= prune_partitions(thd, - tbl->table, - prune_cond); - } + Item **prune_cond= get_sargable_cond(this, tbl->table); + tbl->table->all_partitions_pruned_away= + prune_partitions(thd, tbl->table, *prune_cond); } } #endif @@ -4334,6 +4327,47 @@ void mark_join_nest_as_const(JOIN *join, } } + +/* + @brief Get the condition that can be used to do range analysis/partition + pruning/etc + + @detail + Figure out which condition we can use: + - For INNER JOIN, we use the WHERE, + - "t1 LEFT JOIN t2 ON ..." uses t2's ON expression + - "t1 LEFT JOIN (...) ON ..." uses the join nest's ON expression. +*/ + +static Item **get_sargable_cond(JOIN *join, TABLE *table) +{ + Item **retval; + if (table->pos_in_table_list->on_expr) + { + /* + This is an inner table from a single-table LEFT JOIN, "t1 LEFT JOIN + t2 ON cond". Use the condition cond. + */ + retval= &table->pos_in_table_list->on_expr; + } + else if (table->pos_in_table_list->embedding && + !table->pos_in_table_list->embedding->sj_on_expr) + { + /* + This is the inner side of a multi-table outer join. Use the + appropriate ON expression. + */ + retval= &(table->pos_in_table_list->embedding->on_expr); + } + else + { + /* The table is not inner wrt some LEFT JOIN. Use the WHERE clause */ + retval= &join->conds; + } + return retval; +} + + /** Calculate the best possible join and initialize the join structure. @@ -4919,42 +4953,11 @@ make_join_statistics(JOIN *join, List &tables_list, SQL_SELECT *select= 0; if (!s->const_keys.is_clear_all()) { - Item *sargable_cond; - int cond_source; - /* - Figure out which condition we should use for range analysis. For - INNER JOIN, we use the WHERE, for inner side of LEFT JOIN we should - use the ON expression. - */ - if (*s->on_expr_ref) - { - /* - This is an inner table from a single-table LEFT JOIN, "t1 LEFT JOIN - t2 ON cond". Use the condition cond. - */ - cond_source= 0; - sargable_cond= *s->on_expr_ref; - } - else if (s->table->pos_in_table_list->embedding && - !s->table->pos_in_table_list->embedding->sj_on_expr) - { - /* - This is the inner side of a multi-table outer join. Use the - appropriate ON expression. - */ - cond_source= 1; - sargable_cond= s->table->pos_in_table_list->embedding->on_expr; - } - else - { - /* The table is not inner wrt some LEFT JOIN. Use the WHERE clause */ - cond_source= 2; - sargable_cond= join->conds; - } + Item **sargable_cond= get_sargable_cond(join, s->table); select= make_select(s->table, found_const_table_map, found_const_table_map, - sargable_cond, + *sargable_cond, (SORT_INFO*) 0, 1, &error); if (!select) @@ -4966,19 +4969,7 @@ make_join_statistics(JOIN *join, List &tables_list, Range analyzer might have modified the condition. Put it the new condition to where we got it from. */ - switch (cond_source) { - case 0: - *s->on_expr_ref= select->cond; - break; - case 1: - s->table->pos_in_table_list->embedding->on_expr= select->cond; - break; - case 2: - join->conds= select->cond; - break; - default: - DBUG_ASSERT(0); - } + *sargable_cond= select->cond; s->quick=select->quick; s->needed_reg=select->needed_reg; -- cgit v1.2.1 From 14b62b15784233df105a3684d362827b2883bed4 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Tue, 23 Oct 2018 13:17:14 +0200 Subject: MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins Continuation of the fix: Make condition selectivity estimate use the right estimate, too. --- sql/sql_select.cc | 8 +++++--- 1 file changed, 5 insertions(+), 3 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 08e9b8daf73..39c77dc4dc2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4951,9 +4951,10 @@ make_join_statistics(JOIN *join, List &tables_list, bool impossible_range= FALSE; ha_rows records= HA_POS_ERROR; SQL_SELECT *select= 0; + Item **sargable_cond= NULL; if (!s->const_keys.is_clear_all()) { - Item **sargable_cond= get_sargable_cond(join, s->table); + sargable_cond= get_sargable_cond(join, s->table); select= make_select(s->table, found_const_table_map, found_const_table_map, @@ -4978,10 +4979,11 @@ make_join_statistics(JOIN *join, List &tables_list, } if (!impossible_range) { + if (!sargable_cond) + sargable_cond= get_sargable_cond(join, s->table); if (join->thd->variables.optimizer_use_condition_selectivity > 1) calculate_cond_selectivity_for_table(join->thd, s->table, - *s->on_expr_ref ? - s->on_expr_ref : &join->conds); + sargable_cond); if (s->table->reginfo.impossible_range) { impossible_range= TRUE; -- cgit v1.2.1 From e058a251c10350f3727ca1df022dc5786933535b Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Thu, 1 Nov 2018 11:35:28 +0300 Subject: MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins Part#2: take into account that join nest that we are marking as constant might already have constant tables in it. Don't count these tables twice. --- sql/sql_select.cc | 19 +++++++++++-------- 1 file changed, 11 insertions(+), 8 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 39c77dc4dc2..a99b991af96 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4316,14 +4316,17 @@ void mark_join_nest_as_const(JOIN *join, } JOIN_TAB *tab= tbl->table->reginfo.join_tab; - tab->type= JT_CONST; - tab->info= ET_IMPOSSIBLE_ON_CONDITION; - tab->table->const_table= 1; - - join->const_table_map|= tab->table->map; - *found_const_table_map|= tab->table->map; - set_position(join,(*const_count)++,tab,(KEYUSE*) 0); - mark_as_null_row(tab->table); // All fields are NULL + if (!(join->const_table_map & tab->table->map)) + { + tab->type= JT_CONST; + tab->info= ET_IMPOSSIBLE_ON_CONDITION; + tab->table->const_table= 1; + + join->const_table_map|= tab->table->map; + *found_const_table_map|= tab->table->map; + set_position(join,(*const_count)++,tab,(KEYUSE*) 0); + mark_as_null_row(tab->table); // All fields are NULL + } } } -- cgit v1.2.1 From 54b2e1c1bea357ca37ce537e24c6c120a43f5958 Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Thu, 5 Jul 2018 17:49:44 +0200 Subject: MDEV-16697: Fix difference between 32bit/windows and 64bit systems in allowed select nest level --- sql/sql_select.cc | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f1230666026..f3e69ba2ead 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -645,7 +645,7 @@ setup_without_group(THD *thd, Ref_ptr_array ref_pointer_array, const bool saved_non_agg_field_used= select->non_agg_field_used(); DBUG_ENTER("setup_without_group"); - thd->lex->allow_sum_func&= ~((nesting_map)1 << select->nest_level); + thd->lex->allow_sum_func.clear_bit(select->nest_level); res= setup_conds(thd, tables, leaves, conds); if (thd->lex->current_select->first_cond_optimization) { @@ -658,18 +658,18 @@ setup_without_group(THD *thd, Ref_ptr_array ref_pointer_array, /* it's not wrong to have non-aggregated columns in a WHERE */ select->set_non_agg_field_used(saved_non_agg_field_used); - thd->lex->allow_sum_func|= (nesting_map)1 << select->nest_level; + thd->lex->allow_sum_func.set_bit(select->nest_level); save_place= thd->lex->current_select->context_analysis_place; thd->lex->current_select->context_analysis_place= IN_ORDER_BY; res= res || setup_order(thd, ref_pointer_array, tables, fields, all_fields, order); - thd->lex->allow_sum_func&= ~((nesting_map)1 << select->nest_level); + thd->lex->allow_sum_func.clear_bit(select->nest_level); thd->lex->current_select->context_analysis_place= IN_GROUP_BY; res= res || setup_group(thd, ref_pointer_array, tables, fields, all_fields, group, hidden_group_fields); thd->lex->current_select->context_analysis_place= save_place; - thd->lex->allow_sum_func|= (nesting_map)1 << select->nest_level; + thd->lex->allow_sum_func.set_bit(select->nest_level); res= res || setup_windows(thd, ref_pointer_array, tables, fields, all_fields, win_specs, win_funcs); thd->lex->allow_sum_func= save_allow_sum_func; @@ -1117,7 +1117,7 @@ JOIN::prepare(TABLE_LIST *tables_init, select_lex->master_unit()->global_parameters()) { nesting_map save_allow_sum_func= thd->lex->allow_sum_func; - thd->lex->allow_sum_func|= (nesting_map)1 << select_lex->nest_level; + thd->lex->allow_sum_func.set_bit(select_lex->nest_level); thd->where= "order clause"; for (ORDER *order= select_lex->order_list.first; order; order= order->next) { @@ -1135,7 +1135,7 @@ JOIN::prepare(TABLE_LIST *tables_init, { nesting_map save_allow_sum_func= thd->lex->allow_sum_func; thd->where="having clause"; - thd->lex->allow_sum_func|= (nesting_map)1 << select_lex_arg->nest_level; + thd->lex->allow_sum_func.set_bit(select_lex_arg->nest_level); select_lex->having_fix_field= 1; /* Wrap alone field in HAVING clause in case it will be outer field -- cgit v1.2.1 From 3074beaad6bf259c6427d77783ea821d0b16b424 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 9 Nov 2018 09:45:37 +0400 Subject: MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure Changing the way how a cursor is opened to fetch its structure only, e.g. for a cursor FOR loop record variable. The old methods with setting thd->lex->limit_rows_examined to an Item_uint(0) was not reliable and could push these messages into diagnostics area: The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0) The new method should be more reliable, as it completely prevents the call of do_select() in JOIN::exec_inner() during the cursor structure discovery, so the execution of the cursor SELECT query returns immediately after the preparation step (when the result row structure becomes known), without even entering the code that fetches the result rows. --- sql/sql_select.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f3e69ba2ead..08bf2a897fc 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4025,7 +4025,7 @@ void JOIN::exec_inner() procedure ? procedure_fields_list : *fields, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF); - error= do_select(this, procedure); + error= result->view_structure_only() ? false : do_select(this, procedure); /* Accumulate the counts from all join iterations of all join parts. */ thd->inc_examined_row_count(join_examined_rows); DBUG_PRINT("counts", ("thd->examined_row_count: %lu", -- cgit v1.2.1 From a12b8ac8e9636f8258bcb17a0845b9317d870fd9 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Fri, 9 Nov 2018 19:53:40 +0530 Subject: MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free or Invalid write in JOIN::make_aggr_tables_info During the optimize state of a query, we come know that the result set would atmost contain one row, then for such a query we don't need to compute GROUP BY, ORDER BY and DISTINCT. --- sql/sql_select.cc | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0cdecf1bf2e..db3ed8a1aa9 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2245,6 +2245,18 @@ setup_subq_exit: if (!tables_list || !table_count) { choose_tableless_subquery_plan(); + + /* The output has atmost one row */ + if (group_list) + { + group_list= NULL; + group_optimized_away= 1; + rollup.state= ROLLUP::STATE_NONE; + } + order= NULL; + simple_order= TRUE; + select_distinct= FALSE; + if (select_lex->have_window_funcs()) { if (!(join_tab= (JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB)))) -- cgit v1.2.1 From cd29aee50d75918a94b5d019c78a459c2824d7e0 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 16 Nov 2018 18:24:58 +0100 Subject: misc cleanup --- sql/sql_select.cc | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1c7010ebb81..dc0aaad7868 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2501,7 +2501,7 @@ int JOIN::optimize_stage2() { JOIN_TAB *tab= &join_tab[const_tables]; - if (order) + if (order && !need_tmp) { /* Force using of tmp table if sorting by a SP or UDF function due to @@ -3206,7 +3206,7 @@ bool JOIN::make_aggr_tables_info() or end_write_group()) if JOIN::group is set to false. */ // the temporary table was explicitly requested - DBUG_ASSERT(MY_TEST(select_options & OPTION_BUFFER_RESULT)); + DBUG_ASSERT(select_options & OPTION_BUFFER_RESULT); // the temporary table does not have a grouping expression DBUG_ASSERT(!curr_tab->table->group); } @@ -13191,7 +13191,8 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond, tab++) tab->cached_eq_ref_table= FALSE; - *simple_order= *join->join_tab[join->const_tables].on_expr_ref ? 0 : 1; + JOIN_TAB *head= join->join_tab + join->const_tables; + *simple_order= head->on_expr_ref[0] == NULL; } else { -- cgit v1.2.1 From f0f0d0725070c74974d6fb74983347691fb6751c Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 16 Nov 2018 18:28:01 +0100 Subject: MDEV-14500 filesort to support engines with slow rnd_pos If the engine wants to avoid rnd_pos() - force a temporary table before a filesort. But don't do it if addon_fields are used. --- sql/sql_select.cc | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index dc0aaad7868..e5940ae4d3d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -13193,6 +13193,21 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond, JOIN_TAB *head= join->join_tab + join->const_tables; *simple_order= head->on_expr_ref[0] == NULL; + if (*simple_order && head->table->file->ha_table_flags() & HA_SLOW_RND_POS) + { + uint u1, u2, u3; + /* + normally the condition is (see filesort_use_addons()) + + length + sortlength <= max_length_for_sort_data + + but for HA_SLOW_RND_POS tables we relax it a bit, as the alternative + is to use a temporary table, which is rather expensive. + + TODO proper cost estimations + */ + *simple_order= filesort_use_addons(head->table, 0, &u1, &u2, &u3); + } } else { -- cgit v1.2.1 From 46960365b102b1b446c300ed4da606e63ddfab5d Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 1 Dec 2018 15:06:04 -0800 Subject: MDEV-17871 Crash when running explain with CTE When the with clause of a query contains a recursive CTE that is not used then processing of EXPLAIN for this query does not require optimization of the unit specifying this CTE. In this case if 'derived' is the TABLE_LIST object created for this CTE then derived->derived_result is NULL and any assignment to derived->derived_result->table causes a crash. After fixing this problem in the code of st_select_lex_unit::prepare() EXPLAIN for such a query worked without crashes. Yet an execution plan for the recursive CTE appeared there. The cause of this problem was an incorrect condition used in JOIN::save_explain_data_intern() that determined whether CTE was to be optimized or not. A similar condition was used in select_describe() and this patch has corrected it as well. --- sql/sql_select.cc | 11 ++++++----- 1 file changed, 6 insertions(+), 5 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index db3ed8a1aa9..1309c7bae0c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25041,13 +25041,13 @@ int JOIN::save_explain_data_intern(Explain_query *output, (1) they are not parts of ON clauses that were eliminated by table elimination. (2) they are not merged derived tables - (3) they are not unreferenced CTE + (3) they are not hanging CTEs (they are needed for execution) */ if (!(tmp_unit->item && tmp_unit->item->eliminated) && // (1) (!tmp_unit->derived || tmp_unit->derived->is_materialized_derived()) && // (2) - !(tmp_unit->with_element && - !tmp_unit->with_element->is_referenced())) // (3) + !(tmp_unit->with_element && + (!tmp_unit->derived || !tmp_unit->derived->derived_result))) // (3) { explain->add_child(tmp_unit->first_select()->select_number); } @@ -25108,11 +25108,12 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, Save plans for child subqueries, when (1) they are not parts of eliminated WHERE/ON clauses. (2) they are not VIEWs that were "merged for INSERT". - (3) they are not unreferenced CTE. + (3) they are not hanging CTEs (they are needed for execution) */ if (!(unit->item && unit->item->eliminated) && // (1) !(unit->derived && unit->derived->merged_for_insert) && // (2) - !(unit->with_element && !unit->with_element->is_referenced())) // (3) + !(unit->with_element && + (!unit->derived || !unit->derived->derived_result))) // (3) { if (mysql_explain_union(thd, unit, result)) DBUG_VOID_RETURN; -- cgit v1.2.1 From c353b2a8fc10e16107ee6c7e26877f4243d4eaef Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 12 Dec 2018 10:39:06 +0400 Subject: --echo # --echo # MDEV-17979 Assertion `0' failed in Item::val_native upon SELECT with timestamp, NULLIF, GROUP BY --echo # --- sql/sql_select.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f99e60020c3..4b15cba1597 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -23907,7 +23907,7 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param, on how the value is to be used: In some cases this may be an argument in a group function, like: IF(ISNULL(col),0,COUNT(*)) */ - if (!(pos=new (thd->mem_root) Item_copy_string(thd, pos))) + if (!(pos= pos->type_handler()->create_item_copy(thd, pos))) goto err; if (i < border) // HAVING, ORDER and GROUP BY { -- cgit v1.2.1 From 32eeed21297f0e5a2836daca058e38dbe3a82bc4 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Mon, 17 Dec 2018 00:35:44 +0530 Subject: MDEV-17676: Assertion `inited==NONE || (inited==RND && scan)' failed in handler::ha_rnd_init While calculating distinct with the function remove_dup_with_compare, we don't have rnd_end calls when we have completed the scan over the temporary table. Added ha_rnd_end calls when we are done with the scan of the table. --- sql/sql_select.cc | 2 ++ 1 file changed, 2 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1309c7bae0c..dc948ff676a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -22138,9 +22138,11 @@ static int remove_dup_with_compare(THD *thd, TABLE *table, Field **first_field, } file->extra(HA_EXTRA_NO_CACHE); + (void) file->ha_rnd_end(); DBUG_RETURN(0); err: file->extra(HA_EXTRA_NO_CACHE); + (void) file->ha_rnd_end(); if (error) file->print_error(error,MYF(0)); DBUG_RETURN(1); -- cgit v1.2.1 From d1f399408d245dd8b971ba331eaaedf488e083b6 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Sun, 16 Dec 2018 21:50:49 +0200 Subject: MDEV-6453: Assertion `inited==NONE || (inited==RND && scan)' failed in handler::ha_rnd_init(bool) with InnoDB, joins, AND/OR conditions The inited parameter handler is not initialised when we do a quick_select after a table scan. --- sql/sql_select.cc | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ca9a6a46fda..6fafbbb11df 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -18883,6 +18883,10 @@ test_if_quick_select(JOIN_TAB *tab) delete tab->select->quick; tab->select->quick=0; + + if (tab->table->file->inited != handler::NONE) + tab->table->file->ha_index_or_rnd_end(); + return tab->select->test_quick_select(tab->join->thd, tab->keys, (table_map) 0, HA_POS_ERROR, 0, FALSE); -- cgit v1.2.1 From e631ea4a7c597557f2be6b9cd1118f4f3a71dccd Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 21 Dec 2018 09:41:23 +0400 Subject: MDEV-17738 Server crashes in Item::delete_self on closing connection after unsuccessful PS Also fixes: MDEV-17741 Assertion `thd->Item_change_list::is_empty()' failed in mysql_parse after unsuccessful PS The problem was introduced by: commit f033fbd9f2366619c52186a1a902066495539141 Changed the test case for MDEV-15571 It was later fixed, but in 10.3 only: commit ce2cf855bfc0d9c8adb64f02a7b32ddd81f9948a MDEV-16043 Assertion thd->Item_change_list::is_empty() failed in mysql_parse upon SELECT from a view reading from a versioned table This patch is a backport of ce2cf855bfc0d9c8adb64f02a7b32ddd81f9948a to 10.2 --- sql/sql_select.cc | 8 +++++--- 1 file changed, 5 insertions(+), 3 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index dc948ff676a..221bdc9480c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1318,10 +1318,12 @@ JOIN::optimize_inner() /* Convert all outer joins to inner joins if possible */ conds= simplify_joins(this, join_list, conds, TRUE, FALSE); - if (thd->is_error()) - DBUG_RETURN(1); - if (select_lex->save_leaf_tables(thd)) + if (thd->is_error() || select_lex->save_leaf_tables(thd)) + { + if (arena) + thd->restore_active_arena(arena, &backup); DBUG_RETURN(1); + } build_bitmap_for_nested_joins(join_list, 0); sel->prep_where= conds ? conds->copy_andor_structure(thd) : 0; -- cgit v1.2.1 From b7a784ae259c0fd8ed1adc88b84dcdaa2441987c Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Wed, 23 Jan 2019 15:49:49 +0300 Subject: MDEV-17761: Odd optimizer choice with ORDER BY LIMIT and condition selectivity Make the "ORDER BY ... LIMIT n" optimizer take into account condition selectivity data from EITS (not just from potential range accesses). --- sql/sql_select.cc | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 978f0785887..4da7cd1a004 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26884,7 +26884,11 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, uint tablenr= (uint)(tab - join->join_tab); read_time= join->best_positions[tablenr].read_time; for (uint i= tablenr+1; i < join->table_count; i++) + { fanout*= join->best_positions[i].records_read; // fanout is always >= 1 + // But selectivity is =< 1 : + fanout*= join->best_positions[i].cond_selectivity; + } } else read_time= table->file->scan_time(); @@ -27022,6 +27026,23 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, */ select_limit= (ha_rows) (select_limit < fanout ? 1 : select_limit/fanout); + + /* + refkey_rows_estimate is E(#rows) produced by the table access + strategy that was picked without regard to ORDER BY ... LIMIT. + + It will be used as the source of selectivity data. + Use table->cond_selectivity as a better estimate which includes + condition selectivity too. + */ + { + // we use MIN(...), because "Using LooseScan" queries have + // cond_selectivity=1 while refkey_rows_estimate has a better + // estimate. + refkey_rows_estimate= MY_MIN(refkey_rows_estimate, + table_records * table->cond_selectivity); + } + /* We assume that each of the tested indexes is not correlated with ref_key. Thus, to select first N records we have to scan @@ -27032,6 +27053,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, N/(refkey_rows_estimate/table_records) > table_records <=> N > refkey_rows_estimate. */ + if (select_limit > refkey_rows_estimate) select_limit= table_records; else -- cgit v1.2.1 From 3238f2a6e93e0ed41f69badcc80af6f2618a810d Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Wed, 23 Jan 2019 16:26:09 +0300 Subject: MDEV-18073: get_range_limit_read_cost() doesnt adjust LIMIT for the range access The computation about which "fraction" of range/ref access cost we will need to perform, was incorrect. Adjusted the computation. --- sql/sql_select.cc | 43 +++++++++++++++++++++++++++++++++++++------ 1 file changed, 37 insertions(+), 6 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4da7cd1a004..d547ff31144 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26685,16 +26685,22 @@ void JOIN::cache_const_exprs() /* - Get a cost of reading rows_limit rows through index keynr. + Get the cost of using index keynr to read #LIMIT matching rows @detail - If there is a quick select, we try to use it. - if there is a ref(const) access, we try to use it, too. - quick and ref(const) use different cost formulas, so if both are possible we should make a cost-based choice. - + + rows_limit is the number of rows we would need to read when using a full + index scan. This is generally higher than the N from "LIMIT N" clause, + because there's a WHERE condition (a part of which is used to construct a + range access we are considering using here) + @param tab JOIN_TAB with table access (is NULL for single-table UPDATE/DELETE) + @param rows_limit See explanation above @param read_time OUT Cost of reading using quick or ref(const) access. @@ -26707,6 +26713,7 @@ void JOIN::cache_const_exprs() static bool get_range_limit_read_cost(const JOIN_TAB *tab, const TABLE *table, + ha_rows table_records, uint keynr, ha_rows rows_limit, double *read_time) @@ -26773,8 +26780,32 @@ static bool get_range_limit_read_cost(const JOIN_TAB *tab, } } } + + /* + Consider an example: + + SELECT * + FROM t1 + WHERE key1 BETWEEN 10 AND 20 AND col2='foo' + ORDER BY key1 LIMIT 10 + + If we were using a full index scan on key1, we would need to read this + many rows to get 10 matches: + + 10 / selectivity(key1 BETWEEN 10 AND 20 AND col2='foo') + + This is the number we get in rows_limit. + But we intend to use range access on key1. The rows returned by quick + select will satisfy the range part of the condition, + "key1 BETWEEN 10 and 20". We will still need to filter them with + the remainder condition, (col2='foo'). + + The selectivity of the range access is (best_rows/table_records). We need + to discount it from the rows_limit: + */ + double rows_limit_for_quick= rows_limit * (best_rows / table_records); - if (best_rows > rows_limit) + if (best_rows > rows_limit_for_quick) { /* LIMIT clause specifies that we will need to read fewer records than @@ -26783,7 +26814,7 @@ static bool get_range_limit_read_cost(const JOIN_TAB *tab, only need 1/3rd of records, it will cost us 1/3rd of quick select's read time) */ - best_cost *= rows_limit / best_rows; + best_cost *= rows_limit_for_quick / best_rows; } *read_time= best_cost; res= true; @@ -27076,8 +27107,8 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, index_scan_time= select_limit/rec_per_key * MY_MIN(rec_per_key, table->file->scan_time()); double range_scan_time; - if (get_range_limit_read_cost(tab, table, nr, select_limit, - &range_scan_time)) + if (get_range_limit_read_cost(tab, table, table_records, nr, + select_limit, &range_scan_time)) { if (range_scan_time < index_scan_time) index_scan_time= range_scan_time; -- cgit v1.2.1 From f3ce9edaf9414fed9dc43f33cef4b275836ba4ef Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Thu, 24 Jan 2019 17:15:46 +0300 Subject: Fix compilation on Windows --- sql/sql_select.cc | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d547ff31144..1478dd93af4 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -27071,7 +27071,8 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, // cond_selectivity=1 while refkey_rows_estimate has a better // estimate. refkey_rows_estimate= MY_MIN(refkey_rows_estimate, - table_records * table->cond_selectivity); + ha_rows(table_records * + table->cond_selectivity)); } /* -- cgit v1.2.1