[MDEV-11652] SP with cursors does not work well in combination with ALTER TABLE Created: 2016-12-23  Updated: 2022-12-05

Status: Confirmed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Dmitry Shulga
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-5816 MySQL WL#4179 - Stored programs: vali... Closed
Duplicate
is duplicated by MDEV-774 LP:948583 - Stored procedure doesn't ... Confirmed

 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 |
 
+------+------+



 Comments   
Comment by Elena Stepanova [ 2016-12-26 ]

Probably a duplicate of MDEV-774.

Generated at Thu Feb 08 07:51:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.