MySQL Bugs: #30441: \u mysql cli command does not handle quoted identifiers

Bug #30441	\u mysql cli command does not handle quoted identifiers
Submitted:	15 Aug 2007 15:47	Modified:	19 Sep 2007 11:43
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

there is an inconsequence in the documentation and the server behaviour.

The documentation:
http://dev.mysql.com/doc/refman/5.1/de/legal-names.html
http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

The content of these two is different.

I miss in the English documentation that table names that ends with space are not allowed.

By accident I made some tests with server: 5.1.19

Names containing ".", "\" or "/" work very well.

It's not really clear, when I have to escape the backslash with a backslash and when not. Sometimes the server needs the escape and sometimes it didn't need it. For example using the CLI and making a \u, than I have to escape the backslash.

It always works fine and it doesn't matter if you use accent aigu or double quote (ANSI_QUOTES).

Spaces at the beginning of the name and in the middle are no problem.

But when I try to create a table with ending space, I get an error.

Ok, the German documentation says this is not allowed. But the German documentation also says that dots, slashes and backslashes are not allowed. When I only read the German documentation, than I am asking me, why is there a check for ending space but not for dots, slashes and backslashes.

Ok, the english documentation says, that dots, slashes and backslashes are allowed but this documentation don't tell me, that ending spaces are forbidden.

Something is very crazy here ....

How to repeat:
Just create databases and tables with dots, slashes, backslashes and spaces (at the end).
Switch to sql_mode=ANSI_QUOTES and back.

Suggested fix:
Fixing the documentation chaos on that topic.

Thank you for the bug report.

This bug report consists of a narrative description. Please show the exact statements that you tried and the resulting error messages. Also, please specify the sql_mode value in effect when these statements were issued. Thanks.

of course:

$ mysql
mysql> create database ` / .. \ ... --- ...`;

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
|  / .. \ ... --- ... |

mysql> \u ` / .. \ ... --- ...`
ERROR 1049 (42000): Unbekannte Datenbank ' / ..  ... --- ...'
Translation: unknown database

mysql> \u ` / .. \\ ... --- ...`
Database changed

mysql> create table ` ... / --- \ ... `(id serial);
ERROR 1103 (42000): Unerlaubter Tabellenname ' ... / --- \ ... '
Translation: table name is not allowed

mysql> create table ` ... / --- \ ...`(id serial);
Query OK, 0 rows affected (0.03 sec)

mysql> \q

FYI: my Shell is a SH:

$ mysql ` / .. \ ... --- ...`
/: Permission denied

$ mysql " / .. \ ... --- ..."

mysql> show tables;
+-------------------------------+
| Tables_in_ / .. \ ... --- ... |
+-------------------------------+
|  ... / --- \ ...              | 
+-------------------------------+

mysql> \q

$ bash
[/usr/home/miracee]$ mysql ` / .. \ ... --- ...`
bash: /: is a directory

/usr/home/miracee]$ mysql " / .. \ ... --- ..."
mysql> show tables;
+-------------------------------+
| Tables_in_ / .. \ ... --- ... |
+-------------------------------+
|  ... / --- \ ...              | 
+-------------------------------+

mysql> \q

/usr/home/miracee]$ mysql " / .. \\ ... --- ..."
mysql> show tables;
+-------------------------------+
| Tables_in_ / .. \ ... --- ... |
+-------------------------------+
|  ... / --- \ ...              | 
+-------------------------------+

mysql> insert into ` ... / --- \ ...` values ();

mysql> select * from ` ... / --- \ ...`;
+----+
| id |
+----+
|  1 | 
+----+

mysql> set sql_mode=ANSI_QUOTES;

mysql> select * from " ... / --- \ ...";
+----+
| id |
+----+
|  1 | 
+----+

it works with insert too.

Also all works similar using csh.

My first tests were with using ODBC and therefor you have to escape the \ with \\.

As you can see: ending spaces in table names are not allowed all other stuff is allowed.

Also you sometime has to escape the \.

It works similar with ANSI_QUOTES and without them (accent aigu is very difficult to type on a German keyboard, that's why I often use ANSI_QUOTES).

Susanne

Hi again,

what I forget to say:

It is not clear, when you need to escape the \ and when you don't need it.

The German documentation says that ending spaces are not allowed.
The English documentation don't say this.

Also the German doc says that . / and \ are not allowed.
The English doc says, that they are allowed when you use 3.51.6 or higher.

I use 3.51.19

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

\u is a client command: the client program is not affected by ANSI_QUOTES sql_mode.
Perhaps this inconsistency should be regarded as a bug.

As a workaround one should use 'USE <db_name' syntax.

Updating the synopsis, since this is not just an ANSI_QUOTES problem. the \u command simply does not expect quoted arguments at all, which means that databases with a leading space in the name are impossible to specify.