MySQL Bugs: #34840: optimizer_prune_level accepts numeric values and gives error on ON/OFF values

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:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	ON OFF values, optimizer_prune_level

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.

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.

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

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)