Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16117

SP with a single FOR statement creates but further fails to load

    XMLWordPrintable

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: