fix(databases): pin the grid sort tie breaker to the sort direction - #3153
Closed
HarshMN2345 wants to merge 4 commits into
Closed
fix(databases): pin the grid sort tie breaker to the sort direction#3153HarshMN2345 wants to merge 4 commits into
HarshMN2345 wants to merge 4 commits into
Conversation
The API appends `$sequence` to any sort that has no unique attribute, and always ascending. A descending grid sort therefore reached the engine as `column DESC, _id ASC` — a direction mix neither scan direction of the index can serve, so it filesorted the whole table. On large tables that turned a 25-row page into seconds of work, up to a query timeout. Send `$sequence` ourselves, in the same direction as the sort, so the index stays usable. Ascending sorts already lined up with the appended tie breaker and are left alone, as are sorts already on `$id` or `$sequence`.
Contributor
Greptile SummaryThe PR fixes descending database-grid sorts by appending an explicit descending
Confidence Score: 5/5The PR appears safe to merge. The prior filter-versus-sort defect is fixed by checking the parsed query method, and no blocking failure remains. Important Files Changed
Reviews (3): Last reviewed commit: "chore(deps): raise the nanoid floor to c..." | Re-trigger Greptile |
`orderAsc`/`orderDesc` can appear inside a filter's value, so searching the serialized query for that text can pick a filter instead of the sort. When a filter like that was added before a descending sort, the tie breaker was skipped and the sort fell back to the mixed-direction filesort. Parse the query and compare its method. Applies to the same substring check in `extractSortFromQueries` and in the grid's `sort()`, where a matching filter would have been silently dropped when the user changed the sort.
GHSA-28wg-ghj8-5hjv and GHSA-2v37-7h3g-55p8 patch the 3.x line at 3.3.17 and the 5.x line at 5.1.16, so no single override covers both: forcing 5.x onto postcss would hand a CJS consumer an ESM-only package. Every consumer's declared range already permits a patched release, so only the pinned floors and the resolved versions move: nanoid (direct) ^5.1.11 -> ^5.1.16 postcss (override) ^8.5.18 -> ^8.5.26, which floors nanoid at ^3.3.17 @ai-sdk/provider-utils 3.3.11 -> 3.3.18 (declares ^3.3.8) @melt-ui/svelte 5.1.7 -> 5.1.16 (declares ^5.0.4) postcss 3.3.16 -> 3.3.18 (declares ^3.3.17) Integrity hashes come from the registry and no other package is touched. `bun install --frozen-lockfile` leaves the lockfile byte-identical.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
1 participant
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
What does this PR do?
Descending sorts in the database grid were forcing a full filesort on the server.
The API appends
$sequenceas a tie breaker to any sort that has no unique attribute (Database::find), but it appends it without an order type, so the SQL adapter defaults it toASC. A grid sort oforderDesc(column)therefore reached the engine as:That direction mix matches neither scan direction of the index, so MySQL/MariaDB falls back to sorting the whole table. On large tables a 50-row page took seconds, and at times hit the 15s query timeout and returned a 408.
This sends
$sequenceexplicitly, in the same direction as the sort, so the index stays usable:Ascending sorts already lined up with the appended tie breaker and are left untouched, as are sorts already on
$idor$sequence. The default (unsorted) grid query is unchanged —Query.orderDesc('')already resolves to plain_id DESC.Three call sites, covering tables and collections:
buildGridQueries()indatabase-[database]/store.ts— the page-load path, where the user's sort comes back off the URLqueryparamgetCorrectOrderQueries()intable-[table]/spreadsheet.svelteandcollection-[collection]/spreadsheet.svelte— the client-side pagination pathTest Plan
Open a table with enough rows for the index to matter, sort a column descending, and page through it. The outbound request now carries a trailing
{"method":"orderDesc","attribute":"$sequence"}, and the resultingORDER BYis single-direction. Ascending sorts and the unsorted default produce the same queries as before.Ran
bun run format && bun run check && bun run lint && bun run test:unit && bun run build— 0 errors, 239 unit tests passing, build clean.Related PRs and Issues
GET /v1/storage/buckets/{bucketId}/filesWorth noting that this only covers the console. The same mixed-direction
ORDER BYaffects any SDK caller that sorts descending on a non-unique attribute, which is where the slow/v1/usersand/v1/storage/.../filesqueries are coming from. The general fix is for the appended tie breaker to inherit the direction of the preceding order attribute inutopia-php/database.