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.