MySQL Bugs: #3552: Wrong error message with table update clause:ERROR 1054:

Bug #3552	Wrong error message with table update clause:ERROR 1054:
Submitted:	23 Apr 2004 12:31	Modified:	5 Dec 2005 11:33
Reporter:	Philip	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.0.18-log	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
Wrong error message with update clause:
The syntax is 
 UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET ….
Or
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...] SET
If  after the valid tbl_name but before SET you enter some missing identifier (let’s say you have 2 tables t1 and t1info but you enter a spave between t1 and info) and everything else in the sql is OK the error complays about wrong fieeld in the field list

How to repeat:

mysql> create table t1(a int);
Query OK, 0 rows affected (0.06 sec)

mysql> create table t1info(aa char);
Query OK, 0 rows affected (0.06 sec)

mysql> update t1 info set aa=1;
ERROR 1054: Unknown column 'aa' in 'field list'
mysql>

The SQL parser of MySQL accepts table aliases in the UPDATE clause.  What you wrote is equivalent to UPDATE t1 AS info SET aa=1;.

It is very hard to implement robust error recovery and accurate error reporting.

Marko,
Very good point, maybe if the message was unknown column t1.aa or info.aa then it would be more clear that the problem was actually with the table name rather than the column…

I am changing this to a feature request for a more specific error message.

I do not think, that somebody will fix it really. MySQL supports multiple tables update, and in this case what table name should be appended? 

Current error message clearly explains, that the table(s) you refer to has no such column. If you'll refer to the table that does not exist, you'll get the other error message:

mysql> desc tcc1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  | UNI | NULL    |       |
| c2    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (1.03 sec)

mysql> update tcc1 a set c11 = 10;
ERROR 1054 (42S22): Unknown column 'c11' in 'field list'
mysql> update tcc2 a set c11 = 10;
ERROR 1146 (42S02): Table 'test.tcc2' doesn't exist

And, in any case, you may have another table, with that column, and simply update it by such a mistake. DBMS can not check and prevent all such user errors.