December 14, 2017
Query component bug returns extra column
Comments
(6)
December 14, 2017
Query component bug returns extra column
Newbie 1 posts
Followers: 0 people
(6)

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.

6 Comments
2017-12-15 20:24:13
2017-12-15 20:24:13

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.

Like
(5)
>
Carl Von Stetten
's comment
2017-12-15 20:25:38
2017-12-15 20:25:38
>
Carl Von Stetten
's comment

To clarify, the “sorted = new query()…” for your Query of Query is using query.cfc. Replace that logic with queryExecute() and your problem should go away.

Like
>
Carl Von Stetten
's comment
2018-01-23 23:01:17
2018-01-23 23:01:17
>
Carl Von Stetten
's comment

I tried same using queryExecute, and found that issue still exists in CF2016
Sample code is provided below

/* 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 = queryExecute(
“select [col1],[col2] from unsorted order by [col3], [col4] asc”, {}, { dbtype=”query” }
);

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

Like
>
vjindal
's comment
2018-01-23 23:11:49
2018-01-23 23:11:49
>
vjindal
's comment

I think you’ve identified a bug in the core Query-of-Query functionality of ColdFusion. I can reproduce this on TryCF: https://trycf.com/gist/258a56456c29bae7dfe5564376f1f0ef/acf2016?theme=monokai

And it’s a little more weird than you think. ColdFusion uses the title of the last column in the ORDER BY clause (“col4”), but the values are actually from the first column of the ORDER BY clause (“col3”).

Please submit a bug report to Adobe here: https://tracker.adobe.com/

Like
>
Carl Von Stetten
's comment
2018-01-24 09:53:55
2018-01-24 09:53:55
>
Carl Von Stetten
's comment

Good to know queryExecute() is an improvement over the query.cfc.
Unfortunately, this bug also occurs when using the original tag and (as Vinay Jindal mentioned, also with queryExecute())

Like
>
Carl Von Stetten
's comment
2018-01-25 08:15:37
2018-01-25 08:15:37
>
Carl Von Stetten
's comment

Thanks! I already made a bug report back in December: https://tracker.adobe.com/#/view/CF-4200408

Like
Add Comment