MySQL Bugs: #35020: illegal sql

Bug #35020	illegal sql_cache select syntax
Submitted:	4 Mar 2008 2:45	Modified:	6 Mar 2010 20:27
Reporter:	Marc ALFF	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:	Martin Hansson	CPU Architecture:	Any

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.

This is also in 5.4.4.

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

2935 Martin Hansson	2009-12-17
      Backport of fix for bug#35020

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

3777 Martin Hansson	2009-12-17 [merge]
      Upmerge of backport of fix for Bug#35020

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

3778 Martin Hansson	2009-12-17 [merge]
      Merge of up-merge of fix for Bug#35020

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091221153807-80nxoli1tw1z9bxn) (version source revid:martin.hansson@sun.com-20091217141112-rz4arnp21dehr12a) (merge vers: 6.0.14-alpha) (pib:15)

Pushed into mysql-next-mr (revid:alik@sun.com-20091221153659-d7bt0fh6mhhclxuf) (version source revid:martin.hansson@sun.com-20091217123827-8b13x3npq3s4fh3v) (pib:15)

Noted in 5.5.1 changelog.

Already fixed in 6.0.x.

Removed 5.5.1 changelog entry.

Setting report to NDI pending push into Celosia.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091221173845-8mqvlwp1bfp2w24g) (merge vers: 5.5.99) (pib:16)

Noted in 5.5.3 changelog and in SELECT section.