diff options
| -rw-r--r-- | mysql-test/main/order_by.result | 138 | ||||
| -rw-r--r-- | mysql-test/main/order_by.test | 32 | ||||
| -rw-r--r-- | sql/filesort.cc | 9 | ||||
| -rw-r--r-- | sql/filesort.h | 13 | ||||
| -rw-r--r-- | sql/opt_subselect.cc | 17 | ||||
| -rw-r--r-- | sql/records.cc | 29 | ||||
| -rw-r--r-- | sql/records.h | 1 | ||||
| -rw-r--r-- | sql/sql_select.cc | 106 | ||||
| -rw-r--r-- | sql/sql_select.h | 4 | ||||
| -rw-r--r-- | sql/sql_sort.h | 3 | ||||
| -rw-r--r-- | sql/table.h | 1 |
11 files changed, 289 insertions, 64 deletions
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 26e42e4d6f9..7568095699c 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -3417,7 +3417,7 @@ WHERE books.library_id = 8663 AND books.scheduled_for_removal=0 ) ORDER BY wings.id; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using filesort 1 PRIMARY wings eq_ref PRIMARY PRIMARY 4 test.books.wings_id 1 100.00 2 MATERIALIZED books ref library_idx library_idx 4 const 2 100.00 Using where Warnings: @@ -4023,4 +4023,140 @@ COUNT(DISTINCT a) 34 SET @@tmp_memory_table_size= @save_tmp_memory_table_size; DROP TABLE t1; +# +# MDEV-13694: Wrong result upon GROUP BY with orderby_uses_equalities=on +# +CREATE TABLE t1 (a INT, b int, primary key(a)); +CREATE TABLE t2 (a INT, b INT); +INSERT INTO t1 (a,b) VALUES (58,1),(96,2),(273,3),(23,4),(231,5),(525,6), +(2354,7),(321421,3),(535,2),(4535,3); +INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3); +# Join order should have the SJM scan table as the first table for both +# the queries with GROUP BY and ORDER BY clause. +EXPLAIN SELECT t1.a +FROM t1 +WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) +ORDER BY t1.a DESC; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 Using filesort +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +EXPLAIN FORMAT=JSON SELECT t1.a +FROM t1 +WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) +ORDER BY t1.a DESC; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "read_sorted_file": { + "filesort": { + "sort_key": "t1.a desc", + "table": { + "table_name": "<subquery2>", + "access_type": "ALL", + "possible_keys": ["distinct_key"], + "rows": 3, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t2.b = 3 and t2.a is not null" + } + } + } + } + } + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["a"], + "ref": ["test.t2.a"], + "rows": 1, + "filtered": 100, + "using_index": true + } + } +} +SELECT t1.a +FROM t1 +WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) +ORDER BY t1.a DESC; +a +273 +96 +58 +EXPLAIN SELECT t1.a, group_concat(t1.b) +FROM t1 +WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) +GROUP BY t1.a DESC; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 Using filesort +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +EXPLAIN FORMAT=JSON SELECT t1.a, group_concat(t1.b) +FROM t1 +WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) +GROUP BY t1.a DESC; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "read_sorted_file": { + "filesort": { + "sort_key": "t1.a desc", + "table": { + "table_name": "<subquery2>", + "access_type": "ALL", + "possible_keys": ["distinct_key"], + "rows": 3, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t2.b = 3 and t2.a is not null" + } + } + } + } + } + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["a"], + "ref": ["test.t2.a"], + "rows": 1, + "filtered": 100 + } + } +} +SELECT t1.a, group_concat(t1.b) +FROM t1 +WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) +GROUP BY t1.a DESC; +a group_concat(t1.b) +273 3 +96 2 +58 1 +DROP TABLE t1, t2; # End of 10.5 tests diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index be4a3e4253e..ff73d0db157 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2500,5 +2500,37 @@ SELECT COUNT(DISTINCT a) FROM t1; SET @@tmp_memory_table_size= @save_tmp_memory_table_size; DROP TABLE t1; +--echo # +--echo # MDEV-13694: Wrong result upon GROUP BY with orderby_uses_equalities=on +--echo # + +CREATE TABLE t1 (a INT, b int, primary key(a)); +CREATE TABLE t2 (a INT, b INT); + +INSERT INTO t1 (a,b) VALUES (58,1),(96,2),(273,3),(23,4),(231,5),(525,6), + (2354,7),(321421,3),(535,2),(4535,3); +INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3); + +--echo # Join order should have the SJM scan table as the first table for both +--echo # the queries with GROUP BY and ORDER BY clause. + +let $query= SELECT t1.a + FROM t1 + WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) + ORDER BY t1.a DESC; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +let $query= SELECT t1.a, group_concat(t1.b) + FROM t1 + WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) + GROUP BY t1.a DESC; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; +DROP TABLE t1, t2; --echo # End of 10.5 tests diff --git a/sql/filesort.cc b/sql/filesort.cc index f5d57a36685..03af9c7b49a 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -251,6 +251,9 @@ SORT_INFO *filesort(THD *thd, TABLE *table, Filesort *filesort, param.init_for_filesort(sort_len, table, max_rows, filesort->sort_positions); + param.set_all_read_bits= filesort->set_all_read_bits; + param.unpack= filesort->unpack; + sort->addon_fields= param.addon_fields; sort->sort_keys= param.sort_keys; @@ -883,6 +886,8 @@ static ha_rows find_all_keys(THD *thd, Sort_param *param, SQL_SELECT *select, goto err; } + if (param->set_all_read_bits) + sort_form->column_bitmaps_set(save_read_set, save_write_set); DEBUG_SYNC(thd, "after_index_merge_phase1"); for (;;) @@ -890,7 +895,11 @@ static ha_rows find_all_keys(THD *thd, Sort_param *param, SQL_SELECT *select, if (quick_select) error= select->quick->get_next(); else /* Not quick-select */ + { error= file->ha_rnd_next(sort_form->record[0]); + if (param->unpack) + param->unpack(sort_form); + } if (unlikely(error)) break; file->position(sort_form->record[0]); diff --git a/sql/filesort.h b/sql/filesort.h index 9f71da02c96..29ae5e20cc6 100644 --- a/sql/filesort.h +++ b/sql/filesort.h @@ -62,6 +62,13 @@ public: Filesort_tracker *tracker; Sort_keys *sort_keys; + /* + TRUE means all the fields of table of whose bitmap read_set is set + need to be read while reading records in the sort buffer. + FALSE otherwise + */ + bool set_all_read_bits; + Filesort(ORDER *order_arg, ha_rows limit_arg, bool sort_positions_arg, SQL_SELECT *select_arg): order(order_arg), @@ -71,7 +78,9 @@ public: own_select(false), using_pq(false), sort_positions(sort_positions_arg), - sort_keys(NULL) + sort_keys(NULL), + set_all_read_bits(FALSE), + unpack(NULL) { DBUG_ASSERT(order); }; @@ -79,6 +88,8 @@ public: ~Filesort() { cleanup(); } /* Prepare ORDER BY list for sorting. */ Sort_keys* make_sortorder(THD *thd, JOIN *join, table_map first_table_bit); + /* Unpack temp table columns to base table columns*/ + void (*unpack)(TABLE *); private: void cleanup(); diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 62f5964cd3c..ca5f00cbdae 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -4285,11 +4285,11 @@ bool setup_sj_materialization_part2(JOIN_TAB *sjm_tab) sjm_tab->type= JT_ALL; /* Initialize full scan */ - sjm_tab->read_first_record= join_read_record_no_init; + sjm_tab->read_first_record= join_init_read_record; sjm_tab->read_record.copy_field= sjm->copy_field; sjm_tab->read_record.copy_field_end= sjm->copy_field + sjm->sjm_table_cols.elements; - sjm_tab->read_record.read_record_func= rr_sequential_and_unpack; + sjm_tab->read_record.read_record_func= read_record_func_for_rr_and_unpack; } sjm_tab->bush_children->end[-1].next_select= end_sj_materialize; @@ -7132,3 +7132,16 @@ exit: thd->lex->current_select= save_curr_select; DBUG_RETURN(FALSE); } + +/* + @brief + Check if a table is a SJM Scan table + + @retval + TRUE SJM scan table + FALSE Otherwise +*/ +bool TABLE_LIST::is_sjm_scan_table() +{ + return is_active_sjm() && sj_mat_info->is_sj_scan; +} diff --git a/sql/records.cc b/sql/records.cc index e1766322e2f..e4659537199 100644 --- a/sql/records.cc +++ b/sql/records.cc @@ -822,3 +822,32 @@ inline void SORT_INFO::unpack_addon_fields(uchar *buff) field->unpack(field->ptr, buff + addonf->offset, buff_end, 0); } } + + +/* + @brief + Read and unpack next record from a table + + @details + The function first reads the next record from the table. + If a success then it unpacks the values to the base table fields. + This is used by SJM scan table to unpack the values of the materialized + table to the base table fields + + @retval + 0 Record successfully read. + @retval + -1 There is no record to be read anymore. + >0 Error +*/ +int read_record_func_for_rr_and_unpack(READ_RECORD *info) +{ + int error; + if ((error= info->read_record_func_and_unpack_calls(info))) + return error; + + for (Copy_field *cp= info->copy_field; cp != info->copy_field_end; cp++) + (*cp->do_copy)(cp); + + return error; +} diff --git a/sql/records.h b/sql/records.h index 04dc06b3c74..852312530db 100644 --- a/sql/records.h +++ b/sql/records.h @@ -55,6 +55,7 @@ struct READ_RECORD TABLE *table; /* Head-form */ Unlock_row_func unlock_row; Read_func read_record_func; + Read_func read_record_func_and_unpack_calls; THD *thd; SQL_SELECT *select; uint ref_length, reclength, rec_cache_size, error_offset; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fc9fea42a99..cef61ece5d8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3858,6 +3858,16 @@ JOIN::add_sorting_to_table(JOIN_TAB *tab, ORDER *order) tab->select); if (!tab->filesort) return true; + + TABLE *table= tab->table; + if ((tab == join_tab + const_tables) && + table->pos_in_table_list && + table->pos_in_table_list->is_sjm_scan_table()) + { + tab->filesort->set_all_read_bits= TRUE; + tab->filesort->unpack= unpack_to_base_table_fields; + } + /* Select was moved to filesort->select to force join_init_read_record to use sorted result instead of reading table through select. @@ -14244,37 +14254,8 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond, can be used without tmp. table. */ bool can_subst_to_first_table= false; - bool first_is_in_sjm_nest= false; - if (first_is_base_table) - { - TABLE_LIST *tbl_for_first= - join->join_tab[join->const_tables].table->pos_in_table_list; - first_is_in_sjm_nest= tbl_for_first->sj_mat_info && - tbl_for_first->sj_mat_info->is_used; - } - /* - Currently we do not employ the optimization that uses multiple - equalities for ORDER BY to remove tmp table in the case when - the first table happens to be the result of materialization of - a semi-join nest ( <=> first_is_in_sjm_nest == true). - - When a semi-join nest is materialized and scanned to look for - possible matches in the remaining tables for every its row - the fields from the result of materialization are copied - into the record buffers of tables from the semi-join nest. - So these copies are used to access the remaining tables rather - than the fields from the result of materialization. - - Unfortunately now this so-called 'copy back' technique is - supported only if the rows are scanned with the rr_sequential - function, but not with other rr_* functions that are employed - when the result of materialization is required to be sorted. - - TODO: either to support 'copy back' technique for the above case, - or to get rid of this technique altogether. - */ if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_ORDERBY_EQ_PROP) && - first_is_base_table && !first_is_in_sjm_nest && + first_is_base_table && order->item[0]->real_item()->type() == Item::FIELD_ITEM && join->cond_equal) { @@ -20243,19 +20224,6 @@ do_select(JOIN *join, Procedure *procedure) } -int rr_sequential_and_unpack(READ_RECORD *info) -{ - int error; - if (unlikely((error= rr_sequential(info)))) - return error; - - for (Copy_field *cp= info->copy_field; cp != info->copy_field_end; cp++) - (*cp->do_copy)(cp); - - return error; -} - - /** @brief Instantiates temporary table @@ -21551,6 +21519,8 @@ bool test_if_use_dynamic_range_scan(JOIN_TAB *join_tab) int join_init_read_record(JOIN_TAB *tab) { + bool need_unpacking= FALSE; + JOIN *join= tab->join; /* Note: the query plan tree for the below operations is constructed in save_agg_explain_data. @@ -21558,6 +21528,12 @@ int join_init_read_record(JOIN_TAB *tab) if (tab->distinct && tab->remove_duplicates()) // Remove duplicates. return 1; + if (join->top_join_tab_count != join->const_tables) + { + TABLE_LIST *tbl= tab->table->pos_in_table_list; + need_unpacking= tbl ? tbl->is_sjm_scan_table() : FALSE; + } + tab->build_range_rowid_filter_if_needed(); if (tab->filesort && tab->sort_table()) // Sort table. @@ -21565,6 +21541,11 @@ int join_init_read_record(JOIN_TAB *tab) DBUG_EXECUTE_IF("kill_join_init_read_record", tab->join->thd->set_killed(KILL_QUERY);); + + + if (!tab->preread_init_done && tab->preread_init()) + return 1; + if (tab->select && tab->select->quick && tab->select->quick->reset()) { /* Ensures error status is propagated back to client */ @@ -21575,19 +21556,7 @@ int join_init_read_record(JOIN_TAB *tab) /* make sure we won't get ER_QUERY_INTERRUPTED from any code below */ DBUG_EXECUTE_IF("kill_join_init_read_record", tab->join->thd->reset_killed();); - if (!tab->preread_init_done && tab->preread_init()) - return 1; - - if (init_read_record(&tab->read_record, tab->join->thd, tab->table, - tab->select, tab->filesort_result, 1,1, FALSE)) - return 1; - return tab->read_record.read_record(); -} - -int -join_read_record_no_init(JOIN_TAB *tab) -{ Copy_field *save_copy, *save_copy_end; /* @@ -21597,12 +21566,19 @@ join_read_record_no_init(JOIN_TAB *tab) save_copy= tab->read_record.copy_field; save_copy_end= tab->read_record.copy_field_end; - init_read_record(&tab->read_record, tab->join->thd, tab->table, - tab->select, tab->filesort_result, 1, 1, FALSE); + if (init_read_record(&tab->read_record, tab->join->thd, tab->table, + tab->select, tab->filesort_result, 1, 1, FALSE)) + return 1; tab->read_record.copy_field= save_copy; tab->read_record.copy_field_end= save_copy_end; - tab->read_record.read_record_func= rr_sequential_and_unpack; + + if (need_unpacking) + { + tab->read_record.read_record_func_and_unpack_calls= + tab->read_record.read_record_func; + tab->read_record.read_record_func = read_record_func_for_rr_and_unpack; + } return tab->read_record.read_record(); } @@ -29426,6 +29402,20 @@ void JOIN::init_join_cache_and_keyread() } +/* + @brief + Unpack temp table fields to base table fields. +*/ + +void unpack_to_base_table_fields(TABLE *table) +{ + JOIN_TAB *tab= table->reginfo.join_tab; + for (Copy_field *cp= tab->read_record.copy_field; + cp != tab->read_record.copy_field_end; cp++) + (*cp->do_copy)(cp); +} + + /** @} (end of group Query_Optimizer) */ diff --git a/sql/sql_select.h b/sql/sql_select.h index 545db41798c..3d2f6003ba1 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -223,7 +223,7 @@ typedef enum_nested_loop_state (*Next_select_func)(JOIN *, struct st_join_table *, bool); Next_select_func setup_end_select_func(JOIN *join, JOIN_TAB *tab); int rr_sequential(READ_RECORD *info); -int rr_sequential_and_unpack(READ_RECORD *info); +int read_record_func_for_rr_and_unpack(READ_RECORD *info); Item *remove_pushed_top_conjuncts(THD *thd, Item *cond); Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, COND_EQUAL **cond_eq, @@ -2358,7 +2358,6 @@ create_virtual_tmp_table(THD *thd, Field *field) int test_if_item_cache_changed(List<Cached_item> &list); int join_init_read_record(JOIN_TAB *tab); -int join_read_record_no_init(JOIN_TAB *tab); void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key); inline Item * and_items(THD *thd, Item* cond, Item *item) { @@ -2416,6 +2415,7 @@ int print_explain_message_line(select_result_sink *result, void explain_append_mrr_info(QUICK_RANGE_SELECT *quick, String *res); int append_possible_keys(MEM_ROOT *alloc, String_list &list, TABLE *table, key_map possible_keys); +void unpack_to_base_table_fields(TABLE *table); /**************************************************************************** Temporary table support for SQL Runtime diff --git a/sql/sql_sort.h b/sql/sql_sort.h index 40f0c5ede5f..3230052dd84 100644 --- a/sql/sql_sort.h +++ b/sql/sql_sort.h @@ -533,6 +533,7 @@ public: Addon_fields *addon_fields; // Descriptors for companion fields. Sort_keys *sort_keys; bool using_pq; + bool set_all_read_bits; uchar *unique_buff; bool not_killable; @@ -553,6 +554,8 @@ public: } void init_for_filesort(uint sortlen, TABLE *table, ha_rows maxrows, bool sort_positions); + + void (*unpack)(TABLE *); /// Enables the packing of addons if possible. void try_to_pack_addons(ulong max_length_for_sort_data); diff --git a/sql/table.h b/sql/table.h index 94c08fdce4b..4db27270509 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2769,6 +2769,7 @@ struct TABLE_LIST */ const char *get_table_name() const { return view != NULL ? view_name.str : table_name.str; } bool is_active_sjm(); + bool is_sjm_scan_table(); bool is_jtbm() { return MY_TEST(jtbm_subselect != NULL); } st_select_lex_unit *get_unit(); st_select_lex *get_single_select(); |