QoQ UNION not distinct

dev support
1

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

2

@Brad_Wood also just noticed this bug too

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

1 Like
3

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

4

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

5

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.

6

Seems like, unfortunately, this is still an issue - just came across it today.

7

which version? always mention it pretty please :slight_smile:

8

This has been fixed for years. Click here and see for yourself.

9

I’m running Lucee 5.3.8.206
A QoQ with a UNION in it does not do a distinct in my code. Worked fine under ACF, same code under Lucee produces duplicate values from both sides.

trycf.com suggests it’s running on ACF - the parameter suggests Lucee but the output and cfdump format still suggest ACF behind the scenes
image

10

a really basic rule of thumb with lucee (and any other software TBH) , if you are using an older release, first try the latest stable release

2 Likes
11

You’re probably hitting this
https://luceeserver.atlassian.net/browse/LDEV-3830
which was fixed months ago in the 5.3.9 release.

Yeah, we get it. Telling us your symptom over and over isn’t doing any good. Update your server to the latest stable version and you’ll get the fix :slight_smile:

Yes, trycf is capable of running all sorts of versions of Railo, Lucee, and Adobe CF, but I’m not clear what you’re suggesting. If you’re asking what specific CF engine was running the code I posted above in the trycf link, you’re looking in the wrong place.

image
image1195×409 36.4 KB

1 Like
12

Many thanks, that’s the issue.

13

The original ticket that fixed the distincting behavior of UNION was resolved in December of 2020. [LDEV-3042] - Lucee
Later, a bug was found where that original fix didn’t account for all scenarios, which led to [LDEV-3830] - Lucee

1 Like