| Bug #94132 | The description of "13.1.16 ...CREATE FUNCTION Syntax" chapter is not rigorous | ||
|---|---|---|---|
| Submitted: | 30 Jan 2019 8:40 | Modified: | 25 Apr 2019 14:29 |
| Reporter: | JianJun Shi | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S4 (Feature request) |
| Version: | 5.7 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[30 Jan 2019 14:05]
MySQL Verification Team
Hi, Thank you for your bug report. I fully agree with your conclusions. Verified as reported.
[30 Jan 2019 14:05]
MySQL Verification Team
A documentation bug.
[25 Apr 2019 14:29]
Paul Dubois
Posted by developer: Added a clarifying final sentence to this paragraph at https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html: The routine_body consists of a valid SQL routine statement. This can be a simple statement such as SELECT or INSERT, or a compound statement written using BEGIN and END. Compound statements can contain declarations, loops, and other control structure statements. The syntax for these statements is described in Section 13.6, “Compound-Statement Syntax”. In practice, stored functions tend to use compound statements, unless the body consists of a single RETURN statement.
Description: "13.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax" chapter in the "MySQL 5.7 Reference Manual" was describe as following: CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body ... routine_body: Valid SQL routine statement ... The routine_body consists of a valid SQL routine statement. This can be a simple statement such as SELECT or INSERT, or a compound statement written using BEGIN and END. In a word, "CREATE PROCEDURE" syntax and "CREATE FUNCTION" syntax all accord with this rule. However, "CREATE FUNCTION" syntax can not be combined with INSERT sentence, because of "No RETURN found in FUNCTION", and will throw an error message "ERROR 1320 (42000): No RETURN found in FUNCTION test.f1". Nevertheless, the same INSERT sentence can be used on "CREATE PROCEDURE" syntax with no problem. How to repeat: use test; create table t1(id INT,name varchar(50),sex ENUM('male','female'),birthday DATE); insert into t1 values(1,'tom','male','1996-01-01'); insert into t1 values(2,'jack','male','1996-06-12'); insert into t1 values(3,'jerry','female','1995-09-12'); create procedure p1() insert into t1 values(4,'mike','male','1998-03-27'); create function f1() returns int insert into t1 values(5,'judy','female','1997-08-01');