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

[31 May 2008 16:55] Paul DuBois
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.
[31 May 2008 16:55] Paul DuBois
Fix typo in synopsis
[31 May 2008 17:25] Paul DuBois
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?
[31 May 2008 17:47] Sveta Smirnova
Thank you for the report.

Verified as described.
[11 Jun 2008 17:06] Sergey Petrunya
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.
[11 Jun 2008 17:31] Paul DuBois
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?
[11 Jun 2008 18:39] Sergey Petrunya
Right, now that is really a bug.
[11 Jun 2008 23:18] Bugs System
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.
[28 Aug 2008 20:16] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)
[2 Sep 2008 18:32] Paul DuBois
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.
[13 Sep 2008 19:34] Bugs System
Pushed into 6.0.6-alpha  (revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (version source revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (pib:3)