Bug #33204 INTO is allowed in subselect, causing inconsistent results
Submitted: 13 Dec 2007 10:37 Modified: 24 Apr 2008 16:28
Reporter: Martin Hansson
Status: Closed
Category:Server: Parser Severity:S2 (Serious)
Version:5.1, 6.0 OS:Any
Assigned to: Martin Hansson Target Version:
Triage: D2 (Serious)

[13 Dec 2007 10: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 12: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 13: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 13: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 15: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 16: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 12: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 22:06] Marc Alff 
See related bug#30105 (SELECT [...] INTO subquery Invalid return.)
[4 Mar 2008 15: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 15: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 9: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 20:27] Bugs System 
Pushed into 6.0.5-alpha
[24 Apr 2008 16: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 13: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 10: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)