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

The problem is reproducible on all of MariaDB 5.1, 5.2, 5.3, 5.5, MySQL 5.1, 5.5, 5.6, and has also been filed as http://bugs.mysql.com/bug.php?id=64574.

A stored procedure or a trigger, having been executed once, on the second run might not take into account changes in a table structure, which causes a wrong result on the server where it is run, and also might cause SQL slave abort if the master wrongly returns OK after such a change, while the slave throws the expected error.

Below are two test cases. The first one is for the basic scenario: the initial execution of the stored procedure ends with the expected error, then the table structure gets fixed, but the procedure fails anyway, with

query 'CALL p()' failed: 1054: Unknown column 'test.t2.b' in 'field list'

The second test case is a reversed scenario – first execution of the procedure runs fine (correctly), then the table structure is altered, and the second execution must fail, but it does not on master; instead, the statement is written in the binlog with error code 0 and later fails on slave, thus causing a replication failure:

1136 Error 'Column count doesn't match value count at row 1' on query.

I found the ancient bug http://bugs.mysql.com/bug.php?id=6120 which looks similar, only for views, but it was fixed ages ago.

Test case 1 (wrong result - the last procedure call fails while it shouldn't)

CREATE TABLE t1 (a INT);

CREATE TABLE t2 (b INT, c INT);

--delimiter |

CREATE PROCEDURE p()

BEGIN

  INSERT INTO t1 SELECT * FROM t2;

END |

--delimiter ;

--error ER_WRONG_VALUE_COUNT_ON_ROW

CALL p();

ALTER TABLE t2 DROP COLUMN b;

CALL p();

Test case 2 (replication failure)

--source include/master-slave.inc

CREATE TABLE t1 (a INT);

CREATE TABLE t2 (b INT);

--delimiter |

CREATE PROCEDURE p()

BEGIN

  INSERT INTO t1 SELECT * FROM t2;

END |

--delimiter ;

CALL p();

ALTER TABLE t2 ADD COLUMN c INT;

CALL p();

--sync_slave_with_master