I 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
Top comments (1)
I've encountered issues when continuing to work with a query after using SetColumnNames(). When retrieving the column names using columnlist, getMetaData().getColumnLabels() and getColumnNames(), the getMetaData() function would only return the initial columns names instead of the modified column name.
To work around this, perform a QofQ using an alias. Here's a user-defined function (UDF).
gist.github.com/JamoCA/3bb02cdbdea...