WL#681: Hint to temporarily set session variable for current statement

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Some of the variable settings has much more meaning being set for query rather
than for connection.   

That is you might with to increase sort_buffer_size before doing large sort
query but it is very likely other queries in a session  are simple and quite
OK with default settings.

Now one can set variable value on query basics the following way:
1) store current value got by SHOW SESSION VARIABLES LIKE "name";
2) set new variable value
3) run the query.
4) restore old settings

Which require 4 steps instead of one, which can override all benefit of tuning,
also it requires changing the application rather than the query itself.

The good solution is for queries which make sense in query contexts such as
record_buffer, record_rnd_buffer, join_buffer, sort_buffer, tmp_table_size
allow setting them inside of the query.  We will use the new hint syntax for
this. Example:

SELECT /*+ SET_VAR(sort_buffer = 16M) */  name FROM people ORDER BY name;


This worklog shall implement statement hints that will set the
value for a given system variable (session scope) in a one-shot
way. Thus the value will be reset to the previous value after the
statement is over.

Functional requirements:

F-1: Hints must be enclosed into /*+ */ comment.

F-2: Hints must be specified after SELECT|INSERT|REPLACE|UPDATE|DELETE key words.

F-3: EXPLAIN must understand hints.

F-4: Active hints must be printed in EXPLAIN warning.

F-5: Subsequent conflicting/duplicating hints are ignored with warning.

F-6: Unresolved hints cause warning.

F-7: Behavior of the SET_VAR hint should be equivalent to what is achieved
     setting the variable before the statement and resetting it afterwards. 

F-8: If the corresponding SET statement would give warnings/errors, the hint 
     will produced a similar warning. 

F-9: Security: There are no issues since same restrictions apply as for SET

F-10: Replication: Slave threads will ignore SET_VAR hints to avoids security 
     issues since slave threads run as root, avoids potential problems with 
     variables replicated with SBR.

F-11: Observability: Variable value which is set using SET_VAR hint can be 
     visible using 'performance_schema.session_variables' table. For example,

     SELECT /*+  SET_VAR(var_name=var_value) */ VARIABLE_VALUE FROM   
     where VARIABLE_NAME = 'var_name';
     Statement above returns 'var_name' variable value which is set by the hint.
Non-Functional requirements:

NF-1: Use of SET_VAR hint should not break replication.

1. SET_VAR hint is used to temporarily set session variable for
   current statement.

Syntax of the SET_VAR hint is:

 /*+ SET_VAR( = ) */


  is the name of the system variable


  is the value to be set during statement execution.
         It can be string or number value. Suffixes K, M, or G are
         also supported for for number values.

System variable specified in the SET_VAR hint is set before
statement execution and restored back after the execution.
It's possible the use of SET_VAR several hints for different variables.
Limited set of system variables is supported for the updating using
SET_VAR hint. Variables can be updated using the hint only if they have
'HINT_UPDATEABLE' flag(see set_var.h). The idea of such a limitation is
to allow update only those variables which are related to the optimizer
behavior (like optimizer_switch) or query execution(various buffer size 
variables). In addition, some variables become effective before we can get
the value for optimizer hint(for example, parser_max_mem_size).

List of settable variables:

optimizer_search_depth variables

Examples of the SET_VAR hint use:

The hint temporarily disables optimizer_switch session variable
flag 'mrr_cost_based' for the current statement.

SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;
The hint temporarily sets session variable 'max_heap_table_size'
to 1G for the current statement.

SELECT /*+ SET_VAR(max_heap_table_size = 1G) */ 1;

System variables 'max_heap_table_size' and 'optimizer_switch' are
set with the values specified in the hints for the current statement.

SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') 
           SET_VAR(max_heap_table_size = 1G) */ 1;

2. Limitations of SET_VAR hint use.

It's not possible to use several hints with the same variable name
in the statement. The first specified hint is applied and subsequent
hints with the same variable name are ignored in this case.


SELECT /*+ SET_VAR(max_heap_table_size = 1G)
           SET_VAR(max_heap_table_size = 3G) */ 1;

Second hint 'SET_VAR(max_heap_table_size = 3G)' is ignored with
warning as conflicting.

Hint is ignored with warning if there is no system variable with
the name specified in the hint.


SELECT /*+ SET_VAR(max_size = 1G) */ 1;

There is no 'max_size' variable, so hint is ignored.

Hint is ignored with warning if system variable value specified
in the hint is incorrect.


SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;

It's not possible to set 'mrr_cost_based' flag to 'yes' value.

SET_VAR hints is only allowed at statement level. It can not be used in 
subqueries and the hint ignored with warning in this case.

