Bug #45507 Misleading error message (Error 1054)
Submitted: 15 Jun 2009 19:05 Modified: 16 Jun 2009 7:29
Reporter: Thomas Hamacher Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S4 (Feature request)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[15 Jun 2009 19:05] Thomas Hamacher
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
[16 Jun 2009 7:29] Valeriy Kravchuk
Thank you for the feature request.