ColdFusion Query-of-Query Reserved Words or Bug?

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"]);

Enter fullscreen mode Exit fullscreen mode

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")#]

Enter fullscreen mode Exit fullscreen mode

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

Source Code

原文链接：ColdFusion Query-of-Query Reserved Words or Bug?