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