MySQL Bugs: #30234: Unexpected behavior using DELETE with AS and USING

Bug #30234	Unexpected behavior using DELETE with AS and USING
Submitted:	3 Aug 2007 21:39	Modified:	18 Apr 2008 20:56
Reporter:	Dan Rue
Status:	Closed
Category:	Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.41/5.1/4.1	OS:	Any
Assigned to:	Davi Arnaut	Target Version:
Tags:	delete, as, USING
Triage:	D1 (Critical)

Description:
When executing the following query: 

DELETE FROM foo AS b 
USING foo AS f, bar AS b 
WHERE f.id=b.id;

The most unexpected behavior happens.  It actually will delete the matching records from
the bar table!  This is completely unexpected, and extremely dangerous.

How to repeat:
DROP TABLE IF EXISTS foo;
DROP TABLE IF EXISTS bar;

CREATE TABLE foo (
|       id INT
);
INSERT INTO foo (id) VALUES (1);
INSERT INTO foo (id) VALUES (2);
INSERT INTO foo (id) VALUES (3);
INSERT INTO foo (id) VALUES (4);
INSERT INTO foo (id) VALUES (5);

CREATE TABLE bar (
|       id INT
);
INSERT INTO bar (id) VALUES (4);
INSERT INTO bar (id) VALUES (5);
INSERT INTO bar (id) VALUES (6);
INSERT INTO bar (id) VALUES (7);
INSERT INTO bar (id) VALUES (8);

-- Show the overlapping rows
SELECT foo.* FROM foo, bar WHERE foo.id=bar.id;

-- What do you think will happen?  
DELETE FROM foo AS b USING foo AS f, bar AS b WHERE f.id=b.id;

-- User intended to delete records 4 and 5 from foo, but nothing was deleted.
SELECT * FROM foo;

-- Instead, 4 and 5 were deleted from bar.  How astonishing!
SELECT * FROM bar;

Suggested fix:
Simple: it should be a syntax error to cross AS references.

Thank you for the bug report.

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/32557

ChangeSet@1.2502, 2007-08-15 14:41:19+02:00, mhansson@linux-st28.site +4 -0
  Bug #30234: Unexpected behavior using DELETE with AS and USING
  
  DELETE statements with the following type of non-unique aliases
  gave unexpected results:
  DELETE FROM t1 AS alias USING t1, t2 AS alias WHERE t1.a = alias.a;
  This query would leave table t1 intact but delete rows from t2.
  Fixed by prohibiting non-unique aliases.

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/33024

ChangeSet@1.2502, 2007-08-24 15:40:16+02:00, mhansson@linux-st28.site +4 -0
  Bug #30234: Unexpected behavior using DELETE with AS and USING
  
  DELETE FROM ... USING ... statements with the following type of 
  ambiguous aliasing gave unexpected results:
  DELETE FROM t1 AS alias USING t1, t2 AS alias WHERE t1.a = alias.a;
  This query would leave table t1 intact but delete rows from t2.
  Fixed by changing DELETE FROM ... USING syntax so that only alias 
  references (as opposed to alias declarations) may be used in FROM.

Trudy says the following about which version to push this bug to:

I've looked at bug#30234. It's tagged for 5.2 so if
you feel the patch is too big/risky for 5.1, then
please push only to 5.2. It would be great if this
can be fixed in 5.1, of course, but there are
workarounds for the functionality.

Note: If you decide to fix only in 5.2, please ask
Docs team to provide some warning in the Reference
Manual that deleting from multiple tables in this
way doesn't work. Thanks!

Patch approved by email

Pushed into 5.1.23-beta

Pushed into 5.0.50

Noted in 5.0.50, 5.1.23 changelogs.

Multiple-table DELETE statements could delete rows from the wrong
table.

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/38935

ChangeSet@1.2589, 2007-11-30 10:11:46+01:00, mhansson@linux-st28.site +3 -0
  Bug#30234: Unexpected behavior using DELETE with AS and USING
  
  Anti-patch. This patch undoes the previously pushed patch.

There, an 'undo' patch is committed. I will check in with  the reviewers if this should be
pushed to 5.0.

In order to properly implement Bug#27525 it's needed that a DELETE statement must have no
ambiguous alias. This bug only fixed it for one of the forms of a DELETE statement -- the
 DELETE FROM <list> USING <list> WHERE ..., and the DELETE <list> FROM <list> WHERE ...
form was left unfixed, so this bug fix is incomplete.

So I'm taking over this bug to fix both DELETE forms and to address the ambiguous cases,
as it is a necessary step for Bug#27525.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Patch committed in Bug#27525

Queued in 6.0-runtime

  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
  Bug#30234 Unexpected behavior using DELETE with AS and USING

Pushed into 6.0.5-alpha

There are multiple changelog entries because this bugfix has a more complicated history
than most:

- Original push was into 5.0.50 and 5.1.23.
- The 5.0.50 push was reverted in 5.0.54, leaving the 5.0 server unchanged relative to
pre-5.0.50.
- The 5.1.23 push is not reverted (the second push into 5.1.23 and 6.0.5 was a null
merge)

Changelog entry for 5.0.50:

Multiple-table DELETE statements containing ambiguous aliases could
have unintended side effects such as deleting rows from the wrong 
table. Example:  

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

This bugfix enables alias declarations to be declared only in the
table_references part. Elsewhere in the statement, alias references
are allowed but not alias declarations. However, this patch was
reverted in MySQL 5.0.54 because it changed the behavior of a General
Availability MySQL release.

Changelog entry for 5.0.54:

The MySQL 5.0.50 patch for this bug was reverted because it changed
the behavior of a General Availability MySQL release.

Changelog entry for 5.1.23:

Multiple-table DELETE statements containing ambiguous aliases could
have unintended side effects 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. Statements containing aliases that are no
longer allowed must be rewritten.

The work begun for this bug report was extended in 6.0 by Bug#27525, which see.

See bug: http://bugs.mysql.com/bug.php?id=46379.

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.