MySQL Bugs: #21392: multi-table delete with alias table name fails with 1003: Incorrect table name

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:	None
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

Description:
When using "DELETE tbl_name FROM table_references WHERE where_condition" with tbl_name aliased (and with otherwise invalid table name characters), mysql returns error 1003: Incorrect table name.

For example:

delete `4.temp` from temp as `4.temp` where `4.temp`.identity = 5;

It works correctly when the "4." is removed everywhere which is fine for this trivial example, but that wreaks havoc with my approach to more complex table handling.

How to repeat:
Repeatable with:

delete `4.temp` from temp as `4.temp` where `4.temp`.identity = 5;

The table doesn't even need to exist to reproduce the error.

It fails as expected when I attempt to change the first `4.temp` to plain temp (Unknown table).

Suggested fix:
Unknown

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.

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;

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>

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.

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.

Pushed into 4.1.22/5.0.26/5.1.12-beta

Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.