User search filtered to group times out
Summary
Searching for a user filtered to a group sometimes (but not always) results in a 500 error.
User search always goes through DB queries, even with Elasticsearch enabled.
Steps to reproduce
- Go to https://gitlab.com/search
- Enter and submit a search term, e.g.
gitlab
- Click on the
Users
tab - Add a group filter by selecting one in the dropdown, e.g.
gitlab-org
Example Project
https://gitlab.com/search?group_id=9970&scope=users&search=gitlab
What is the current bug behavior?
A 500 error is returned.
What is the expected correct behavior?
The search results should be displayed.
Relevant logs and/or screenshots
Running locally in development, the generated SQL query looks like this:
SELECT COUNT(*)
FROM
(SELECT 1 AS one
FROM "users"
WHERE (("users"."name" ILIKE '%gitlab%'
OR "users"."username" ILIKE '%gitlab%')
OR "users"."email" = 'gitlab')
AND "users"."id" IN
(SELECT "users"."id"
FROM
(SELECT "users".*
FROM "users"
WHERE "users"."id" IN
(SELECT "members"."user_id"
FROM "members"
LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE "members"."type" IN ('GroupMember')
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN
(WITH RECURSIVE "base_and_ancestors" AS
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" = 2
UNION SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" = "base_and_ancestors"."parent_id"),
"base_and_descendants" AS
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" = 2
UNION SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."parent_id" = "base_and_descendants"."id") SELECT "namespaces"."id"
FROM
(SELECT "namespaces".*
FROM "base_and_ancestors" AS "namespaces"
WHERE "namespaces"."type" IN ('Group')
UNION SELECT "namespaces".*
FROM "base_and_descendants" AS "namespaces"
WHERE "namespaces"."type" IN ('Group')) namespaces
WHERE "namespaces"."type" IN ('Group')))
UNION SELECT "users".*
FROM "users"
INNER JOIN "members" ON "members"."user_id" = "users"."id"
AND "members"."type" IN ('ProjectMember')
AND "members"."source_type" = 'Project'
AND "members"."requested_at" IS NULL
INNER JOIN "projects" ON "projects"."id" = "members"."source_id"
INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
AND "namespaces"."type" IN ('Group')
AND "namespaces"."type" = 'Group'
WHERE "namespaces"."id" IN
(WITH RECURSIVE "base_and_descendants" AS
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" = 2
UNION SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."parent_id" = "base_and_descendants"."id") SELECT "id"
FROM "base_and_descendants" AS "namespaces")) users)
AND "users"."id" IN
(SELECT members.user_id
FROM "namespaces"
INNER JOIN "members" ON "members"."source_id" = "namespaces"."id"
AND "members"."type" IN ('GroupMember')
AND "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
INNER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE "namespaces"."type" IN ('Group')
ORDER BY "namespaces"."id" DESC)
LIMIT 1001) subquery_for_count;
Output of checks
This bug happens on GitLab.com
Possible fixes
The query seems to check for descendant groups through multiple subqueries, not sure if this is really necessary or duplicating work.
The query seems to check for ancestor groups as well, which doesn't seem necessary for this use-case.
Edited by Markus Koller