tracker issue : CF-3947110

select a category, or use search below
(searches all categories and all time range)
Title:

Query-of-Query DISTINCT w/missing ORDER BY column should throw an error

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): James Moberg / James Moberg (James Moberg)

Created: 03/02/2015

Components: Database

Versions: 10.0

Failure Type:

Found In Build/Fixed In Build: Final /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Win 2008 Server R2

Vote Count: 2

Listed in the version 2016.0.0.297996 Issues Fixed doc
Verification notes: verified_fixed on August 06, 2017 using build 2016.0.01.298513 
Problem Description: Performing a DISTINCT query-of-queries and sorting by column names that aren't returned should return a syntax error and not non-DISTINCT results.

Steps to Reproduce:
https://gist.github.com/JamoCA/cd4c409501ea5199cd5a

Try it here: http://www.trycf.com/gist/cd4c409501ea5199cd5a (Correctly fails w/Railo & Lucee)

<CFSET TestQuery = QueryNew("Letter,Score", "varchar,integer")>
<CFLOOP LIST="A,B,C" INDEX="thisLetter">
	<CFLOOP LIST="1,2,3" INDEX="thisScore">
		<CFSET QueryAddRow(TestQuery)>
		<CFSET QuerySetCell(TestQuery, "Letter", ThisLetter)>
		<CFSET QuerySetCell(TestQuery, "Score", ThisScore)>
	</CFLOOP>
</CFLOOP>
 
<CFQUERY NAME="Test" DBTYPE="query">SELECT DISTINCT Letter
FROM TestQuery
ORDER BY Score</CFQUERY>
<!--- MSSQL Execution Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified  --->
 
<h2>Distinct Letters</h2>
<p>This query should have thrown a syntax error, but Adobe ColdFusion returns duplicated rows instead.</p>
<CFDUMP VAR="#Test#">
 
<h2>Full Data</h2>
<CFDUMP VAR="#TestQuery#">

Actual Result: Duplicated non-DISTINCT records

Expected Result:  An error.  The SQL syntax is invalid.

Any Workarounds:  Test CFML on an alternate ColdFusion engine to determine if valid syntax before deploying on ACF?

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3947110

External Customer Info:
External Company:  
External Customer Name: James
External Customer Email:  
External Test Config: My Hardware and Environment details:



Not important. Tested it on multiple installations of ColdFusion 9 & 10.

Attachments:

Comments:

I wasn't able to test it on a third-party installation of ColdFusion 11, but the bug probably exists there too. (TryCF.com & CFLive.net do not currently provide testing for ColdFusion 11.) I'm reporting this error specifically on version 10 in hopes that it will be fixed in v10 and not patched only in newer/future versions of ColdFusion.
Comment by External U.
8203 | March 02, 2015 12:44:56 PM GMT
SUBSCRIBE Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters Vote must be between 25 and 4000 characters
Vote by External U.
8208 | March 02, 2015 02:31:00 PM GMT
I'm not sure the issue is that it doesn't error: I cannot see any rule in the ANSI standard that forces this. And, for example, MySQL does not enforce this. If it's a vendor-specific behaviour - even a popular one - Adobe are not compelled to implement it. The problem as I see it is that QoQ doesn't actually perform the DISTINCT operation. Briefer repro case: <cfscript> numbers = queryNew("id,number", "integer,varchar", [ [1, "tahi"], [2, "rua"], [3, "rua"], [4, "toru"], [5, "toru"], [6, "toru"] ]); uniqueNumbers = queryExecute( "SELECT DISTINCT number FROM numbers ORDER BY id DESC", [], {dbtype="query",numbers=numbers} ); writeDump(variables); </cfscript> With the ORDER BY, this returns all records. Without the ORDER BY, it returns just tahi, rua, toru -- Adam
Comment by External U.
8204 | March 02, 2015 02:36:49 PM GMT
This issue is fixed and the fix will be available as part of next major release of ColdFusion.
Comment by Nimit S.
8205 | September 29, 2015 05:46:18 AM GMT
This will now return distinct values even with order by on a column not present in select column list.
Comment by Himavanth R.
8206 | September 29, 2015 07:17:37 AM GMT
subscribing ......................
Vote by External U.
8209 | September 30, 2015 01:28:11 AM GMT
Verified this is fixed in CF2016 Update 1 (build 2016.0.01.298513). Thanks!, -Aaron
Comment by Aaron N.
8207 | August 06, 2017 07:27:44 AM GMT