Resolve "Can't sort group issues by popularity when searching"
What does this MR do?
Reverts c03386c3, which solves #50246 (closed). I also added some specs to ensure that the feature itself works.
To test this, I re-ran https://gitlab.com/gitlab-org/gitlab-ce/snippets/1721019 with these changes. Before: https://gitlab.com/gitlab-org/gitlab-ce/snippets/1751315 After: https://gitlab.com/gitlab-org/gitlab-ce/snippets/1751318
There are a couple of notable things:
-
A query on the dashboard times out. This isn't great, but it's also hard to optimise the dashboard, and it was failing before.
-
There are a couple of fewer COUNT queries in the second version. I think (but I'm not sure) that's the absence of the CTE simplifying matters.
-
I added a third change, to make the query simpler if the user can see all confidential issues in the group. I don't think this is strictly needed for this bug, but I see this query time out on some DB nodes and not others, while the simplified one doesn't time out on any:
SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE ( issues.confidential IS NOT TRUE OR ( issues.confidential = TRUE AND ( issues.author_id = 443319 OR EXISTS ( SELECT TRUE FROM issue_assignees WHERE user_id = 443319 AND issue_id = issues.id ) OR issues.project_id IN ( SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 443319 AND (project_authorizations.access_level >= 20) ) ) ) ) AND "projects"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS ( SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."id" = 9970 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" ) AND ( EXISTS ( SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 443319 AND (project_authorizations.project_id = projects.id) ) OR projects.visibility_level IN ( 10, 20 ) ) AND ( "project_features"."issues_access_level" IN ( NULL, 20 ) OR ( "project_features"."issues_access_level" = 10 AND EXISTS ( SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 443319 AND (project_authorizations.project_id = projects.id) ) ) ) AND ( "issues"."title" ILIKE '%test%' OR "issues"."description" ILIKE '%test%' ) ORDER BY "issues"."id" DESC
Does this MR meet the acceptance criteria?
-
Changelog entry added, if necessary -
Tests added for this feature/bug -
Conforms to the code review guidelines -
Conforms to the merge request performance guidelines -
Conforms to the style guides -
Conforms to the database guides
Closes #50246 (closed).