MySQL Bugs: #35020: illegal sql

Bug #35020	illegal sql_cache select syntax
Submitted:	4 Mar 2008 3:45	Modified:	25 Jul 2008 3:50
Reporter:	Marc Alff
Status:	Closed
Category:	Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1/6.0	OS:	Any
Assigned to:	Martin Hansson	Target Version:
Triage:	D3 (Medium)

Description:
The parser accepts queries that are ambiguous or meaningless,
using the select options SQL_CACHE or SQL_NO_CACHE

Expected result:
global SELECT option flags should be available in select *statements* only,
not in select subqueries.

see related bug#33204, which relates to the use of INTO in subselects.

How to repeat:
Meaningless:
select sql_cache sql_no_cache * from t1;
select sql_no_cache sql_cache * from t1;

Meaningless:
select sql_cache 1 a union select sql_no_cache 2 a;
select sql_no_cache 1 a union select sql_cache 2 a;

global option flag used in a sub query, this should be illegal:
set @broken = (select sql_cache 1);
set @broken = (select sql_no_cache 1);

global option flag used in a sub query, this should be illegal:
select a from t1 where a in (select sql_cache a from t1);
select a from t1 where a in (select sql_no_cache a from t1);

Thank you for the bug report.

See related Bug#34943

This is actually two orthogonal bugs:

#1 SQL_NO_CACHE/SQL_CACHE should not be allowed in subqueries.
#2 It should not be possible to combine SQL_NO_CACHE and SQL_CACHE.

#2 has been reported as Bug#35138: Incompatible SELECT options should not be allowed.

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/43665

ChangeSet@1.2609, 2008-03-10 10:22:24+01:00, mhansson@riffraff.(none) +8 -0
  Bug#35020: illegal sql_cache select syntax
  
  The select option flags SQL_CACHE and SQL_NO_CACHE were available
  for use in subqueries, which they're not supposed to.
  
  Fixed by creating special parser rules for select options in
  subqueries.

Patch approved by email, with minor comments.

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/44721

ChangeSet@1.2609, 2008-04-01 11:29:01+02:00, mhansson@riffraff.(none) +3 -0
  Bug#35020: illegal sql_cache select syntax
  
  The select option flags SQL_CACHE and SQL_NO_CACHE were available
  for use in subqueries, which they're not supposed to.
  
  Fixed by creating special parser rules for select options in
  subqueries.
  
  Added tests that SQL_CACHE / SQL_NO_CACHE may only be
  used once on the first top-level SELECT statement and not
  in presence of its opposite.

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/44901

ChangeSet@1.2610, 2008-04-04 11:43:32+02:00, mhansson@riffraff.(none) +1 -0
  Bug#35020: Post-merge fix.

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/44910

ChangeSet@1.2623, 2008-04-04 13:08:25+02:00, mhansson@riffraff.(none) +1 -0
  Bug#35020: Post-merge fix.

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

The use of the SQL_CACHE and SQL_NO_CACHE options in SELECT
statements now is checked more restrictively: 1) Previously, both
options could be given in the same statement. This is no longer true;
only one can be given. 2) Previously, these options could be given in
SELECT statements that were not at the top-level. This is no longer
true; the options are disallowed in subqueries (including subqueries
in the FROM clause, and SELECT statements in unions other than the 
first SELECT.