MySQL Bugs: #52921: invalid syntax for cross database deletes using aliases

Bug #52921	invalid syntax for cross database deletes using aliases
Submitted:	17 Apr 2010 18:55	Modified:	4 Sep 2010 15:22
Reporter:	Roland Volkmann	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.5.3-m3, 5.5.5-m3	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	alias, cross database, delete

Description:
On version 5.5.3-m3 the required syntax for cross database deletes using aliases is invalid relating to documentation (end of chapter 12.2.2) and earlier versions of mysql. This prevents e.g. restoring a dump from version 5.1 which contains stored procedures using cross database deletes with aliases.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.3-m3-community MySQL Community Server (GPL)

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

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

mysql> create table db1.t1 (id integer);
Query OK, 0 rows affected (0.11 sec)

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

mysql> create table db2.t2 (id integer);
Query OK, 0 rows affected (0.00 sec)

mysql> delete db1.a1 from db1.t1 as a1 inner join db2.t2 as a2 on a2.id=a1.id;
ERROR 1109 (42S02): Unknown table 'a1' in MULTI DELETE
mysql> delete a1 from db1.t1 as a1 inner join db2.t2 as a2 on a2.id=a1.id;
Query OK, 0 rows affected (0.03 sec)

mysql>

*** the same steps with version 5.5.2-m2 or 5.1.45 ***

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.45-community MySQL Community Server (GPL)

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

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

mysql> create table db1.t1 (id integer);
Query OK, 0 rows affected (0.02 sec)

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

mysql> create table db2.t2 (id integer);
Query OK, 0 rows affected (0.02 sec)

mysql> delete db1.a1 from db1.t1 as a1 inner join db2.t2 as a2 on a2.id=a1.id;
Query OK, 0 rows affected (0.00 sec)

mysql> delete a1 from db1.t1 as a1 inner join db2.t2 as a2 on a2.id=a1.id;
ERROR 1046 (3D000): No database selected
mysql>

Thank you for the bug report. Verified just as described:

77-52-4-109:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.47-debug 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> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql>  create table db1.t1 (id integer);
Query OK, 0 rows affected (0.06 sec)

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

mysql>  create table db2.t2 (id integer);
Query OK, 0 rows affected (0.08 sec)

mysql>  delete db1.a1 from db1.t1 as a1 inner join db2.t2 as a2 on a2.id=a1.id;
Query OK, 0 rows affected (0.01 sec)

mysql>  delete a1 from db1.t1 as a1 inner join db2.t2 as a2 on a2.id=a1.id;
ERROR 1109 (42S02): Unknown table 'a1' in MULTI DELETE

Now, compare this to recent 5.5.x:

77-52-4-109:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.5-m3-debug 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> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql>  create table db1.t1 (id integer);
Query OK, 0 rows affected (0.10 sec)

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

mysql>  create table db2.t2 (id integer);
Query OK, 0 rows affected (0.05 sec)

mysql>  delete db1.a1 from db1.t1 as a1 inner join db2.t2 as a2 on a2.id=a1.id;
ERROR 1109 (42S02): Unknown table 'a1' in MULTI DELETE
mysql>  delete a1 from db1.t1 as a1 inner join db2.t2 as a2 on a2.id=a1.id;
Query OK, 0 rows affected (0.00 sec)

5.5.x behaviour contradicts the manual, http://dev.mysql.com/doc/refman/5.5/en/delete.html.

I believe this is a documentation problem as there is a contradiction in the explanations:

"If you declare an alias for a table, you must use the alias when referring to the table"

The part that begins with "Cross-database deletes are supported for multiple-table deletes .." should have been removed when Bug#27525 was fixed.

If you will handle this as a documentation problem, what about update scenarios for existing 5.1 environments using such cross database deletes with aliases in client program code and/or stored procedures on server side? Such incompatible changes without a transitional period, where both syntax is accepted, will result in trouble upgrading complex/distributed systems.

Every syntax change has the potential to break something and we approach it with great care. Unfortunately, in this case, the old syntax was ambiguous and, as a consequence, it is unfeasible to support both methods of alias resolution as the new one is based upon assumptions that can't guaranteed if the old syntax is also present. It ends up cutting the transitional period short (between major releases), but weighting the options, providing a consistent syntax free of ambiguity that could lead silent problems (ie delete from wrong table) seemed the right decision. As for complex upgrade scenarios, the usual best practices apply -- checking the list of incompatible changes, appropriate testing, etc.

Bug #56215 was marked as duplicate of this one.

Here is the situation with aliases and multiple-table DELETE.

These are the relevant bugs:
Bug#30234: Pushed in 5.0.50, reverted in 5.0.54, Pushed in 5.1.23.
Bug#27525: Pushed in 5.5.3, 6.0.5.

These notes apply only to multiple-table DELETE. Also, "alias
reference" means "alias reference in the list of tables from which
to delete rows", i.e., in the first (leftmost) table list of the
syntax.

Considering the declaration and reference issues separately, we have this:

Whether alias declarations are allowed outside table_references:

Before 5.1.23: Allowed

5.1.23 - 5.5.2: Non-USING variant: Allowed; USING variant: Not allowed

5.5.3+: Not allowed

Whether qualified alias references are required (or permitted) for
tables outside default database:

Before 5.5.3: Must be qualified 

5.5.3+: Must not be qualified. Qualified names refer to tables, not aliases.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated DELETE sections:

http://dev.mysql.com/doc/refman/5.1/en/delete.html
http://dev.mysql.com/doc/refman/5.5/en/delete.html
http://dev.mysql.com/doc/refman/5.6/en/delete.html