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

SP with cursors does not work well in combination with ALTER TABLE

    Details

      Description

      This script correctly returns an error, as the number of columns in the table (3) does not match the number of fetch variables (2).

      DROP TABLE IF EXISTS t1;
      DROP PROCEDURE p1;
       
      DELIMITER $$
      CREATE TABLE t1 (a INT, b VARCHAR(10),c INT);
      INSERT INTO t1 VALUES (1,'bbb1',11),(2,'bbb2',22);
      CREATE PROCEDURE p1()
      BEGIN
        DECLARE a INT;
        DECLARE b VARCHAR(10);
        DECLARE c CURSOR FOR SELECT * FROM t1 ORDER BY a;
        OPEN c;
        FETCH c INTO a,b;
        SELECT a, b;
        CLOSE c;
      END;
      $$
      DELIMITER ;
       
      CALL p1();
      

      ERROR 1328 (HY000): Incorrect number of FETCH variables
      

      Now I drop the column c, to make the number of columns in the table match the number of fetch variables, and re-execute the procedure:

      ALTER TABLE t1 DROP c;
      CALL p1();
      

      It returns a strange error:

      ERROR 1054 (42S22): Unknown column 'test.t1.c' in 'field list'
      

      It seems it somehow cached the table structure.

      Now I exit the session, start a new session and re-execute the procedure.

      CALL p1();
      

      It correctly returns the results:

      +------+------+
      | a    | b    |
      +------+------+
      |    1 | bbb1 |
      +------+------+
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated: