Bug #33204 INTO is allowed in subselect, causing inconsistent results
Submitted: 13 Dec 2007 9:37 Modified: 6 Mar 2010 20:01
Reporter: Martin Hansson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.1, 6.0 OS:Any
Assigned to: Martin Hansson CPU Architecture:Any

[13 Dec 2007 9:37] Martin Hansson
Description:
Standard SQL does not have "SELECT ... INTO ..." in subqueries. In MySQL, this is normally not allowed wither. However with UNION queries it is still possible to use them, with inconsistent results. Therefore, along with fixing Bug#32858 for version 5.1 and above, such uses of INTO will be disallowed.

How to repeat:
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM ( 
  SELECT 6 a 
  UNION 
  SELECT a INTO @var FROM t1 WHERE a = 2 
) alias
WHERE a = 1;
SELECT @var;

+------+
| @var |
+------+
| 1    |
+------+

MySQL has no concept of the INTO being inside the nested SELECT, and behaves as if it were on the top level.

Suggested fix:
- Forbid the use of INTO except on top-level queries by means of changing the SQL        grammar.
- Stop checking subquery UNIONs for INTO, they can't be there anyway.
[14 Feb 2008 11:40] 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/42270

ChangeSet@1.2659, 2008-02-14 12:39:53+01:00, mhansson@client-10-129-10-216.upp.off.mysql.com +3 -0
  Bug#33204: INTO is allowed in subselect, causing inconsistent results
  
  The parser rule for UNION in nested queries produced, by transitivity,
  top-level selects on its right-hand side. This allowed users to use 
  SELECT INTO in this position, which violates the standard and has 
  undefined result.
  Fixed by duplicating the intermediate rules into a subgrammar for
  nested selects where INTO is forbidden, extracting common code 
  into helper functions.
[14 Feb 2008 12:02] 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/42274

ChangeSet@1.2659, 2008-02-14 11:58:20+01:00, mhansson@riffraff.(none) +3 -0
  Bug#33204: INTO is allowed in subselect, causing inconsistent results
  
  The parser rule for UNION in nested queries produced, by transitivity,
  top-level selects on its right-hand side. This allowed users to use 
  SELECT INTO in this position, which violates the standard and has 
  undefined result.
  Fixed by duplicating the intermediate rules into a subgrammar for
  nested selects where INTO is forbidden, extracting common code 
  into helper functions.
[14 Feb 2008 12:02] 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/42275

ChangeSet@1.2659, 2008-02-14 12:12:22+01:00, mhansson@riffraff.(none) +3 -0
  Bug#33204: INTO is allowed in subselect, causing inconsistent results
  
  The parser rule for UNION in nested queries produced, by transitivity,
  top-level selects on its right-hand side. This allowed users to use 
  SELECT INTO in this position, which violates the standard and has 
  undefined result.
  Fixed by duplicating the intermediate rules into a subgrammar for
  nested selects where INTO is forbidden, extracting common code 
  into helper functions.
