MySQL Bugs: #2245: Create Function Problem

Bug #2245	Create Function Problem
Submitted:	31 Dec 2003 7:32	Modified:	6 Aug 2005 16:16
Reporter:	Matthew Yonkovit	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
The following function errors out on creation:  

create function lookup() returns char(1) 
begin
 return "P";
end

the error returned is error 1064 (42000): You have and error in your SQL syntax.  Check the manual that corresponds to your MySql Server version for the right syntax to use near '' at line 1

I also tried this function from the mysql test dir:
create function fac(n int unsigned) returns bigint unsigned
begin
declare f bigint unsigned default 1;
 while n > 1 do
   set f = f * n;
   set n = n - 1;
   end while;
   return f;
end
this fails for me also.  

How to repeat:
create function lookup() returns char(1) 
begin
 return "P";
end

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:

All you need to do is to change query delimiter.

mysql> delimiter |
mysql> create function lookup() returns char(1)
    -> begin
    ->  return "P";
    -> end;
    -> |
Query OK, 0 rows affected (0.00 sec)
 
mysql> select lookup()|

+----------+
| lookup() |
+----------+
| P        |
+----------+
1 row in set (0.01 sec)

Hi,

Honestly, I am having the same problem with the above syntax as well. I can't seem to create any function

I have a few lines of sql codes:

DELIMITER //

CREATE FUNCTION TRUE_RANGE(DK INT, DIM_SYMBOL_SYS_ID BIGINT) RETURNS DECIMAL(10, 4)
BEGIN
	DECLARE TR DECIMAL(10, 4);
	DECLARE PDC DECIMAL(10, 4);
	DECLARE H, DECIMAL(10, 4);
	DECLARE L DECIMAL(10, 4);
	SELECT HIGH, LOW INTO H, L FROM FACT_PRICES WHERE DATE_KEY = DK;
	SELECT CLOSE INTO PDC FROM FACT_PRICES WHERE DATE_KEY < DK ORDER BY DATE_KEY DESC LIMIT 1;
	SET TR = MAX(H - L, H - PDC, PDC - L);

	RETURN TR;
END;
//

DELIMITER ;

In the console, it says:
mysql> CREATE FUNCTION TRUE_RANGE(DK INT, DIM_SYMBOL_SYS_ID BIGINT) RETURNS DECIMAL(10, 4)
    -> BEGIN
    -> DECLARE TR DECIMAL(10, 4);
    -> DECLARE PDC DECIMAL(10, 4);
    -> DECLARE H, DECIMAL(10, 4);
    -> DECLARE L DECIMAL(10, 4);
    -> SELECT HIGH, LOW INTO H, L FROM FACT_PRICES WHERE DATE_KEY = DK;
    -> SELECT CLOSE INTO PDC FROM FACT_PRICES WHERE DATE_KEY < DK ORDER BY DATE_KEY DESC LIMIT 1;
    -> SET TR = MAX(H - L, H - PDC, PDC - L);
    ->
    -> RETURN TR;
    -> 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 '(DK INT, DIM_SYMBOL_SYS_ID BIGINT) RETURNS DECIMAL(10, 4)
BEGIN
DECLARE TR DECIM' at line 1
mysql>
mysql> DELIMITER ;
mysql>

I'm running mysql-4.1.10a-3.2 on a linux box by the way. Any advise will be much appreciated.

"I'm running mysql-4.1.10a-3.2 on a linux box by the way. Any advise
will be much appreciated."

Upgrade to 5.0.XX, the server 4.1.XX not supports SP and functions.

I too am having the same problem, it seems.
I have tried to simplify this as much as possible, and even the below will not work.

delimiter |
create function lookup() returns char(1)
begin
return "P";
end;
|

I get back:
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 '() returns char(1)
begin
return "P";
end' at line 1

... whats up with that?

'mysql -V' output: mysql Ver 14.7 Distrib 4.1.13a, for Win32 (ia32)

"'mysql -V' output: mysql Ver 14.7 Distrib 4.1.13a, for Win32 (ia32)"

There are no stored procedures in version 4.1. Version 5.0 is needed.

This is a 'create function' call, I thought they were different from stored procedures??? ... 

I thought this because when you do a 'Create Procedure' mysql throws up an error suggesting it does not regonise the word 'procedure' ... but when I do a 'Create Function' it seems to just complain that something is missing.

Is there 'CREATE FUNCTION' in version 4?