Bug #56215 DELETE FROM db.alias USING db.table AS alias - fails to find alias
Submitted: 24 Aug 2010 9:35 Modified: 24 Aug 2010 14:22
Reporter: Jordi Boggiano Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.5.5-m3 (Win32/x86), 5.6.99 bzr OS:Any (MS Win7 x64, Linux)
Assigned to: Assigned Account CPU Architecture:Any
Tags: alias, delete, regression

[24 Aug 2010 9:35] Jordi Boggiano
Description:
I am doing a DELETE FROM * USING * query and it fails on 5.5, while working on 5.1/5.0, if I remove the database qualifier in front of the alias it works in 5.5 but then fails on the lower versions, so I'm stuck in compatibility hell between all our versions on various dev machines.

How to repeat:
Running:

DELETE FROM db1.alias
USING db1.test AS alias, test2 AS alias2
WHERE alias.id = alias2.id

Produces:

Unknown table 'alias' in MULTI DELETE

However removing the db qualifier from the first line makes it works, as such:

DELETE FROM alias
USING db1.test AS alias, test2 AS alias2
WHERE alias.id = alias2.id

Suggested fix:
It should work with the db qualifier imo, there is no reason for this behavior and even the docs say you can or should specify the db depending on the case.
[24 Aug 2010 10:35] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Alias is not part of particular database, but just new name for the element. So it is expected you get "unknown table" error.
[24 Aug 2010 10:46] Sveta Smirnova
Please ignore my last comment: it contradicts with last example at http://dev.mysql.com/doc/refman/5.5/en/delete.html

Verified as described. Not repeatable in earlier versions, so regression.
[24 Aug 2010 10:47] Sveta Smirnova
Workaround for 5.5. series:

DELETE FROM alias
USING db1.test AS alias, test2 AS alias2
WHERE alias.id = alias2.id;
[24 Aug 2010 11:04] Jordi Boggiano
Yes, the workaround was in my report already, and the problem is that the workaround fails on 5.1/5.0 with our multi-database query, and our production boxes run 5.0, while dev machines range from 5.0 to 5.5, so it's sadly not as easy as just throwing in that workaround.
[24 Aug 2010 13:57] Davi Arnaut
This behavior was changed on purpose in the context of Bug#27525. There is also Bug#52921 which is about fixing the documentation, so closing this as a duplicate.