search

aarondfrancis / fast-paginate

A fast implementation of offset/limit pagination for Laravel.
MIT License
1.21k stars 56 forks source link

add test to prove pagination via relation is ordered differently than… #34

Closed Naoray closed 2 years ago

Naoray commented 2 years ago

… on normal pagination

Naoray commented 2 years ago

@aarondfrancis alright. it fails locally, but not here. That's strange

Naoray commented 2 years ago

Interestingly this only seems to fail locally with sqlite instead of mysql as db driver. In the other project I do use mysql and it's also returning results in the wrong order. I will report back if I find the cause of it, otherwise just disregard my comment :)

aarondfrancis commented 2 years ago

It looks like from the test that you aren't ordering the query in any way, which leaves it up to the database to order it however it chooses, which could be different each time. That may explain it!

Naoray commented 2 years ago

I understand that the type of database does make a difference. But in the other project where I encountered this issue, the database was always MySQL, but when using ->fastPaginate() instead of ->paginate() I got different results during tests. I understand that it's nearly impossible for you to fix something where you don't have a failing test nor something to reproduce it, so I'm only asking whether you know of a case where fastPaginate() would order results differently than paginate() when no order by clause was added to the query?

aarondfrancis commented 2 years ago

I'm only asking whether you know of a case where fastPaginate() would order results differently than paginate() when no order by clause was added to the query?

If you don't add an orderBy to the query, MySQL gets to decide how to order the rows. Even if you ran paginate twice with no orderBy, it could return different orders each time. In this case, it doesn't have anything to do with different databases, MySQL itself can change per query.

Even if you use an orderBy, if it's not specific enough then you could still get non-deterministic results! See for example: https://blog.pythian.com/mysql-may-return-results-in-non-deterministic-order-with-order-by.

Basically, MySQL makes no guarantees about deterministic ordering without a deterministic orderBy. So why is paginate returning a different order than fastPaginate? Because MySQL gets to decide what order the results show up in, and it may be different every time you run the query!

Naoray commented 2 years ago

@aarondfrancis thanks a lot for your patience and your throughout explanation!