MySQL Bugs: #60993: Name quoted with quote instead of backtick gives no syntax error

Bug #60993	Name quoted with quote instead of backtick gives no syntax error
Submitted:	28 Apr 2011 16:11	Modified:	16 Aug 2011 1:26
Reporter:	Mark Robson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1.50, 5.1.57	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	parser

Description:
I was surprised to find that this SQL does not generate a syntax error, but rather confusingly, renames the table to a name ending in '

How to repeat:
Run this test case:

CREATE TABLE t (
	ID INT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

ALTER TABLE t RENAME TO `t1';

Expected result: Syntax error

Actual result: table renamed to `t1'`

Please, check with recent version, 5.1.56. I can not repeat with current code:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
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
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> drop table t;
ERROR 1051 (42S02): Unknown table 't'
mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> CREATE TABLE t (
    -> ID INT NOT NULL PRIMARY KEY
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> 
mysql> ALTER TABLE t RENAME TO `t1';
    `> `;
ERROR 1103 (42000): Incorrect table name 't1';
'
mysql>

It appears that this bug cannot be reproduced interactively from the mysql CLI. Instead use a file:

CREATE TABLE t (
	ID INT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

ALTER TABLE t RENAME TO `t1';

"bug.sql"

Then

mysql -u root bug < bug.sql

Gives 0 exit status, but renames the table despite the syntax error.

Verified just as described on Mac OS X:

macbook-pro:5.1 openxs$ cat /tmp/bug60993.sql
CREATE TABLE t (
	ID INT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

ALTER TABLE t RENAME TO `t1';

macbook-pro:5.1 openxs$ bin/mysql -uroot test < /tmp/bug60993.sql 
macbook-pro:5.1 openxs$ echo $?
0
macbook-pro:5.1 openxs$ bin/mysql -uroot test
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 3
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> show tables like 't%';
+---------------------+
| Tables_in_test (t%) |
+---------------------+
| t1'                 |
| tablets             |
| ti                  |
+---------------------+
3 rows in set (0.00 sec)

Noted in 5.6.3 changelog.

If a statement ended with mismatched quotes, the server accepted the
statement and interpreted whatever was after the initial quote as a
text string.