Description:
If you have a trigger defined for a table 'x', which tries to manipulate data in another table's column 'y.a', a non-existent column 'a' in table 'y' leads to the (correct) error message:
ERROR 1054 (42S22): Unknown column 'a' in field list
The problem is, that the error is shown while executing the INSERT or UPDATE statement, so it is very misleading if you have a column 'a' defined in table 'x'.
How to repeat:
1. Create a table x with a column a
2. Create a insert trigger which manipulates another table's column 'a'
3. Try to insert data to table x
CREATE TABLE x (
a INT
);
CREATE TABLE y (
b INT
);
DELIMITER |
CREATE TRIGGER doit BEFORE INSERT ON x
FOR EACH ROW BEGIN
INSERT INTO y (a) VALUES (1);
END;
|
DELIMITER ;
INSERT INTO x (a) VALUES (1);
Suggested fix:
Change the error message to
ERROR 1054 (42S22): Unknown column '<schema>.<table>.<column>' in field list
Description: If you have a trigger defined for a table 'x', which tries to manipulate data in another table's column 'y.a', a non-existent column 'a' in table 'y' leads to the (correct) error message: ERROR 1054 (42S22): Unknown column 'a' in field list The problem is, that the error is shown while executing the INSERT or UPDATE statement, so it is very misleading if you have a column 'a' defined in table 'x'. How to repeat: 1. Create a table x with a column a 2. Create a insert trigger which manipulates another table's column 'a' 3. Try to insert data to table x CREATE TABLE x ( a INT ); CREATE TABLE y ( b INT ); DELIMITER | CREATE TRIGGER doit BEFORE INSERT ON x FOR EACH ROW BEGIN INSERT INTO y (a) VALUES (1); END; | DELIMITER ; INSERT INTO x (a) VALUES (1); Suggested fix: Change the error message to ERROR 1054 (42S22): Unknown column '<schema>.<table>.<column>' in field list