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:Mac OS X (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] Umesh Shastry
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] Umesh Shastry
Syntax error

Attachment: 72991.png (image/png, text), 66.08 KiB.

[13 Jun 2014 5:02] Umesh Shastry
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.