MySQL Bugs: #51116: Stored procedure syntax validation

Bug #51116	Stored procedure syntax validation
Submitted:	11 Feb 2010 18:39	Modified:	12 Feb 2010 6:45
Reporter:	Vijay Konda	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.4.4-alpha	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	stored procedure syntax check

Description:
When stored function/procedures are compiled, a lot of syntax errors are not detected. For example,  statements such as  "SET a := 10;" or "SELECT count(id) table" is not detected as error during compilation but throws a SQL Exception when function/procedure is called. A lot of other erroneous statements included in stored procedure/function are not detected during the compilation but exceptions occur when the procedure/function is called. 

How to repeat:
Create a stored procedure. This will compile but when executed gives an error.

----------------------------------------------------------

DELIMITER $$
DROP PROCEDURE IF EXISTS test_syntax $$
CREATE  PROCEDURE  test_syntax(IN AI_FOR_USER          VARCHAR(80),
       IN AI_D_CODE            VARCHAR(15),
       IN AI_SHORT_NAME        VARCHAR(25))
BEGIN

DECLARE v_user  varchar(20);

-- error but not detected
SET v_user := 'test'; 

-- error but not detected
SELECT count(dept_id)  table_name;

END $$
DELIMITER;

Thank you for the report.

Both statements are syntactically valid, they just return runtime error. So this is not a bug what such a procedure can be created.

This is a huge bug. If the procedure cant be ran due to bad syntax it shouldnt save / compile. This is especially an issue in a live environment.  Why have any sproc syntax checking if you dont catch all bad SQL? All other major DBMS's catch this at save.

Was this addressed? If so when? Thanks!