MySQL Bugs: #27525: table not found when using multi-table-deletes with aliases over several databas

Bug #27525	table not found when using multi-table-deletes with aliases over several databas
Submitted:	29 Mar 2007 14:53	Modified:	6 Mar 2010 18:35
Reporter:	Thomas Mayer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.33/5.1	OS:	Linux (OpenSuse 10.2)
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	1109, 42S02, alias, multi-table delete, USING

Description:
multi-table-deletes using aliases work well, in case all tables are in the same table. 

In case the tables are spanned over several databases, mysql throws an error "ERROR 1109 (42S02): Unknown table 't1' in MULTI DELETE"

How to repeat:
create database test1;
create database test2;
use test1;
create table test1.table1 (f int(10));
create table test2.table1 (f int(10));
create table test1.table2 (f int(10));
delete t1 from test1.table1 as t1 inner join table2 t2 on t1.f=t2.f;
delete t1 from test2.table1 as t1 inner join table2 t2 on t1.f=t2.f;
delete from t1 using test1.table1 as t1, table2 as t2 where t1.f=t2.f;
delete from t1 using test2.table1 as t1, table2 as t2 where t1.f=t2.f;

mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 5.0.26

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

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

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

mysql> use test1;
Database changed
mysql> create table test1.table1 (f int(10));
Query OK, 0 rows affected (0.11 sec)

mysql> create table test2.table1 (f int(10));
Query OK, 0 rows affected (0.07 sec)

mysql> create table test1.table2 (f int(10));
Query OK, 0 rows affected (0.11 sec)

mysql> delete t1 from test1.table1 as t1 inner join table2 t2 on t1.f=t2.f;
Query OK, 0 rows affected (0.00 sec)

mysql> delete t1 from test2.table1 as t1 inner join table2 t2 on t1.f=t2.f;
ERROR 1109 (42S02): Unknown table 't1' in MULTI DELETE
mysql> delete from t1 using test1.table1 as t1, table2 as t2 where t1.f=t2.f;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 using test2.table1 as t1, table2 as t2 where t1.f=t2.f;
ERROR 1109 (42S02): Unknown table 't1' in MULTI DELETE

Suggested fix:
there should not be any difference, concerning the database of the table, when using multi table deletes. So both errors should not occur.

delete from test2.table1 using test2.table1, table2 where test2.table1.f=table2.f;

This works, but I do not consider this as a solution. It's just a work-around.

With mysql 5.0.33, the problem persists

Same problem found in Debian Linux 4.0 running MySQL 5.0.32-7etch1. 

CPU info:

  vendor_id       : AuthenticAMD
  cpu family      : 6
  model           : 10
  model name      : AMD Athlon(tm) XP 2600+

Thank you for the bug report.

See also Bug #23413  "ERROR 1046 (3D000): No database selected" occurs when executing DELETE FROM

Please ensure that the patch resolves table names unambiguously, cover with tests, do not forget to alert the docs team to remove the documented limitation from the manual.
Fix in 5.2 only.

Bug exists with MySQL 5.0.46

This feature request is also dependent on a much needed fixup of the ambiguities due to a improper extension in the DELETE clause.

It shouldn't be possible to specify aliases in the tbl_name part of a DELETE clause:

DELETE [..] FROM tbl_name ..
DELETE [..] tbl_name[.*] [, tbl_name[.*]] FROM table_references
DELETE [..] FROM tbl_name[.*] [, tbl_name[.*]] USING table_references

Yet statements like:

DELETE a1 AS a1 FROM db1.t1 AS a1, db2.t2 AS a1;

are accepted. This deletes from db1.t1 if the selected database is db1
or from db2.t2 if db2 is the selected database. It gets even crazier:

USE db2;
DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1; # accepted, should not.
DELETE a1 FROM db1.t1 AS a1; # fail, should accept.
DELETE a1 FROM db2.t2 AS a1; # accepted, ok
DELETE a1 AS a1 FROM db1.t1 AS a1, db2.t2 AS a1; # accepted, should not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43096

