MySQL Bugs: #42323: Documentation states identifiers can't have ASCII NULL incorrectly

Bug #42323	Documentation states identifiers can't have ASCII NULL incorrectly
Submitted:	24 Jan 2009 17:08	Modified:	2 Mar 2009 18:48
Reporter:	Sheeri Cabral (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	6.0, probably 5.1 as well	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	identifiers

Description:
On the manual page at:

http://dev.mysql.com/doc/refman/6.0/en/identifiers.html

It states:

"No identifier can contain ASCII 0 (0x00) or a byte with a value of 255. "

However, this is not true.  I believe it's likely untrue in 5.1 as well, and with bytes that have a value of 255 though I haven't tested that.  My guess is based on the fact that in 5.1 and up, any character is allowed in identifier names so long as it's quoted.

See the examples, they allowed me to create databases and tables with 0x00 in them (quoted and not quoted).

How to repeat:
mysql> CREATE TABLE `0x00` (`0x00` INT);
Query OK, 0 rows affected (0.37 sec)

mysql> drop table `0x00`;
Query OK, 0 rows affected (0.00 sec)

mysql> create database test0x00;
Query OK, 1 row affected (0.00 sec)

mysql> show databases like 'test%';
+------------------+
| Database (test%) |
+------------------+
| test             | 
| test0x00         | 
+------------------+
2 rows in set (0.00 sec)

mysql> create database test0x00;
ERROR 1007 (HY000): Can't create database 'test0x00'; database exists
mysql> create database `test0x00`;
ERROR 1007 (HY000): Can't create database 'test0x00'; database exists
mysql> show databases like 'test%';
+------------------+
| Database (test%) |
+------------------+
| test             | 
| test0x00         | 
+------------------+
2 rows in set (0.00 sec)

mysql> 

Suggested fix:
Change the manual.

Thank you for the report.

But you create table/field/database not ASCII NULL character, but with string "0x00" which is allowed in identifiers names.

Please try example with real NULL:

$php -r 'echo "create table `test\0` (f1 int)";' 
create table `test` (f1 int)

$php -r 'echo "create table `test\0` (f1 int)";' | mysql test
ERROR 1113 (42000) at line 1: A table must have at least 1 column

$php -r 'echo "create table `test` (`f1\0` int)";' | mysql test
ERROR 1064 (42000) at line 1: 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 '' at line 1

$php -r 'echo "create database `foo\0bar`";' | mysql test

$mysql -e "show databases like 'foo%';"
+-----------------+
| Database (foo%) |
+-----------------+
| foo             | 
+-----------------+

So it really does not contain null, although not reject statements which contain such values with proper message.

Further investigation showed this is mysql command line client problem as a program which uses C API returns correct error message:

$php -r 'mysql_connect("127.0.0.1:3306", "root", ""); mysql_query("create database `foo\0bar`"); var_dump(mysql_error());'
string(146) "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 '' at line 1"

$php -r 'mysql_connect("127.0.0.1:3306", "root", ""); mysql_select_db("test"); mysql_query("create table `test\0` (f1 int)"); var_dump(mysql_error());'
string(146) "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 '' at line 1"

$php -r 'mysql_connect("127.0.0.1:3306", "root", ""); mysql_select_db("test"); mysql_query("create table `test` (`f1\0` int)"); var_dump(mysql_error());'
string(146) "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 '' at line 1"

I copied from the manual page.

"No identifier can contain ASCII 0 (0x00) or a byte with a value of
255. "

So I used 0x00.  The page itself is wrong anyway because it's stating that ASCII 0 is 0x00 (hex 0!)

As of 5.1, any character is legal except ASCII NUL (0x00).

I'll remove "255" from the description in 5.1 and up. Also, I'll clarify that \0 is ASCII NUL, not ASCII 0, in all versions.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.