[MDEV-11652] SP with cursors does not work well in combination with ALTER TABLE - Jira

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 |

+------+------+

blocks

MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP

is blocked by

MDEV-5816 MySQL WL#4179 - Stored programs: validation of stored program statements

is duplicated by

MDEV-774 LP:948583 - Stored procedure doesn't not take into account ALTER TABLE, causes wrong result or replication abort