Re: [Maria-developers] Is it a big? CURSOR does not see changes made by ALTER TABLE

2 Mar 2017


      Seems to be related to https://jira.mariadb.org/browse/MDEV-774


On 03/02/2017 07:04 PM, Alexander Barkov wrote:
...
Hello Elena, all.
I noticed that CURSORs in combination with ALTER TABLE work strange.
Is it a known bug?
Thanks.
-- I create a table with two columns
-- And a procedure fetching two columns from this table
SET sql_mode=DEFAULT;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (10,20);
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
  DECLARE a,b INT;
  DECLARE cur CURSOR FOR SELECT * FROM t1;
  OPEN cur;
  FETCH cur INTO a,b;
  CLOSE cur;
  SELECT a,b;
END;
$$
DELIMITER ;
CALL p1();
-- It returns
-- +------+------+
-- | a    | b    |
-- +------+------+
-- |   10 |   20 |
-- +------+------+
-- So far so good
-- Now I add a new column to the table and call the procedure again
ALTER TABLE t1 ADD c INT;
CALL p1();
-- Woops. It should have failed, because there are now 3 columns in the
table
-- But it returns the result, like if the table still had 2 columns.