MySQL Bugs: #65497: sql_no_cache processed as field name in dependent subquery on mysql 5.5

Bug #65497	sql_no_cache processed as field name in dependent subquery on mysql 5.5
Submitted:	2 Jun 2012 21:51	Modified:	3 Jun 2012 7:45
Reporter:	morrison morrison	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	dependent subquery, SQL_NO_CACHE

Description:
sql_no_cache directive doesn`t works in dependent subquery in mysql 5.5

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37781230
Server version: 5.5.23 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>  create table T(a int); INSERT INTO T VALUES(1);
Query OK, 0 rows affected (0.10 sec)

Query OK, 1 row affected (0.02 sec)

mysql> SELECT sql_no_cache a from T WHERE a IN (SELECT sql_no_cache a FROM T);
ERROR 1054 (42S22): Unknown column 'sql_no_cache' in 'field list'

On 5.1 it works ok

How to repeat:
create table T(a int); 
INSERT INTO T VALUES(1);
SELECT sql_no_cache a from T WHERE a IN (SELECT sql_no_cache a FROM T);

mysql> SELECT  a from T WHERE a IN (SELECT sql_no_cache a FROM T);
ERROR 1054 (42S22): Unknown column 'sql_no_cache' in 'field list'

I think this is properly explained in the manual, http://dev.mysql.com/doc/refman/5.5/en/select.html:

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

So, it is not expected to see/process SQL_NO_CACHE in subquery in any special way, and it is processed as a column alias.

I'd prefer to see more specific/clear error message, but otherwise this is a documented behavior in 5.5.