Bug #21148 MULTI-DELETE fails to resolve a table by alias if it's from a different database
Submitted: 19 Jul 2006 13:09
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version: OS:
Assigned to:

[19 Jul 2006 13:09] Konstantin Osipov
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 ...
[15 Mar 2007 18:11] Hans Ginzel
Why the limitation? What is it good for?