MySQL Bugs: #39066: Problems with setting variables with expression/functions

Bug #39066	Problems with setting variables with expression/functions
Submitted:	27 Aug 2008 10:06	Modified:	27 Aug 2008 12:50
Reporter:	Nicklas Westerlund (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.x, 6.0.x	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	pow, variables

Description:
When I ran into a problem with falcon_record_memory_max I wanted to increase it to 512MB, for which I executed SET GLOBAL falcon_record_memory_max = 512 * POW(1024,2); 

sql01 [localhost] (blogs) > SET GLOBAL falcon_record_memory_max=512 * pow(1024,2);
ERROR 1232 (42000): Incorrect argument type to variable 'falcon_record_memory_max'

However, when doing 512*1024*1024, it works:
sql01 [localhost] (blogs) > SET GLOBAL falcon_record_memory_max=512 *1024 *1024;
Query OK, 0 rows affected (0.00 sec)

Why isn't stuff like pow() allowed when setting a variable?

How to repeat:
sql01 [localhost] (blogs) > SELECT 512*pow(1024,2);
+-----------------+
| 512*pow(1024,2) |
+-----------------+
|       536870912 | 
+-----------------+
1 row in set (0.00 sec)

sql01 [localhost] (blogs) > SHOW VARIABLES LIKE 'falcon_record_memory_max';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| falcon_record_memory_max | 262144000 | 
+--------------------------+-----------+
1 row in set (0.01 sec)

sql01 [localhost] (blogs) > SET GLOBAL falcon_record_memory_max=512 *1024 *1024;
Query OK, 0 rows affected (0.00 sec)

sql01 [localhost] (blogs) > SHOW VARIABLES LIKE 'falcon_record_memory_max';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| falcon_record_memory_max | 536870912 | 
+--------------------------+-----------+
1 row in set (0.00 sec)

sql01 [localhost] (blogs) > SET GLOBAL falcon_record_memory_max=512 * pow(1024,2);
ERROR 1232 (42000): Incorrect argument type to variable 'falcon_record_memory_max'

Thank you for a problem report. Verified just as described with 6.0.6:

C:\Program Files\MySQL\MySQL Server 6.0\bin>mysql -uroot -proot -P3311 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 6.0.6-alpha-community MySQL Community Server (GPL)

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

mysql> SET GLOBAL falcon_record_memory_max=512 * pow(1024,2);
ERROR 1232 (42000): Incorrect argument type to variable 'falcon_record_memory_ma
x'
mysql> SET GLOBAL falcon_record_memory_max=512 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL falcon_record_memory_max=512 * abs(1024 * 1024);
Query OK, 0 rows affected (0.00 sec)

As you can see, expressions with functions can be used, but they should have proper type of the return value. So, we have the following workaround:

mysql> SET GLOBAL falcon_record_memory_max=512 * cast(pow(1024,2) as unsigned);
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL falcon_record_memory_max=512 * cast(pow(1024,2) as signed);
Query OK, 0 rows affected (0.00 sec)

As manual (http://dev.mysql.com/doc/refman/6.0/en/set-option.html) say nothing eplicit about expr that can be used, I still think this is a minor bug.

Thank you, yes, I also classify it as a minor issue, considering that there are several other ways of working around it.