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
Triage: D3 (Medium)

[4 Mar 2008 2:45] Marc Alff
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);
[4 Mar 2008 2:52] Miguel Solorzano
Thank you for the bug report.
[4 Mar 2008 3:27] Marc Alff
See related Bug#34943
[7 Mar 2008 9:58] Martin Hansson
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.
[10 Mar 2008 9:53] 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/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.
[13 Mar 2008 15:49] Marc Alff
Patch approved by email, with minor comments.
[1 Apr 2008 9:35] 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/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.
[4 Apr 2008 9: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/44901

ChangeSet@1.2610, 2008-04-04 11:43:32+02:00, mhansson@riffraff.(none) +1 -0
  Bug#35020: Post-merge fix.
[4 Apr 2008 11:14] 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/44910

ChangeSet@1.2623, 2008-04-04 13:08:25+02:00, mhansson@riffraff.(none) +1 -0
  Bug#35020: Post-merge fix.
[28 May 2008 10:01] Bugs System
Pushed into 6.0.6-alpha
[25 Jul 2008 1:50] Paul Dubois
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.
[30 Nov 2009 20:33] Paul Dubois
This is also in 5.4.4.
[17 Dec 2009 12:38] 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/94729

2935 Martin Hansson	2009-12-17
      Backport of fix for bug#35020
[17 Dec 2009 14:08] 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/94757

3777 Martin Hansson	2009-12-17 [merge]
      Upmerge of backport of fix for Bug#35020
[17 Dec 2009 14:11] 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/94758

3778 Martin Hansson	2009-12-17 [merge]
      Merge of up-merge of fix for Bug#35020
[21 Dec 2009 15:39] Bugs System
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)
[21 Dec 2009 15:41] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091221153659-d7bt0fh6mhhclxuf) (version source revid:martin.hansson@sun.com-20091217123827-8b13x3npq3s4fh3v) (pib:15)
[5 Jan 2010 20:20] Paul Dubois
Noted in 5.5.1 changelog.

Already fixed in 6.0.x.
[11 Jan 2010 15:26] Paul Dubois
Removed 5.5.1 changelog entry.
[11 Jan 2010 15:27] Paul Dubois
Setting report to NDI pending push into Celosia.
[6 Mar 2010 11:05] Bugs System
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)
[6 Mar 2010 20:27] Paul Dubois
Noted in 5.5.3 changelog and in SELECT section.