Bug #34840 optimizer_prune_level accepts numeric values and gives error on ON/OFF values
Submitted: 26 Feb 2008 13:13 Modified: 29 Feb 2008 8:59
Reporter: Rizwan Maredia Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.1.22 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: ON OFF values, optimizer_prune_level
Triage: Triaged: D5 (Feature request)

[26 Feb 2008 13:13] Rizwan Maredia
Description:
optimizer_prune_level does not give error on numeric values although value assigned to variable is 0 or 1. Also it gives errors on ON/OFF values whereas being a boolean variable it should work on these values.

How to repeat:
Here is how we can reproduce this bug

Sample Code: 
--Error ER_WRONG_TYPE_FOR_VAR
SET @@global.optimizer_prune_level = ON;
SET @@session.optimizer_prune_level = 65550;
SELECT @@session.optimizer_prune_level;
 
Output: 
SET @@global.optimizer_prune_level = ON;
ERROR 42000: Incorrect argument type to variable 'optimizer_prune_level'
SET @@session.optimizer_prune_level = 65550;
SELECT @@session.optimizer_prune_level;
@@session.optimizer_prune_level
1

Suggested fix:
Variable should be documented as numeric, otherwise it should not work on numbers other than 0,1. Also it should support ON/OFF as a value.
[29 Feb 2008 8:59] Valeriy Kravchuk
I do agree that accepting ON|OFF would be nice. 

As for integer values >1, they are accpeted with warnings and 1 is used insted. This is a usual and normal behaviour.
[29 Feb 2008 18:34] Sinisa Milivojevic
I totally disagree with ON/OFF level ...

As variable is numeric and takes values between 1 and 5, it can't be Boolean !!!!

Sinisa Milivojevic
[1 Mar 2008 8:59] Valeriy Kravchuk
Sinisa,

Sorry, but values between 1 and 5 are NOT accepted:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.23-rc-community-debug MySQL Community Server - Debug (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set @@global.optimizer_prune_level=2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect optimizer_prune_level value: '2'
1 row in set (0.00 sec)