MySQL Bugs: #3709: SELECT INTO 1 FROM DUAL not parsed as expected

Bug #3709	SELECT INTO 1 FROM DUAL not parsed as expected
Submitted:	11 May 2004 8:26	Modified:	19 May 2004 15:01
Reporter:	Murali N	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1	OS:
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
I am having problem in compiling simple procedure.

Here is the code in a file.

create procedure test (OUT param1 INT)
BEGIN
SELECT 1 INTO param1 FROM DUAL;
END

If I remove SELECT statement and compile then it compiles. I am not sure what is the problem. Can anyone let me know whats wrong in the code.

Thanks in advance.
Murali

How to repeat:
Create test.sql file with the code
test.sql
create procedure test (OUT param1 INT)
BEGIN
SELECT 1 INTO param1 FROM DUAL;
END;

mysql> source test.sql

Does table DUAL exist? What if you remove the "FROM DUAL" part?

Verified against 5.0.1/Linux.  SELECT ... INTO does not work with the "FROM DUAL" compatibility syntax.  As Marko notes, FROM DUAL is not actually necessary in MySQL.

This is not a stored procedure specific problem, it's a parser problem
already in version 4.1:

mysql> select 1 into @x from dual;
ERROR 1146 (42S02): Table 'test.dual' doesn't exist

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Note that this is now fixed in 4.1. It will take a while before it has propagated to 5.0 (where it was originally reported).