Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Running a Query of Queries with UNION does not merge the records

Description

When performing a Query of Queries with an `UNION` in the sql statement to join two queries, the returning query does not merge the records as expected.

Please see the following example:

query1 = queryNew( "id", "integer", [ {id=1}, {id=2}, {id=3} ]); query2 = queryNew( "id", "integer", [ {id=3}, {id=4}, {id=5} ]); qoq = new Query( dbtype = "query", sql = "SELECT id FROM query1 UNION SELECT id FROM query2", query1 = query1, query2 = query2 ).execute(); writeDump([ query1, query2, qoq.getResult() ]);

The entry with id 3 is twice in the result:
http://trycf.com/gist/cdbadc7cbc0a6bbc8d58/lucee

With Adobe CF 10 / CF 11 the result has only 5 entries:
http://trycf.com/gist/cdbadc7cbc0a6bbc8d58/acf

If i want to get the unmerged data, i can use the `UNION ALL` in the sqlstatement, which works as expected in ACF and Lucee.

Environment

None

Activity

Show:

Pothys - MitrahSoft 30 December 2020 at 14:05

I've checked this ticket and the issue was solved by the ticket https://luceeserver.atlassian.net/browse/LDEV-3042#icft=LDEV-3042 fix. I added the fixed version and close this ticket.

Timothy Lux 27 August 2018 at 17:35

Just discovered this bug – sorry, change of behavior between ACF and Lucee. Thanks for posting it. Wish they would have addressed it.

Former user 25 March 2015 at 16:16

correct trycf link for lucee

Fixed

Details

Assignee

Reporter

Priority

Labels

Fix versions

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Affects versions

Created 25 March 2015 at 16:09
Updated 30 December 2020 at 14:06
Resolved 30 December 2020 at 14:06

Flag notifications