diff --git a/mysql-test/suite/opt_trace/r/eq_range_statistics.result b/mysql-test/suite/opt_trace/r/eq_range_statistics.result index 81fbeb0dbdb..f06b767ac9a 100644 --- a/mysql-test/suite/opt_trace/r/eq_range_statistics.result +++ b/mysql-test/suite/opt_trace/r/eq_range_statistics.result @@ -727,6 +727,244 @@ EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b=4) { } 0 0 SET SESSION DEBUG="-d,crash_records_in_range"; +# Bug #117776: Index statistics is used unexpectedly to estimate eq ranges with null part +# +# 3 equality ranges: should use index statistics, except for +# the one containing null part 'b IS NULL'. +# Note that '"index_dives_for_eq_ranges": false' in the opt trace is +# restricted to equality ranges without any null part. +SET SESSION DEBUG="+d,crash_index_statistics_null_part"; +EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b=4 OR b IS NULL); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 10 NULL 4 100.00 Using where; Using index +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`b` = 3) or (`test`.`t1`.`b` = 4) or (`test`.`t1`.`b` is null))) +SELECT * FROM information_schema.OPTIMIZER_TRACE; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b=4 OR b IS NULL) { + "steps": [ + { + "join_preparation": { + "select#": 1, + "steps": [ + { + "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where ((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4) or (`t1`.`b` is null)))" + } + ] /* steps */ + } /* join_preparation */ + }, + { + "join_optimization": { + "select#": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4) or (`t1`.`b` is null)))", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or multiple equal(4, `t1`.`b`) or (`t1`.`b` is null)) and multiple equal(5, `t1`.`a`))" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or multiple equal(4, `t1`.`b`) or (`t1`.`b` is null)) and multiple equal(5, `t1`.`a`))" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "((multiple equal(2, `t1`.`b`) or multiple equal(3, `t1`.`b`) or multiple equal(4, `t1`.`b`) or (`t1`.`b` is null)) and multiple equal(5, `t1`.`a`))" + } + ] /* steps */ + } /* condition_processing */ + }, + { + "substitute_generated_columns": { + } /* substitute_generated_columns */ + }, + { + "table_dependencies": [ + { + "table": "`t1`", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [ + ] /* depends_on_map_bits */ + } + ] /* table_dependencies */ + }, + { + "ref_optimizer_key_uses": [ + { + "table": "`t1`", + "field": "a", + "equals": "5", + "null_rejecting": true + } + ] /* ref_optimizer_key_uses */ + }, + { + "rows_estimation": [ + { + "table": "`t1`", + "range_analysis": { + "table_scan": { + "rows": 9, + "cost": 3.25 + } /* table_scan */, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": [ + "a", + "b" + ] /* key_parts */ + } + ] /* potential_range_indexes */, + "best_covering_index_scan": { + "index": "a", + "cost": 1.1539, + "chosen": true + } /* best_covering_index_scan */, + "setup_range_conditions": [ + ] /* setup_range_conditions */, + "group_index_skip_scan": { + "chosen": false, + "cause": "not_group_by_or_distinct" + } /* group_index_skip_scan */, + "skip_scan_range": { + "potential_skip_scan_indexes": [ + { + "index": "a", + "usable": false, + "cause": "no_range_predicate" + } + ] /* potential_skip_scan_indexes */ + } /* skip_scan_range */, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": [ + "a = 5 AND b = NULL", + "a = 5 AND b = 2", + "a = 5 AND b = 3", + "a = 5 AND b = 4" + ] /* ranges */, + "index_dives_for_eq_ranges": false, + "rowid_ordered": false, + "using_mrr": false, + "index_only": true, + "in_memory": 1, + "rows": 4, + "cost": 0.661462, + "chosen": true + } + ] /* range_scan_alternatives */, + "analyzing_roworder_intersect": { + "usable": false, + "cause": "too_few_roworder_scans" + } /* analyzing_roworder_intersect */ + } /* analyzing_range_alternatives */, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a", + "rows": 4, + "ranges": [ + "a = 5 AND b = NULL", + "a = 5 AND b = 2", + "a = 5 AND b = 3", + "a = 5 AND b = 4" + ] /* ranges */ + } /* range_access_plan */, + "rows_for_plan": 4, + "cost_for_plan": 0.661462, + "chosen": true + } /* chosen_range_access_summary */ + } /* range_analysis */ + } + ] /* rows_estimation */ + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [ + ] /* plan_prefix */, + "table": "`t1`", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "chosen": false, + "cause": "range_uses_more_keyparts" + }, + { + "rows_to_scan": 4, + "filtering_effect": [ + ] /* filtering_effect */, + "final_filtering_effect": 1, + "access_type": "range", + "range_details": { + "used_index": "a" + } /* range_details */, + "resulting_rows": 4, + "cost": 1.06146, + "chosen": true + } + ] /* considered_access_paths */ + } /* best_access_path */, + "condition_filtering_pct": 100, + "rows_for_plan": 4, + "cost_for_plan": 1.06146, + "chosen": true + } + ] /* considered_execution_plans */ + }, + { + "attaching_conditions_to_tables": { + "original_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4) or (`t1`.`b` is null)))", + "attached_conditions_computation": [ + ] /* attached_conditions_computation */, + "attached_conditions_summary": [ + { + "table": "`t1`", + "attached": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4) or (`t1`.`b` is null)))" + } + ] /* attached_conditions_summary */ + } /* attaching_conditions_to_tables */ + }, + { + "finalizing_table_conditions": [ + { + "table": "`t1`", + "original_table_condition": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4) or (`t1`.`b` is null)))", + "final_table_condition ": "((`t1`.`a` = 5) and ((`t1`.`b` = 2) or (`t1`.`b` = 3) or (`t1`.`b` = 4) or (`t1`.`b` is null)))" + } + ] /* finalizing_table_conditions */ + }, + { + "refine_plan": [ + { + "table": "`t1`" + } + ] /* refine_plan */ + } + ] /* steps */ + } /* join_optimization */ + }, + { + "join_explain": { + "select#": 1, + "steps": [ + ] /* steps */ + } /* join_explain */ + } + ] /* steps */ +} 0 0 +SET SESSION DEBUG="-d,crash_index_statistics_null_part"; + # 2 equality ranges: should not use index statistics EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b>4); id select_type table partitions type possible_keys key key_len ref rows filtered Extra diff --git a/mysql-test/suite/opt_trace/t/eq_range_statistics.test b/mysql-test/suite/opt_trace/t/eq_range_statistics.test index 9b6a438b207..6584210062c 100644 --- a/mysql-test/suite/opt_trace/t/eq_range_statistics.test +++ b/mysql-test/suite/opt_trace/t/eq_range_statistics.test @@ -78,6 +78,18 @@ EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b=4); SELECT * FROM information_schema.OPTIMIZER_TRACE; SET SESSION DEBUG="-d,crash_records_in_range"; +--echo +--echo # Bug #117776: Index statistics is used unexpectedly to estimate eq ranges with null part +--echo # +--echo # 3 equality ranges: should use index statistics, except for +--echo # the one containing null part 'b IS NULL'. +--echo # Note that '"index_dives_for_eq_ranges": false' in the opt trace is +--echo # restricted to equality ranges without any null part. +SET SESSION DEBUG="+d,crash_index_statistics_null_part"; +EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b=4 OR b IS NULL); +SELECT * FROM information_schema.OPTIMIZER_TRACE; +SET SESSION DEBUG="-d,crash_index_statistics_null_part"; + --echo --echo # 2 equality ranges: should not use index statistics EXPLAIN SELECT * FROM t1 WHERE a=5 AND (b=2 OR b=3 OR b>4); diff --git a/sql/handler.cc b/sql/handler.cc index d7d947e481f..c1aa0e7637a 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -6424,6 +6424,17 @@ ha_rows handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, 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)) { + 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; + KEY *k = &table->key_info[keyno]; + for (uint kp = 0; kp < actual_key_parts(k); kp++) { + if (k->key_part[kp].field->is_nullable() && *p) { + DBUG_SUICIDE(); + } + p += k->key_part[kp].store_length; + } + }); rows = static_cast( table->key_info[keyno].records_per_key(keyparts_used - 1)); } else { diff --git a/sql/range_optimizer/index_range_scan_plan.cc b/sql/range_optimizer/index_range_scan_plan.cc index c35cc113113..3512aedca90 100644 --- a/sql/range_optimizer/index_range_scan_plan.cc +++ b/sql/range_optimizer/index_range_scan_plan.cc @@ -532,7 +532,30 @@ static uint sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) { if (cur_key_info->flags & HA_NOSAME && // 1) (uint)key_tree->part + 1 == cur_key_info->user_defined_key_parts) // 2) - range->range_flag |= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); + range->range_flag |= UNIQUE_RANGE; + + /* + Reinterprete NULL_RANGE as a supplimentary flag for range estimation + indicating that the range has at least one null part "kp_i IS NULL", + so that ranges could be precisely classified. + + 1) An equality range without null part: + (flag & (EQ_RANGE | NULL_RANGE)) == EQ_RANGE + 2) An equality range with at least one null part: + (flag & (EQ_RANGE | NULL_RANGE)) == (EQ_RANGE | NULL_RANGE) + 3) A non-equality range without null part: + (flag & (EQ_RANGE | NULL_RANGE)) == 0 + 4) A non-equality range with at least one null part: + (flag & (EQ_RANGE | NULL_RANGE)) == NULL_RANGE + + Note that: An equality range is one that both ends, or one end for + geometry, are present, inclusive, and have the same value. The value + could be either null or non-null. + + Note also that NULL_RANGE in class 3) and 4) is inherited as the + range_flag is set for a non-quality range. + */ + range->range_flag |= (cur->min_key_flag & NULL_RANGE); } if (*seq->is_ror_scan) {