Bug #72991 Creating an IF/ELSEIF/ELSE statement with 45 or more clauses gives errors
Submitted: 12 Jun 2014 20:18 Modified: 21 Mar 2018 23:36
Reporter: David Suehring Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:6.1.6 OS:MacOS (10.9)
Assigned to: CPU Architecture:Any

[12 Jun 2014 20:18] David Suehring 
Description:
When creating a complex IF/ELSEIF/ELSE structure in MySQL Workbench's Stored Procedure Editor, Workbench begins giving syntax when the structure hits 45 clauses.

How to repeat:
1.)Create the following Stored Procedure:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `test_procedure` ()
BEGIN
DECLARE myvar INT;
SET myvar = 1;

  IF myvar = 1 THEN
    SELECT 1 FROM `dual`;
  ELSEIF myvar = 2 THEN
    SELECT 2 FROM `dual`;
  ELSEIF myvar = 3 THEN
    SELECT 3 FROM `dual`;
  ELSEIF myvar = 4 THEN
    SELECT 4 FROM `dual`;
  ELSEIF myvar = 5 THEN
    SELECT 5 FROM `dual`;
  ELSEIF myvar = 6 THEN
    SELECT 6 FROM `dual`;  
  ELSEIF myvar = 7 THEN
    SELECT 7 FROM `dual`;
  ELSEIF myvar = 8 THEN
    SELECT 8 FROM `dual`;
  ELSEIF myvar = 9 THEN
    SELECT 9 FROM `dual`;
  ELSEIF myvar = 10 THEN
    SELECT 10 FROM `dual`;
  ELSEIF myvar = 11 THEN
    SELECT 11 FROM `dual`;
  ELSEIF myvar = 12 THEN
    SELECT 12 FROM `dual`;
  ELSEIF myvar = 13 THEN
    SELECT 13 FROM `dual`;
  ELSEIF myvar = 14 THEN
    SELECT 14 FROM `dual`;
  ELSEIF myvar = 15 THEN
    SELECT 15 FROM `dual`;
  ELSEIF myvar = 16 THEN
    SELECT 16 FROM `dual`;  
  ELSEIF myvar = 17 THEN
    SELECT 17 FROM `dual`;
  ELSEIF myvar = 18 THEN
    SELECT 18 FROM `dual`;
  ELSEIF myvar = 19 THEN
    SELECT 19 FROM `dual`;
  ELSEIF myvar = 20 THEN
    SELECT 20 FROM `dual`;
  ELSEIF myvar = 21 THEN
    SELECT 21 FROM `dual`;
  ELSEIF myvar = 22 THEN
    SELECT 22 FROM `dual`;
  ELSEIF myvar = 23 THEN
    SELECT 23 FROM `dual`;
  ELSEIF myvar = 24 THEN
    SELECT 24 FROM `dual`;
  ELSEIF myvar = 25 THEN
    SELECT 25 FROM `dual`;
  ELSEIF myvar = 26 THEN
    SELECT 26 FROM `dual`;  
  ELSEIF myvar = 27 THEN
    SELECT 27 FROM `dual`;
  ELSEIF myvar = 28 THEN
    SELECT 28 FROM `dual`;
  ELSEIF myvar = 29 THEN
    SELECT 29 FROM `dual`;
  ELSEIF myvar = 30 THEN
    SELECT 30 FROM `dual`;
  ELSEIF myvar = 31 THEN
    SELECT 31 FROM `dual`;
  ELSEIF myvar = 32 THEN
    SELECT 32 FROM `dual`;
  ELSEIF myvar = 33 THEN
    SELECT 33 FROM `dual`;
  ELSEIF myvar = 34 THEN
    SELECT 34 FROM `dual`;
  ELSEIF myvar = 35 THEN
    SELECT 35 FROM `dual`;
  ELSEIF myvar = 36 THEN
    SELECT 36 FROM `dual`;  
  ELSEIF myvar = 37 THEN
    SELECT 37 FROM `dual`;
  ELSEIF myvar = 38 THEN
    SELECT 38 FROM `dual`;
  ELSEIF myvar = 39 THEN
    SELECT 39 FROM `dual`;
  ELSEIF myvar = 40 THEN
    SELECT 40 FROM `dual`;
  ELSEIF myvar = 41 THEN
    SELECT 41 FROM `dual`;
  ELSEIF myvar = 42 THEN
    SELECT 42 FROM `dual`;
  ELSEIF myvar = 43 THEN
    SELECT 43 FROM `dual`;
  ELSEIF myvar = 44 THEN
    SELECT 44 FROM `dual`;

  END IF;

END

2.) Add a 45th ELSEIF to the structure, and Workbench will begin showing syntax errors.
[13 Jun 2014 4:58] MySQL Verification Team 
Hello David,

Thank you for the bug report.
Verified as described on Win7.

Interestingly, SP is created but later on it doesn't allow to alter etc

Thanks,
Umesh
[13 Jun 2014 5:02] MySQL Verification Team 
Syntax error

Attachment: 72991.png (image/png, text), 66.08 KiB.
[13 Jun 2014 5:02] MySQL Verification Team 
Syntax error2

Attachment: 72991_2.png (image/png, text), 79.10 KiB.
[21 Mar 2018 23:36] Christine Cole 
Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 8.0.11 release, and here's the changelog entry:

A complex IF, ELSEIF, ELSE structure when used to create a stored
procedure within the new procedure – Routine tab produced a syntax error
message as clause 45 was added and for each clause that followed.

Thank you for the bug report.