Bug #103965 Stored procedure execution fails if contains a functional index
Submitted: 9 Jun 2021 22:20 Modified: 10 Jun 2021 6:33
Reporter: Mateo Fleitas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:8.0.25, 8.0.23 OS:Any
Assigned to: CPU Architecture:x86
Tags: regression

[9 Jun 2021 22:20] Mateo Fleitas
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.
[10 Jun 2021 6:33] MySQL Verification Team
Hello Mateo,

Thank you for the report and test case.
Verified as described with 8.0.25 build.

regards,
Umesh