MySQL Bugs: #14652: Queries with leading paren not cached

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

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.

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.

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

4.0 should be affected, too.

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

sorry, http://lists.mysql.com/internals/32521 was patch for other bug

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

Approved by email to be pushed into 4.1

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

Thank you for bugreport!
The bugfix is pushed to 4.1.17, 5.0.17 and 5.1.4
(4.0 is untouched)

Noted in 4.1.17, 5.0.17, 5.1.4 changelogs.

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