MySQL Bugs: #109502: Insufficient checks for trigger

Bug #109502	Insufficient checks for trigger
Submitted:	30 Dec 2022 3:17	Modified:	30 Dec 2022 10:06
Reporter:	Huaxiong Song (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	all	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Sometimes the trigger can be confusing. For example:

1. Trigger can be created successfully to handle an non-existing tables(t2), and it's definer can be an no-existing user(user_1), just like:

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`user_1`@`%`*/ /*!50003 TRIGGER `trigger_1` AFTER UPDATE ON `t1` FOR EACH ROW BEGIN
            INSERT INTO t2 VALUES(new.id);
END */;;

As a result, when update on t1, errors will happen. In my opinion, this trigger should not be successfully created in this case. Errors should be found in the create phase instead of the use phase.

2. Trigger can be created successfully to handle an non-existing column(col_1), just like:

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`user`@`%`*/ /*!50003 TRIGGER `trigger_2` AFTER UPDATE ON `t1` FOR EACH ROW BEGIN
            select col_1 from t3;
END */;;

If t1 has the field col_1, but t3 does not, and user don't know or forget trigger2, then the error report will be quite confusing. The error message is "Unknown column 'col_1' in 'field list'", but it is insuffient, because we don't know "col_1" is in which table. As a user, I will first check table "t1" instead of trigger "trigger_2"(table "t3").

This might be by design, but in some cases, it can be really confusing.

How to repeat:
As above.

Suggested fix:
Checks about trigger should be done in the create phase instead of the use phase.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.  

https://bugs.mysql.com/bug.php?id=109501