Note for Documentation team:
List of settable variables can be obtained from sys_var.cc file,
settable variable has HINT_UPDATABLE flag. 

Simplified algorithm of SET_VAR hint processing.

1. Initialization of Sys_var_hint object.

Method PT_hint_sys_var::contextualize(Parse_context *pc)
creates Sys_var_hint object and initializes list of
set_var variables. set_var variables are created basing
on information provided by SET_VAR hint(variable name and value).

2. Updating and restoring of system variables.
Sys_var_hint::update_var() updates system variables using
list of set_var variables. It is called in mysql_execute_command()
right before the command execution.
Sys_var_hint::restore_var() restores system variables using
list of set_var variables. It is called in mysql_execute_command()
right after the command execution.


Added new test files:

t/opt_hints_set_var.test, r/opt_hints_set_var.result

Changes in parse_tree_hints.h/.cc.

Added new class PT_hint_sys_var:

class PT_hint_sys_var : public PT_hint
  const LEX_CSTRING sys_var_name;
  Item *sys_var_value;

  typedef PT_hint super;
  explicit PT_hint_sys_var(const LEX_CSTRING sys_var_name_arg,
                           Item *sys_var_value_arg)
    : PT_hint(MAX_HINT_ENUM, true), sys_var_name(sys_var_name_arg),
    Function initializes SET_VAR hint.

    @param pc   Pointer to Parse_context object

    @return  true in case of error,
             false otherwise
  virtual bool contextualize(Parse_context *pc);

    Function prints SET_VAR hint.

    @param thd   Pointer to thread object
    @param str   Pointer to string object
  virtual void print_hint(THD *thd, String *str) const;

Changes in opt_hints.h/.cc.

Added new field to Opt_hints_global class:
   Sys_var_hint *sys_var_hint;

Added new function to Opt_hints_global class:
   virtual void print_irregular_hints(THD *thd, String *str);

Added new class Sys_var_hint:

class Sys_var_hint : public Sql_alloc
  // List of str_var variables which need to be updated.
  List  var_list;
    Add variable to hint list.

    @param sys_var_name  variable name

    @return true if variable is added,
            false otherwise
  bool add_var(set_var *var)
    return var_list.push_back(var);
    Find variable in hint list.

    @param sys_var_name  variable name

    @return true if variable is found,
            false otherwise
  bool find_var(const LEX_CSTRING *sys_var_name);
    Update system variables with hints.

    @param thd   Pointer to thread object
  void update_var(THD *thd);
    Restore system variables with original values.

    @param thd   Pointer to thread object
  void restore_var(THD *thd);
    Print applicable hints.

    @param thd   Pointer to thread object
    @param str   Pointer to string object
  void print(THD *thd, String *str);

    Print hint.

    @param thd   Pointer to thread object
    @param str   Pointer to string object
    @param var   Pointer to set_var object
  void print_hint(THD *thd, String *str, set_var *var)
    var->print(thd, str, true);
    str->append(STRING_WITH_LEN(") "));

Changes in error_handler.h.

Added new error handler Set_var_error_handler:

  Implements the error handler for SET_VAR hint.

class Set_var_error_handler : public Internal_error_handler
  virtual bool handle_condition(THD*,
                                uint sql_errno,
                                const char*,
                                const char*)
    if (sql_errno == ER_WARN_WRONG_ARGS_HINT)
      return false;
    /* Ignore error */
    return true;

Changes in set_var.h.

class sys_var changes:

Added new flag for system variable:
  HINT_UPDATEABLE= 0x4000 // Variable is updateable using SET_VAR hint

Added new function:
virtual ulong get_var_type() { return (option.var_type & GET_TYPE_MASK); }

Added new function:
bool is_hint_updateable() const { return flags & HINT_UPDATEABLE; }

Added new function:
Item *get_orig_value(THD *thd);

class set_var changes:

Added new parameter to print() function:
-  virtual void print(THD *thd, String *str)=0;	///< To self-print
+  virtual void print(THD *thd, String *str,
+                     bool short_form)=0;   ///< To self-print

Added new field:
+  Item *orig_value; ///< original variable value, used for SET_VAR hint

Changes in set_var.cc

HINT_UPDATEABLE flag is set for optmizer_switch, max_heap_table_size, 
tmp_table_size, join_buffer_size variables.

Changes in sql_parse.cc.

Added code to mysql_execute_command() function which
updates and restores system variable specified in the

Parser changes:

Files sql/lex.h, sql/sql_hints.yy, sql/sql_lex.h, sql/sql_lex_hints.h,
sql/sql_lex_hints.cc, include/sql_chars.h, mysys/sql_chars.cc are modifed
for supporting new syntax.