ColdFusion Query-of-Query Reserved Words or Bug?
Tags: coldfusion, cfml, sql, java
Published: 2020-04-08 15:28:08

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](https://helpx.adobe.com/coldfusion/developing-applications/accessing-and-using-data/using-query-of-queries/about-query-of-queries.html) and [user guide](https://helpx.adobe.com/coldfusion/developing-applications/accessing-and-using-data/using-query-of-queries/query-of-queries-user-guide.html) 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](https://docs.microsoft.com/en-us/sql/connect/jdbc/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](https://lucee.org/) or [Railo](https://github.com/getrailo/railo), it works.

I checked against Pete Freitag's [SQL Reserved Words Checker](https://www.petefreitag.com/tools/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()](http://www.neiland.net/blog/article/using-java-to-rename-columns-in-a-coldfusion-query-object/) to rename the columns.

```
myQuery.SetColumnNames(["firstName", "lastName"]);
```

Zac Spitzer recommended [using brackets](https://twitter.com/zackster/status/1247920846292897793), 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](https://tracker.adobe.com/#/view/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](https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=acf) = error
- [ColdFusion 11](https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=acf11) = error
- [ColdFusion 2016](https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=acf2016) = error
- [ColdFusion 2018](https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=acf2018) = error
- [Railo](https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=railo) = works
- [Lucee](https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=lucee) = works

## Source Code

{% gist https://gist.github.com/JamoCA/4fbd1a8336c71f5f21e982ea2b10d448 %}