ChangeSet@1.2575, 2008-02-27 14:56:00-03:00, davi@mysql.com +9 -0
  Bug#27525 table not found when using multi-table-deletes with aliases over several databas
  Bug#30234 Unexpected behavior using DELETE with AS and USING
  
  The multi-delete statement has a documented limitation that
  cross-database multiple-table deletes using aliases are not
  supported because it fails to find the tables by alias if it
  belongs to a different database. The problem is that when
  building the list of tables to delete from, if a database
  name is not specified (maybe an alias) it defaults to the
  name of the current selected database, making impossible to
  to properly resolve tables by alias later. Another problem
  is a inconsistency of the multiple table delete syntax that
  permits ambiguities in a delete statement (aliases that refer
  to multiple different tables or vice-versa).
  
  The first step for a solution and proper implementation of
  the cross-databse multiple table delete is to get rid of any
  ambiguities in a multiple table statement. Currently, the parser
  is accepting multiple table delete statements that have no obvious
  meaning, such as:
  
  DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1;
  DELETE a1 AS a1 FROM db1.t1 AS a1, db2.t2 AS a1;
  
  The solution is to resolve the left part of a delete statement
  using the right part, if the a table on right has an alias,
  it must be referenced in the left using the given alias. Also,
  each table on the left side must match unambiguously only one
  table in the right side.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43122

ChangeSet@1.2575, 2008-02-27 19:59:48-03:00, davi@mysql.com +9 -0
  Bug#27525 table not found when using multi-table-deletes with aliases over several databas
  Bug#30234 Unexpected behavior using DELETE with AS and USING
  
  The multi-delete statement has a documented limitation that
  cross-database multiple-table deletes using aliases are not
  supported because it fails to find the tables by alias if it
  belongs to a different database. The problem is that when
  building the list of tables to delete from, if a database
  name is not specified (maybe an alias) it defaults to the
  name of the current selected database, making impossible to
  to properly resolve tables by alias later. Another problem
  is a inconsistency of the multiple table delete syntax that
  permits ambiguities in a delete statement (aliases that refer
  to multiple different tables or vice-versa).
  
  The first step for a solution and proper implementation of
  the cross-databse multiple table delete is to get rid of any
  ambiguities in a multiple table statement. Currently, the parser
  is accepting multiple table delete statements that have no obvious
  meaning, such as:
  
  DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1;
  DELETE a1 AS a1 FROM db1.t1 AS a1, db2.t2 AS a1;
  
  The solution is to resolve the left part of a delete statement
  using the right part, if the a table on right has an alias,
  it must be referenced in the left using the given alias. Also,
  each table on the left side must match unambiguously only one
  table in the right side.

Queued in 6.0-runtime

Pushed into 6.0.5-alpha

Noted in 6.0.5 changelog.

Several changes were made to the processing of multiple-table DELETE
statements: 

- Statements could not perform cross-database deletes unless the tables
were referred to without using aliases. This limitation has been
lifted and table aliases now are allowed.
        
- Previously, alias declarations could be given for tables elsewhere
than in the table_references part of the syntax. This could lead to
ambiguous statements that have unexpected results such as deleting
rows from the wrong table. Example:

DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;

Now alias declarations can be declared only in the table_references
part. Elsewhere in the statement, alias references are allowed but
not alias declarations.

- Alias resolution was improved so that it is no longer possible to
have inconsistent or ambiguous aliases for tables.

Statements containing alias constructs that are no longer allowed
must be rewritten.

New information on version 5.0.45

This bug can be reproduced without using a multi-database query.  I was able to do this while testing XMB.  I created a second copy of the database to use for testing DELETE statements.  While testing only the second copy...

DELETE xmb_attachments, xmb_posts FROM xmb_posts AS p LEFT JOIN xmb_attachments USING(pid) WHERE p.author='kuba1'

...MySQL Threw...

#1109 - Unknown table 'xmb_posts' in MULTI DELETE

I repeated the statement several times in PHP and phpMyAdmin with the same result.

The error disappeared when I separated the statement as:

DELETE xmb_attachments FROM xmb_posts AS p LEFT JOIN xmb_attachments USING(pid) WHERE p.author='kuba1'

DELETE FROM xmb_posts WHERE author='kuba1'

I hope I'm not just repeating what was said earlier.  My scenario seemed much simpler than the multi-database ambiguous alias case.

Robert Chapin
Chapin Information Services, Inc.

My comment might duplicate http://bugs.mysql.com/bug.php?id=4630

Bug #45656 was marked as duplicate of this one.

