QoQ UNION not distinct

I am using Lucee 5.3.2.77 and notice that Query of Query unions do not return the expected ‘DISTINCT’ result set but rather the ‘UNION ALL’ behaviour. Is there a way to get a distinct result set other than passing through a second QoQ? Is this a bug?

ColdFusion documentation at https://helpx.adobe.com/coldfusion/developing-applications/accessing-and-using-data/using-query-of-queries/query-of-queries-user-guide.html says “By default, the UNION operator removes duplicate rows from the result table. If you use the keyword ALL, then duplicates are included.”

Without reference to an external data source, here is a method of reproducing the behaviour.

<cfset Query1 = QueryNew("Fruit")>
<cfset QueryAddRow(Query1, 2)>
<cfset temp = QuerySetCell(Query1, "Fruit", "Apple", 1)>
<cfset temp = QuerySetCell(Query1, "Fruit", "Orange", 2)>
<cfset Query2 = QueryNew("Fruit")>
<cfset QueryAddRow(Query2, 2)>
<cfset temp = QuerySetCell(Query2, "Fruit", "Apple", 1)>
<cfset temp = QuerySetCell(Query2, "Fruit", "Pear", 2)>
<cfquery dbtype="query" name="Query3">
	SELECT Fruit FROM Query1
	UNION
	SELECT Fruit FROM Query2
	ORDER BY Fruit
</cfquery>
<cfoutput><cfdump var="#Query3#"></cfoutput>

The results are
Fruit
1 Apple
2 Apple
3 Orange
4 Pear

@Brad_Wood also just noticed this bug too

https://twitter.com/bdw429s/status/1188203788399710208?s=19

1 Like

You can vote on the bug I linked to my tweet and maybe it will get fixed.

A workaround is to add a GROUP BY clause on either query and the query will return distinct records:

SELECT Fruit FROM Query1
UNION
SELECT Fruit FROM Query2 GROUP BY fruit
ORDER BY Fruit

Simon, thanks for the suggestion of the GROUP BY work around that avoids a second QoQ. However every column would need to be included in the GROUP BY. It still seems to be a bug to be aware of and the behaviour should be the same as ACF.