2

I am working on a utility that exposes data from a warehouse that has machine-generated table/column names. The user is allowed to specify new names for the tables/columns to make the results more meaningful. The alias can be any valid MySQL identifier name but I have found an issue with column names that contain a leading space. Now I know that would be a foolish column name but I do not wish to impose any arbitrary limit on the user just because I was lazy and couldn't find the correct way to do something.

This simple SQL statement reproduces the problem:

SELECT ` COL1` FROM (SELECT 1 AS ` COL1`) BAR

Error Code: 1054. Unknown column ' COL1' in 'field list'

Am I missing something? I am using MySQL 5.6.11.

rkOliver
  • 23
  • 3
  • Note that this query is successful although it shouldn't be (no leading space in the outer select): SELECT "COL1" FROM (SELECT 1 AS " COL1") BAR – rkOliver Jul 24 '14 at 14:28

1 Answers1

0

Can you retrieve the table structure? Wanted to see the actual column names of the table BAR. MSSQL with trim out any spaces in the column name when you run the query you have above.

Yoseph Hagos
  • 11
  • 1
  • 3
  • BAR is not a table but rather an alias for the subquery. This query involves no actual tables and could be re-written as: SELECT ` COL1` FROM (SELECT 1 AS ` COL1` FROM DUAL) AS BAR – rkOliver Jul 24 '14 at 14:26