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

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Fix Version/s: N/A
Component/s: Data Definition - Alter Table
Labels:
None

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

Probably a duplicate of ~~MDEV-774~~.

Elena Stepanova added a comment - 2016-12-26 19:51 Probably a duplicate of MDEV-774 .

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

MariaDB Server