Bug #21392 | multi-table delete with alias table name fails with 1003: Incorrect table name | ||
---|---|---|---|
Submitted: | 1 Aug 2006 15:04 | Modified: | 25 Sep 2006 19:49 |
Reporter: | Jeff Clewley | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.0.22/4.0;4.1;5.0BK | OS: | Linux (CENTOS) |
Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[1 Aug 2006 15:04]
Jeff Clewley
[1 Aug 2006 15:41]
MySQL Verification Team
Thank you for the bug report. Have you experienced that behavior just with the sample provided "4."? because please read: 9.2. Database, Table, Index, Column, and Alias Names http://dev.mysql.com/doc/refman/5.0/en/legal-names.html Table names cannot contain ‘/’, ‘\’, ‘.’, or characters that are not allowed in a filename. Otherwise could you please provide a test case. Thanks in advance.
[1 Aug 2006 15:48]
Jeff Clewley
I used the ticks (`) to quote the table name, knowing it has the otherwise invalid period character (use of ticks is documented on the page you referenced). Not sure what you mean by test case, does this code suffice: create table temp (identity int not null primary key auto_increment); delete `4.temp` from temp as `4.temp` where `4.temp`.identity = 5; Note that similar usage of ticks is valid in select statements, such as: select * from temp as `4.temp` where `4.temp`.identity = 5;
[1 Aug 2006 18:20]
MySQL Verification Team
Thank you for the feedback. The version 5.1 isn't affected by this bug: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.27-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table temp (identity int not null primary key auto_increment); Query OK, 0 rows affected (0.01 sec) mysql> insert into temp values (5); Query OK, 1 row affected (0.00 sec) mysql> select * from temp as `4.temp` where `4.temp`.identity = 5; +----------+ | identity | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> delete `4.temp` from temp as `4.temp` where `4.temp`.identity = 5; ERROR 1103: Incorrect table name '4.temp' mysql> ------------------------------------------------------------------------------- Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.1.22-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table temp (identity int not null primary key auto_increment); Query OK, 0 rows affected (0.01 sec) mysql> insert into temp values (5); Query OK, 1 row affected (0.00 sec) mysql> select * from temp as `4.temp` where `4.temp`.identity = 5; +----------+ | identity | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> delete `4.temp` from temp as `4.temp` where `4.temp`.identity = 5; ERROR 1103 (42000): Incorrect table name '4.temp' mysql> ---------------------------------------------------------------------------- miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbo Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.25-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table temp (identity int not null primary key auto_increment); Query OK, 0 rows affected (0.01 sec) mysql> insert into temp values (5); Query OK, 1 row affected (0.01 sec) mysql> select * from temp as `4.temp` where `4.temp`.identity = 5; +----------+ | identity | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> delete `4.temp` from temp as `4.temp` where `4.temp`.identity = 5; ERROR 1103 (42000): Incorrect table name '4.temp' mysql> ------------------------------------------------------------------------------ mysql> select version(); +-------------------+ | version() | +-------------------+ | 5.1.12-beta-debug | +-------------------+ 1 row in set (0.00 sec) mysql> create table temp (identity int not null primary key auto_increment); Query OK, 0 rows affected (0.00 sec) mysql> insert into temp values (5); Query OK, 1 row affected (0.00 sec) mysql> select * from temp as `4.temp` where `4.temp`.identity = 5; +----------+ | identity | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> delete `4.temp` from temp as `4.temp` where `4.temp`.identity = 5; Query OK, 1 row affected (0.00 sec) mysql> select * from temp; Empty set (0.00 sec) mysql>
[2 Sep 2006 5:09]
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/11300 ChangeSet@1.2534, 2006-09-02 08:05:50+03:00, gkodinov@macbook.local +5 -0 Bug #21392: multi-table delete with alias table name fails with 1003: Incorrect table name in multi-table DELETE the set of tables to delete from actually references then tables in the other list, e.g: DELETE alias_of_t1 FROM t1 alias_of_t1 WHERE .... is a valid statement. So we must turn off the check for alias_of_t1 because it's not a table name (even if it looks like one). In order to do that we add a special flag (TL_OPTION_ALIAS) to disable the name checking for the aliases in multi-table DELETE.
[4 Sep 2006 15:41]
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/11362 ChangeSet@1.2534, 2006-09-04 18:40:30+03:00, gkodinov@macbook.gmz +5 -0 Bug #21392: multi-table delete with alias table name fails with 1003: Incorrect table name in multi-table DELETE the set of tables to delete from actually references then tables in the other list, e.g: DELETE alias_of_t1 FROM t1 alias_of_t1 WHERE .... is a valid statement. So we must turn off table name syntactical validity check for alias_of_t1 because it's not a table name (even if it looks like one). In order to do that we add a special flag (TL_OPTION_ALIAS) to disable the name checking for the aliases in multi-table DELETE.
[19 Sep 2006 8:28]
Georgi Kodinov
Pushed into 4.1.22/5.0.26/5.1.12-beta
[25 Sep 2006 19:49]
Paul DuBois
Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.