MySQL Bugs: #46379: Use of table alias in DELETE fails in 5.1; succeeds in 5.0

Bug #46379	Use of table alias in DELETE fails in 5.1; succeeds in 5.0
Submitted:	24 Jul 2009 20:53	Modified:	24 Jul 2009 22:27
Reporter:	Greg Ferrar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.1.36	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	alias, delete, error, syntax

Description:
This query on 5.1.36:

  delete from main_table x using main_table x where (x.date_time < '2009-06-30 14:45:38')

gives an error:

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 'x using main_table x where (x.date_time < '2009-06-29 14:52:57')'

The same query on 5.0.67 succeeds without an error.

From the syntax error, it appears to be complaining about the alias "x" after the first "main_table". I can't tell from the documentation whether this syntax *should* be allowed or not, but it *was* allowed in 5.0, and it is convenient to have it, and we used it in our shipping product, so this seems to be a bug in backward compatibility. This query string is generated programmatically, which is why it seems needlessly wordy; in this case the alias isn't needed at all, or the "using" for that matter, but other more complex versions of this query do operate on multiple tables, and uses these aliases.

How to repeat:
Run query:

  delete from main_table x using main_table x where (x.date_time < '2009-06-30 14:45:38')

in 5.1.36 (on a table called main_table, with a datetime column called date_time).

Suggested fix:
Restore the 5.0 support for an alias on the main table.

miguel@lara:~/dbs/5.1$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1                            
Server version: 5.1.38-debug Source distribution         

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table main_table (date_time datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> delete from main_table x using main_table x where (x.date_time < '2009-06-30 14:45:38');
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 'x using main_table x where (x.date_time < '2009-06-30 14:45:38')' at line 1
mysql> exit
Bye
miguel@lara:~/dbs/5.1$ bin/mysqladmin -uroot shutdown
miguel@lara:~/dbs/5.1$ cd ..
miguel@lara:~/dbs$ cd 5.0
miguel@lara:~/dbs/5.0$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.85-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table main_table (date_time datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> delete from main_table x using main_table x where (x.date_time < '2009-06-30 14:45:38');
Query OK, 0 rows affected (0.01 sec)

mysql>

Thank you for the bug report. Please read the last 2 comments of bug: http://bugs.mysql.com/bug.php?id=30234 for explanation of this behavior on 5.0 and 5.1+. Thanks in advance.