Query of query maxrows returns incorrect value when used with DISTINCT

Description

See http://trycf.com/gist/028d7bb6b0c69e2a462dcbd07e6944f5/lucee5

When using maxrows on a grouped query-of-queries (using either DISTINCT or GROUP BY) the limit is applied prior to the grouping, returning an incorrect total.

Adding an aggregate operator (seen in third query) is a workaround.

Environment

Linux

Attachments

1

Activity

Brad Wood 
22 September 2020 at 15:35

Again, the select has no order by, therefore the rows may be returned in any order. This is not an incompatibility. In any database engine, when you do not provide an order by, the rows may come back in any order based on the internal algorithm used to build the select. If you add an order by and the rows are still coming back in a different order, then that would be a finding.

Pothys - MitrahSoft 
22 September 2020 at 11:18
(edited)

, I've checked this ticket with lucee version 5.3.8.74-SNAPSHOT as you said. Seems now, it is incompatibility with ACF. The query result showing as wrong and because, the rows are misorder.

Brad Wood 
7 September 2020 at 06:03

My pull request for ticket https://luceeserver.atlassian.net/browse/LDEV-3042 has addressed this bug (and others)

Pothys - MitrahSoft 
13 April 2017 at 07:18

I've tested and confirmed this issue on lucee 5.1.4.8, Added test suite for this issue.
And Created pull request ( https://github.com/lucee/Lucee/pull/206 )

Jamie Purchase 
12 April 2017 at 14:09

Details

Assignee

Reporter

Priority

Labels

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 12 April 2017 at 13:58
Updated 22 September 2020 at 15:35