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: | |
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
[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.