Bug #8049 MULTI DELETE does not see aliased temporary tables

• Submitted: 20 Jan 2005 16:00
• Modified: 21 Jan 2005 4:42
• Reporter: Fyodor Golos
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.9-standard
• OS: AMD64
• Assigned to: Dean Ellis
• CPU Architecture: Any

[20 Jan 2005 16:00] Fyodor Golos

Description:
It appears that "DELETE FROM ... USING ... WHERE ..." operations do not see temporary tables if they are aliased. Explicitly specifying table names in the WHERE clause works.

How to repeat:
create tables:

CREATE TEMPORARY TABLE temp_a (a INT);
CREATE TEMPORARY TABLE temp_b (b INT);

this fails (ERROR 1109 (42S02): Unknown table 'temp_a' in MULTI DELETE):

DELETE FROM temp_a USING temp_a a, temp_b b WHERE a.a = b.b;

this works:

DELETE FROM temp_a USING temp_a, temp_b WHERE temp_a.a = temp_b.b;

Suggested fix:
Allow using aliases in MULTI DELETE operations that involve temporary tables.

[20 Jan 2005 16:46] MySQL Verification Team

Duplicate for #7816

[20 Jan 2005 20:34] Fyodor Golos

Are you sure about this issue being a duplicate of #7816?

Are temporary tables created in a database different from current database? Some "virtual" database that holds temporary tables?

Even so, second DELETE works just fine and the only difference from the first DELETE is not using table aliases. Does not seem to have anything to do with being in the "wrong" database when running a cross-database delete.

[20 Jan 2005 23:27] MySQL Verification Team

Verified with BK source on Slackware Linux.

Thank you for the bug report.

[21 Jan 2005 4:42] Dean Ellis

If you alias the table, your DELETE must refer to the alias.

ie: DELETE FROM a USING temp_a a, temp_b b WHERE a.a = b.b;