MySQL Bugs: #64743: SQL CACHE + JOINS not working

Bug #64743	SQL CACHE + JOINS not working
Submitted:	23 Mar 2012 10:19	Modified:	23 Mar 2012 11:34
Reporter:	Lavesh Rawat	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S1 (Critical)
Version:	5.5.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cache, joins, MYSQL5.5

Description:
I Upgraded mysql5.1 to 5.5 and following query begins to fail.

(SELECT SQL_CACHE COLUMN1 FROM TABLE1 WHERE CONDITION1) UNION (SELECT SQL_CACHE COLUMN1 FROM TABLE1 WHERE CONDITION2) 

this works fine for mysqk5.1 but fails in 5.5 giving following error.
ERROR 1234 (42000): Incorrect usage/placement of 'SQL_CACHE'.

Please Suggest

How to repeat:
Write any similar query

While this is really a change in behavior of 5.5 vs 5.1, our manual clearly describes this case (see http://dev.mysql.com/doc/refman/5.5/en/select.html):

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

So formally this is not a bug.

Where exactly have u mentioned this in your manual
and why this has been done ??

I gave you URL to the manual page, describing details of SELECT, and test to search there.

As change was made in 5.5.3, you can find explanation at http://dev.mysql.com/doc/refman/5.5/en/news-5-5-3.html:

"The use of the SQL_CACHE and SQL_NO_CACHE options in SELECT statements now is checked more restrictively: 1) Previously, both options could be given in the same statement. This is no longer true; only one can be given. 2) Previously, these options could be given in SELECT statements that were not at the top-level. This is no longer true; the options are not permitted in subqueries (including subqueries in the FROM clause, and SELECT statements in unions other than the first SELECT. (Bug #35020)"