MySQL Bugs: #16252: multi table delete does not support table aliases any more in 5.0

Bug #16252	multi table delete does not support table aliases any more in 5.0
Submitted:	6 Jan 2006 14:23	Modified:	6 Jan 2006 16:10
Reporter:	Thomas Mayer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15	OS:	Windows (windows xp)
Assigned to:		CPU Architecture:	Any

Description:
Tried the following on mysql 4.0.25 and 5.0.15 with unexpected results in 5.0.15:

Seems 5.0.15 can't handle multiple table deletes with table aliases any more.
The same error occurse when using the "DELETE FROM ... USING ..." syntax.
I don't think it's because of the OS (Linux/Windows), as done in the test case.

How to repeat:

use test
create table table1 (field1 int(10));
create table table2 (field2 int(10));
delete table1 from table1, table2 where table1.field1=table2.field2;
delete table1 from table1 t1, table2 t2 where t1.field1=t2.field2;

(execution on mysql 4.0.25 with Linux)
mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.25-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> create table table1 (field1 int(10));
Query OK, 0 rows affected (0.07 sec)

mysql> create table table2 (field2 int(10));
Query OK, 0 rows affected (0.04 sec)

mysql> delete table1 from table1, table2 where table1.field1=table2.field2;
Query OK, 0 rows affected (0.00 sec)

mysql> delete table1 from table1 t1, table2 t2 where t1.field1=t2.field2;
Query OK, 0 rows affected (0.00 sec)
-->THIS WORKS.

(execution on mysql 4.0.25 with Windows)
mysql>
C:\Programme\XAMPP\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64 to server version: 5.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> create table table1 (field1 int(10));
Query OK, 0 rows affected (0.05 sec)

mysql> create table table2 (field2 int(10));
Query OK, 0 rows affected (0.06 sec)

mysql> delete table1 from table1, table2 where table1.field1=table2.field2;
Query OK, 0 rows affected (0.00 sec)

mysql> delete table1 from table1 t1, table2 t2 where t1.field1=t2.field2;
ERROR 1109 (42S02): Unknown table 'table1' in MULTI DELETE
mysql>
--> THIS DOES NOT WORK ANY MORE

Suggested fix:
table aliases should work with both:
DELETE ... FROM ... WHERE...;
DELETE FROM ... USING ... WHERE ...;

of course the second execution on windows was done with mysql 5.0.15, not 4.0.25 as accidently written...

Thank you for a problem report. It looks like intended and documented behaviour in 5.0.x. Please, read http://dev.mysql.com/doc/refman/5.0/en/delete.html:

"Note: You must use the alias (if one was given) when referring to a table name:

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 ..."

And http://dev.mysql.com/doc/refman/4.1/en/delete.html:

"Note: In MySQL 4.0, you should refer to the table names to be deleted with the true table name. In MySQL 4.1, you must use the alias (if one was given) when referring to a table name:

In MySQL 4.0:

DELETE test FROM test AS t1, test2 WHERE ...

In MySQL 4.1:

DELETE t1 FROM test AS t1, test2 WHERE ...

The reason we did not make this change in 4.0 was to avoid breaking any old 4.0 applications that were using the old syntax."