Description:
A Stored Procedure execution fails after some executions when a functional index is present in the SP definition. The error behaves differently depending on the versions I tested:
8.0.23: It only fails when using a temporary table. After the third execution, all the subsequent executions fail.
8.0.25: It also fails on normal tables as well.
How to repeat:
DROP PROCEDURE IF EXISTS `sproc`;
DELIMITER $$
CREATE PROCEDURE `sproc`()
BEGIN
DROP TEMPORARY TABLE IF EXISTS `T1`;
CREATE TEMPORARY TABLE `T1`(
`C1` INT,
KEY ( ( `C1` > 0 ) )
);
SELECT 1 FROM ( SELECT 1 `C2` ) `T2`;
END$$
DELIMITER ;
Now if you invoke that procedure 3 times in a row:
mysql> CALL `sproc`(); CALL `sproc`(); CALL `sproc`();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 3107 (HY000): Generated column can refer only to generated columns defined prior to it.
----
On 8.0.25: The error is
ERROR 3754 (HY000): Functional index 'functional_index' cannot refer to an auto-increment column.
Description: A Stored Procedure execution fails after some executions when a functional index is present in the SP definition. The error behaves differently depending on the versions I tested: 8.0.23: It only fails when using a temporary table. After the third execution, all the subsequent executions fail. 8.0.25: It also fails on normal tables as well. How to repeat: DROP PROCEDURE IF EXISTS `sproc`; DELIMITER $$ CREATE PROCEDURE `sproc`() BEGIN DROP TEMPORARY TABLE IF EXISTS `T1`; CREATE TEMPORARY TABLE `T1`( `C1` INT, KEY ( ( `C1` > 0 ) ) ); SELECT 1 FROM ( SELECT 1 `C2` ) `T2`; END$$ DELIMITER ; Now if you invoke that procedure 3 times in a row: mysql> CALL `sproc`(); CALL `sproc`(); CALL `sproc`(); +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) ERROR 3107 (HY000): Generated column can refer only to generated columns defined prior to it. ---- On 8.0.25: The error is ERROR 3754 (HY000): Functional index 'functional_index' cannot refer to an auto-increment column.