MySQL Bugs: #35138: Incompatible SELECT options should not be allowed.

Bug #35138	Incompatible SELECT options should not be allowed.
Submitted:	7 Mar 2008 9:57	Modified:	28 Nov 2020 15:14
Reporter:	Martin Hansson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
There is no reason to allow SELECT options that are incompatible as this is error-prone for the user and semantics are unclear.

This is one part of a broader problem reported as Bug#35020.

How to repeat:
-- Meaningless to allow and confusing to the user.
select sql_cache sql_no_cache * from t1;
select sql_no_cache sql_cache * from t1;

-- Meaningless to allow and confusing to the user.
select sql_cache 1 a union select sql_no_cache 2 a;
select sql_no_cache 1 a union select sql_cache 2 a;

Suggested fix:
This is not a syntactical problem. The syntax is simply

SELECT <select options> <select list> ...

<select options> ::= <empty> 
                   | <select_option_list>

<select option list> ::= <select option>
                       | <select option list> <select option>

<select option> ::= ALL | DISTINCT | SQL_CACHE | SQL_NO_CACHE | ...

Note that this is not how it's described in the manual.

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

There is nothing forcing this fix to be part of the parser, although it could.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43752

ChangeSet@1.2609, 2008-03-11 12:30:36+01:00, mhansson@riffraff.(none) +5 -0
  Bug#35138: illegal sql_cache select syntax
  
  The options SQL_CACHE and SQL_NO_CACHE were allowed in combination in 
  any SELECT queries, where the use of one should deny 
  use of the other.
  Fixed by adding flags to the parse tree and a semantic check after parsing.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43760

ChangeSet@1.2609, 2008-03-11 15:05:53+01:00, mhansson@riffraff.(none) +5 -0
  Bug#35138: illegal sql_cache select syntax
  
  The options SQL_CACHE and SQL_NO_CACHE were allowed in combination in 
  any SELECT queries, where the use of one should deny 
  use of the other.
  Fixed by adding flags to the parse tree and a semantic check after parsing.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43768

ChangeSet@1.2609, 2008-03-11 14:45:48+01:00, mhansson@riffraff.(none) +5 -0
  Bug#35138: illegal sql_cache select syntax
  
  The options SQL_CACHE and SQL_NO_CACHE were allowed in combination in 
  any SELECT queries, where the use of one should deny 
  use of the other.
  Fixed by adding flags to the parse tree and a semantic check after parsing.

Sent review comments by email,
proposed an alternate solution, to investigate.

I will start working on this bug after Bug#35020 is pushed, as they have code in common.

Looks like Bug#35020 was pushed a year ago.

Fixed in 5.6:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.16    |
+-----------+
1 row in set (0.00 sec)

mysql> select sql_cache sql_no_cache * from t1;
ERROR 1221 (HY000): Incorrect usage of SQL_CACHE and SQL_NO_CACHE

mysql> select sql_cache 1 a union select sql_no_cache 2 a;
ERROR 1234 (42000): Incorrect usage/placement of 'SQL_NO_CACHE'

Fixed in 5.5, too:

mysql> select sql_cache sql_no_cache * from t1;
ERROR 1221 (HY000): Incorrect usage of SQL_CACHE and SQL_NO_CACHE
mysql> select sql_cache 1 a union select sql_no_cache 2 a;
ERROR 1234 (42000): Incorrect usage/placement of 'SQL_NO_CACHE'
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.29    |
+-----------+
1 row in set (0.00 sec)

Fixed in 5.5.3 as fix for Bug#35020.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

5.6:

mysql> select sql_cache sql_no_cache * from t1;
ERROR 1221 (HY000): Incorrect usage of SQL_CACHE and SQL_NO_CACHE

mysql> select sql_no_cache sql_cache * from t1;
ERROR 1221 (HY000): Incorrect usage of SQL_NO_CACHE and SQL_CACHE

mysql> select sql_cache 1 a union select sql_no_cache 2 a;
ERROR 1234 (42000): Incorrect usage/placement of 'SQL_NO_CACHE'

mysql> select sql_no_cache 1 a union select sql_cache 2 a;
ERROR 1234 (42000): Incorrect usage/placement of 'SQL_CACHE'