CF Query query of several orders by operator adds the last ordered column to select

Question

I found a very strange behavior of the ColdFusion query . When you use this component to build a query query (QoQ) of another ColdFusion Query and use order by in several columns, the last column in the order by list is added to the selected output. This happens 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 at 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 anyone else know a better way to organize multiple columns in QoQ ColdFusion?

Answer 1

Ok, I reported an error with Adobe: https://tracker.adobe.com/#/view/CF-4200408 and decided to work around the error using two QoQs, separating the data order and the choice of columns, for example:

 /* 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 will get great success with this. There may be other solutions until Adobe fixes the error. For example, I found this ancient comment from Ben Nadel demonstrating where to find the code for the custom cfquery tag and realized that this code could be edited if they were so inclined.