CF Query of Query multiple order by statements add last ordered column to select

Question

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

Is this a known bug for Adobe CF?

Does anybody know of a different, better way to order by multiple columns in a ColdFusion QoQ?

Answer 1

Alright, I reported the bug with Adobe: https://tracker.adobe.com/#/view/CF-4200408 and decided to work around the bug by using two QoQ's, seperating the ordering of the data and selecting of the columns, like so:

/* 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");  

selected = new query(
    dbtype = "query"
    ,sorted = sorted
    ,sql = "select [col1],[col2] from sorted"
    ).execute().getresult();

Of course, you'll take a big performance hit with this. There might be other solutions until Adobe fixes the bug themselves. For example, I found this ancient comment from Ben Nadel demonstrating where to find the code for the cfquery custom tag and figured that this code could be edited, if one were so inclined.