MySQL Bugs: #37120: optimizer_switch allowable values not according to specification

Bug #37120	optimizer_switch allowable values not according to specification
Submitted:	31 May 2008 16:55	Modified:	2 Sep 2008 18:32
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
WL#3952 contains the specification for the optimizer_switch system variable. Regarding allowable values, it says:

- Allowed values
  - Type: Comma-separated list of mnemonic names.
          Currently only two names are possible: 
          "no_materialization"
          "no_semijoin"

However, the value can be set to (some) numeric values. The result of setting the variable this way yields no discernible pattern.

How to repeat:
mysql> set optimizer_switch=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@optimizer_switch;
+--------------------+
| @@optimizer_switch |
+--------------------+
|                    | 
+--------------------+
1 row in set (0.00 sec)

mysql> set optimizer_switch=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@optimizer_switch;
+--------------------+
| @@optimizer_switch |
+--------------------+
| no_materialization | 
+--------------------+
1 row in set (0.00 sec)

mysql> set optimizer_switch=2;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@optimizer_switch;
+--------------------+
| @@optimizer_switch |
+--------------------+
| no_semijoin        | 
+--------------------+
1 row in set (0.00 sec)

mysql> set optimizer_switch=3;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@optimizer_switch;
+--------------------+
| @@optimizer_switch |
+--------------------+
| no_materialization | 
+--------------------+
1 row in set (0.00 sec)

mysql> set optimizer_switch=-1;
ERROR 1231 (42000): Variable 'optimizer_switch' can't be set to the value of '-1'

Suggested fix:
DIsallow values that are not listed in the specification.

Alternatively, define what numeric values are allowable and how they map to the string variables. In my opinion, this is less desirable because it means that there are two parallel sets of values to implement and document. One set (the string values) is sufficient.

Fix typo in synopsis

Here is a further problem. The spec says that the value is a comma-separated list of values. But only one value in the list is accepted:

mysql> set optimizer_switch='no_semijoin,no_materialization';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@optimizer_switch;
+--------------------+
| @@optimizer_switch |
+--------------------+
| no_materialization | 
+--------------------+
1 row in set (0.00 sec)

mysql> set optimizer_switch='no_materialization,no_semijoin';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@optimizer_switch;
+--------------------+
| @@optimizer_switch |
+--------------------+
| no_materialization | 
+--------------------+
1 row in set (0.01 sec)

In both cases shown, the value should be set to include both no_semijoin and no_materialization, shouldn't it?

Thank you for the report.

Verified as described.

mysql> set tx_isolation=1;                
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED | 
+----------------+
1 row in set (0.00 sec)

Paul, 

This property wasn't documented intentionally because
 - there is no additional functionality. Every accepted numeric value has string representation
 - there is no warranty that numeric values will have the same (or any) meaning in subsequent versions.

We're not introducing anything new here - it is possible to use numeric constants for any variable that has name from a fixed set of strings. For example:

mysql> set tx_isolation=1;                
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED | 
+----------------+
1 row in set (0.00 sec)

mysql> set tx_isolation=2;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ | 
+-----------------+
1 row in set (0.00 sec)

So the numeric equivalents have been present and not documented for a while and I could not find any complaints about that.

I think we're ok with the current situation, it's not a bug.

Okay, let's accept that assigning numeric values has an undefined effect. But it's still the case that assigning a value consisting of multiple comma-separated string values assigns only one of those values. (See comment for [31 May 19:25].) That cannot be right?

Right, now that is really a bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47765

2650 Sergey Petrunia	2008-06-12
      BUG#37120: optimizer_switch allowable values not according to specification
      - Use the correct length of "no_materialization" string in optimizer_switch_names_len.
        It was 19 instead of 18 which caused \0 to be included which caused the string representation
        of @@optimizer_switch to be truncated.

Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)

Noted in 6.0.7 changelog.

The optimizer_switch system variable takes a comma-separated list of
values, but only the first value in the list was used.

Pushed into 6.0.6-alpha  (revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (version source revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (pib:3)