revision-id: 37579f943fed52842bb04467707750c7f6e67e9b (mariadb-10.4.3-14-g37579f9) parent(s): 09d29dfc766613ffd6ac6b8a0bc1c3e6f9388eb2 author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-28 14:44:38 -0800 message: MDEV-18755 Assertion `inited==INDEX' failed in handler::ha_index_read_map When the chosen execution plan accesses a join table employing a range rowid filter a quick select to scan this range has to be built. This quick select is built by a call of SQL_SELECT::test_quick_select(). At this call the function should allow to evaluate only single index range scans. In order to be able to do this a new parameter was added to this function. --- mysql-test/main/rowid_filter.result | 1 + mysql-test/main/rowid_filter.test | 2 + mysql-test/main/rowid_filter_innodb.result | 109 +++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 40 +++++++++++ sql/opt_range.cc | 19 +++-- sql/opt_range.h | 6 +- sql/sql_select.cc | 20 +++--- 7 files changed, 180 insertions(+), 17 deletions(-) diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index 34df303..ea7de8c 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -1940,4 +1940,5 @@ ALTER TABLE orders DROP CONSTRAINT o_price; ALTER TABLE orders DROP COLUMN o_totaldiscount; DROP VIEW v1; DROP DATABASE dbt3_s001; +use test; set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index 0e8a3de..a674021 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -228,4 +228,6 @@ DROP VIEW v1; DROP DATABASE dbt3_s001; +use test; + set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 65f6896..cd09f1d 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1869,5 +1869,114 @@ ALTER TABLE orders DROP CONSTRAINT o_price; ALTER TABLE orders DROP COLUMN o_totaldiscount; DROP VIEW v1; DROP DATABASE dbt3_s001; +use test; set @@use_stat_tables=@save_use_stat_tables; +# +# MDEV-18755: possible RORI-plan and possible plan with range filter +# +create table t1 ( +pk int not null primary key, f1 varchar(10), f2 varchar(30), a int(10), +key (f1), key (f2) +) engine=innodb; +insert into t1 values +(2,'a','a',2),(3,'a','a',null),(4,'a','a',55),(5,'a','a',4),(6,'a','a',0), +(7,'a','a',1),(8,'a','a',4),(9,'a','a',null),(10,'a','a',0),(11,'a','a',0), +(12,'a','a',null),(13,'a','a',49778),(14,'a','a',6),(15,'a','a',3), +(16,'a','a',233),(17,'a','a',-1),(18,'a','a',5),(19,'a','a',-1), +(20,'a','a',null),(21,'a','a',0),(22,'a','a',null),(23,'a','a',53840), +(24,'a','a',null),(25,'a','a',null),(26,'a','a',5),(27,'a','a',43454), +(28,'a','a',0),(29,'a','a',0),(30,'a','a',null),(59,'a','a',null), +(60,'a','a',null),(61,'a','a',-1),(62,'a','a',null),(63,'a','a',0), +(64,'a','a',14468),(65,'a','a',0),(66,'a','a',28),(67,'a','a',null), +(68,'a','a',14983),(69,'a','a',null),(70,'a','a',3),(71,'a','a',null), +(72,'a','a',null),(73,'a','a',237),(74,'a','a',2),(75,'a','a',0), +(76,'a','a',6),(77,'a','a',5),(78,'a','a',0),(79,'a','a',1),(80,'a','a',-1), +(81,'a','a',20),(82,'a','a',0),(83,'a','a',0),(84,'a','a',null), +(85,'a','a',-1),(86,'a','a',5),(87,'a','a',null),(88,'a','a',160), +(89,null,null,null),(90,'a','a',14785),(91,'a','a',0),(92,'a','a',null); +( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) +union +( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); +pk f1 f2 a +explain ( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) +union +( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref|filter f1,f2 f2|f1 33|13 const 1 (2%) Using index condition; Using where; Using rowid filter +2 UNION t1 ref|filter f1,f2 f2|f1 33|13 const 1 (2%) Using index condition; Using where; Using rowid filter +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +explain format=json ( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) +union +( select * from t1 +where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["f1", "f2"], + "key": "f2", + "key_length": "33", + "used_key_parts": ["f2"], + "ref": ["const"], + "rowid_filter": { + "range": { + "key": "f1", + "used_key_parts": ["f1"] + }, + "rows": 1, + "selectivity_pct": 1.5873 + }, + "rows": 1, + "filtered": 100, + "index_condition": "t1.f2 is null", + "attached_condition": "t1.f1 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["f1", "f2"], + "key": "f2", + "key_length": "33", + "used_key_parts": ["f2"], + "ref": ["const"], + "rowid_filter": { + "range": { + "key": "f1", + "used_key_parts": ["f1"] + }, + "rows": 1, + "selectivity_pct": 1.5873 + }, + "rows": 1, + "filtered": 100, + "index_condition": "t1.f2 is null", + "attached_condition": "t1.f1 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" + } + } + } + ] + } + } +} +drop table t1; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 5408f04..803f284 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -4,4 +4,44 @@ SET SESSION STORAGE_ENGINE='InnoDB'; --source rowid_filter.test +--echo # +--echo # MDEV-18755: possible RORI-plan and possible plan with range filter +--echo # + +create table t1 ( + pk int not null primary key, f1 varchar(10), f2 varchar(30), a int(10), + key (f1), key (f2) +) engine=innodb; + +insert into t1 values + (2,'a','a',2),(3,'a','a',null),(4,'a','a',55),(5,'a','a',4),(6,'a','a',0), + (7,'a','a',1),(8,'a','a',4),(9,'a','a',null),(10,'a','a',0),(11,'a','a',0), + (12,'a','a',null),(13,'a','a',49778),(14,'a','a',6),(15,'a','a',3), + (16,'a','a',233),(17,'a','a',-1),(18,'a','a',5),(19,'a','a',-1), + (20,'a','a',null),(21,'a','a',0),(22,'a','a',null),(23,'a','a',53840), + (24,'a','a',null),(25,'a','a',null),(26,'a','a',5),(27,'a','a',43454), + (28,'a','a',0),(29,'a','a',0),(30,'a','a',null),(59,'a','a',null), + (60,'a','a',null),(61,'a','a',-1),(62,'a','a',null),(63,'a','a',0), + (64,'a','a',14468),(65,'a','a',0),(66,'a','a',28),(67,'a','a',null), + (68,'a','a',14983),(69,'a','a',null),(70,'a','a',3),(71,'a','a',null), + (72,'a','a',null),(73,'a','a',237),(74,'a','a',2),(75,'a','a',0), + (76,'a','a',6),(77,'a','a',5),(78,'a','a',0),(79,'a','a',1),(80,'a','a',-1), + (81,'a','a',20),(82,'a','a',0),(83,'a','a',0),(84,'a','a',null), + (85,'a','a',-1),(86,'a','a',5),(87,'a','a',null),(88,'a','a',160), + (89,null,null,null),(90,'a','a',14785),(91,'a','a',0),(92,'a','a',null); + +let $q= +( select * from t1 + where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) + union +( select * from t1 + where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); + +eval $q; +eval explain $q; +eval explain format=json $q; + +drop table t1; + + SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index fcf0d22..1e60bb9 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2586,6 +2586,9 @@ static int fill_used_fields_bitmap(PARAM *param) limit Query limit force_quick_range Prefer to use range (instead of full table scan) even if it is more expensive. + remove_false_parts_of_where Remove parts of OR-clauses for which range + analysis produced SEL_TREE(IMPOSSIBLE) + only_single_index_range_scan Evaluate only single index range scans NOTES Updates the following in the select parameter: @@ -2644,7 +2647,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, table_map prev_tables, ha_rows limit, bool force_quick_range, bool ordered_output, - bool remove_false_parts_of_where) + bool remove_false_parts_of_where, + bool only_single_index_range_scan) { uint idx; double scan_time; @@ -2824,7 +2828,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, } TABLE_READ_PLAN *best_trp= NULL; - TRP_GROUP_MIN_MAX *group_trp; + TRP_GROUP_MIN_MAX *group_trp= NULL; double best_read_time= read_time; if (cond) @@ -2859,7 +2863,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, Try to construct a QUICK_GROUP_MIN_MAX_SELECT. Notice that it can be constructed no matter if there is a range tree. */ - group_trp= get_best_group_min_max(¶m, tree, best_read_time); + if (!only_single_index_range_scan) + group_trp= get_best_group_min_max(¶m, tree, best_read_time); if (group_trp) { param.table->quick_condition_rows= MY_MIN(group_trp->records, @@ -2907,7 +2912,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, table deletes. */ if ((thd->lex->sql_command != SQLCOM_DELETE) && - optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE)) + optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE) && + !only_single_index_range_scan) { /* Get best non-covering ROR-intersection plan and prepare data for @@ -2935,7 +2941,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, */ if (param.table->covering_keys.is_clear_all() && optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE) && - optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE_SORT_INTERSECT)) + optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE_SORT_INTERSECT) && + !only_single_index_range_scan) { if ((intersect_trp= get_best_index_intersect(¶m, tree, best_read_time))) @@ -2948,7 +2955,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, } if (optimizer_flag(thd, OPTIMIZER_SWITCH_INDEX_MERGE) && - head->stat_records() != 0) + head->stat_records() != 0 && !only_single_index_range_scan) { /* Try creating index_merge/ROR-union scan. */ SEL_IMERGE *imerge; diff --git a/sql/opt_range.h b/sql/opt_range.h index 7e92e1f..2dab90b 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -1645,7 +1645,8 @@ class SQL_SELECT :public Sql_alloc { { key_map tmp; tmp.set_all(); - return test_quick_select(thd, tmp, 0, limit, force_quick_range, FALSE, FALSE) < 0; + return test_quick_select(thd, tmp, 0, limit, force_quick_range, + FALSE, FALSE, FALSE) < 0; } /* RETURN @@ -1662,7 +1663,8 @@ class SQL_SELECT :public Sql_alloc { } int test_quick_select(THD *thd, key_map keys, table_map prev_tables, ha_rows limit, bool force_quick_range, - bool ordered_output, bool remove_false_parts_of_where); + bool ordered_output, bool remove_false_parts_of_where, + bool only_single_index_range_scan); }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4f98153..211898f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1606,7 +1606,7 @@ bool JOIN::make_range_rowid_filters() tab->table->force_index= true; (void) sel->test_quick_select(thd, filter_map, (table_map) 0, (ha_rows) HA_POS_ERROR, - true, false, true); + true, false, true, true); tab->table->force_index= force_index_save; if (thd->is_error()) goto no_filter; @@ -4623,7 +4623,8 @@ static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select, select->test_quick_select(thd, *(key_map *)keys, (table_map) 0, limit, 0, FALSE, - TRUE /* remove_where_parts*/)) == + TRUE, /* remove_where_parts*/ + FALSE)) == 1)) DBUG_RETURN(select->quick->records); if (unlikely(error == -1)) @@ -11356,7 +11357,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) OPTION_FOUND_ROWS ? HA_POS_ERROR : join->unit->select_limit_cnt), 0, - FALSE, FALSE) < 0) + FALSE, FALSE, FALSE) < 0) { /* Before reporting "Impossible WHERE" for the whole query @@ -11370,7 +11371,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) OPTION_FOUND_ROWS ? HA_POS_ERROR : join->unit->select_limit_cnt),0, - FALSE, FALSE) < 0) + FALSE, FALSE, FALSE) < 0) DBUG_RETURN(1); // Impossible WHERE } else @@ -20899,7 +20900,8 @@ test_if_quick_select(JOIN_TAB *tab) int res= tab->select->test_quick_select(tab->join->thd, tab->keys, (table_map) 0, HA_POS_ERROR, 0, - FALSE, /*remove where parts*/FALSE); + FALSE, /*remove where parts*/FALSE, + FALSE); if (tab->explain_plan && tab->explain_plan->range_checked_fer) tab->explain_plan->range_checked_fer->collect_data(tab->select->quick); @@ -22763,9 +22765,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, res= select->test_quick_select(tab->join->thd, new_ref_key_map, 0, (tab->join->select_options & OPTION_FOUND_ROWS) ? - HA_POS_ERROR : - tab->join->unit->select_limit_cnt,TRUE, - TRUE, FALSE) <= 0; + HA_POS_ERROR : + tab->join->unit->select_limit_cnt,TRUE, + TRUE, FALSE, FALSE) <= 0; if (res) { select->cond= save_cond; @@ -22867,7 +22869,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, join->select_options & OPTION_FOUND_ROWS ? HA_POS_ERROR : join->unit->select_limit_cnt, - TRUE, FALSE, FALSE); + TRUE, FALSE, FALSE, FALSE); if (cond_saved) select->cond= saved_cond;