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 18:55	Modified:	2 Sep 2008 20:32
Reporter:	Paul DuBois
Status:	Closed
Category:	Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Bugs System	Target Version:
Triage:	D4 (Minor)

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)