MySQL Bugs: #107987: DROP FUNCTION ERROR 1109 (442S02) if function references INNODB

Bug #107987	DROP FUNCTION ERROR 1109 (442S02) if function references INNODB_SYS tables
Submitted:	26 Jul 2022 19:39	Modified:	27 Jul 2022 12:20
Reporter:	Aaron Boudreaux	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	drop function, information schema, information_schema.INNODB_SYS_FOREIGN_COLS

Description:
We created functions in mysql 5.7 that query the INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS table. For example `return (select FOR_COL_NAME from INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS)`. In mysql 8 when we attempt to drop the function
with `DROP FUNCTION function_name;` an error is thrown with message "ERROR 1109 (42S02): Unknown table 'INNODB_SYS_FOREIGN_COLS' in information_schema". 
The function appears to be dropped but the sql script immediately exits with an error so no further queries are run.

Removing a function should not result in an error due to non existent tables the function may be referencing.

How to repeat:
In mysql 5.7 create a function that queries one of the INFORMATION_SCHEMA.INNODB_SYS* tables that were replaced with views in mysql 8 like INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS. Upgrade the database to mysql 8. Make a `DROP FUNCTION function_name` query to remove the function.

Hi Mr. Boudreaux,

Thank you for your bug report.

However, we need a test case that will include upgrade from the latest 5.7 release to the latest 8.0 release, which is 8.0.30.

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

We also need a set of SQL statements that lead to the error. It can include the exact steps that you have undertaken in your upgrade process from the latest 5.7 to the latest 8.0 release.

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Thank you for your interest in MySQL.

We are waiting for your full feedback.