MySQL Bugs: #15273: Errors and inconsistencies in SP syntax description

Bug #15273	Errors and inconsistencies in SP syntax description
Submitted:	27 Nov 2005 15:12	Modified:	29 Nov 2005 18:43
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	NA
Assigned to:		CPU Architecture:	Any

Description:
In the section that describes the stored procedure syntax, the syntax for the create procedure statement (http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html) introduces the term:

routine_body:
    Valid SQL procedure statement or statements

In reality, either only a single statement may appear there; that may be a compound statement (that is, a BEGIN..END block is one of the statements that may appear there.

The syntax for BEGIN..END (http://dev.mysql.com/doc/refman/5.0/en/begin-end.html) introduces the term: statement_list. The statement list is denoted within the BEGIN..END keywords enclosed within square braces (e.g. [statement_list]) indicating optional occurrence. That is not correct: a BEGIN..END block cannot be empty, that is, it must contain at least one statement.

The section on handlers (http://dev.mysql.com/doc/refman/5.0/en/declare-handlers.html) introduces the term: 

sp_statement

to denote the occurence of a single stored procedure statement. This raises a question: are these the same as what may appear inside a 

statement_list

? Or is there something called a 

sp_statement_list

?

How to repeat:
na

Suggested fix:
1) decide on what to use, 

sp_statement, and sp_statement_list 

or just

statement and statement_list

2) In http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html, replace the term:  

Valid SQL procedure statement or statements

for either sp_statement or statement (whatever you decided on in solution 1)

3) remove the square brackets around the statement_list in the definition of BEGIN..END

Thank you for a problem report. 

I already asked our documentation team about the clarification of what statements are allowed inside SP and more formal description of that. I hope, this your request will become yet another notification on what should be done.

What you are mistaken about is BEGIN ... END part. Our documentation correctly describes current situation in MySQL:

mysql> create procedure pp1 () begin end;
Query OK, 0 rows affected (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0.00 sec)

So, BEGIN .. END may contain no statements inside. I am not sure it is a proper behaviour (Oracle does not allow this, for example), but it is properly documented.

Thanks Valeriy for your quick reaction.

I did recall trying an empty begin end and failing with a syntax error, so I assumed it was down to that. However, I just tried a couple of empty begin..ends, in different contexts, and they all seem to work. 

So, thanks for clearing that up. 

An empty begin end is actually very handy for CASE..END CASE statements. Sometimes you need to do something in a particular case, and nothing in another. Because you must catch every case (else, a runtime error 1339 willl result), a NOOP statement can be very handy (an empty ELSE or WHEN is not allowed, right?).

So, thank you again.

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:

I've updated the stored procedures chapter.

CREATE PROCEDURE section: change statement or statements
to be statement.

HANDLER section: change sp_statement to statement.

I've also pointed out that statement can be a simple statement
or a compound statement.

No change needed for BEGIN/END - it can indeed be empty.

Cheers Paul, thank alot!