[10.x] Update SQL Server to FETCH and OFFSET for queries that do not include an order by#44937
[10.x] Update SQL Server to FETCH and OFFSET for queries that do not include an order by#44937taylorotwell merged 2 commits intolaravel:masterfrom dunhamjared:master
Conversation
|
@dunhamjared this introduced a big bug! Page 1 has no offset and thus is not explicitly sorted by column 0 and thus has another sort order, this must be fixed!! |
|
Hi @joelharkes -- that sounds very odd! I haven't experienced that issue myself, also tried quickly replicating the issue you described with no success. Could you create an issue for this and provide a query example? |
|
@dunhamjared We use guids as primary key and generating the guids in laravel (creates alphabetically data time sorted guis). But SQL server orders differently. This means the inserted record might be on the last 'page' in the databse but when you sort on ID it is actually not the last record in the the list. |
|
I will make a PR to fix this issue ASAP. It should also sort on Select 0 when there is a limit set to avoid this issue. |
|
Opened PR to fix it: #47763 |
|
Nice, glad to see it was a simple fix. :) |
|
@dunhamjared i broke distincts + limits but i can't get it to work, see: #47766 could you perhaps check what im missing? |
3 participants
Overview
SQL Server 2012 introduced the fetch and offset feature.
Laravel started using FETCH and OFFSET in 2021 -- but only when you include an order by.
See PR: #39863
Laravel also only officially supports 2017 and up:
https://laravel.com/docs/9.x/database#introduction
This PR updates the query builder to also use FETCH and OFFSET for queries that do not include an order by.
Others have voiced support for this PR:
https://www.reddit.com/r/laravel/comments/ypooqe
This update improves the speed by 33% and requires less execution steps!
https://gist.github.com/dunhamjared/cb40bbf294ed6f8e48bd60010a31b4f3
Changes
row_number()logic to match the current use ofOFFSET/FETCHOFFSET/FETCHlogic into built in functions$selectComponentsin the correct order for SQL ServerExample
Currently, if you include an order by, it uses offset and fetch:
However, if order by is not included, it falls back to the old method of offsetting the results:
This PR would instead produce:
Edge cases
row_numin their applications when order by is not used, so this could be considered a breaking change.