[22 Feb 2008 14: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/42836

ChangeSet@1.2659, 2008-02-22 15:49:29+01:00, mhansson@client-10-129-10-216.mysql.com +3 -0
  Bug#33204: INTO is allowed in subselect, causing inconsistent results
  
  The parser rule for UNION in nested queries produced, by transitivity,
  top-level selects on its right-hand side. This allowed users to use 
  SELECT INTO in this position, which violates the standard and has 
  undefined result.
  Fixed by duplicating the intermediate rules into a subgrammar for
  nested selects where INTO is forbidden.
[22 Feb 2008 15:37] 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/42847

ChangeSet@1.2659, 2008-02-22 16:37:15+01:00, mhansson@client-10-129-10-216.mysql.com +3 -0
  Bug#33204: INTO is allowed in subselect, causing inconsistent results
  
  The parser rule for UNION in nested queries produced, by transitivity,
  top-level selects on its right-hand side. This allowed users to use 
  SELECT INTO in this position, which violates the standard and has 
  undefined result.
  Fixed by duplicating the intermediate rules into a subgrammar for
  nested selects where INTO is forbidden.
[26 Feb 2008 11:59] 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/42980

ChangeSet@1.2659, 2008-02-26 12:52:12+01:00, mhansson@riffraff.(none) +3 -0
  Bug#33204: INTO is allowed in subselect, causing inconsistent results
  
  The parser rule for UNION in nested queries produced, by transitivity,
  top-level selects on its right-hand side. This allowed users to use 
  SELECT INTO in this position, which violates the standard and has 
  undefined result.
  Fixed by duplicating the intermediate rules into a subgrammar for
  nested selects where INTO is forbidden.
[3 Mar 2008 21:06] Marc ALFF
See related bug#30105 (SELECT [...] INTO subquery Invalid return.)
[4 Mar 2008 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/43387

ChangeSet@1.2659, 2008-03-04 14:27:15+01:00, mhansson@riffraff.(none) +3 -0
  Bug#33204: INTO is allowed in subselect, causing inconsistent results
  
  The parser rule for UNION in nested queries produced, by transitivity,
  top-level selects on its right-hand side. This allowed users to use 
  SELECT INTO in this position, which violates the standard and has 
  undefined result.
  Fixed by duplicating the intermediate rules into a subgrammar for
  nested selects where INTO is forbidden.
[5 Mar 2008 14:34] 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/43463

ChangeSet@1.2608, 2008-03-05 15:30:02+01:00, mhansson@riffraff.(none) +3 -0
  Bug#33204: INTO is allowed in subselect, causing inconsistent results
    
  The parser rule for UNION in nested queries produced, by transitivity,
  top-level selects on its right-hand side. This allowed users to use 
  SELECT INTO in this position, which violates the standard and has 
  undefined result.
  Fixed by duplicating the intermediate rules into a subgrammar for
  nested selects where INTO is forbidden.
[6 Mar 2008 8:24] 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/43502

ChangeSet@1.2608, 2008-03-06 09:20:51+01:00, mhansson@riffraff.(none) +6 -0
  Bug#33204: INTO is allowed in subselect, causing inconsistent results
    
  The parser rule for UNION in nested queries produced, by transitivity,
  top-level selects on its right-hand side. This allowed users to use 
  SELECT INTO in this position, which violates the standard and has 
  undefined result.
  Fixed by duplicating the intermediate rules into a subgrammar for
  nested selects where INTO is forbidden.
[13 Mar 2008 19:27] Bugs System
Pushed into 6.0.5-alpha
[24 Apr 2008 14:28] Paul DuBois
Noted in 6.0.5 changelog.

The parser accepted an INTO clause in nested SELECT statements, which
is invalid because such statements must return their results to the
outer context.
[10 Nov 2009 12:52] 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/89948

2945 Martin Hansson	2009-11-10
      Backport of Bug#33204 from mysql-pe to
      mysql-next-mr-bugfixing.
      
      Bug no 32858 was fixed in two different ways in what was
      then called mysql 5.1 and 6.0. The fix in 6.0 was very
      different since bugfix no 33204 was present.  Furthermore,
      the two fixes were not compatible. Hence in order to
      backport Bug#33204 to the 5.1-based mysql-next-mr-bugfixing,
      it was necessary to remove the 5.1 fix of 32858 and apply
      the 6.0 version of the fix.
     @ mysql-test/r/subselect.result
        Bug#33204-backport: Test result
     @ mysql-test/r/union.result
        
        Bug#33204-backport:
        - Reversal of test result: bugfix no 32858 for 5.1
        - Application of test result: bugfix no 32858 for 6.0
     @ mysql-test/t/subselect.test
        Bug#33204-backport: 
        - Changed tests
        - Test case
     @ mysql-test/t/union.test
        Bug#33204-backport:
        - Reversal of test: bugfix no 32858 for 5.1
        - Application of test: bugfix no 32858 for 6.0
     @ sql/sql_class.cc
        Bug#33204-backport: Reversal of bugfix no 32858 for 5.1
     @ sql/sql_class.h
        Bug#33204-backport: Reversal of bugfix no 32858 for 5.1
     @ sql/sql_yacc.yy
        Bug#33204-backport:
        - Reversal of bugfix no 32858 for 5.1
        - Application of bugfix no 32858 for 6.0
        - Application of bugfix no 33204
[20 Nov 2009 9:16] 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/91040

3718 Martin Hansson	2009-11-20 [merge]
      Up merge of backport of Bug#33204 from mysql-pe to
      mysql-next-mr-bugfixing. (Minor test case/result changes)
[11 Dec 2009 6:01] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)
[11 Dec 2009 6:03] Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)
[11 Dec 2009 19:33] Paul DuBois
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.
[6 Mar 2010 11:00] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)
[6 Mar 2010 20:01] Paul DuBois
Moved 5.6.0 changelog entry to 5.5.3.