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.