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

XML

Word

Printable

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 |

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

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