Bug #32858 Error: "Incorrect usage of UNION and INTO" does not take subselects into account
Submitted: 29 Nov 2007 20:48 Modified: 5 Oct 2008 17:03
Reporter: Chris Stephens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.0.45, 5.1.x, 6.0.3 OS:Linux (Debian)
Assigned to: Martin Hansson CPU Architecture:Any
Tags: INTO, regression, subselect, UNION
Triage: Triaged: D2 (Serious)

[29 Nov 2007 20:48] Chris Stephens
Description:
The check to see if an into is in the last query of a union does not take subselects into account.  If you do a "SELECT INTO" at a higher level that does not use union and is absolutely guaranteed to result in only one row, you will receive the error message "Incorrect usage of UNION and INTO"

This worked correctly in previous versions of MySQL.  I have used it successfully in 5.0.34, however it results in the above error in 5.0.45.

How to repeat:
 CREATE TABLE `test_unions` (
`num` INT NOT NULL ,
`flag` INT NOT NULL
) ENGINE = MYISAM;

INSERT INTO test_unions (num,flag) VALUES (1,1);
INSERT INTO test_unions (num,flag) VALUES (3,0);

DROP FUNCTION IF EXISTS UNION_CALC
DELIMITER |
CREATE FUNCTION UNION_CALC()
RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE total INT DEFAULT 0;

		SELECT
			SUM(num) INTO total
		FROM
			(
				SELECT
					num
				FROM
					test_unions
				WHERE
					flag = 0
		
				UNION
				
				SELECT
					num
				FROM
					test_unions
				WHERE
					flag = 1
			) AS derived;

RETURN total;
END |

Creating the function results in the error "ERROR 1221 (HY000) at line 3: Incorrect usage of UNION and INTO"

The function should create successfully and return 4 for the given example.

Suggested fix:
Test the top level for incorrect usage of UNION and INTO, instead of testing for INTO at the top level and failing if a subquery does a UNION.
[30 Nov 2007 4:23] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[30 Nov 2007 14:00] Martin Hansson
Query result when used outside procedure:

mysql> SELECT SUM(num) INTO @total FROM (
    ->   SELECT num FROM test_unions WHERE flag = 0
    ->   UNION
    ->   SELECT num FROM test_unions WHERE flag = 1
    -> )
    -> AS derived;
ERROR 1221 (HY000): Incorrect usage of UNION and INTO
[30 Nov 2007 14:15] Martin Hansson
IMHO, both the UNION and subquery are totally superfluous in this case. I also find the alias 'derived' quite pointless, unless it is for tracing purposes. It is trivial to rewrite the procedure into an equivalent one without neither UNION, nor subquery, or alias.

DELIMITER |
CREATE FUNCTION UNION_CALC() RETURNS INT DETERMINISTIC
BEGIN
  DECLARE total INT DEFAULT 0;
  SELECT SUM(num) into total FROM test_unions WHERE flag IN (0, 1);  
RETURN total;
END |

I suspect that this test case is an over-simplification of a more complicated case, though.
[30 Nov 2007 17:18] Chris Stephens
Martin, you are correct.  The example I provided is simplified, and the result can be accomplished with a more straight forward query.  The actual queries in our application make proper usage of a union with a derived table.
[3 Dec 2007 11:08] Martin Hansson
The problem is that a subselect is not defined according to the standard in MySQL. If it were, this problem would go away. Peter Gulutzan states the following.

"
I can guarantee that:

Standard SQL does not have "SELECT ... INTO ..." in subqueries.
For those who care:
SELECT statements, and only SELECT statements, may have INTO:
"
<select statement: single row> ::=
SELECT [ <set quantifier> ] <select list>
INTO <select target list>
<table expression>
"
but subqueries don't contain SELECT statements, they contain
query expressions.
"
<subquery> ::=
<left paren> <query expression> <right paren>
"
and <query expression> at bottom contains query specification:
"
<query specification> ::=
SELECT [ <set quantifier> ] <select list> <table expression>
"
Notice that this looks like the syntax for a SELECT statement
-- except that it has no "[INTO <select target list>]".

Unfortunately the MySQL Reference Manual says:
"
A subquery is a SELECT statement within another statement.
...
A subquery can contain any of the keywords or clauses that an ordinary
SELECT can contain: DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, index
hints, UNION constructs, comments, functions, and so on.
"
I wrote that, so I can't complain too hard. But I regret that
it doesn't exclude SELECT ... INTO clearly.

I believe that the "back door" should be closed in MySQL version 5.1,
and the above-quoted section of the manual should say "except INTO".
But it's up to trudy to decide the version number.
"

The "back door" mentioned above is the ability to do:

SELECT a FROM t1 (SELECT 1 a UNION SELECT a FROM t1 INTO @v WHERE key=1) alias;

Which has the same effect as

SELECT a FROM t1 INTO @v (SELECT 1 a UNION SELECT a FROM t1 WHERE key=1) alias;

If we close the "back door" we can simply stop checking if we have an INTO when parsing a subquery UNION, since we know that the INTO couldn't belong to that UNION anyway.
[5 Dec 2007 15:30] Martin Hansson
Quite a few people have expressed an interest in this matter. Thank you all! I summarize below:

