Support Variable-Setting Hint SET_VAR

[zz-jason]

Feature Request

Is your feature request related to a problem? Please describe:

TiDB supports lots of variables for applications. For example, tidb_opt_distinct_agg_push_down, the typical usage is:

set @original_value = @@tidb_opt_distinct_agg_push_down;
set @@tidb_opt_distinct_agg_push_down = 1;  -- enables aggregate pushdown through join
select ...;
set @@tidb_opt_distinct_agg_push_down = @original_value;

With this hint, there is no need to save and restore the variable value, the above example can be rewritten to:

select /*+ set_var(tidb_opt_distinct_agg_push_down=1) */ ...

#11703 also described another use case on tidb_snapshot.

Describe the feature you'd like:

Support the set_var hint like MySQL Variable-Setting Hint Syntax

Describe alternatives you've considered:

N/A

Teachability, Documentation, Adoption, Migration Strategy:

N/A

[b41sh]

SetExecutor is used to execute SET statements, can we use it to execute SET_VAR hint?
As SET_VAR hint takes effect temporarily, we can build two plannercore.Set in PlanBuilder.
The first is used to set the value of the session, the second change it back.

[qw4990]

SetExecutor is used to execute SET statements, can we use it to execute SET_VAR hint?
As SET_VAR hint takes effect temporarily, we can build two plannercore.Set in PlanBuilder.
The first is used to set the value of the session, the second change it back.

I think we cannot use SetExecutor directly here, since it is used to set variables in global or session level.
In this feature, the variable is in SQL level since it should be removed after finishing this SQL.
So I think it's better to introduce a new approach to set these SQL-level variables instead of reusing SetExecutor.

What're your options? @zz-jason @SunRunAway