Bug #36939 Multiple-table DELETE syntax worked in 5.0.45 but doesn't work in 5.0.51b

• Submitted: 24 May 2008 20:40
• Modified: 25 May 2008 0:00
• Reporter: Roman Kramar
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.51b
• OS: Windows
• CPU Architecture: Any

[24 May 2008 20:40] Roman Kramar

Description:
The following multiple-table DELETE statement worked fine in 5.0.45:

DELETE FROM
    user_account t1
USING
    user t2 join ( user_account t1, account t3 )
        ON ( t1.user_account_user=t2.user_id AND t3.account_id=t1.user_account_account )
WHERE
    t2.user_id=12;

When issued in 5.0.51b it generates the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your My SQL server version for the right syntax to use near 't1
USING
    user t2 join ( user_account t1, account t3 )
        ON ( t1.user_a' at line 2

How to repeat:
create table user ( user_id INT UNSIGNED NOT NULL, primary key (user_id) );
create table account ( account_id INT UNSIGNED NOT NULL, primary key (account_id) );
create table user_account ( user_account_user INT, user_account_account INT );

DELETE FROM
    user_account t1
USING
    user t2 join ( user_account t1, account t3 )
        ON ( t1.user_account_user=t2.user_id AND t3.account_id=t1.user_account_account )
WHERE
    t2.user_id=12

[25 May 2008 0:00] MySQL Verification Team

Thank you for the bug report. This behavior is related to the bug:
http://bugs.mysql.com/bug.php?id=30234

See comment:

[18 Apr 20:56] Paul DuBois

<cut>

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.

<cut>

So I am closing this bug because the source server doesn't present anymore
the behavior reported in this bug report:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3350 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.64-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > use test
Database changed
mysql 5.0 > create table user ( user_id INT UNSIGNED NOT NULL, primary key (user_id) );
Query OK, 0 rows affected (0.09 sec)

mysql 5.0 > create table account ( account_id INT UNSIGNED NOT NULL, primary key (account_id) );
Query OK, 0 rows affected (0.11 sec)

mysql 5.0 > create table user_account ( user_account_user INT, user_account_account INT );
Query OK, 0 rows affected (0.06 sec)

mysql 5.0 >
mysql 5.0 > DELETE FROM
    ->     user_account t1
    -> USING
    ->     user t2 join ( user_account t1, account t3 )
    ->         ON ( t1.user_account_user=t2.user_id AND t3.account_id=t1.user_account_account )
    -> WHERE
    ->     t2.user_id=12;
Query OK, 0 rows affected (0.08 sec)

mysql 5.0 >