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

Bug #14652	Queries with leading paren not cached
Submitted:	4 Nov 2005 20:00	Modified:	8 Dec 2005 6:21
Reporter:	[ name withheld ]
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.0	OS:	Linux (Linux)
Assigned to:	Bugs System	Target Version:

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