Re-closed as fix exists in Azalea.

The patch had incompatible changes, shouldn't be back-ported to 5.1.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89996

2919 Davi Arnaut	2009-11-10
      Backport of Bug#27525 to mysql-next-mr
      ------------------------------------------------------------
      revno: 2572.2.1
      revision-id: sp1r-davi@mysql.com/endora.local-20080227225948-16317
      parent: sp1r-anozdrin/alik@quad.-20080226165712-10409
      committer: davi@mysql.com/endora.local
      timestamp: Wed 2008-02-27 19:59:48 -0300
      message:
        Bug#27525 table not found when using multi-table-deletes with aliases over several databas
        Bug#30234 Unexpected behavior using DELETE with AS and USING
      
        The multi-delete statement has a documented limitation that
        cross-database multiple-table deletes using aliases are not
        supported because it fails to find the tables by alias if it
        belongs to a different database. The problem is that when
        building the list of tables to delete from, if a database
        name is not specified (maybe an alias) it defaults to the
        name of the current selected database, making impossible to
        to properly resolve tables by alias later. Another problem
        is a inconsistency of the multiple table delete syntax that
        permits ambiguities in a delete statement (aliases that refer
        to multiple different tables or vice-versa).
      
        The first step for a solution and proper implementation of
        the cross-databse multiple table delete is to get rid of any
        ambiguities in a multiple table statement. Currently, the parser
        is accepting multiple table delete statements that have no obvious
        meaning, such as:
      
        DELETE a1 FROM db1.t1 AS a1, db2.t2 AS a1;
        DELETE a1 AS a1 FROM db1.t1 AS a1, db2.t2 AS a1;
      
        The solution is to resolve the left part of a delete statement
        using the right part, if the a table on right has an alias,
        it must be referenced in the left using the given alias. Also,
        each table on the left side must match unambiguously only one
        table in the right side.
     @ mysql-test/r/delete.result
        Add test case result for Bug#27525 and Bug#21148
     @ mysql-test/r/derived.result
        Update error.
     @ mysql-test/suite/rpl/r/rpl_multi_delete2.result
        Update syntax.
     @ mysql-test/suite/rpl/t/rpl_multi_delete2.test
        Update syntax.
     @ mysql-test/t/delete.test
        Add test case for Bug#27525 and Bug#21148
     @ mysql-test/t/derived.test
        Update statement error, alias is properly resolved now.
     @ sql/sql_parse.cc
        Implement new algorithm for the resolution of alias in
        a multiple table delete statement.
     @ sql/sql_yacc.yy
        Rework multi-delete parser rules to not accept table alias
        for the table source list.
     @ sql/table.h
        Add flag to signal that the table has a alias set or
        that fully qualified table name was given.

Queued to mysql-next-mr-runtime

Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:13)

Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:13)

Noted in 5.6.0 changelog.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:davi.arnaut@sun.com-20090522170916-fzc5ca3tjs9roy1t) (merge vers: 6.0.12-alpha) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.

Revised changelog entry.

Several changes were made to alias resolution in multiple-table
DELETE statements so that it is no longer possible to have
inconsistent or ambiguous table aliases.

* In MySQL 5.1.23, alias declarations outside the table_references part
  of the statement were disallowed for the USING variant of
  multiple-table DELETE syntax, to reduce the possibility of ambiguous
  aliases that could lead to ambiguous statements that have unexpected
  results such as deleting rows from the wrong table.

  Now alias declarations outside table_references are disallowed for
  all multiple-table DELETE statements. Alias declarations are
  permitted only in the table_references part.
          
  Incorrect:
          
  DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;
  DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;

  Correct:

  DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2;
  DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2;

* Previously, for alias references in the list of tables from which to
  delete rows in a multiple-table delete, the default database is used
  unless one is specified explicitly. For example, if the default
  database is db1, the following statement does not work because the
  unqualified alias reference a2 is interpreted as having a database of
  db1:

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

  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;

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

Statements containing alias constructs that are no longer permitted
must be rewritten.

MySQL version 5.5.11

With no default database selected and attempting to use multi delete

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

Error : Unknown table 'a1' in MULTI DELETE

Aaron,

thank you for the feedback. This is correct behavior after this bug fix. You should not prefix alias with database name:

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