Peter Gulutzan:

"I believe that the "back door" should be closed in MySQL version 5.1,
and the above-quoted section of the manual should say "except INTO".
But it's up to trudy to decide the version number."

Trudy Pelzer:

"I want to recommend this change be made in
5.1 and up -- do you agree?"

Dean Ellis:

"1) We always have the option to extend the standard,
so do we want to block this behavior or "fix" it?

2) If we intend to block it, then certainly, do so
in a non-GA version.  It looks like a D3/I4 bug;
no point risking stability in GA to resolve that
sort of thing, and no reason to alter behavior
in this case (looks like the bug report is
triggered by a behavior change, though). I'm not
sure I would bother fixing this even in an RC
unless there are deeper consequences. "

Sinisa Milivojevic:

"SELECT ... INTO .. in a nested query and not in sub-query (PeterG will
turn us all to the thought police) is an abomination.

Hence I agree that it is fixed in 5.1 and higher, but .... I truly
would like to see, not only big red letters in the manual, but also
some info on our site and in our newsletters."

"As I said before, this bug should be fixed in 5.0.

In 5.1, IMHO, we should disallow INTO clause in nested queries. But, I
also pledged for big red letters in our manual and for adding this
behaviour change in 5.1 in all our newsletters / alerts."

Trudy Pelzer:

"Sinisa's recommendation is to block the behaviour
in 5.1. I'll wait for Salle's opinion before
deciding."
[6 Dec 2007 7:43] Martin Hansson
I got an answer from Salle yesterday

"I know it sounds impossible, but I fully agree with Sinisa in this case /.../"
[7 Dec 2007 10:54] Martin Hansson
Final word from Trudy:

"I would prefer that you forbid INTO in 5.1 and up;
that's my recommendation as well as the recommendation
from Sinisa and Salle."
[9 Dec 2007 12:28] Sveta Smirnova
Bug #33096 was marked as duplicate of this one.
[10 Dec 2007 8:03] 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/39622

ChangeSet@1.2546, 2007-12-10 09:04:31+01:00, mhansson@linux-st28.site +4 -0
  Bug #32858: Erro: "Incorrect usage of UNION and INTO" does not take 
  subselects into account
  
  It is forbidden to use the SELECT INTO construction inside UNION statements
  unless on the last SELECT of the union. The parser records whether it 
  has seen INTO or not when parsing a UNION statement. But if the INTO was
  legally used in an outer query, an error is thrown if UNION is seen in a
  subquery. Fixed in 5.0 by remembering the nesting level of INTO tokens and 
  mitigate the error unless it collides with the UNION.
[10 Dec 2007 8:04] 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/39623

ChangeSet@1.2587, 2007-12-10 09:05:38+01:00, mhansson@linux-st28.site +3 -0
  Bug#32858: Erro: "Incorrect usage of UNION and INTO" does not take subselects 
  into account
  
  It is forbidden to use the SELECT INTO construction inside UNION statements
  unless on the last SELECT of the union. The parser records whether it 
  has seen INTO or not when parsing a UNION statement. But if the INTO was
  legally used in an outer query, an error is thrown if UNION is seen in a
  subquery. Fixed in 5.1 by only allowing INTO in top level SELECT's, and
  only doing the check for misplaced INTO when parsing top level UNION.
[10 Dec 2007 8:27] 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/39625

ChangeSet@1.2546, 2007-12-10 09:03:15+01:00, mhansson@linux-st28.site +4 -0
  Bug #32858: Erro: "Incorrect usage of UNION and INTO" does not take 
  subselects into account
  
  It is forbidden to use the SELECT INTO construction inside UNION statements
  unless on the last SELECT of the union. The parser records whether it 
  has seen INTO or not when parsing a UNION statement. But if the INTO was
  legally used in an outer query, an error is thrown if UNION is seen in a
  subquery. Fixed in 5.0 by remembering the nesting level of INTO tokens and 
  mitigate the error unless it collides with the UNION.
