MySQL Bugs: #26091: Query Browser gives SYNTAX ERROR when displaying hyphen containing columns

Bug #26091	Query Browser gives SYNTAX ERROR when displaying hyphen containing columns
Submitted:	5 Feb 2007 18:09	Modified:	6 Feb 2007 8:48
Reporter:	Clayton Nicholson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.2.9rc	OS:	Windows (XP SP2)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Dash, hyphen, Minus, query browser

Description:
If you have a field name containing a hyphen, you can include that in your where clause using backticks (`), but it doesn't work in the column selection. For example

SELECT * FROM tablename WHERE `field-name`="blah";
is OK, but
SELECT `field-name` FROM tablename;
will fail with a SYNTAX ERROR.

How to repeat:
create a table with a field that has a hyphen in its name. Try to do a select by dragging the field onto the query bar, the Query Browser will make 
SELECT b.`field-name` FROM test.blah b;
which will fail with a "You have an error in your SQL syntax;" etc.

The backticks will work in the WHERE clause, but not for the part where you tell it which fields to return.

It WILL work if you do
SELECT (`field-name`) FROM test.blah;
and
SELECT (`field-name1`),(`field-name2`) FROM test.blah;

Suggested fix:
There may be some reason for this, but it escapes me. I would expect that if you can escape a name with backticks in the WHERE clause, you SHOULD be able to do it for the column selection as well. Suggested fix is to extend the parsing code to include the column selection portion. Or maybe just note it in the documentation that this could be a problem. Ideally, everything is more readable and less prone to error if the DB doesn't have fields with dashes or hyphens in them, but sometimes you chose the database, and sometimes the database chooses you :(

changed wording a little bit. The problem isn't selecting from hyphen containing columns, it is trying to return columns that have hyphens in their name.

Thank you for the bug report. Verified with a table like below:

mysql> SELECT t.`col-2` FROM tb t;
+-------+
| col-2 |
+-------+
| aa    |
| bb    |
+-------+
2 rows in set (0.00 sec)

mysql> show create table tb\G
*************************** 1. row ***************************
       Table: tb
Create Table: CREATE TABLE `tb` (
  `col-1` bigint(20) unsigned NOT NULL auto_increment,
  `col-2` char(2) default NULL,
  UNIQUE KEY `col-1` (`col-1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html