DELETE with alias reference impossible without selecting a db since MySQL 5.5.3?

Question

I used to do complex multi-table DELETEs like this:

DELETE db1.t1 FROM db1.table1 AS t1, db1.table2 AS t2...

now after upgrading to MySQL 5.5.5 that doesn't work anymore because according to documentation since 5.5.3 alias names are not allowed to be qualified with db name in a DELETE statement. so I should do this:

DELETE t1 FROM db1.table1 AS t1, db1.table2 AS t2...

which doesn't work either due to bug #61376 and gives ERROR 1046 (3D000): No database selected.

I don't USE a database because my code should be integrateable in unknown environments and selecting a db might break existing code.

Should I assume that DELETE statement with alias reference is impossible without selecting a database since MySQL 5.5.3 and I should start looking for a dirty hack or do you know a clean solution to this?

---

update:

Adding a USE db1 before this query when there is no db selected (Bill Karwin's suggestion):

DELETE t1 FROM db1.table1 AS t1, db1.table2 AS t2...

makes it work in 5.5.5, but both in 4.1 and 5.1 it gives this error:

ERROR 1109 (42S02): Unknown table 't1' in MULTI DELETE

I think for now I'm going to give up using aliases altogether and instead use full db.table name everywhere which makes the queries big and unreadable but I can't think of any better solution.

Answer 1

I would just ready a default database. It's unfortunate that it's necessary, but USE db1 is really not a heavyweight statement to run, compared to any actual query that accesses a table.

You don't have to do two queries for every DELETE. The SELECT DATABASE() shown in the bug report isn't necessary, it's just showing that no default database is selected.

You only need to ready a default database once per session, e.g. right after you connect, not before every statement.

---

Re your comment:

If you're USEing another database, then the error goes away. It's only when you have no database as your default that you have an error with the aliases. You don't necessarily have to ready db1 to delete from it.

I don't have time to test all the permutations right now, so part of this is based on assumption.

---

Re your update:

MySQL has changed behavior a couple of times over the years. I suggest you recommend upgrade any MySQL prior to 5.1, and make sure you're using the InnoDB plugin. Not only will it simplify your development, but you'll gain the scalability improvements they've made in recent years.

MySQL 4.1/5.0/5.1 says:

As of MySQL 4.1.2, aliases can be used, but for alias references in the list of tables from which to delete rows, the default database is used unless one is specified explicitly.

To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database:

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

MySQL 5.5 says:

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.

So you're right, you can't uses aliases in a multi-table delete spanning multiple databases, and have the same code work against 4.1/5.0/5.1 versus 5.5. The workaround is to skip aliases and give the full names of tables, qualified by database.

This should work, except for cases when you have a multi-table DELETE involving both a self-join and a cross-database join.