Bug #7816 Cross database delete not 100% functional

• Submitted: 11 Jan 2005 18:51
• Modified: 29 Sep 2008 21:07
• Reporter: [ name withheld ]
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.7 and 4.1.8a tested
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[11 Jan 2005 18:51] [ name withheld ]

Description:
When trying to delete rows from a table in a database other than the current one (selected with the USE command), when joining with data that _is_ in the current database, MySQL fails (see example query 1).

When deleting from a table in the current database, and joining one or more tables from another database to find out what to delete, everything works like a charm (see example query 2).

The MySQL manual page on DELETE does noet mention whether MySQL supports cross-database deletes, so I am not sure whether this behavior is by design or a bug.

How to repeat:
CREATE DATABASE `test1` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE test1;

CREATE TABLE `tmp1` (
  `id` int(2) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE DATABASE `test2` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE test2;

CREATE TABLE `tmp2` (
  `id` int(2) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

USE test2;
# EXAMPLE 1
DELETE t1 FROM test1.tmp1 t1, test2.tmp2 t2 WHERE t1.id=t2.id AND t2.id>5
#1109 - Unknown table 't1' in MULTI DELETE 

# EXAMPLE 2
DELETE t2 FROM test1.tmp1 t1, test2.tmp2 t2 WHERE t1.id=t2.id AND t2.id>5
# WORKS

USE test1;
# EXAMPLE 3
DELETE t2 FROM test1.tmp1 t1, test2.tmp2 t2 WHERE t1.id=t2.id AND t2.id>5
#1109 - Unknown table 't1' in MULTI DELETE

[2 Feb 2005 5:21] Sergey Petrunya

Current MySQL does support cross-database delete, but you need to specify the tables without using aliases: 
delete test1.tmp1, test2.tmp2 from test1.tmp1, test2.tmp2

[3 Feb 2005 0:37] Sergey Petrunya

We decided not to make any changes in 4.x versions.
We intend to improve handling of aliases with multitable delete in version 5.x, but don't set any date or version for this change yet.
Passing this to docs team to update the documentation as they see appropriate.

[17 Feb 2005 17:26] Paul DuBois

I've noted the restriction in the DELETE section of the manual.

[28 Nov 2006 13:12] Alexey Zakhlestine

5.0.27

this is still not fixed

[29 Sep 2008 21:07] Konstantin Osipov

Fixed in 6.0 (See Bug#30234)