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?

Yes, they are. You can see it in the trycf gist, but I'll also add the results to my question. — Sander, Dec 14 '17 at 12:21
Appears to occur when some of the ORDER BY columns are not also contained in the select list. *Kinda* makes sense ... but if Lucee doesn't have that same issue, sounds like a bug. Is the output done dynamically or can you just ignore the extra column? — SOS, Dec 14 '17 at 17:24
Looks like a bug to me. It's probably not harmful, [but still worth reporting](https://tracker.adobe.com/#/add_bug). — Alex, Dec 14 '17 at 22:26
Yep, looks like a bug. Even worse, swap the order by's and see what appears as "col3". And I'll add (in case anyone may be tempted to try) that your problem happens also with queryexecute (instead of the "new query" approach). Indeed, my problem also happens with queryexecute: sorted=queryexecute("select [col1],[col2] from unsorted order by [col4], [col3]",[],{dbtype="query"}); — charlie arehart, Dec 15 '17 at 15:44

score 0 · Answer 1 · answered Dec 18 '17 at 08:20

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.

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

1 Answers1