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

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;


EXECUTIVE SUMMARY
=================

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
     statement. 

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   
     performance_schema.session_variables
     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.

NONE

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

Syntax of the SET_VAR hint is:

 /*+ SET_VAR(<variable> = <value>) */

where

 <variable> is the name of the system variable

and

 <value> 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:

auto_increment_increment
auto_increment_offset
big_tables
bulk_insert_buffer_size
default_tmp_storage_engine
div_precision_increment
end_markers_in_json
eq_range_index_dive_limit
foreign_key_checks
group_concat_max_len
insert_id
internal_tmp_mem_storage_engine
join_buffer_size
lock_wait_timeout
max_error_count
max_execution_time
max_heap_table_size
max_join_size
max_length_for_sort_data
max_points_in_geometry
max_seeks_for_key
max_sort_length
optimizer_prune_level
optimizer_search_depth variables
optimizer_switch
range_alloc_block_size
range_optimizer_max_mem_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
sql_auto_is_null
sql_big_selects
sql_buffer_result
sql_mode
sql_safe_updates
sql_select_limit
timestamp
tmp_table_size
updatable_views_with_limit
unique_checks
windowing_use_high_precision


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.

Example:

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.

Example:

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.

Example:

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.

Tests.

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;
public:
  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),
      sys_var_value(sys_var_value_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);

private:
  /**
    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<set_var>  var_list;
public:
  Sys_var_hint()
  {}
  /**
    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);

private:
  /**
    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)
  {
    str->append(STRING_WITH_LEN("SET_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
{
public:
  virtual bool handle_condition(THD*,
                                uint sql_errno,
                                const char*,
                                Sql_condition::enum_severity_level*,
                                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
hints.

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.