| Bug #30234 | Unexpected behavior using DELETE with AS and USING | ||
|---|---|---|---|
| Submitted: | 3 Aug 2007 19:39 | Modified: | 18 Apr 2008 18:56 |
| Reporter: | Dan Rue | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0.41/5.1/4.1 | OS: | Any |
| Assigned to: | Davi Arnaut | CPU Architecture: | Any |
| Tags: | as, delete, USING | ||
[3 Aug 2007 19:39]
Dan Rue
[3 Aug 2007 20:42]
MySQL Verification Team
Thank you for the bug report.
[15 Aug 2007 12:41]
Bugs System
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.
[24 Aug 2007 13:39]
Bugs System
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.
[30 Aug 2007 7:04]
Martin Hansson
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!
[31 Aug 2007 1:00]
Marc ALFF
Patch approved by email
[14 Sep 2007 7:45]
Bugs System
Pushed into 5.1.23-beta
[14 Sep 2007 7:46]
Bugs System
Pushed into 5.0.50
[9 Oct 2007 0:52]
Paul DuBois
Noted in 5.0.50, 5.1.23 changelogs. Multiple-table DELETE statements could delete rows from the wrong table.
[30 Nov 2007 9:11]
Bugs System
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.
[30 Nov 2007 9:14]
Martin Hansson
There, an 'undo' patch is committed. I will check in with the reviewers if this should be pushed to 5.0.
[5 Dec 2007 19:13]
Davi Arnaut
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.
[14 Dec 2007 8:14]
Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:17]
Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:20]
Bugs System
Pushed into 6.0.5-alpha
[19 Dec 2007 19:32]
Davi Arnaut
Patch committed in Bug#27525
[27 Feb 2008 23:14]
Davi Arnaut
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
[3 Mar 2008 18:19]
Bugs System
Pushed into 6.0.5-alpha
[18 Apr 2008 18:56]
Paul DuBois
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.
[18 Apr 2008 18:57]
Paul DuBois
The work begun for this bug report was extended in 6.0 by Bug#27525, which see.
[25 Jul 2009 4:13]
MySQL Verification Team
See bug: http://bugs.mysql.com/bug.php?id=46379.
[10 Nov 2009 18:50]
Bugs System
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.
[3 Sep 2010 16:17]
Paul DuBois
Revised 5.1.23 changelog entry. Multiple-table DELETE statements containing ambiguous aliases could have unintended side effects such as deleting rows from the wrong table. Examples: 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; To avoid ambiguity, declaration of aliases other than in the table_references part of the statement should be avoided: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; For the USING variant of multiple-table DELETE syntax, alias declarations outside the table_references part of the statement now are disallowed. (In MySQL 5.5, alias declarations outside table_references are disallowed for all multiple-table DELETE statements.) Statements containing aliases that are no longer permitted must be rewritten.
