Bug #30105 SELECT [...] INTO subquery Invalid return.
Submitted: 27 Jul 2007 18:13 Modified: 24 Apr 2008 14:28
Reporter: Matthew Gaddis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.45 OS:Any
Assigned to: Marc ALFF CPU Architecture:Any

[27 Jul 2007 18:13] Matthew Gaddis
Description:
The SELECT [...] INTO query parsing seems to pull values from the END of a query regardless of the statement being written into a subquery. 

How to repeat:
SELECT 1+1 into @test;
SELECT @test; -- 2
SELECT EXISTS(SELECT 1+1); -- 1
SELECT EXISTS(SELECT 1+1 INTO @test);
SELECT @test; -- 1 ??
[27 Jul 2007 19:11] MySQL Verification Team
Thank you for the bug report. Please read: http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

"13.2.8.6. EXISTS and NOT EXISTS

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE."
[27 Jul 2007 19:21] Matthew Gaddis
My report is an inquiry into the behaviors of SELECT[...] INTO. I realize that SELECT [...] INTO does not return any rows, its more of an update. Your reply addresses exists() behaviors and does not address the question at hand.
[27 Jul 2007 19:33] MySQL Verification Team
Thank you for the feedback. EXIST return TRUE (1) and NOT EXISTS FALSE (0):

mysql> SELECT EXISTS(SELECT 1+1);
+--------------------+
| EXISTS(SELECT 1+1) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT NOT EXISTS(SELECT 1+1);
+------------------------+
| NOT EXISTS(SELECT 1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)
[27 Jul 2007 19:51] Matthew Gaddis
Please review these two statements:
SELECT EXISTS(SELECT 1+1) INTO @test; SELECT @test; --> 1; -- Good Result
SELECT EXISTS(SELECT 1+1 INTO @test); SELECT @test; --> 1; -- Bad Result

Does that help make the problem clear?
[27 Jul 2007 20:01] MySQL Verification Team
Thank you for the clarification. Let our development team discuss that
behavior.
[3 Mar 2008 21:07] Marc ALFF
See related bug#33204 (INTO is allowed in subselect, causing inconsistent results)
[14 Mar 2008 22:50] Marc ALFF
Fixed by bug#33204 (INTO is allowed in subselect, causing inconsistent results)
[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.