MySQL Bugs: #24983: Spaces on a primary key column name cause a select query to fail

Bug #24983	Spaces on a primary key column name cause a select query to fail
Submitted:	11 Dec 2006 23:28	Modified:	12 Dec 2006 1:02
Reporter:	Antonio Tapia	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.2.6 beta	OS:	Windows (Windows XP Pro)
Assigned to:		CPU Architecture:	Any

Description:
If a column that has spaces in the name is primary key, then one cannot execute a select query on any column other than the primary key itself.

This error only happens from within MySQL Query Browser. The command-line client works well. I received this same error with an earlier version of MySQL Query Browser as well (1.1.17).

Note that removing the the primary key (ALTER TABLE B DROP PRIMARY KEY) will allow all queries to work (see "How to repeat" ).

The database server does not seem to affect this issue. It occurs when accessing a  MySQL instance on Windows XP Pro (5.0.18nt) and on Linux (5.0.27).

How to repeat:
Create a table as follows:

create table B ( 
    `a` varchar(20), 
    `Test Space` varchar(20) primary key, 
    `b` 
);

Now select any column other than the primary key:

SELECT b from B;
SELECT a FROM B;

The error message is: 

"Unknown column 'Test' in 'field list'".

Suggested fix:
Note that this may be related to bugs #24789 and #23626. So perhaps the same code that parses the queries is simply not quoting columns correctly.

I proof-read it!! I really did... but here is an immediate correction to the create-table syntax (pretty obvious)...

create table B ( 
    `a` varchar(20), 
    `Test Space` varchar(20) primary key, 
    `b` varchar(20)
);

..<sigh>... I forgot to mention that the selects do work by using a subquery. Thus, where "SELECT a from B" will fail, this will work:

SELECT * FROM ( SELECT a FROM B ) t

Thank you for the bug report. Duplicate of bug http://bugs.mysql.com/bug.php?id=24789