Bug #53665 stored procedure with if Exists(select * into x..)
Submitted: 15 May 2010 0:21 Modified: 7 Jul 2010 15:42
Reporter: musef habra Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: exists, INTO, regression, SELECT, stored

[15 May 2010 0:21] musef habra
Description:
I have stored procedure in Mysql 5.1.24 64bit and this proc work perfect
i transferred it to 5.5 and i have this problem:
when i try to call this

CREATE PROCEDURE `SaveInvoice`(....)
begin

DECLARE IsJournalTransfered smallint(4);     
....

if Exists(Select j.Transfered into IsJournalTransfered 
          from Journals as j 
          Where (j.IDC,j.IDBranch,j.IDY)=(1,1,1) 
          order by j.ID DESC 
          limit 1) then 
.....
end if;

END $$

then i have this error

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'into IsJournalTransfered 
from Journals as j 
Where (j.IDC,j.IDBranch,j.IDY)=(1,' at line 155)

How to repeat:
just  try to use  ... Exists(Select x into y ..) 
inside any stored procedure,
and you will most have this Error
[16 May 2010 13:13] Valeriy Kravchuk
Indeed, in 5.1.x one can use SELECT ... INTO in this context:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.1.46-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select exists(select 1 into @a from mysql.user);
Query OK, 1 row affected (0.03 sec)

mysql> select @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> select exists(select 1 from mysql.user);
+----------------------------------+
| exists(select 1 from mysql.user) |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

But in 5.5.x it is impossible:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot  -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.3-m3-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select exists(select 1 from mysql.user);
+----------------------------------+
| exists(select 1 from mysql.user) |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select exists(select 1 into @a from mysql.user);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'into
@a from mysql.user)' at line 1

It is not clear for me from the manual why it is no longer possible, so I'd call this a regression bug for now.
[18 May 2010 22:48] Omer Barnir
triage: setting tag to SR55RC (regression) - if there is a reason for the change - needs to be documented and retriaged
[30 Jun 2010 22:18] Davi Arnaut
The non-standard use of INTO in a subselect has been disallowed due to inconsistencies. See Bug#33204.

Changing category to documentation and requesting that this be noted as a incompatible change for upgrades.
[7 Jul 2010 15:42] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated upgrading section with this text:

Incompatible change: Previously, the parser accepted an INTO clause
in nested SELECT statements, which is invalid because such statements
must return their results to the outer context. As of MySQL 5.5.3, 
this syntax is no longer allowed and statements that use it must be
changed.
[11 Jan 2011 0:08] John Michael Dumaua
John Michael

Hello there,

Can anyone help me with my problem?

How to save Records in ms access using Mysql querry?

Hope you reply sooner,

Thanks