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 22:53	Modified:	25 Jul 0:27
Reporter:	Greg Ferrar
Status:	Not a Bug
Category:	Server: Parser	Severity:	S2 (Serious)
Version:	5.1.36	OS:	Any
Assigned to:		Target Version:
Tags:	syntax, error, alias, delete

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.