Bug #35138 Incompatible SELECT options should not be allowed.
Submitted: 7 Mar 2008 10:57 Modified: 24 Mar 2008 8:20
Reporter: Martin Hansson
Status: Verified
Category:Server: Parser Severity:S3 (Non-critical)
Version:5.1, 6.0 OS:Any
Assigned to: Martin Hansson Target Version:
Triage: Triaged: D4 (Minor)

[7 Mar 2008 10:57] Martin Hansson 
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.
[12 Mar 2008 0:50] Bugs System 
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.
[12 Mar 2008 0:54] Bugs System 
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.
[12 Mar 2008 1:00] Bugs System 
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.
[14 Mar 2008 0:34] Marc Alff 
Sent review comments by email,
proposed an alternate solution, to investigate.
[24 Mar 2008 8:20] Martin Hansson 
I will start working on this bug after Bug#35020 is pushed, as they have code in common.
[2 Jul 18:18] Paul DuBois 
Looks like Bug#35020 was pushed a year ago.