Bug #53034 Multiple-table DELETE statements not accepting "Access compatibility" syntax
Submitted: 21 Apr 2010 18:15 Modified: 14 Oct 2010 14:47
Reporter: Leandro Morgado Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.x OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: regression

[21 Apr 2010 18:15] Leandro Morgado 
Description:
According to the manual:

============================================
"DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
The syntax allows .* after each tbl_name for compatibility with Access."
http://dev.mysql.com/doc/refman/5.1/en/delete.html
============================================

This works fine on latest 5.0.90:

============================================
mysql [localhost] {msandbox} (world) > SELECT ci.Name, co.Name, ci.Population FROM City AS ci INNER JOIN Country AS co ON (ci.CountryCode = co.Code) WHERE (co.Name='Australia');
+---------------+-----------+------------+
| Name          | Name      | Population |
+---------------+-----------+------------+
| Sydney        | Australia |    3276207 | 
| Melbourne     | Australia |    2865329 | 
| Brisbane      | Australia |    1291117 | 
| Perth         | Australia |    1096829 | 
| Adelaide      | Australia |     978100 | 
| Canberra      | Australia |     322723 | 
| Gold Coast    | Australia |     311932 | 
| Newcastle     | Australia |     270324 | 
| Central Coast | Australia |     227657 | 
| Wollongong    | Australia |     219761 | 
| Hobart        | Australia |     126118 | 
| Geelong       | Australia |     125382 | 
| Townsville    | Australia |     109914 | 
| Cairns        | Australia |      92273 | 
+---------------+-----------+------------+
14 rows in set (0.01 sec)

mysql [localhost] {msandbox} (world) > DELETE FROM ci.* USING City AS ci INNER JOIN Country AS co ON (ci.CountryCode = co.Code) WHERE (co.Name='Australia'); 
Query OK, 14 rows affected (0.00 sec)

mysql [localhost] {msandbox} (world) > SELECT ci.Name, co.Name, ci.Population FROM City AS ci INNER JOIN Country AS co ON (ci.CountryCode = co.Code) WHERE (co.Name='Australia');
Empty set (0.01 sec)
============================================

However in recent 5.1 releases, the syntax isn't accepted:

============================================
mysql  Ver 14.14 Distrib 5.1.41, for apple-darwin9.5.0 (i386) using readline 5.1
...
mysql> DELETE FROM ci.* USING City AS ci INNER JOIN Country AS co ON (ci.CountryCode = co.Code) WHERE (co.Name='Australia'); 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* USING City AS ci INNER JOIN Country AS co ON (ci.CountryCode = co.Code) WHERE ' at line 1

mysql> DELETE FROM ci USING City AS ci INNER JOIN Country AS co ON (ci.CountryCode = co.Code) WHERE (co.Name='Australia'); 
Query OK, 14 rows affected (0.02 sec)
============================================

This might be due to work on the alias code / change of parser introduced in 5.1.23:

"- 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)"
http://bugs.mysql.com/bug.php?id=30234

How to repeat:
- Load the world database: http://dev.mysql.com/doc/world-setup/en/world-setup.html

- Issue a multi-table delete with t.* syntax:

DELETE FROM ci.* USING City AS ci INNER JOIN Country AS co ON (ci.CountryCode = co.Code) WHERE (co.Name='Australia'); 

Suggested fix:
Either allow the syntax or document that this syntax is no longer allowed.
[20 Aug 2010 13:49] 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/116407

3486 Gleb Shchepa	2010-08-20
      Bug #53034: Multiple-table DELETE statements not accepting
                  "Access compatibility" syntax
      
      The "DELETE table_name.* ..." syntax for multi-table DELETE
      statements is documented but it was lost in the fix for the
      bug 30234.
      
      The table_ident_opt_wild parser rule has been added
      to restore the lost syntax.
     @ mysql-test/r/delete.result
        Test case for bug #53034.
     @ mysql-test/t/delete.test
        Test case for bug #53034.
     @ sql/sql_yacc.yy
        Bug #53034: Multiple-table DELETE statements not accepting
                    "Access compatibility" syntax
        
        The table_ident_opt_wild parser rule has been added
        to restore the lost syntax.
[26 Aug 2010 17:45] 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/116935

3486 Gleb Shchepa	2010-08-26
      Bug #53034: Multiple-table DELETE statements not accepting
                  "Access compatibility" syntax
      
      The "wild" "DELETE FROM table_name.* ... USING ..." syntax
      for multi-table DELETE statements is documented but it was
      lost in the fix for the bug 30234.
      
      The table_ident_opt_wild parser rule has been added
      to restore the lost syntax.
     @ mysql-test/r/delete.result
        Test case for bug #53034.
     @ mysql-test/t/delete.test
        Test case for bug #53034.
     @ sql/sql_yacc.yy
        Bug #53034: Multiple-table DELETE statements not accepting
                    "Access compatibility" syntax
        
        The table_ident_opt_wild parser rule has been added
        to restore the lost syntax.
[30 Aug 2010 22:48] 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/117167

3493 Gleb Shchepa	2010-08-31
      Bug #53034: Multiple-table DELETE statements not accepting
                  "Access compatibility" syntax
      
      The "wild" "DELETE FROM table_name.* ... USING ..." syntax
      for multi-table DELETE statements is documented but it was
      lost in the fix for the bug 30234.
      
      The table_ident_opt_wild parser rule has been added
      to restore the lost syntax.
     @ mysql-test/r/delete.result
        Test case for bug #53034.
     @ mysql-test/t/delete.test
        Test case for bug #53034.
     @ sql/sql_yacc.yy
        Bug #53034: Multiple-table DELETE statements not accepting
                    "Access compatibility" syntax
        
        The table_ident_opt_wild parser rule has been added
        to restore the lost syntax.
        Note: simple extending of table_ident with opt_wild in
        the table_alias_ref rule is not acceptable, because
        a) it adds one conflict more and b) this conflict resolves
        in the inappropriate way.
[1 Sep 2010 13:13] Bugs System 
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100901130501-4g2k86dub29auj8y) (version source revid:alik@sun.com-20100901130012-9bmmvzcnnw6n5rw6) (merge vers: 5.6.1-m4) (pib:21)
[1 Sep 2010 13:15] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20100901130614-pgop3m80rmutewxn) (version source revid:alik@sun.com-20100901130033-8k19cjn6n2blm3py) (pib:21)
[1 Sep 2010 13:16] Bugs System 
Pushed into mysql-5.5 5.5.7-m3 (revid:alik@sun.com-20100901125952-4hsrosoa0xreionr) (version source revid:alik@sun.com-20100901125952-4hsrosoa0xreionr) (merge vers: 5.5.7-m3) (pib:21)
[2 Sep 2010 0:56] Paul DuBois 
Noted in 5.1.51, 5.5.7, 5.6.1 changelogs.

The fix for Bug#30234 caused the server to reject the DELETE
tbl_name.* ... Access compatibility syntax for multiple-table DELETE
statements.
[28 Sep 2010 8:47] Bugs System 
Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)
[14 Oct 2010 8:34] Bugs System 
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:49] Bugs System 
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:03] Bugs System 
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[14 Oct 2010 14:47] Jon Stephens 
Already documented in the 5.1.51 changelog; no new changelog entries required. setting back to Closed state.
[12 Feb 2018 9:20] Gleb Shchepa 
A duplicate: bug#45666.