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
• Status: Not a Bug
• 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

[19 Mar 2008 21:00] Philip Stoev

Description:
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.