MySQL Bugs: #40517: CREATE FUNCTION

Bug #40517	CREATE FUNCTION
Submitted:	5 Nov 2008 4:01	Modified:	5 Nov 2008 5:04
Reporter:	Jared S (Silver Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.29	OS:	Windows (Any)
Assigned to:		CPU Architecture:	Any
Tags:	create function, qc, SP

Description:
Hi,

Having trouble creating FUNCTIONS with MySQL 5.1.29.

How to repeat:
DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`Func1` $$
CREATE FUNCTION `test`.`Func1` () RETURNS INT
BEGIN
select 1;
END $$

DELIMITER ;

--ERROR 1415 (0A000) at line 4: Not allowed to return a result set from a function
-- And if you add parameter the error gets worse

Sorry, but this is a documented limitation for 5.0 and up. Read the manual, http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html:

"Statements that return a result set cannot be used within a stored function. This includes SELECT  statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET)."

-- need to use RETURN instead of SELECT and "" instead on IN
DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`Func1` $$
CREATE FUNCTION `test`.`Func1`(xNum INT) RETURNS INT
BEGIN
RETURN 1;
END $$

DELIMITER ;