MySQL Bugs: #61366: table not found when using multi-table delete with alias no default table select

Bug #61366	table not found when using multi-table delete with alias no default table select
Submitted:	31 May 2011 18:31	Modified:	1 Jun 2011 13:24
Reporter:	Aaron Conway	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.11	OS:	Linux (CentOS 5.6)
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	1109, alias, multi-table delete, regression

Description:
This is a continuation of a closed bug http://bugs.mysql.com/bug.php?id=27525.  When attempting to delete records from multiple-tables across multiple databases and no default database is selected an "Unknown Table" error is returned.  In older version there was no issue.

How to repeat:
create database db1;
create database db2;
create table db1.t1 (id int(10));
create table db2.t2 (id int(10));
DELETE db1.a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 ON a1.id = a2.id

Suggested fix:
No default table should be required when deleting across multiple databases and multiple tables.

Thank you for the bug report.

Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation.  All rights reserved.

C:\DBS>51

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.58-Win X64-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >create database db1;
Query OK, 1 row affected (0.02 sec)

mysql 5.1 >create database db2;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >create table db1.t1 (id int(10));
Query OK, 0 rows affected (0.12 sec)

mysql 5.1 >create table db2.t2 (id int(10));
Query OK, 0 rows affected (0.04 sec)

mysql 5.1 >DELETE db1.a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 ON a1.id = a2.id;
Query OK, 0 rows affected (0.02 sec)

mysql 5.1 >exit
Bye

C:\DBS>55

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.14-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >DELETE db1.a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 ON a1.id = a2.id;
ERROR 1109 (42S02): Unknown table 'a1' in MULTI DELETE
mysql 5.5 >

Quoting from the multiple-table DELETE documentation at http://dev.mysql.com/doc/refman/5.5/en/delete.html

"As of MySQL 5.5.3, alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases."

The statement:

DELETE db1.a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 ON a1.id = a2.id;

should be rewritten to:

DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 ON a1.id = a2.id;

DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 ON a1.id = a2.id;

ERROR 1046 (3D000): No database selected

I get the same error with a simple DELETE statement, MySQL 5.1.58:

mysql> DELETE a1 FROM db1.t1 AS a1 where a1.id = 2;
ERROR 1046 (3D000): No database selected

So, not particular to multiple-table delete.

I've reported Bug#61376 to track the default table issue.