CF-4202730: In a query, when you select names with order, the order is incorrect in the result. (Workarounds) ColdFusion CFML

CF-4202730-comment-20221101.cfm

<!---	CF-4202730: In a query, when you select names with order, the order is incorrect in the result.
	AKA "Query-of-query has incorrect letter sorting"
	https://tracker.adobe.com/#/view/CF-4202730 (my comments are here)
    Workaround posted: 11/1/2022
    ColdFusion 2021u5 indicates that this CFML bug is fixed, but it doesn't appear to be. (And you can't perform a QoQ using CFFiddle.org)
    I'd love to be able to use "ORDER BY LOWER(myColumn) ASC", but I don't believe that it's supported by java QoQ
    even though it's supported by MSSQL.
--->

<h1>CF-4202730: In a query, when you select names with order, the order is incorrect in the result.</h1>

<cfset qTest = querynew("id,name","integer,varchar", [
	{id:1, name:"Apple"}
	,{id:2, name:"tree"}
	,{id:3, name:"car"}
	,{id:4, name:"Street"}
	,{id:5, name:"house"}
])>

<h2>Original unsorted query</h2>
<cfdump var="#qTest#" label="qTest (original)"><br><br>

<h2>According to CF2021u5 change log, this query should be sorted and ignore case, but it doesn't.</h2>
<cfquery name="qTestOrder" dbtype="query">
SELECT id, name
FROM qTest
ORDER BY name ASC</cfquery>
<cfdump var="#qTestOrder#" label="qTestOrder"><br><br>

<h2>Workaround #1: Include the column in the results w/LOWER function and sort by column index</h2>
<cftry>
	<cfquery name="qTestOrder" dbtype="query">
	SELECT id, name, LOWER(name)
	FROM qTest
	ORDER BY 3 ASC</cfquery>
	<cfdump var="#qTestOrder#" label="qTestOrder (w/temp lowercased column)"><br><br>
	<cfcatch>
		<cfoutput>
			<p style="color:red;">#cfcatch.message# (#cfcatch.type#)</p>
		</cfoutput>
	</cfcatch>
</cftry>

<h2>Workaround #2: Add temporary column, copy lowercased value(s) to be used for sorting, perform another QoQ & omit temp column from results.</h2>
<cfscript>
queryaddcolumn(qTest, "namesort", "varchar", []);
for(row in qTest){
	qTest.nameSort[qTest.currentRow] = lcase(qTest.name);
}
qTest = queryexecute("SELECT id, name FROM qTest ORDER BY NameSort", {}, {dbtype:"query"});
cfdump(var=qTest, label="qTestOrder (omiting temp lowercased column");
</cfscript>