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

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

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

            The problem is repeatable with statements starting with any keyword that can be a part of keyword contraction in the function MYSQLlex() in sql_lex.cc:

              switch(token) {
              case WITH
              ...
              case FOR_SYM:
              ...
              case VALUES:
              ...
              }
            

            This script demonstrates the same problem with WITH:

            DELIMITER $$
            CREATE OR REPLACE PROCEDURE p1() WITH t1 AS (SELECT 1) SELECT 1;
            $$
            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)
            

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

            +---------------------------+
            | body                      |
            +---------------------------+
            | t1 AS (SELECT 1) SELECT 1 |
            +---------------------------+
            

            This script demonstrates the same problem with VALUES:

            DELIMITER $$
            CREATE OR REPLACE PROCEDURE p1() VALUES (1);
            $$
            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)
            

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

            +------+
            | body |
            +------+
            | (1)  |
            +------+
            

            bar Alexander Barkov added a comment - The problem is repeatable with statements starting with any keyword that can be a part of keyword contraction in the function MYSQLlex() in sql_lex.cc : switch (token) { case WITH ... case FOR_SYM: ... case VALUES: ... } This script demonstrates the same problem with WITH : DELIMITER $$ CREATE OR REPLACE PROCEDURE p1() WITH t1 AS ( SELECT 1) SELECT 1; $$ 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) SELECT body FROM mysql.proc WHERE db= 'test' AND specific_name= 'p1' ; +---------------------------+ | body | +---------------------------+ | t1 AS (SELECT 1) SELECT 1 | +---------------------------+ This script demonstrates the same problem with VALUES : DELIMITER $$ CREATE OR REPLACE PROCEDURE p1() VALUES (1); $$ 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) SELECT body FROM mysql.proc WHERE db= 'test' AND specific_name= 'p1' ; +------+ | body | +------+ | (1) | +------+

            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.