Bug #82237 OPTIMIZER_SWITCH cannot be modified once a different collation is selected

• Submitted: 15 Jul 2016 3:15
• Modified: 15 Jul 2016 5:53
• Reporter: Roel Van de Paar
• Status: Verified
• Category: MySQL Server: Options
• Severity: S1 (Critical)
• Version: 5.7.13, 5.6.31
• OS: Any
• CPU Architecture: Any

[15 Jul 2016 3:15] Roel Van de Paar

Description:
mysql> SET @@SESSION.OPTIMIZER_SWITCH="subquery_materialization_cost_based=ON";
Query OK, 0 rows affected (0.00 sec)
mysql> SET @@SESSION.OPTIMIZER_SWITCH="subquery_materialization_cost_based=OFF";
Query OK, 0 rows affected (0.00 sec)

Yet,

mysql> SET @@SESSION.collation_connection=utf16_persian_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @@SESSION.OPTIMIZER_SWITCH="subquery_materialization_cost_based=ON";
ERROR 1231 (42000): Variable 'optimizer_switch' can't be set to the value of 'subquery_materialization_cost_based=ON'

How to repeat:
SET @@SESSION.OPTIMIZER_SWITCH="subquery_materialization_cost_based=ON";
SET @@SESSION.OPTIMIZER_SWITCH="subquery_materialization_cost_based=OFF";
SET @@SESSION.collation_connection=utf16_persian_ci;
SET @@SESSION.OPTIMIZER_SWITCH="subquery_materialization_cost_based=ON";

[15 Jul 2016 3:22] Roel Van de Paar

The manual does not seem to refer to other collations
https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html

[15 Jul 2016 5:53] MySQL Verification Team

Hello Roel,

Thank you for the report.

Thanks,
Umesh