MySQL Bugs: #76840: MySQL reports wrong information

Bug #76840	MySQL reports wrong information
Submitted:	26 Apr 2015 19:46	Modified:	29 Apr 2015 9:59
Reporter:	Pradeep Kumar Sharma	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.6.21-log	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DML, message, REPLACE, UPDATE

Description:
I have two tables in two separate schemas(Schema1.TableA, Schema2.TableB) with common columns. TableA is superset of TableB. I exported selected column data from TableB and restored into TableA. Later on I find out that the data I selected having abbreviations and further I tried to update/replace with expended data using a where condition. This always report for whatever table's column I put first in where clause as "Unknown  #1054 - Unknown column 'Schema1.TableA.name' in 'where clause'"  however the same statement works fine while converted into 'select' statement.

How to repeat:
1. Create two tables in separate schemas like below

Schema1.TableA (nameA, number, etc.)

Schema2.TableB (nameB, address, abbreviated_name, etc.)

2. Fill sample data ONLY in Schema2.TableB.

3. Export data of TableB

4. Restore data into TableA but keep the TableA.nameA=TableB.abbreviated_name

5. Try to update or replace data of TableA as TableA.nameA=TableB.nameB with condition Schema1.TableA.nameA=Schema2.TableB.abbreviated_name

Suggested fix:
Error message is wrong reported by MySQL I guess and need FIX for appropriate message if I'm doing something wrong in SQL statement.

Please provide the select query which works related to replace query which fails. Thanks.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

I don't think I am asking any product help, well the steps I followed produces an error which is not valid. Appreciate if you re-look my submission. Sample tables with data and query both attached with this ticket already.

Imho it was doing as expected, table_references clause should lists the tables involved in the join. 

mysql> update `sugarcrmt`.`accounts` set `sugarcrmt`.`accounts`.`name`=`superof5`.`contact`.name where `sugarcrmt`.`accounts`.`name`=`superof5`.`contact`.`soundEx`;
ERROR 1054 (42S22): Unknown column 'superof5.contact.soundEx' in 'where clause'
mysql> show errors;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Error | 1054 | Unknown column 'superof5.contact.soundEx' in 'where clause' |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

Below example shows an inner join that uses the comma operator:

mysql> update `sugarcrmt`.`accounts`,`superof5`.`contact` set `sugarcrmt`.`accounts`.`name`=`superof5`.`contact`.name where `sugarcrmt`.`accounts`.`name`=`superof5`.`contact`.`soundEx`;
Query OK, 816 rows affected (0.13 sec)
Rows matched: 818  Changed: 816  Warnings: 0

Please reference - https://dev.mysql.com/doc/refman/5.6/en/update.html