MySQL Bugs: #57664: sql_cache hint disallowed in subqueries since 5.5.x

Bug #57664	sql_cache hint disallowed in subqueries since 5.5.x
Submitted:	22 Oct 2010 16:26	Modified:	22 Oct 2010 17:17
Reporter:	Jan Steemann (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S3 (Non-critical)
Version:	5.5.6-rc	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 5.1.51 and still MySQL 5.5.0-m2, the sql_cache hint could be used in subqueries.

Example query:
mysql> select sql_cache a from test where a in (select sql_cache a from test);

In MySQL 5.5.6-rc, this syntax is no longer valid due to the sql_cache in the subquery. The MySQL server returns an error about the "sql_cache" column being invalid.

How to repeat:
create table test (a int);
insert into test (a) values (1);
select sql_cache a from test where a in (select sql_cache a from test);

Actual result in MySQL 5.5.6-rc:
ERROR 1054 (42S22): Unknown column 'sql_cache' in 'field list'

Expected result (as in MySQL 5.5.0-m2 and MySQL 5.1.51):
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Suggested fix:
Not sure if this is a bug.
The MySQL manual states on http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html:
"The cache is not used for queries of the following types: Queries that are a subquery of an outer query".

From my understanding this means that the query result is not cached for subqueries, however, the manual does not say anything about the syntax being invalid.
Furthermore, this part of the manual was the same for 5.1 where the syntax was still allowed and worked (no idea about the query result being cached though).

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

"
The SQL_CACHE and SQL_NO_CACHE options affect caching of query results in the query cache (see Section 7.9.3, “The MySQL Query Cache”). SQL_CACHE tells MySQL to store the result in the query cache if it is cacheable and the value of the query_cache_type system variable is 2 or DEMAND. SQL_NO_CACHE tells MySQL not to store the result in the query cache.

For views, SQL_NO_CACHE applies if it appears in any SELECT in the query. For a cacheable query, SQL_CACHE applies if it appears in the first SELECT of a view referred to by the query.

As of MySQL 5.5.3, these two options are mutually exclusive and an error occurs if they are both specified. Also, these options are not permitted in subqueries (including subqueries in the FROM clause, and SELECT statements in unions other than the first SELECT.

Before MySQL 5.5.3, for a query that uses UNION or subqueries, the following rules apply:

SQL_NO_CACHE applies if it appears in any SELECT in the query.

For a cacheable query, SQL_CACHE applies if it appears in the first SELECT of the query.
"

looks like bug 54491 ...

So it's a feature.
I would have expected this description in the query cache section of the manual though.

Shouldn't it raise syntax error (1064) then?

I think it is ok to return an error.

Probably the query cache section of the docs can be extended to reflect the change?
As Paul DuBois stated the 5.5.6-rc docs currently mention the issue but only in the SELECT section.
A simple xref from the query cache section to the SELECT description would have made it clear to me that this is not a bug but a feature in 5.5.x.

Thank you for the feedback.

It is either duplicate of bug #54491 or not a bug. Closing as such.