Bug #14652 Queries with leading paren not cached
Submitted: 4 Nov 2005 19:00 Modified: 8 Dec 2005 5:21
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 OS:Linux (Linux)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[4 Nov 2005 19:00] [ name withheld ]
Description:
A select surrounded by parens will not be inserted into the query cache.

A query of the form "(SELECT * FROM table)" will not be cached. Likewise anything of the form "(SELECT ...) UNION (SELECT ...) ORDER BY ..."

The first can be trivially rewritten of course, but the second not being cacheable is really unfortunate for cases where you need to use UNION.

How to repeat:
Do a:

(SELECT * FROM table)

Then do it again. The results of the second will not come from the query cache.

Suggested fix:
Allow queries with leading parens to be cacheable.
[8 Nov 2005 17:45] Valeriy Kravchuk
Thank you for a problem report. Verified just as described on 5.0.16-BK build (ChangeSet@1.1972, 2005-11-05 22:45:54-08:00, igor@rurik.mysql.com):

[openxs@Fedora 5.0]$ bin/mysql -uroot;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 5.0.16

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

mysql> use test;
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> desc t1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| a      | int(11)     | NO   | PRI | 0       |       |
| b      | varchar(10) | NO   | PRI |         |       |
| c      | varchar(10) | YES  |     | NULL    |       |
| filler | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0,01 sec)

mysql> show variables like '%query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| ft_query_expansion_limit     | 20      |
| have_query_cache             | YES     |
| long_query_time              | 10      |
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
10 rows in set (0,00 sec)

mysql> set global query_cache_size = 400000;
Query OK, 0 rows affected (0,00 sec)

mysql> show variables like '%query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| ft_query_expansion_limit     | 20      |
| have_query_cache             | YES     |
| long_query_time              | 10      |
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 399360  |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
10 rows in set (0,00 sec)

mysql> show status like 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 390784 |
| Qcache_hits             | 0      |
| Qcache_inserts          | 0      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 2      |
| Qcache_queries_in_cache | 0      |
| Qcache_total_blocks     | 1      |
+-------------------------+--------+
8 rows in set (0,00 sec)

mysql> select * from t1;
+---+---+------+--------+
| a | b | c    | filler |
+---+---+------+--------+
| 1 | c | c    | c      |
+---+---+------+--------+
1 row in set (0,03 sec)

mysql> show status like 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 389248 |
| Qcache_hits             | 0      |
| Qcache_inserts          | 1      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 3      |
| Qcache_queries_in_cache | 1      |
| Qcache_total_blocks     | 4      |
+-------------------------+--------+
8 rows in set (0,00 sec)

mysql> select * from t1;
+---+---+------+--------+
| a | b | c    | filler |
+---+---+------+--------+
| 1 | c | c    | c      |
+---+---+------+--------+
1 row in set (0,00 sec)

mysql> show status like 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 389248 |
| Qcache_hits             | 1      |
| Qcache_inserts          | 1      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 4      |
| Qcache_queries_in_cache | 1      |
| Qcache_total_blocks     | 4      |
+-------------------------+--------+
8 rows in set (0,00 sec)

So, that is how Query chacher is used for simple select. Now put it in ():

mysql> (select * from t1);
+---+---+------+--------+
| a | b | c    | filler |
+---+---+------+--------+
| 1 | c | c    | c      |
+---+---+------+--------+
1 row in set (0,00 sec)

mysql> show status like 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 388224 |
| Qcache_hits             | 1      |
| Qcache_inserts          | 2      | <---- it is cached!
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 5      |
| Qcache_queries_in_cache | 2      |
| Qcache_total_blocks     | 6      |
+-------------------------+--------+
8 rows in set (0,00 sec)

mysql> (select * from t1);
+---+---+------+--------+
| a | b | c    | filler |
+---+---+------+--------+
| 1 | c | c    | c      |
+---+---+------+--------+
1 row in set (0,01 sec)

mysql> show status like 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 388224 |
| Qcache_hits             | 1      |  <-------------- but not used!
| Qcache_inserts          | 2      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 7      |
| Qcache_queries_in_cache | 2      |
| Qcache_total_blocks     | 6      |
+-------------------------+--------+
8 rows in set (0,00 sec)

It is a bug. It should either not be cached at all, or its results from the cache should be used.
[8 Nov 2005 22:35] [ name withheld ]
> It is a bug. It should either not be cached at all, or its results from the
> cache should be used.

Thanks! Sorry I didn't include the additional data showing the bug. FWIW, if it's at all possible I would definitely prefer the latter choice. Given the "(SELECT ...) UNION (SELECT ...) ORDER BY ..." case, it would be nice to be able to gain the extra performance pulling results from the Qcache.
[22 Nov 2005 9:03] Oleksandr Byelkin
4.0 should be affected, too.
[22 Nov 2005 9:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32521
[22 Nov 2005 9:32] Oleksandr Byelkin
sorry, http://lists.mysql.com/internals/32521 was patch for other bug
[22 Nov 2005 9:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32524
[30 Nov 2005 16:19] Konstantin Osipov
Approved by email to be pushed into 4.1
[1 Dec 2005 12:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32904
[3 Dec 2005 17:51] Oleksandr Byelkin
Thank you for bugreport!
The bugfix is pushed to 4.1.17, 5.0.17 and 5.1.4
(4.0 is untouched)
[8 Dec 2005 5:21] Paul DuBois
Noted in 4.1.17, 5.0.17, 5.1.4 changelogs.
[4 Apr 2006 21:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4463