diff --git a/mysql-test/r/skip_records_in_range.result b/mysql-test/r/skip_records_in_range.result index 39f550cbff8..5f117e005b9 100644 --- a/mysql-test/r/skip_records_in_range.result +++ b/mysql-test/r/skip_records_in_range.result @@ -530,6 +530,7 @@ DROP VIEW v1; DROP TABLE t1; # # Bug #117750: FORCE INDEX does not skip records_in_range() for 'x IS NOT NULL' +# Bug #117791: FORCE INDEX does not skip records_in_range() for 'x IS NULL' # CREATE TABLE t1 (c1 INT, KEY (c1)); INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (NULL), (NULL); @@ -542,5 +543,10 @@ id select_type table partitions type possible_keys key key_len ref rows filtered 1 SIMPLE t1 NULL range c1 c1 5 NULL 1 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` FORCE INDEX (`c1`) where (`test`.`t1`.`c1` is not null) +EXPLAIN SELECT * FROM t1 FORCE INDEX(c1) WHERE c1 IS NULL; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref c1 c1 5 const 1 100.00 Using where; Using index +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` FORCE INDEX (`c1`) where (`test`.`t1`.`c1` is null) set debug='-d,crash_records_in_range'; DROP TABLE t1; diff --git a/mysql-test/t/skip_records_in_range.test b/mysql-test/t/skip_records_in_range.test index b0804002797..db9cacf31e5 100644 --- a/mysql-test/t/skip_records_in_range.test +++ b/mysql-test/t/skip_records_in_range.test @@ -119,6 +119,7 @@ DROP TABLE t1; --echo # --echo # Bug #117750: FORCE INDEX does not skip records_in_range() for 'x IS NOT NULL' +--echo # Bug #117791: FORCE INDEX does not skip records_in_range() for 'x IS NULL' --echo # CREATE TABLE t1 (c1 INT, KEY (c1)); @@ -128,6 +129,7 @@ ANALYZE TABLE t1; set debug='+d,crash_records_in_range'; EXPLAIN SELECT * FROM t1 FORCE INDEX(c1) WHERE c1 IS NOT NULL; +EXPLAIN SELECT * FROM t1 FORCE INDEX(c1) WHERE c1 IS NULL; set debug='-d,crash_records_in_range'; diff --git a/sql/handler.cc b/sql/handler.cc index c1aa0e7637a..c830aff5306 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -6404,26 +6404,19 @@ ha_rows handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, is actually 0, so the row estimate may be too high in this case. Also note: ranges of the form "x IS NULL" may have more than 1 matching row so records_in_range() is called for these. - 2) SKIP_RECORDS_IN_RANGE will be set when skip_records_in_range or - use_index_statistics are true. - Ranges of the form "x IS NULL" will not use index statistics - because the number of rows with this value are likely to be - very different than the values in the index statistics. - - Note: With SKIP_RECORDS_IN_RANGE, use Index statistics if: - a) Index statistics is available. - b) The range is an equality range but the index is either not - unique or all of the keyparts are not used. + 2) SKIP_RECORDS_IN_RANGE is set. + There are supportive statistics and the user requested to use + statistics instead of records_in_range(); or the user is not + interested in cost calculation at all. */ int keyparts_used = 0; if ((range.range_flag & UNIQUE_RANGE) && // 1) !(range.range_flag & NULL_RANGE)) rows = 1; /* there can be at most one row */ - else if (range.range_flag & SKIP_RECORDS_IN_RANGE && // 2) - !(range.range_flag & NULL_RANGE)) { + else if (range.range_flag & SKIP_RECORDS_IN_RANGE) { // 2) if ((range.range_flag & EQ_RANGE) && - (keyparts_used = std::popcount(range.start_key.keypart_map)) && - table->key_info[keyno].has_records_per_key(keyparts_used - 1)) { + !(range.range_flag & NULL_RANGE) && + (keyparts_used = std::popcount(range.start_key.keypart_map))) { DBUG_EXECUTE_IF("crash_index_statistics_null_part", { // It is an equality range, both ends are present, inclusive and same. const uchar *p = range.start_key.key; diff --git a/sql/range_optimizer/index_range_scan_plan.cc b/sql/range_optimizer/index_range_scan_plan.cc index ee01d795107..d7ce9590522 100644 --- a/sql/range_optimizer/index_range_scan_plan.cc +++ b/sql/range_optimizer/index_range_scan_plan.cc @@ -516,13 +516,6 @@ static uint sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) { if (is_eq_range_pred) { range->range_flag = EQ_RANGE; - /* - Use statistics instead of index dives for estimates of rows in - this range if the user requested it - */ - if (param->use_index_statistics) - range->range_flag |= SKIP_RECORDS_IN_RANGE; - /* An equality range is a unique range (0 or 1 rows in the range) if the index is unique (1) and all keyparts are used (2). @@ -583,6 +576,28 @@ static uint sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) { seq->range_count++; seq->max_key_part = max(seq->max_key_part, key_tree->part); + /* + Use statistics instead of index dives for estimates of rows in + this range if: + + 1) The user has requested it. + 2) Index statistics is supportive for the range, which means + a) It is an equality range. + b) It contains no null part. + 3) Index statistics is available. + + Ranges of the form "x IS NULL" will not use index statistics + because the number of rows with this value are likely to be + very different than the values in the index statistics. + */ + int keyparts_used = 0; + if (param->use_index_statistics && // 1) + (range->range_flag & EQ_RANGE) && // 2a) + !(range->range_flag & NULL_RANGE) && // 2b) + (keyparts_used = std::popcount(range->start_key.keypart_map)) && // 3) + param->table->key_info[seq->keyno].has_records_per_key(keyparts_used - 1)) + range->range_flag |= SKIP_RECORDS_IN_RANGE; + if (seq->skip_records_in_range) range->range_flag |= SKIP_RECORDS_IN_RANGE; return 0;