Bug #30105 SELECT [...] INTO subquery Invalid return.
Submitted: 27 Jul 2007 20:13 Modified: 24 Apr 2008 16:28
Reporter: Matthew Gaddis
Status: Closed
Category:Server: Parser Severity:S3 (Non-critical)
Version:5.0.45 OS:Any
Assigned to: Marc Alff Target Version:
Triage: D3 (Medium) / R3 (Medium) / E3 (Medium)

[27 Jul 2007 20: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 21:11] Miguel Solorzano 
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 21: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 21:33] Miguel Solorzano 
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 21: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 22:01] Miguel Solorzano 
Thank you for the clarification. Let our development team discuss that
behavior.
[3 Mar 2008 22:07] Marc Alff 
See related bug#33204 (INTO is allowed in subselect, causing inconsistent results)
[14 Mar 2008 23:50] Marc Alff 
Fixed by bug#33204 (INTO is allowed in subselect, causing inconsistent results)
[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.