Bug #38548 Update syntax description doesn't mention table alias
Description:
The syntax description for single table UPDATE does not reflect that it is valid to use an alias for the table name.
Both the DELETE and UPDATE syntax descriptions say "tbl_name" for the case of the single table variants of the statements, but UPDATE allows a full table reference, even for the single table command.
The description for DELETE is correct and it does not allow an alias.
The relevant portion of the YACC grammar for UPDATE (note the join_table_list):
update:
UPDATE_SYM
{
LEX *lex= Lex;
mysql_init_select(lex);
lex->sql_command= SQLCOM_UPDATE;
lex->lock_option= TL_UNLOCK; /* Will be set later */
lex->duplicates= DUP_ERROR;
}
opt_low_priority opt_ignore join_table_list
SET update_list
How to repeat:
CREATE TABLE `test` (
`a` int(11) DEFAULT NULL
)
mysql> delete from test as t where t.a is null;
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 'as t where t.a is null' at line 1
mysql> update test as t set a=1 where t.a is null;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
To prove that the above query is not using the multi-table codepath with the cornercase of one table, add a LIMIT clause (which is not allowed for multi-table updates):
mysql> update test1 as t set a=1 where t.a is null limit 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
Suggested fix:
Change the description of UPDATE to use `table_reference` instead of `tbl_name` for the single table variant.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: The syntax description for single table UPDATE does not reflect that it is valid to use an alias for the table name. Both the DELETE and UPDATE syntax descriptions say "tbl_name" for the case of the single table variants of the statements, but UPDATE allows a full table reference, even for the single table command. The description for DELETE is correct and it does not allow an alias. The relevant portion of the YACC grammar for UPDATE (note the join_table_list): update: UPDATE_SYM { LEX *lex= Lex; mysql_init_select(lex); lex->sql_command= SQLCOM_UPDATE; lex->lock_option= TL_UNLOCK; /* Will be set later */ lex->duplicates= DUP_ERROR; } opt_low_priority opt_ignore join_table_list SET update_list How to repeat: CREATE TABLE `test` ( `a` int(11) DEFAULT NULL ) mysql> delete from test as t where t.a is null; 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 'as t where t.a is null' at line 1 mysql> update test as t set a=1 where t.a is null; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 To prove that the above query is not using the multi-table codepath with the cornercase of one table, add a LIMIT clause (which is not allowed for multi-table updates): mysql> update test1 as t set a=1 where t.a is null limit 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 Suggested fix: Change the description of UPDATE to use `table_reference` instead of `tbl_name` for the single table variant.