MySQL Bugs: #65106: SQL_NO_CACHE doesn't work with BENCHMARK

Bug #65106	SQL_NO_CACHE doesn't work with BENCHMARK
Submitted:	25 Apr 2012 21:18	Modified:	26 Apr 2012 3:57
Reporter:	Radu Murzea	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.5.16	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When the SQL_NO_CACHE flag is used together with BENCHMARK, the parser rejects the query.

How to repeat:
Given any table with any columns and some queries, for example:

CREATE TABLE mytable (
id int unsigned NOT NULL,
birthday date NOT NULL
) ENGINE=MyISAM;

INSERT INTO mytable (id, birthday) VALUE (1, '1987-03-08');

SELECT birthday FROM mytable WHERE id=1;
___________________________________________

If I try to benchmark this query, it works ok:

SELECT BENCHMARK(1000000,(SELECT birthday FROM mytable WHERE id=1));
+--------------------------------------------------------------+
| BENCHMARK(1000000,(SELECT birthday FROM mytable WHERE id=1)) |
+--------------------------------------------------------------+
|                                                            0 |
+--------------------------------------------------------------+
1 row in set (0.06 sec)

But if I add the SQL_NO_CACHE flag:

SELECT BENCHMARK(1000000,(SELECT SQL_NO_CACHE birthday FROM mytable WHERE id=1));
ERROR 1054 (42S22): Unknown column 'SQL_NO_CACHE' in 'field list'

Suggested fix:
Allow the use of SQL_NO_CACHE together with BENCHMARK.

I forgot to add something important:

This is an important issue because if the cache is used during the BENCHMARK, then the BENCHMARK has no sense and its results are severely affected by the use of the cache.

Of course there are multiple caches that affect performance (disk cache, RAID cache, OS cache, InnoDB buffer pool etc.) and that you cannot control with SQL_NO_CACHE, but that doesn't mean it's not important.

It does not work because it makes no sense, query cache is not used for queries that are a subquery of an outer query.

Our manual clearly says about SQL_NO_CACHE:

"Also, these options are not permitted in subqueries (including subqueries in the FROM clause), and SELECT statements in unions other than the first SELECT."

Check http://dev.mysql.com/doc/refman/5.5/en/select.html.