You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
The text was updated successfully, but these errors were encountered:
Bug Report
I believe LimitSubqueryOutputWalker::preserveSqlOrdering is still broken when there are no WHERE clauses on a related table - Ordering is not right.
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
Results are still be ordered arbitrarily, I guess mysql decides on that because of DISTINCT usage
Results are now be ordered DESC properly
Expected behavior
Ordering should be DESC in both cases
The text was updated successfully, but these errors were encountered: