LimitSubqueryOutputWalker used @ Paginator does not order properly #6069

New Issue

admin · 2026-01-22T15:26:08+01:00

admin commented

2026-01-22 15:26:08 +01:00

Originally created by @wucdbm on GitHub (Sep 24, 2018).

Bug Report

I believe LimitSubqueryOutputWalker::preserveSqlOrdering is still broken when there are no WHERE clauses on a related table - Ordering is not right.

Q	A
BC Break	no
Version	2.6.2

Summary

The generated SQL of preserveSqlOrdering uses DISTINCT, which as far as I understand is considered an aggregate function, and in MySQL 5.7.5+ with ONLY_FULL_GROUP_BY enabled by default it leads to unexpected behaviour

Still unsure whether it's ONLY_FULL_GROUP_BY in particular causing this, but the first time I experienced it was when we upgraded to 5.7.5+

Basically, the code I proposed which was later used in another PR for the final implementation in 2.6 still has this problem.

EDIT: Results are SELECTED in the wrong order, but the displayed result subset is ordered properly, eg:
For a table with records with PK 1-50

selected results will be 1-20 going from 20 to 1 even if desired order is DESC 50 and down

Current behavior

SELECT fields FROM entity JOIN several relations ORDER BY entity.PRIMARY_KEY DESC would not give you the most recent records of entity, and will still have them ordered ASC

SELECT fields FROM entity JOIN several relations WHERE relation.something = :someValue ORDER BY entity.PK DESC would work just fine and will have the results ordered properly

How to reproduce

Create two entities
For example, Reservation and ReservationStatus where each Reservation has a status

Paginate the Reservation, order by its PK DESC

Results are still be ordered arbitrarily, I guess mysql decides on that because of DISTINCT usage

Then do a WHERE status.id = 1
Results are now be ordered DESC properly

Expected behavior

Ordering should be DESC in both cases

Originally created by @wucdbm on GitHub (Sep 24, 2018). ### Bug Report I believe LimitSubqueryOutputWalker::preserveSqlOrdering is still broken when there are no WHERE clauses on a related table - Ordering is not right. | Q | A |------------ | ------ | BC Break | no | Version | 2.6.2 #### Summary The generated SQL of preserveSqlOrdering uses DISTINCT, which as far as I understand is considered an aggregate function, and in MySQL 5.7.5+ with ONLY_FULL_GROUP_BY enabled by default it leads to unexpected behaviour Still unsure whether it's ONLY_FULL_GROUP_BY in particular causing this, but the first time I experienced it was when we upgraded to 5.7.5+ Basically, the code I proposed which was later used in another PR for the final implementation in 2.6 still has this problem. EDIT: Results are SELECTED in the wrong order, but the displayed result subset is ordered properly, eg: For a table with records with PK 1-50 selected results will be 1-20 going from 20 to 1 even if desired order is DESC 50 and down #### Current behavior SELECT fields FROM entity JOIN several relations ORDER BY entity.PRIMARY_KEY DESC would not give you the most recent records of entity, and will still have them ordered ASC SELECT fields FROM entity JOIN several relations WHERE relation.something = :someValue ORDER BY entity.PK DESC would work just fine and will have the results ordered properly #### How to reproduce Create two entities For example, Reservation and ReservationStatus where each Reservation has a status 1) Paginate the Reservation, order by its PK DESC Results are still be ordered arbitrarily, I guess mysql decides on that because of DISTINCT usage 2) Then do a WHERE status.id = 1 Results are now be ordered DESC properly #### Expected behavior Ordering should be DESC in both cases

admin closed this issue

2026-01-22 15:26:10 +01:00

admin commented

2026-01-22 15:26:13 +01:00

@wucdbm commented on GitHub (Mar 11, 2021):

Does not seem to be the case with recent versions of Doctrine, closing

@wucdbm commented on GitHub (Mar 11, 2021): Does not seem to be the case with recent versions of Doctrine, closing

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6069