[MDEV-16117] SP with a single FOR statement creates but further fails to load - Jira

XML

Word

Printable

This problem was reported by Peter Gulutzan:

This script creates without problems a stored procedure consisting of a single FOR statement, but an attempt to execute it fails with an error:

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1()

  FOR i IN 1..10 DO

      set @x = 5;

  END FOR;

$$

DELIMITER ;

CALL p1;

ERROR 1457 (HY000): Failed to load routine test.p1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)

Querying mysql.proc tells that the body value is wrong for this procedure:

SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1';

+-------------------------------------------+

| body                                      |

+-------------------------------------------+

| i IN 1..10 DO

      set @x = 5;

  END FOR |

+-------------------------------------------+

Notice, the keyword FOR is missing in the beginning.

The same problem is repeatable with a stored function consisting of a single FOR statement:

DELIMITER $$

CREATE OR REPLACE FUNCTION f1() RETURNS INT

  FOR i IN 1..10 DO

    RETURN 1;

  END FOR;

$$

DELIMITER ;

SELECT f1();

ERROR 1457 (HY000): Failed to load routine test.f1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)

SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1';

+---------------------------------------+

| body                                  |

+---------------------------------------+

| i IN 1..10 DO

    RETURN 1;

  END FOR |

+---------------------------------------+

It should be fixed to preserve the FOR keyword in body.

relates to

MDEV-16020 SP variables inside GROUP BY..WITH ROLLUP break replication

MDEV-16095 Oracle-style placeholder inside GROUP BY..WITH ROLLUP breaks replication

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.