4

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.

enter image description here

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 !.
Kannan.P
  • 1,263
  • 7
  • 14
  • So based on what you're describing, after the update every column you specify in the `ORDER BY` clause will create a duplicate column in the dump. Have you opened a support ticket with Adobe? – user12031119 Oct 01 '21 at 15:08
  • I noticed the case of the column name and what is in the `WHERE` clause is different - `userID` vs `userid`. Out of curiosity, does this still happen if you match the case? – Scott Stroz Oct 01 '21 at 16:12
  • I'm not able to duplicate this using CF2021u2 with the QoQ hotfix (2021,0,02,328618). If using CommandBox, it doesn't automatically use the hotfix and will need to be added manually. – James Moberg Oct 01 '21 at 18:57

1 Answers1

5

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.

Miguel-F
  • 13,450
  • 6
  • 38
  • 63
  • 1
    How is that a 'showstopping behavior' when there is an easy fix....make the `order by` match the case of the column names? – Scott Stroz Oct 01 '21 at 16:13
  • I have no idea. – Miguel-F Oct 01 '21 at 16:18
  • In CF2021, if I used QoQ SELECT to return 2 columns while using more columns in the SORT BY clause, ACF would return the columns. They fixed this behavior with a post-patch hotfix. They may have one for 2018 too. (For more info and sample code, check out CF-4211538 https://tracker.adobe.com/#/view/CF-4211538 ) – James Moberg Oct 01 '21 at 19:00