James MobergI encountered an Adobe ColdFusion error where a query would throw an error if certain column names were accessed. A third-party client uploaded an Excel file using "first" and "last" column names. When I attempted to access those columns independently using ColdFusion 2016.0.14.318307 with a query-of-queries CFQuery, an error "Query Of Queries syntax error. Encountered FIRST. Incorrect Select List, Incorrect select column," was thrown. I checked the official about and user guide support pages and there's no indication that any column names are reserved. (NOTE: I'm also using the Microsoft JDBC Driver for SQL Server instead of the native DataDirect drivers when performing non-in-memory SQL queries.)
"first" and "last" aren't reserved keywords in MSSQL. I can create queries with these column names and access them if the asterisk is used to "select all" columns, but sometimes I only need to access specific columns, wish to create an alias, concat values or change the column order.
If I perform the same QofQ query using either Lucee or Railo, it works.
I checked against Pete Freitag's SQL Reserved Words Checker and do see that "first" and "last" are reserved when using ODBC, DB2, PostgreSQL 8 and ISO/ANSI,SQL99. So which SQL standard is used by Adobe versus Lucee/Railo?
As a result, I'm wondering what other undocumented keywords may be reserved. Since this works in Railo (from 2014) & Lucee, I'm going to consider this a bug. Adobe will probably respond with "not a bug. works as expected" and then not follow up to identify all undocumented reserved column names.
Workarounds
Create a struct of all reserved keywords and the safe alternative that you wish to replace it with and use java SetColumnNames() to rename the columns.
myQuery.SetColumnNames(["firstName", "lastName"]);
Zac Spitzer recommended using brackets, so I used the following (which may not be the most elegant, but it works if using a comma-delimited list of column names):
SELECT [#replace(Test.columnList, ",", "],[", "all")#]
Bug Reported
I reported bug CF-4207962 to Adobe. I tried to search to see if it had already been reported, but didn't know exactly know what to search for as the results were too numerous.
Demos
- ColdFusion 10 = error
- ColdFusion 11 = error
- ColdFusion 2016 = error
- ColdFusion 2018 = error
- Railo = works
- Lucee = works
