Bug #24317 Queries with CONVERT_TZ() are not cached
Submitted: 14 Nov 2006 18:51 Modified: 5 Oct 2007 11:29
Reporter: Neil Skrypuch Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.0.26, 5.1 BK OS:Linux (Linux)
Assigned to: Kristofer Pettersson CPU Architecture:Any

[14 Nov 2006 18:51] Neil Skrypuch
Description:
Queries that use the CONVERT_TZ() function are not inserted into the query cache, with the obvious consequence that they also cannot be read from the query cache later. This is not an issue of the query cache statistics being wrong as for slow queries the time difference is quite obvious.

There is nothing special about the data in the attached dump, it was an arbitrary construction to demonstrate the bug. The actual data with which I spotted the bug is much too large to attach here. I have not found any times where a query that uses CONVERT_TZ() is cached. Furthermore, the CONVERT_TZ() need not be in the column portion of the SELECT, I have also seen this issue where 

How to repeat:
Import the attached dump (which assumes the existence of an empty `testtz` database) and restart mysqld to reset the query cache, then login to the mysql monitor and follow the mysql monitor session as detailed below.

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.26-log

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

mysql> USE `testtz`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768408 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 4        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
| Questions               | 6        |
+-------------------------+----------+
9 rows in set (0.00 sec)

mysql> SELECT CONVERT_TZ(`date1`, 'GMT', 'EST') FROM `dates` WHERE `date2` = '2006-11-14 12:46:29';
+-----------------------------------+
| CONVERT_TZ(`date1`, 'GMT', 'EST') |
+-----------------------------------+
| 2006-11-14 07:46:29               |
| 2006-11-14 07:46:29               |
| 2006-11-14 07:46:29               |
| 2006-11-14 07:46:29               |
| 2006-11-14 07:46:29               |
| 2006-11-14 07:46:29               |
| 2006-11-14 07:46:29               |
+-----------------------------------+
7 rows in set (0.01 sec)

mysql> SHOW STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768408 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 6        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
| Questions               | 8        |
+-------------------------+----------+
9 rows in set (0.00 sec)

mysql> SELECT `date1` FROM `dates` WHERE `date2` = '2006-11-14 12:46:29';
+---------------------+
| date1               |
+---------------------+
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
+---------------------+
7 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16766872 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 7        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
| Questions               | 10       |
+-------------------------+----------+
9 rows in set (0.00 sec)

Note that the query is only cached when it does not use CONVERT_TZ().

Suggested fix:
Queries that use CONVERT_TZ() should be cached, provided they do not use NOW(), RAND(), etc as described in <http://mysql.org/doc/refman/5.0/en/query-cache-how.html>.

Alternatively, if for some reason, queries using CONVERT_TZ() cannot be cached, CONVERT_TZ() should be added to the list of functions in the above link.
[14 Nov 2006 18:53] Neil Skrypuch
database dump

Attachment: mysqlqcbug.mysql (text/plain), 1.91 KiB.

[14 Nov 2006 20:06] Sveta Smirnova
Thank you for the report.

Verified as described.
[14 Nov 2006 22:29] Neil Skrypuch
Oops, the last sentence of Description there should read:

Furthermore, the CONVERT_TZ() need not be in the column portion of the SELECT, I have also seen this issue where CONVERT_TZ() is in the WHERE clause.
[5 Oct 2007 11:29] Kristofer Pettersson
Expanding the Query Cache to deal with CONVERT_TZ() is added to the internal wish list of QueryCache development. Thank you for this contribution. This is however not a bug, but we will change the documentation to be more clear.