Bug #26370 Multi-table delete does not work across databases
Submitted: 14 Feb 2007 15:06 Modified: 15 Feb 2007 12:33
Reporter: Joshua Butcher Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.33 5.1.15 OS:
Assigned to: Assigned Account CPU Architecture:Any
Tags: Multi-table delete does not work across databases

[14 Feb 2007 15:06] Joshua Butcher
Description:
Joining tables across different schema's for deletion purposes causes MySQL to barf.

How to repeat:
CREATE DATABASE db1;
CREATE DATABASE db2;

USE db1;

CREATE TABLE `db1`.`t1` (
  `a` INT NOT NULL,
  `b` INT,
  PRIMARY KEY (`a`)
)
CHARACTER SET utf8;

CREATE TABLE `db2`.`t2` (
  `a` INT NOT NULL,
  `c` INT,
  PRIMARY KEY (`a`)
)
CHARACTER SET utf8;

Insert into db1.t1 (a,b) VALUES(1,1);
Insert into db2.t2 (a,c) VALUES(1,2);

Delete x,y from db1.t1 x Inner Join db2.t2 y on x.a = y.a Where x.a=1;

DROP TABLE db1.t1;
DROP TABLE db2.t2;

DROP DATABASE db1;
DROP DATABASE db2;

Suggested fix:
Make it so this works.
[14 Feb 2007 17:18] Andy Bang
This is a MySQL server issue, not a Merlin issue.
[15 Feb 2007 12:33] Sveta Smirnova
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of Bug #23413