Bug #45273 optimizer_switch docs should state that spaces can't be present in SET statement

• Submitted: 2 Jun 2009 18:19
• Modified: 3 Jun 2009 15:03
• Reporter: Patrick Crews
• Status: Not a Bug
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.1+
• OS: Any
• Assigned to: Paul DuBois
• CPU Architecture: Any
• Tags: documentation, Optimizer, optimizer_switch, system variables, whitespace

[2 Jun 2009 18:19] Patrick Crews

Description:
The documentation for the optimizer_switch variable should state that spaces can't be present in a SET statement or it will fail.

As an example:
A statement of (note the spaces between commas and value settings)
SET GLOBAL OPTIMIZER_SWITCH = 'materialization=off,semijoin=off, index_merge=off, index_merge_union=off, index_merge_sort_union=off, index_merge_intersection=off, loosescan=off, firstmatch=off';
results in:
ERROR: 1231  Variable 'optimizer_switch' can't be set to the value of ' index_merge=off, index_merge_union=off, index_merge_sort_union=off, index_merg'

While this isn't a huge issue and can't result in a bad value for the optimizer_switch variable, it could save users some frustration.

How to repeat:
Attempt the provided SET statement against either a 5.1 or 6.0 server:

SET GLOBAL OPTIMIZER_SWITCH = 'materialization=off, semijoin=off';

Observe the returned error.

Suggested fix:
Add an explanation to the relevant docs:
http://dev.mysql.com/doc/refman/<version>/en/switchable-optimizations.html
http://dev.mysql.com/doc/refman/<version>/en/server-system-variables.html#sysvar_optimizer_switch

[2 Jun 2009 18:23] Paul DuBois

This isn't a bug.

The docs say "comma separated." Spaces are not commas, so they're not allowed, just like #, *, %, etc. are not allowed.

Similar considerations apply to other variables such as sql_mode.

[2 Jun 2009 18:39] Paul DuBois

Alternatively, reclassify as feature request and change parsing for system variables that take SET-style values to produce a warning, the same way you get a warning if you assign a SET column a value containing spaces:

mysql> insert t values('a, b, c');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 's' at row 1 | 
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> insert t values('a,b,c');
Query OK, 1 row affected (0.00 sec)