MySQL Bugs: #12493: CREATE PROCEDURE with carrige return after last END will not compile

Bug #12493	CREATE PROCEDURE with carrige return after last END will not compile
Submitted:	10 Aug 2005 13:39	Modified:	24 Aug 2005 17:01
Reporter:	Anders Karlsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.13	OS:	Windows (Windows)
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any

Description:
If the last END of a PREOCEDURE is followed by a CR/LF before the delimiter, then compilation of the procedure fails with error 1064, else not.
Tested against server 5.0.10 on Linux

How to repeat:
Start the query brower with test as the default database. Right-click in the object browser window to the right and select "Create New Procedure/Function". Name the procedure "p1" in the dialog and choose "Create PROCEDURE". A simple empty procedure is generated like this:
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`p1`$$
CREATE PROCEDURE `test`.`p1` ()
BEGIN

END$$

DELIMITER ;
Create this procedure by clicking "Execute" in the top right of the window. This works fine. Now do a slight edit to the generated code, and add a CR/LF after the END statement, before the $$ delimiter, so the code looks like this:
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`p1`$$
CREATE PROCEDURE `test`.`p1` ()
BEGIN

END
$$

DELIMITER ;
Execute this in the same way as above. This fails with a 1064 error.

Wromg version, this is of cource 1.1.13, not 1.1.15

tested on linux against mysql 5.0.12 beta and qb 1.1.12 (last published on linux) and no error happened. plan to test it on windows  against qb 1.1.13 for verifying

This looks like it could be a dup of 12170, or at least realte to the same issue.

Verified with 1.1.13

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Victoria, I think:

1. this is not a QB bug
proof: try to copy it to an SQL file and run via mysql cmd line utility with the 'source' command - it fails with pretty the same error

2. and is not a bug at all
proof: like any other MySQL utility statement this one uses EOL as statement delimiter. E.g. in mysql cmd-line utility try 'use <new-line> dbname' - it will fail

This is very much a bug, and it's a QB bug.
The proof that this is not a bug doesn't hold:

Proof 1) That it doesn't work with the source command is a different problem. This is reported as bug #11523. Exchange the delimter command with a \d and run it from the mysql source command, and this works just fine. With either \d or delimiter it works fine when piped into mysql.

Proof 2) That a CR/LF is a statement terminator isn't true either. The use command is different, and is not a good example. Any SQL-statement can be split over several lines, in both QB and from the MySQL Commandline:
mysql> DROP PROCEDURE IF EXISTS p1;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE p1() BEGIN END
    ->
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>

Ok, now I see what the problem is. Initially I just uncarefully read the desc.

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