[10 Dec 2007 22:23] Trudy Pelzer
Bug#32490 has been marked a duplicate of this bug.
Please ensure its test case gets added to the 
regression test suite as well.
[12 Dec 2007 19: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/39811

ChangeSet@1.2546, 2007-12-12 20:40:55+01:00, mhansson@linux-st28.site +5 -0
  Bug #32858: Erro: "Incorrect usage of UNION and INTO" does not take 
  subselects into account
  
  It is forbidden to use the SELECT INTO construction inside UNION statements
  unless on the last SELECT of the union. The parser records whether it 
  has seen INTO or not when parsing a UNION statement. But if the INTO was
  legally used in an outer query, an error is thrown if UNION is seen in a
  subquery. Fixed in 5.0 by remembering the nesting level of INTO tokens and 
  mitigate the error unless it collides with the UNION.
[13 Dec 2007 10:15] Martin Hansson
The fix for 5.1 is addressed as bug#33204.
[13 Dec 2007 10:18] 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/39870

ChangeSet@1.2546, 2007-12-13 11:19:05+01:00, mhansson@linux-st28.site +5 -0
  Bug #32858: Erro: "Incorrect usage of UNION and INTO" does not take 
  subselects into account
  
  It is forbidden to use the SELECT INTO construction inside UNION statements
  unless on the last SELECT of the union. The parser records whether it 
  has seen INTO or not when parsing a UNION statement. But if the INTO was
  legally used in an outer query, an error is thrown if UNION is seen in a
  subquery. Fixed in 5.0 by remembering the nesting level of INTO tokens and 
  mitigate the error unless it collides with the UNION.
[13 Dec 2007 10:32] 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/39876

ChangeSet@1.2546, 2007-12-12 20:36:18+01:00, mhansson@linux-st28.site +5 -0
  Bug #32858: Erro: "Incorrect usage of UNION and INTO" does not take 
  subselects into account
  
  It is forbidden to use the SELECT INTO construction inside UNION statements
  unless on the last SELECT of the union. The parser records whether it 
  has seen INTO or not when parsing a UNION statement. But if the INTO was
  legally used in an outer query, an error is thrown if UNION is seen in a
  subquery. Fixed in 5.0 by remembering the nesting level of INTO tokens and 
  mitigate the error unless it collides with the UNION.
[13 Dec 2007 10:32] 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/39877

ChangeSet@1.2546, 2007-12-12 20:37:54+01:00, mhansson@linux-st28.site +5 -0
  Bug #32858: Erro: "Incorrect usage of UNION and INTO" does not take 
  subselects into account
  
  It is forbidden to use the SELECT INTO construction inside UNION statements
  unless on the last SELECT of the union. The parser records whether it 
  has seen INTO or not when parsing a UNION statement. But if the INTO was
  legally used in an outer query, an error is thrown if UNION is seen in a
  subquery. Fixed in 5.0 by remembering the nesting level of INTO tokens and 
  mitigate the error unless it collides with the UNION.
[11 Jan 2008 12:18] Bugs System
Pushed into 6.0.5-alpha
[11 Jan 2008 12:21] Bugs System
Pushed into 5.1.23-rc
[11 Jan 2008 12:22] Bugs System
Pushed into 5.0.56
[15 Feb 2008 17:49] Paul Dubois
Noted in 5.0.56 changelog.
[13 May 2008 13:44] Shane Bester
was this fixed in 5.1 ?

mysql> select 1 into @`avar` from (select 1 union select 1) `a`;
ERROR 1221 (HY000): Incorrect usage of UNION and INTO
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.1.24-rc-community-log |
+-------------------------+
1 row in set (0.01 sec)
[13 May 2008 13:47] Shane Bester
5.0 seems fixed:

mysql> select 1 into @`avar` from (select 1 union select 1) `a`;
Query OK, 1 row affected (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.62-debug |
+--------------+
1 row in set (0.00 sec)
[13 May 2008 15:13] 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/46657

ChangeSet@1.2632, 2008-05-13 18:10:46+03:00, gkodinov@magare.gmz +5 -0
  Bug #32858: Erro: "Incorrect usage of UNION and INTO" does not take 
  subselects into account
    
  It is forbidden to use the SELECT INTO construction inside UNION statements
  unless on the last SELECT of the union. The parser records whether it 
  has seen INTO or not when parsing a UNION statement. But if the INTO was
  legally used in an outer query, an error is thrown if UNION is seen in a
  subquery. Fixed in 5.0 by remembering the nesting level of INTO tokens and 
  mitigate the error unless it collides with the UNION.
[14 May 2008 15:21] Bugs System
Pushed into 5.1.25-rc
[22 May 2008 9:50] Bugs System
Pushed into 6.0.6-alpha
[10 Sep 2008 12:45] Martin Hansson
Opening this bug up again since it is not yet fixed in 6.0.
[10 Sep 2008 12:46] Martin Hansson
Setting to NDI as requested by joro.
[15 Sep 2008 13:43] 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/54103

2826 Martin Hansson	2008-09-15
      Bug#32858: Error: "Incorrect usage of UNION and INTO" does not 
      take subselects into account
      
      When parsing a new UNION branch, there is a check if the previous
      branch contained INTO, which would be illegal syntax. But this is
      only the case if the new branch is on the top-level.
      Fixed in 6.0 by introducing a boolean argument so that the check
      is disabled when parsing nested UNION expressions.
[15 Sep 2008 14:07] 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/54106

2839 Martin Hansson	2008-09-15 [merge]
      Bug#32858: Auto merged.
[15 Sep 2008 14:51] 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/54112

2839 Martin Hansson	2008-09-15 [merge]
      Bug#32858: Auto merged.
[15 Sep 2008 15:33] 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/54130
[16 Sep 2008 10:14] Martin Hansson
Now pushed to 6.0 as well.
[5 Oct 2008 17:03] Jon Stephens
Fix now also documented for 5.1.25/6.0.7; closed.
[17 Oct 2008 16:42] Bugs System
Pushed into 6.0.8-alpha  (revid:mhansson@mysql.com-20080915140656-vb192913fkasbqfp) (version source revid:kpettersson@mysql.com-20080915213305-1ljm3tx7tgsdrne9) (pib:5)