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

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.3(EOL)
    • 10.3.7
    • Stored routines
    • None

    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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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