Bug #33847 The character '-' in a table name causes problems

• Submitted: 13 Jan 2008 16:40
• Modified: 14 Jan 2008 5:20
• Reporter: David VANTYGHEM
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.45
• OS: Any
• CPU Architecture: Any
• Tags: -, name, table

[13 Jan 2008 16:40] David VANTYGHEM

Description:
When I use a '-' in a table name (sylibre-installer_Logiciels in my case), the following SQL querry doesn't work :
SELECT * FROM sylibre-installer_Logiciels ORDER BY Nom

#1064 - 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 '- installer_Logiciels ORDER BY Nom
LIMIT 0, 30' at line 1 

But this works fine after renaming the table into sylibre_installer_Logiciels :
SELECT * FROM sylibre_installer_Logiciels ORDER BY Nom

How to repeat:
Create a table named sylibre-installer_Logiciels, with one field named "Nom".
The following SQL query doesn't work :
SELECT * FROM sylibre-installer_Logiciels ORDER BY Nom

[14 Jan 2008 5:20] Valeriy Kravchuk

This is not a bug. You just have to quote table name properly, both in CREATE and in all other SQL statements. Look:

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.54-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.01 sec)

mysql> create table sylibre-installer_Logiciels(nom int);
ERROR 1064 (42000): 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 '-inst
aller_Logiciels(nom int)' at line 1
mysql> create table `sylibre-installer_Logiciels`(nom int);
Query OK, 0 rows affected (0.61 sec)

mysql> SELECT * FROM sylibre-installer_Logiciels ORDER BY nom;
ERROR 1064 (42000): 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 '-inst
aller_Logiciels ORDER BY nom' at line 1
mysql> SELECT * FROM `sylibre-installer_Logiciels` ORDER BY nom;
Empty set (0.03 sec)