MySQL Bugs: #37018: Routine editor always reports syntax errors on ending seperator

Bug #37018	Routine editor always reports syntax errors on ending seperator
Submitted:	27 May 2008 21:07	Modified:	28 May 2008 16:50
Reporter:	Chen-Hsiao Shih	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.21	OS:	Windows (XP SP2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Here are two related issues about syntax errors on ending of a function. The first one is whenever I have following ending styles, given "//" as the delimiter:
1. "END; //" -- a space between END and
2. "END;" -- first line
   "//" -- next line right after END

The passed syntax MUST be "END;//" however.

The second issue is after re-open the project, Workbench changes the ending format to the second example above. If I add new functions, I have to spend time on removing newline and space characters between "END;" and "//" of every function. However, it looks fine if I just modify existing functions.

How to repeat:
1. Open a project using Workbench 5.0.21.
2. Create a Routine Group object and define a function through editor.
3. Set the delimiter as "//".
4. Create a function.
5. Apply one of the above two ways upon closing the function definition.

Suggested fix:
At least, output "END;//" instead of two-line format to relieve us from the clean up work after adding a new function.

Thank you for the bug report. I could not repeat the behavior reported,
could you please provide a sample of function you have typed. Thanks in
advance.

Here are part of the codes. The first part shows I have to remove new-line characters and space between "END;" and "//". The second shows the re-open output and will get syntax errors after the parser is "triggered".

Not sure if there are any invisible characters causing this problem but from the editor we couldn't tell. If you want screen snapshot or more information, please let me know. However, I will be offline now until tomorrow. Thanks!

====================== Passed without syntax errors ===================
CREATE FUNCTION `SystemDB`.`sfDeleteCAPoolUser` (inCiscoID VARCHAR(32), inServiceType VARCHAR(64))
    RETURNS INT
BEGIN
    DECLARE SUCCESS INT DEFAULT 1;
    DECLARE FAIL INT DEFAULT 0;
    DECLARE ExitStatus INT;
    DECLARE VerifiedID VARCHAR(32) DEFAULT NULL;
    DECLARE CONTINUE HANDLER FOR 1329 BEGIN SET ExitStatus = SUCCESS; END;

    -- Set FAIL to exit status and trap 1329 will set it to SUCCESS if the entry is gone.
    SET ExitStatus = FAIL;
    
    IF (inServiceType IS NOT NULL) THEN
    	DELETE FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID AND `fk_ServiceType` = inServiceType;
        -- To trigger 1329 warning: select zero row
        SELECT fk_CiscoID INTO VerifiedID FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID AND `fk_ServiceType` = inServiceType;
    ELSE
    	DELETE FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID;
        -- To trigger 1329 warning: select zero row
        SELECT fk_CiscoID INTO VerifiedID FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID;
    END IF;
    
    RETURN ExitStatus;
END;//
CREATE FUNCTION `SystemDB`.`sfToggleCAPoolUserAvailibility` (inCiscoID VARCHAR(32), inServiceType VARCHAR(64))
    RETURNS VARCHAR(32)
BEGIN
    DECLARE newAvailabilityStatus VARCHAR(32) DEFAULT NULL;

    IF (inServiceType IS NOT NULL) THEN
    	UPDATE `SystemDB`.`CaseAssignmentPool` SET `Availability` = IF(`Availability` = 'Yes', 'No', 'Yes') WHERE `fk_CiscoID` = inCiscoID AND `fk_ServiceType` = inServiceType;
        SELECT `Availability` INTO newAvailabilityStatus FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID AND `fk_ServiceType` = inServiceType; 

    ELSE
    	UPDATE `SystemDB`.`CaseAssignmentPool` SET `Availability` = IF(`Availability` = 'Yes', 'No', 'Yes') WHERE `fk_CiscoID` = inCiscoID;
        SELECT `Availability` INTO newAvailabilityStatus FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID;
    END IF;
    
    
    RETURN newAvailabilityStatus;
END;//
CREATE FUNCTION `SystemDB`.`sfAddCAPoolUser` (inCiscoID VARCHAR(32), inServiceType VARCHAR(64), inWorkingShift VARCHAR(64))
    RETURNS VARCHAR(32)
BEGIN
    DECLARE SUCCESS INT DEFAULT 1;
    DECLARE FAIL INT DEFAULT 0;
    DECLARE VerifiedID VARCHAR(32) DEFAULT NULL;
    
    INSERT INTO `SystemDB`.`CaseAssignmentPool`(`fk_CiscoID`, `fk_ServiceType`, `WorkingShift`, `LastAssignedDate`, `Availability`) 
    	VALUES (inCiscoID, inServiceType, inWorkingShift, NOW(), 'Yes') ON DUPLICATE KEY UPDATE `fk_CiscoID` = inCiscoID;
    SELECT `fk_CiscoID` INTO VerifiedID FROM `SystemDB`.`CaseAssignmentPool` 
        WHERE (`fk_CiscoID` = inCiscoID) AND (`fk_ServiceType` = inServiceType) AND (`WorkingShift` = inWorkingShift);
        
    RETURN VerifiedID;
END;//

===== gave syntax errors at lines described in the problem =============
CREATE FUNCTION `SystemDB`.`sfDeleteCAPoolUser` (inCiscoID VARCHAR(32), inServiceType VARCHAR(64))
    RETURNS INT
BEGIN
    DECLARE SUCCESS INT DEFAULT 1;
    DECLARE FAIL INT DEFAULT 0;
    DECLARE ExitStatus INT;
    DECLARE VerifiedID VARCHAR(32) DEFAULT NULL;
    DECLARE CONTINUE HANDLER FOR 1329 BEGIN SET ExitStatus = SUCCESS; END;

    -- Set FAIL to exit status and trap 1329 will set it to SUCCESS if the entry is gone.
    SET ExitStatus = FAIL;
    
    IF (inServiceType IS NOT NULL) THEN
    	DELETE FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID AND `fk_ServiceType` = inServiceType;
        -- To trigger 1329 warning: select zero row
        SELECT fk_CiscoID INTO VerifiedID FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID AND `fk_ServiceType` = inServiceType;
    ELSE
    	DELETE FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID;
        -- To trigger 1329 warning: select zero row
        SELECT fk_CiscoID INTO VerifiedID FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID;
    END IF;
    
    RETURN ExitStatus;
END;
//
CREATE FUNCTION `SystemDB`.`sfToggleCAPoolUserAvailibility` (inCiscoID VARCHAR(32), inServiceType VARCHAR(64))
    RETURNS VARCHAR(32)
BEGIN
    DECLARE newAvailabilityStatus VARCHAR(32) DEFAULT NULL;

    IF (inServiceType IS NOT NULL) THEN
    	UPDATE `SystemDB`.`CaseAssignmentPool` SET `Availability` = IF(`Availability` = 'Yes', 'No', 'Yes') WHERE `fk_CiscoID` = inCiscoID AND `fk_ServiceType` = inServiceType;
        SELECT `Availability` INTO newAvailabilityStatus FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID AND `fk_ServiceType` = inServiceType; 

    ELSE
    	UPDATE `SystemDB`.`CaseAssignmentPool` SET `Availability` = IF(`Availability` = 'Yes', 'No', 'Yes') WHERE `fk_CiscoID` = inCiscoID;
        SELECT `Availability` INTO newAvailabilityStatus FROM `SystemDB`.`CaseAssignmentPool` WHERE `fk_CiscoID` = inCiscoID;
    END IF;
    
    
    RETURN newAvailabilityStatus;
END;
//
CREATE FUNCTION `SystemDB`.`sfAddCAPoolUser` (inCiscoID VARCHAR(32), inServiceType VARCHAR(64), inWorkingShift VARCHAR(64))
    RETURNS VARCHAR(32)
BEGIN
    DECLARE SUCCESS INT DEFAULT 1;
    DECLARE FAIL INT DEFAULT 0;
    DECLARE VerifiedID VARCHAR(32) DEFAULT NULL;
    
    INSERT INTO `SystemDB`.`CaseAssignmentPool`(`fk_CiscoID`, `fk_ServiceType`, `WorkingShift`, `LastAssignedDate`, `Availability`) 
    	VALUES (inCiscoID, inServiceType, inWorkingShift, NOW(), 'Yes') ON DUPLICATE KEY UPDATE `fk_CiscoID` = inCiscoID;
    SELECT `fk_CiscoID` INTO VerifiedID FROM `SystemDB`.`CaseAssignmentPool` 
        WHERE (`fk_CiscoID` = inCiscoID) AND (`fk_ServiceType` = inServiceType) AND (`WorkingShift` = inWorkingShift);
        
    RETURN VerifiedID;
END;
//

This issue was already addressed as solution for
Bug #36815 [NEW]: auto formatting trigger cause SYNTAX_ERROR_1
Fix is ready in v5.0.22

Yes. 5.0.22 fixed the issue. Thanks!