Query component bug returns extra column

I found some very strange behaviour of ColdFusion’s query component. When you use this component to build a query of query (QoQ) of another ColdFusion Query and use order by on  several colums, the last column in the order by list is added to the selected output. This appears to happen in CF9, 10, 11 and 2016, but not in Lucee.

/* Create an unsorted CF-query */
unsorted = QueryNew("col1,col2,col3,col4","VarChar,VarChar,Integer,VarChar");
for (a=10;a gte 1;a--){
    QueryAddRow(unsorted);
    QuerySetCell(unsorted,"col1","col1 #a#");
    QuerySetCell(unsorted,"col2","col2 #a#");
    QuerySetCell(unsorted,"col3","#a#");
    QuerySetCell(unsorted,"col4","col4 #a#");
}

writeDump(var="#unsorted#");

/* Create a new CF query of query with the unsorted table */ 
sorted = new query(
    dbtype = "query"
    ,unsorted = unsorted
    ,sql = "select [col1],[col2] from unsorted order by [col3], [col4] asc"
    ).execute().getresult();

/* The last column in the order by list will be displayed in the result */  
writeDump(var="#sorted#", label="sorted");  

Try this on trycf.com

This is the result of the last query:

    col1        col2        col4
1   col1 1      col2 1      1
2   col1 2      col2 2      2
3   col1 3      col2 3      3
4   col1 4      col2 4      4
5   col1 5      col2 5      5
6   col1 6      col2 6      6
7   col1 7      col2 7      7
8   col1 8      col2 8      8
9   col1 9      col2 9      9
10  col1 10     col2 10     10

As you can see, the third column is labeled col4, but contains in fact data from col3. I found that this bug doesn’t only apply to cfscript and the direct use of the query component, but also when using the traditional <cfquery> tag.

A pragmatic solution would be to create two cfqueries, one with the order by statement and one selecting only the columns you need. However, this will impact performance and create a lot of code overhead.

2 Responses

  1. In general, avoid using the query.cfc functions. It should be viewed as deprecated and replaced by queryExecute().

    The query.cfc was a poor 1st attempt by the developers to implement queries in script. It has several bugs already reported which Adobe has stated they won’t fix. This might be one of the ones previously reported, or it might be new. Either way, don’t use it.

Leave a Reply to Carl Von Stetten Cancel reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.

Related