Bug #12488 END IF needs a ';' after it.

• Submitted: 10 Aug 2005 10:06
• Modified: 20 Aug 2005 0:17
• Reporter: Peter Colclough
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.10-beta
• OS: Windows (Win2000 /Fedora Linux)
• Assigned to: Peter Gulutzan
• CPU Architecture: Any

[10 Aug 2005 10:06] Peter Colclough

Description:
Create Proc someproc()
......
IF (sServer IS NULL) OR (LENGTH(LTRIM(sServer)) = 0) THEN
    IF (sDatabase IS NULL) OR (LENGTH(LTRIM(sServer)) = 0) THEN
        SET sRet = sTable;
    ELSE
        SET sRet = CONCAT(sDatabase,'.',sTable);
    END IF;
ELSE	
    SET sRet =CONCAT(sServer,'.',sDatabase,'.',sTable); 
END IF;
......
/* End Proc */
===========
The ';' after the 'End If' is not in the documentation, and is contrary to all other implementations of procedural code.
Either the docs need changing, or the Stored Procedure code needs changing... not sure which <s>.

Peter Colclough
[e-mail]
([phone])

How to repeat:
Create a procedure with a 'IF... END IF' statement, try with and without a ';' after the 'END IF'.

Suggested fix:
1. Remove the need for a ';' after the 'END IF'.
2. Change the manual so you know you Have to add it in. (quicker and cheaper ...)

[10 Aug 2005 12:44] MySQL Verification Team

Simple test case:
create procedure sp1(a int)
begin
if a > 3 then select 'a>3';
else select 'a<=3';
end if
end //

[10 Aug 2005 13:10] Peter Colclough

Victoria,
Thanks for that, but I tried yours and this is what I got:

************** Sample *************
mysql> create procedure sp1(a int)
    -> begin
    -> if a>3 then select 'a>3';
    -> else select 'a<=3';
    -> end if
    -> end
    -> //
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 'end'
at line 6
************* End Sample ************

If I add the ';' after the 'end if' it works.

Have I missed something in my set up?

Ta

Peter C

[17 Aug 2005 6:39] Antony Curtis

I have examined the SQL03 PSM spec and it would appear that we currently do follow the spec - ie, each statement in a statement list is terminated by a semicolon.

[17 Aug 2005 9:17] Peter Colclough

The issue is not with the 'statement list', but with the 'END IF'. I'll repeat the code snippet, under the Win2000 version:
************** Sample Failure *************
mysql> create procedure sp1(a int)
    -> begin
    -> if a>3 then select 'a>3';
    -> else select 'a<=3';
    -> end if
    -> end
    -> //
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 'end'
at line 6
************* End Sample Failure ************

************** Sample Works *************
mysql> create procedure sp1(a int)
    -> begin
    -> if a>3 then select 'a>3';
    -> else select 'a<=3';
    -> end if;
    -> end
    -> //
************* End Sample Works ************
Note the ';' at end of the 'end if'.....

Peter Colclough
[e-mail]
[phone]

[20 Aug 2005 0:17] 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
product(s).

Additional info:

This is actually not an issue specific to IF ... END IF.
It's a manifestation of the requirement that every
statement within a compound statement must be
terminated by a semicolon.  As such, it an issue of
compound statement syntax.  I'll amend
http://dev.mysql.com/doc/mysql/en/begin-end.html
to make this clearer.