[#MDEV-15097] Stored procedures are unaware of metadata changes

In MySQL 5.6 stored routines are aware of metadata changes, and automatic re-prepare happens in a similar fashion as with prepared statements, see e.g.:

http://alexandernozdrin.blogspot.de/2012/09/mysql-56-handling-metadata-changes-in.html

MariaDB stored procedures are not aware of metadata changes though.

Example:

Setup:

MariaDB [test]> CREATE TABLE t1(a INT);

Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> insert into t1 values(1),(2),(3);

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> CREATE PROCEDURE p1()     SELECT * FROM t1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> call p1();

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> ALTER TABLE t1 ADD COLUMN b INT DEFAULT -1;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

Now calling the procedure again in the same session
MySQL 5.6 returns:

MySQL[test]> call p1();

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

| a    | b    |

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

|    1 |   -1 |

|    2 |   -1 |

|    3 |   -1 |

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

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB does not notice the schema change though, and so the procedure still returns only one column instead of two when called from the same session as before:

MariaDB [test]> call p1();

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[MDEV-15097] Stored procedures are unaware of metadata changes Created: 2018-01-27 Updated: 2020-08-25 Resolved: 2018-01-27
Status:	Closed
Project:	MariaDB Server
Component/s:	Stored routines
Affects Version/s:	10.2.12, 10.3.4
Fix Version/s:	N/A

[MDEV-15097] Stored procedures are unaware of metadata changes Created: 2018-01-27 Updated: 2020-08-25 Resolved: 2018-01-27