diff --git a/mysql-test/r/opt_hint_records_in_range.result b/mysql-test/r/opt_hint_records_in_range.result new file mode 100644 index 00000000000..9358d0b8938 --- /dev/null +++ b/mysql-test/r/opt_hint_records_in_range.result @@ -0,0 +1,41 @@ +# RECORDS_IN_RANGE hint testing +use test; +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; +CREATE TABLE t1 ( +id int NOT NULL, +version int NOT NULL, +data char(10) default 'dummy', +PRIMARY KEY (id,version) +); +INSERT INTO t1 (id,version) VALUES (1,1), (1,2),(1,3),(1,4),(1,5),(1,6),(1,7), +(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),(2,7), +(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),(3,7), +(4,1),(4,2),(4,3),(4,4),(4,5),(4,6),(4,7); +# testing duplicate hint +select /*+ RECORDS_IN_RANGE(1) RECORDS_IN_RANGE(1) */ * from t1 where id > 3 or (id = 3 and version > 4) limit 1; +id version data +3 5 dummy +Warnings: +Warning 3128 Hint RECORDS_IN_RANGE(1) is ignored as conflicting/duplicated +# testing more than ulong, 2^32 +select /*+ RECORDS_IN_RANGE(4294967296) */ * from t1 where id > 3 or (id = 3 and version > 4) limit 1; +id version data +3 5 dummy +Warnings: +Warning 1064 Unsupported RECORDS_IN_RANGE near ') */ * from t1 where id > 3 or (id = 3 and version > 4) limit 1' at line 1 +# forcing a range, should be already +explain select /*+ RECORDS_IN_RANGE(1) */ * from t1 where id > 3 or (id = 3 and version > 4) limit 1; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range PRIMARY PRIMARY 8 NULL 2 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select /*+ RECORDS_IN_RANGE(1) */ `test`.`t1`.`id` AS `id`,`test`.`t1`.`version` AS `version`,`test`.`t1`.`data` AS `data` from `test`.`t1` where ((`test`.`t1`.`id` > 3) or ((`test`.`t1`.`id` = 3) and (`test`.`t1`.`version` > 4))) limit 1 +# forcing a table scan +explain select /*+ RECORDS_IN_RANGE(100000000) */ * from t1 where id > 3 or (id = 3 and version > 4) limit 1; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 28 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select /*+ RECORDS_IN_RANGE(100000000) */ `test`.`t1`.`id` AS `id`,`test`.`t1`.`version` AS `version`,`test`.`t1`.`data` AS `data` from `test`.`t1` where ((`test`.`t1`.`id` > 3) or ((`test`.`t1`.`id` = 3) and (`test`.`t1`.`version` > 4))) limit 1 +DROP TABLE t1; diff --git a/mysql-test/t/opt_hint_records_in_range.test b/mysql-test/t/opt_hint_records_in_range.test new file mode 100644 index 00000000000..814f5f4a6a9 --- /dev/null +++ b/mysql-test/t/opt_hint_records_in_range.test @@ -0,0 +1,38 @@ +--echo # RECORDS_IN_RANGE hint testing + +use test; +drop table if exists t1; +--enable_warnings +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; +CREATE TABLE t1 ( + id int NOT NULL, + version int NOT NULL, + data char(10) default 'dummy', + PRIMARY KEY (id,version) +); + +INSERT INTO t1 (id,version) VALUES (1,1), (1,2),(1,3),(1,4),(1,5),(1,6),(1,7), +(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),(2,7), +(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),(3,7), +(4,1),(4,2),(4,3),(4,4),(4,5),(4,6),(4,7); + +-- disable_query_log +-- disable_result_log +analyze table t1; +-- enable_result_log +-- enable_query_log + +--echo # testing duplicate hint +select /*+ RECORDS_IN_RANGE(1) RECORDS_IN_RANGE(1) */ * from t1 where id > 3 or (id = 3 and version > 4) limit 1; + +--echo # testing more than ulong, 2^32 +select /*+ RECORDS_IN_RANGE(4294967296) */ * from t1 where id > 3 or (id = 3 and version > 4) limit 1; + +--echo # forcing a range, should be already +explain select /*+ RECORDS_IN_RANGE(1) */ * from t1 where id > 3 or (id = 3 and version > 4) limit 1; + +--echo # forcing a table scan +explain select /*+ RECORDS_IN_RANGE(100000000) */ * from t1 where id > 3 or (id = 3 and version > 4) limit 1; + +DROP TABLE t1; + diff --git a/share/messages_to_clients.txt b/share/messages_to_clients.txt index f74a06ebd9e..8f741e4fbc1 100644 --- a/share/messages_to_clients.txt +++ b/share/messages_to_clients.txt @@ -7535,6 +7535,12 @@ ER_BOOST_GEOMETRY_INCONSISTENT_TURNS_EXCEPTION ER_WARN_OPTIMIZER_HINT_SYNTAX_ERROR eng "Optimizer hint syntax error" +ER_WARN_BAD_RECORDS_IN_RANGE + eng "Unsupported RECORDS_IN_RANGE" + +ER_WARN_UNSUPPORTED_RECORDS_IN_RANGE + eng "RECORDS_IN_RANGE hint is supported by top-level standalone SELECT statements only" + ER_WARN_BAD_MAX_EXECUTION_TIME eng "Unsupported MAX_EXECUTION_TIME" diff --git a/sql/handler.cc b/sql/handler.cc index 217a0c19f9b..b6abc203119 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -6173,11 +6173,19 @@ ha_rows handler::multi_range_read_info_const( } else { DBUG_EXECUTE_IF("crash_records_in_range", DBUG_SUICIDE();); assert(min_endp || max_endp); - if (HA_POS_ERROR == - (rows = this->records_in_range(keyno, min_endp, max_endp))) { - /* Can't scan one range => can't do MRR scan at all */ - total_rows = HA_POS_ERROR; - break; + + /* Check if the RECORS_IN_RANGE hint is set, + if set use the value, if not call records_in_range + */ + if (thd->lex->records_in_range > 0) + rows = thd->lex->records_in_range; + else { + if (HA_POS_ERROR == + (rows = this->records_in_range(keyno, min_endp, max_endp))) { + /* Can't scan one range => can't do MRR scan at all */ + total_rows = HA_POS_ERROR; + break; + } } } total_rows += rows; diff --git a/sql/lex.h b/sql/lex.h index 74fb8362bdc..367a4f212d5 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -901,6 +901,7 @@ static const SYMBOL symbols[] = { {SYM_H("NO_ORDER_INDEX", NO_ORDER_INDEX_HINT)}, {SYM_H("DERIVED_CONDITION_PUSHDOWN", DERIVED_CONDITION_PUSHDOWN_HINT)}, {SYM_H("NO_DERIVED_CONDITION_PUSHDOWN", - NO_DERIVED_CONDITION_PUSHDOWN_HINT)}}; + NO_DERIVED_CONDITION_PUSHDOWN_HINT)}, + {SYM_H("RECORDS_IN_RANGE", RECORDS_IN_RANGE_HINT)}}; #endif /* LEX_INCLUDED */ diff --git a/sql/opt_hints.cc b/sql/opt_hints.cc index af3b4e3bd76..0095c69fdf3 100644 --- a/sql/opt_hints.cc +++ b/sql/opt_hints.cc @@ -85,6 +85,7 @@ struct st_opt_hint_info opt_hint_info[] = { {"GROUP_INDEX", false, false, false}, {"ORDER_INDEX", false, false, false}, {"DERIVED_CONDITION_PUSHDOWN", true, true, false}, + {"RECORDS_IN_RANGE", false, false, false}, {nullptr, false, false, false}}; /** @@ -191,6 +192,7 @@ void Opt_hints::check_unresolved(THD *thd) { PT_hint *Opt_hints_global::get_complex_hints(opt_hints_enum type) { if (type == MAX_EXEC_TIME_HINT_ENUM) return max_exec_time; + if (type == RECORDS_IN_RANGE_HINT_ENUM) return records_in_range; assert(0); return nullptr; diff --git a/sql/opt_hints.h b/sql/opt_hints.h index e6eba0f875f..bf2323debe4 100644 --- a/sql/opt_hints.h +++ b/sql/opt_hints.h @@ -84,6 +84,7 @@ enum opt_hints_enum { GROUP_INDEX_HINT_ENUM, ORDER_INDEX_HINT_ENUM, DERIVED_CONDITION_PUSHDOWN_HINT_ENUM, + RECORDS_IN_RANGE_HINT_ENUM, MAX_HINT_ENUM }; @@ -144,6 +145,7 @@ class Opt_hints_map { class Opt_hints_key; class PT_hint; class PT_hint_max_execution_time; +class PT_hint_records_in_range; /** Opt_hints class is used as ancestor for Opt_hints_global, @@ -351,11 +353,13 @@ class Opt_hints { class Opt_hints_global : public Opt_hints { public: PT_hint_max_execution_time *max_exec_time; + PT_hint_records_in_range *records_in_range; Sys_var_hint *sys_var_hint; Opt_hints_global(MEM_ROOT *mem_root_arg) : Opt_hints(nullptr, nullptr, mem_root_arg) { max_exec_time = nullptr; + records_in_range=nullptr; sys_var_hint = nullptr; } diff --git a/sql/parse_tree_hints.cc b/sql/parse_tree_hints.cc index b3413928bd4..ae357bb28b1 100644 --- a/sql/parse_tree_hints.cc +++ b/sql/parse_tree_hints.cc @@ -491,6 +491,33 @@ bool PT_hint_qb_name::contextualize(Parse_context *pc) { return false; } +bool PT_hint_records_in_range::contextualize(Parse_context *pc) { + if (super::contextualize(pc)) return true; + + if (pc->thd->lex->sql_command != SQLCOM_SELECT || // not a SELECT statement + pc->thd->lex->sphead || // or in a SP/trigger/event + pc->select != pc->thd->lex->query_block) // or in a subquery + { + push_warning(pc->thd, Sql_condition::SL_WARNING, + ER_WARN_UNSUPPORTED_RECORDS_IN_RANGE, + ER_THD(pc->thd, ER_WARN_UNSUPPORTED_RECORDS_IN_RANGE)); + return false; + } + + Opt_hints_global *global_hint = get_global_hints(pc); + if (global_hint->is_specified(type())) { + // Hint duplication: /*+ RECORDS_IN_RANGE ... RECORDS_IN_RANGE */ + print_warn(pc->thd, ER_WARN_CONFLICTING_HINT, nullptr, nullptr, nullptr, + this); + return false; + } + + pc->thd->lex->records_in_range = records_in_range; + global_hint->set_switch(switch_on(), type(), false); + global_hint->records_in_range = this; + return false; +} + bool PT_hint_max_execution_time::contextualize(Parse_context *pc) { if (super::contextualize(pc)) return true; diff --git a/sql/parse_tree_hints.h b/sql/parse_tree_hints.h index ed57faa5b80..cd1b6f00ed2 100644 --- a/sql/parse_tree_hints.h +++ b/sql/parse_tree_hints.h @@ -259,6 +259,32 @@ class PT_hint_qb_name : public PT_hint { } }; +/** + Parse tree hint object for RECORDS_IN_RANGE hint. +*/ + +class PT_hint_records_in_range : public PT_hint { + typedef PT_hint super; + + public: + ulong records_in_range; + + explicit PT_hint_records_in_range(ulong records_in_range_arg) + : PT_hint(RECORDS_IN_RANGE_HINT_ENUM, true), + records_in_range(records_in_range_arg) {} + /** + Function initializes RECORDS_IN_RANGE hint + + @param pc Pointer to Parse_context object + + @return true in case of error, + false otherwise + */ + bool contextualize(Parse_context *pc) override; + void append_args(const THD *, String *str) const override { + str->append_ulonglong(records_in_range); + } +}; /** Parse tree hint object for MAX_EXECUTION_TIME hint. */ diff --git a/sql/sql_hints.yy b/sql/sql_hints.yy index d3780b6ade0..1e4e6748187 100644 --- a/sql/sql_hints.yy +++ b/sql/sql_hints.yy @@ -130,6 +130,7 @@ static bool parse_int(longlong *to, const char *from, size_t from_length) %token DERIVED_CONDITION_PUSHDOWN_HINT 1047 %token NO_DERIVED_CONDITION_PUSHDOWN_HINT 1048 %token HINT_ARG_FLOATING_POINT_NUMBER 1049 +%token RECORDS_IN_RANGE_HINT 1050 /* YYUNDEF in internal to Bison. Please don't change its number, or change @@ -153,6 +154,7 @@ static bool parse_int(longlong *to, const char *from, size_t from_length) %type hint max_execution_time_hint + records_in_range_hint index_level_hint table_level_hint qb_level_hint @@ -185,6 +187,7 @@ static bool parse_int(longlong *to, const char *from, size_t from_length) HINT_ARG_TEXT HINT_IDENT_OR_NUMBER_WITH_SCALE MAX_EXECUTION_TIME_HINT + RECORDS_IN_RANGE_HINT opt_qb_name set_var_ident set_var_text_value @@ -230,11 +233,31 @@ hint: | qb_level_hint | qb_name_hint | max_execution_time_hint + | records_in_range_hint | set_var_hint | resource_group_hint ; +records_in_range_hint: + RECORDS_IN_RANGE_HINT '(' HINT_ARG_NUMBER ')' + { + longlong n; + if (parse_int(&n, $3.str, $3.length) || n > UINT_MAX32) + { + scanner->syntax_warning(ER_THD(thd, + ER_WARN_BAD_RECORDS_IN_RANGE)); + $$= NULL; + } + else + { + $$= NEW_PTN PT_hint_records_in_range(n); + if ($$ == NULL) + YYABORT; // OOM + } + } + ; + max_execution_time_hint: MAX_EXECUTION_TIME_HINT '(' HINT_ARG_NUMBER ')' { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 146a4604714..400150a7d26 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -481,6 +481,7 @@ void LEX::reset() { mark_broken(false); reset_exec_started(); max_execution_time = 0; + records_in_range = 0; reparse_common_table_expr_at = 0; opt_hints_global = nullptr; binlog_need_explicit_defaults_ts = false; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 564b373da98..5c368aa4770 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -4064,6 +4064,9 @@ struct LEX : public Query_tables_list { // Maximum execution time for a statement. ulong max_execution_time; + // Records in range value used for a statement. + ulong records_in_range; + /* To flag the current statement as dependent for binary logging on explicit_defaults_for_timestamp diff --git a/sql/sql_lex_hints.cc b/sql/sql_lex_hints.cc index a8d5caed34f..b602d41e3f1 100644 --- a/sql/sql_lex_hints.cc +++ b/sql/sql_lex_hints.cc @@ -192,6 +192,7 @@ void Hint_scanner::add_hint_token_digest() { case LOOSESCAN_HINT: case MATERIALIZATION_HINT: case MAX_EXECUTION_TIME_HINT: + case RECORDS_IN_RANGE_HINT: case MRR_HINT: case NO_BKA_HINT: case NO_BNL_HINT: