Description:
Multi-delete fails to find the table by alias if it belongs to a different database
mysql> create database mysqltest1;
Query OK, 1 row affected (0.02 sec)
mysql> use mysqltest1;
Database changed
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.03 sec)
mysql> create database mysqltest2;
Query OK, 1 row affected (0.00 sec)
mysql> use mysqltest2;
Database changed
mysql> create table t2 (a int);
Query OK, 0 rows affected (0.01 sec)
mysql> use mysqltest1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> delete p1.*, p2.* from t1 as p1 inner join mysqltest2.t2 as p2 on p1.a = p2.a;
ERROR 1109 (42S02): Unknown table 'p2' in MULTI DELETE
-- As one can see, everything works okay if the database of t1 and t2 is the same:
mysql> alter table mysqltest2.t2 rename mysqltest1.t2;
Query OK, 0 rows affected (0.00 sec)
mysql> delete p1.*, p2.* from t1 as p1 inner join mysqltest1.t2 as p2 on p1.a = p2.a;
Query OK, 0 rows affected (0.00 sec)
How to repeat:
create database mysqltest1;
use mysqltest1;
create table t1 (a int);
create database mysqltest2;
use mysqltest2;
create table t2 (a int);
use mysqltest1;
delete p1.*, p2.* from t1 as p1 inner join mysqltest2.t2 as p2 on p1.a = p2.a;
Suggested fix:
Fix function multi_delete_set_locks_and_link_aux_tables to properly resolve tables by alias.
This is not a bug but a documented limitation according to
http://dev.mysql.com/doc/refman/5.0/en/delete.html
Note: If you provide an alias for a table, you must use the alias when referring to the table:
DELETE t1 FROM test AS t1, test2 WHERE ...
Cross-database deletes are supported for multiple-table deletes, but in this case, you must refer to the tables without using aliases. For example:
DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...
