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

Error in the procedure when called a second time in the same session, where a newly created or replace table is used within a for loop

    XMLWordPrintable

Details

    Description

      In the procedure ci_gp2t_1, where a new table gp_gr is created or replace, and the same table is used within a for loop in the same procedure, calling the procedure ci_gp2t_1 a second time in the same session always results in a syntax error. If the table is not used inside a for loop, no error occurs.

      /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 */
      

      DELIMITER //
      CREATE OR REPLACE PROCEDURE `ci_gp2t_1`(
      ans out VARCHAR2 
      ) 
      AS
      BEGIN
      EXECUTE immediate 'create or replace table gp_gr (
      Gr_code CHAR(80),
      Gr_name CHAR(120),
      Gr_type_code CHAR(20) 
      )ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;';
       
      FOR t IN (SELECT * FROM gp_gr)
      LOOP
      SELECT 1 FROM dual;
      END LOOP;
      END//
      DELIMITER ;
       
       
      CALL ci_gp2t_1(@d);
      SELECT @d
      

      Attachments

        Activity

          People

            shulga Dmitry Shulga
            Justinas Bagdonas Justinas Bagdonas
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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