Make autocomplete prefix sorting case-insensitive
What does this MR do and why?
When sorting matches for autocomplete results, we prioritize matches
that start with the search term. This should be case-insensitive to make
it consistent with the old frontend sorting behavior when the
mention_autocomplete_backend_filtering
is disabled.
Before:
SELECT id, username, name
FROM "users"
INNER JOIN "project_authorizations" ON "users"."id" = "project_authorizations"."user_id"
WHERE "project_authorizations"."project_id" = 278964 AND (REPLACE(users.name, ' ', '') ILIKE '%adam%' OR users.username ILIKE '%adam%')
ORDER BY CASE WHEN starts_with(REPLACE(users.name, ' ', ''), 'adam') OR starts_with(users.username, 'adam') THEN 1 ELSE 2 END, "users"."username" ASC, "users"."id" ASC
LIMIT 10
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/26870/commands/83691
After:
SELECT id, username, name
FROM "users"
INNER JOIN "project_authorizations" ON "users"."id" = "project_authorizations"."user_id"
WHERE "project_authorizations"."project_id" = 278964 AND (REPLACE(users.name, ' ', '') ILIKE '%adam%' OR users.username ILIKE '%adam%')
ORDER BY CASE WHEN REPLACE(users.name, ' ', '') ILIKE 'adam%' OR users.username ILIKE 'adam%' THEN 1 ELSE 2 END, "users"."username" ASC, "users"."id" ASC
LIMIT 10
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/26870/commands/83692
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
How to set up and validate locally
- Enable
mention_autocomplete_backend_filtering
. - Try autocompleting a user and verify that prefix matches are prioritized regardless of case.
Edited by Heinrich Lee Yu