Bug #35445 query_cache_size can not be set to certain expressions
Submitted: 19 Mar 2008 21:00 Modified: 20 Aug 2009 20:17
Reporter: Philip Stoev Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:6.0.4, 5.0, 5.1, 5.4 OS:Any
Assigned to: Magne Mæhre CPU Architecture:Any
Tags: regression
Triage: Triaged: D4 (Minor)

[19 Mar 2008 21:00] Philip Stoev
query_cache_size and possibly other dynamic global variables accept an expression as the desired SET GLOBAL value, however some expressions are invalid without reason, e.g. RAND() and SIN().

How to repeat:
mysql> SET GLOBAL query_cache_size = FLOOR(100000);
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL query_cache_size = FLOOR(SIN(1) * 100000);
ERROR 1232 (42000): Incorrect argument type to variable 'query_cache_size'

Both of those expressions return an INTEGER.

Suggested fix:
Make all suitable expressions allowed.
[20 Mar 2008 4:28] Valeriy Kravchuk
Thank you for a bug report.
[10 Jul 2009 9:43] Sveta Smirnova
Bug does not exist in version 4.1
[20 Aug 2009 20:08] Magne Mæhre
This is probably not a bug.
In 5.0, the definition of the FLOOR function changed.  In previous versions, the return value was always converted to BIGINT, while in 5.0 and later the return value is dependent on the argument.  

From the manual: "For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type."

In the case of SIN() and RAND() as the argument, those return floating point results, thus leaving the return value of FLOOR as floating point.

Since the query_cache_size expects an exact-value type (numeric), the assigment fails.