MySQL Bugs: #32858: Error: "Incorrect usage of UNION and INTO" does not take subselects into account

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

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.

Thank you for a bug report. Verified just as described.

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

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.

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.

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.

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."

I got an answer from Salle yesterday

"I know it sounds impossible, but I fully agree with Sinisa in this case /.../"

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."

Bug #33096 was marked as duplicate of this one.

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.

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.

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.

Bug#32490 has been marked a duplicate of this bug.
Please ensure its test case gets added to the 
regression test suite as well.

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.

The fix for 5.1 is addressed as bug#33204.

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.

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.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.56

Noted in 5.0.56 changelog.

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)

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)

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.

Pushed into 5.1.25-rc

Pushed into 6.0.6-alpha

Opening this bug up again since it is not yet fixed in 6.0.

Setting to NDI as requested by joro.

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.

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.

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.

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

Now pushed to 6.0 as well.

Fix now also documented for 5.1.25/6.0.7; closed.

Pushed into 6.0.8-alpha  (revid:mhansson@mysql.com-20080915140656-vb192913fkasbqfp) (version source revid:kpettersson@mysql.com-20080915213305-1ljm3tx7tgsdrne9) (pib:5)