WL#5116: Changes to optimizer-controlling session variables

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

Certain optimizer features are controlled by @@optimizer_switch, whereas some others
are controlled by dedicated session variables: @@optimizer_use_mrr,

The task aims at removing the dedicated variables and adding corresponding
switches to @@optimizer_switch.

- We want to standardize on using @@optimizer_switch. It has more visibility
than other optimizer controlling variables,
for example it has its own manual section in
- @@engine_condition_pushdown controls two features which is not practical
from a user's point of view, and testability point of view.

In 6.0 and 6.0 only we introduced two Optimizer features:
- Multi-range-read MRR (WL#2474); user can enable or disable it with "set
- Index Condition Pushdown ICP
: user can enable or disable it with "set
engine_condition_pushdown=on|off"; this variable exists since 5.0 but in
5.x it controls only "engine condition pushdown"
which is a different feature from "index condition pushdown", so in 6.0 we have
one variable controlling both condition pushdowns.

For several other 6.0 features and 5.1 features, enabling or disabling is done
with "set optimizer_switch=semijoin=off,index_merge=off" etc (on|off|default are
the only 3 possible values).

In a discussion with some members of the Optimizer team it has been found that
it would be more uniform from the point of view of the user if the following
changes would be implemented.


Those changes all require a tree which has WL#4738 because that WL has
re-designed server veriables.

TO BE DONE IN 6.0-codebase

1) remove @@optimizer_use_mrr (possible as introduced in 6.0 which
isn't RC yet); MRR will be controlled by "set optimizer_switch=X=Y"
where X=Y is defined like this:
  "mrr=off" replaces "set optimizer_use_mrr=disable" (described as "do not use MRR")
  "mrr=on,mrr_cost_based=on" replaces "set optimizer_use_mrr=auto" (described as
"attempt to make a cost-based choice between using and not using MRR")
  "mrr=on,mrr_cost_based=off" replaces "set optimizer_use_mrr=force" (described
as "use MRR when possible; this is the default")
  "mrr=off,mrr_cost_based=on" is equal to "mrr=off" (precedence of "mrr").

2) "set engine_condition_pushdown" will not control Index Condition Pushdown
but only Engine Condition Pushdown (possible as ICP was introduced in 6.0);
Index Condition Pushdown will be controlled by a new "set
optimizer_switch=index_condition_pushdown=on|off"; once done
certain DBUG_EXECUTE_IF can be removed.

3) @@join_cache_level will be renamed to
@@optimizer_join_cache_level (possible as variable was introduced only in 6.0);
the interest is to have most optimizer variables starting with "optimizer", so
that you can see most of them with a single "SHOW VARIABLES LIKE 'optimizer%'".

TO BE DONE IN next-mr (Celosia?)

Move @@engine_condition_pushdown into @@optimizer_switch; for administrative
purposes (simplifying the work of Docs etc), this has been
made into a subtask, WL#5197. WL#5116 will serve to track only changes to 6.0.
For 2)
- make sure that @@optimizer_switch=engine_condition_pushdown=on|off and
@@engine_condition_pushdown=on|off do not influence Index condition pushdown
- add an index_condition_pushdown=on|off switch to @@optimizer_switch

For 1):
- add mrr* switches to @@optimizer_switch
- remove optimizer_use_mrr

For 3):
rename the variable.

And add tests for all this.