Bug #54491 inconsistent handling of sql_no_cache between 5.1 and 5.5
Submitted: 14 Jun 2010 16:12 Modified: 14 Jun 2010 16:18
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.5.3 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D4 (Minor)

[14 Jun 2010 16:12] Shane Bester
Description:
This is not consistent:

5.0.91 and 5.1.47:
---------------------
mysql> set @a=(select sql_no_cache 1);
Query OK, 0 rows affected (0.00 sec)

5.5.3:
-------
mysql> set @a=(select sql_no_cache 1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1)' at line 1

I would expect from 5.5.X one of the following results:
1. ERROR 1234 (42000): Incorrect usage/placement of 'SQL_NO_CACHE'
2. No error.

We notice in the manual:
http://dev.mysql.com/doc/refman/5.5/en/select.html
"Also, these options are disallowed in subqueries (including subqueries in the FROM clause, and SELECT statements in unions other than the first SELECT"

How to repeat:
#compare output and errors of the following on 5.0, 5.1 and 5.5:

select 1 from (select sql_no_cache 1) a;
set @a=(select sql_no_cache 1);
[14 Jun 2010 16:18] Miguel Solorzano
miguel@tikal ~]$ dbs/5.5mr/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.99-m4 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> set @a=(select sql_no_cache 1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1)' at line 1
mysql>
[14 Jun 2010 16:20] Miguel Solorzano
Forgot to paste:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> set @a=(select sql_no_cache 1);
Query OK, 0 rows affected (0.01 sec)
[17 Jun 2010 0:16] Omer Barnir
triage: - the change of not supporting the query is expected. The bug is about the error message that is not wrong but can be improved as 'sql_no_cache' used to be a keyword - setting as CHECKED