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

Bug #35138	Incompatible SELECT options should not be allowed.
Submitted:	7 Mar 10:57	Modified:	24 Mar 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:	6.0
Triage:	Triaged: D4 (Minor)

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.