The query of query ( QoQ ) with order by return duplicate column after update12 Coldfusion 2018

Question

I've updated my coldfusion 2018 server with most recent update12 by Adobe. As soon as I've updated the server I've wrote some QoQ for my application with ORDER BY in my QoQ. Whenever I use order by in QoQ then the result data have some duplicate columns.

For my simple sample query as example, 

<cfquery name="testRead" datasource="testmssql">
        SELECT * FROM loginDetails
    </cfquery>

    <cfdump var="#testRead#" label="Main Query">

    <cfquery name='readSub' dbtype="query">
      SELECT userID, Username FROM testRead 
      ORDER BY userid DESC
    </cfquery>

    <cfdump var="#readSub#" label="QoQ Result" abort="true">

**Output:** Refer my image please.

Here you can see the second QoQ dump have two userID column. I'm not sure why we are having it here & where it's come from. ? If I add one more column in ORDER BY list then that column also get duplicated in result query. For example, If add ORDER BY userid DESC, userName then the query dump query having userID,userid,userName,username.

Note : It's not happening before my update12. And it's not happening for main query.

Any thoughts ? Please share. Thank you advance !.

Answer 1

This is a known issue with the update and a bug has been filed with Adobe. I would recommend you add a comment and vote for the bug.

Adobe Bug Tracker - CF-4212383

Duplicate columns with the same name in a Query of Queries which contains an ORDER BY clause.

Description from that bug:

Problem Description:

After applying CF 2021 Update 2, when using an ORDER BY clause in a QoQ, the fields in the ORDER BY clause have become case sensitive, and if they don't match exactly the case of the fields in the SELECT list, then a duplicate column is added to the resultant query, resulting in a query that has two (or more) columns with the same name.

Further, if no fields are added to the SELECT list and * is used instead, the fields in the ORDER BY clause must be upper case, otherwise duplicate columns with the same name (but different case) again appear in the resultant query.

This showstopping behaviour has been introduced in CF 2021 Update 2. CF2021 Update 1 behaves as expected. (CF2016 also behaves as expected).

Even though the bug mentions CF 2021 Update 2, it also affects CF 2018 Update 12. As verified by bug CF-4212430 submitted for CF 2018 Update 12 which was closed as a duplicate of the CF 2021 bug.