Redo reverted Assignee filter change for MergeRequest
What does this MR do?
This MR reverts !45602 (merged) which reverted original MR !43497 (merged)
There was an issue which raised ActiveRecord:StatementInvalid due to GROUP BY functions not working with UNION results.
I found two places in merge requests filters that were using GROUP BY and this attempts to replace those statements with subqueries as well as brining back the original MR content.
It's difficult to test all possible combinations so I've just added one complex filters to catch this kind of errors.
I feel it needs some bigger refactoring around filtering, but I've just made the necessary changes for now. Also, I find that the test coverage weren't that great so I hope I didn't miss anything.
EDITED: I've switched to use IN with UNION which doesn't cause this problem so no need to change existing GROUP BY statements.
Detail for the incident can be found at https://gitlab.com/gitlab-com/gl-infra/production/-/issues/2857 with some suggestions. However, I went down the path of close to !34503 (merged) due to performance and correctness.
Related to: #237922 (closed)
Database
These are a couple of queries that I've discovered during my investigation.
Filtering MRs via MR dashboard with Assignee(which includes Reviewer) + 2 Approved by users
Sql query with IN and UNION
SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "approvals" ON "approvals"."merge_request_id" = "merge_requests"."id"
INNER JOIN "users" ON "users"."id" = "approvals"."user_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND "merge_requests"."id" IN
(SELECT "merge_requests"."id"
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND EXISTS
(SELECT TRUE
FROM "merge_request_reviewers"
WHERE "merge_request_reviewers"."merge_request_id" = "merge_requests"."id"
AND "merge_request_reviewers"."user_id" = 1))) merge_requests)
AND "projects"."archived" = FALSE
AND "users"."username" IN ('root',
'reported_user_15')
GROUP BY "merge_requests"."id"
HAVING (COUNT(users.id) = 2)Explain: https://explain.depesz.com/s/pHkF
Sql query with just UNION (previous approach)
EXPLAIN
SELECT “merge_requests”.*
FROM (
(SELECT “merge_requests”.*
FROM “merge_requests”
INNER JOIN “projects” ON “projects”.“id” = “merge_requests”.“target_project_id”
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM “project_authorizations”
WHERE “project_authorizations”.“user_id” = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0, 10, 20))
AND (“project_features”.“merge_requests_access_level” > 0
OR “project_features”.“merge_requests_access_level” IS NULL)
AND (“merge_requests”.“state_id” IN (1))
AND (EXISTS
(SELECT TRUE
FROM “merge_request_assignees”
WHERE “merge_request_assignees”.“user_id” IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT “merge_requests”.*
FROM “merge_requests”
INNER JOIN “projects” ON “projects”.“id” = “merge_requests”.“target_project_id”
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM “project_authorizations”
WHERE “project_authorizations”.“user_id” = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0, 10, 20))
AND (“project_features”.“merge_requests_access_level” > 0
OR “project_features”.“merge_requests_access_level” IS NULL)
AND (“merge_requests”.“state_id” IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN “projects” ON “projects”.“id” = “merge_requests”.“target_project_id”
WHERE “projects”.“archived” = FALSE
AND (EXISTS
(SELECT 1
FROM “approvals”
INNER JOIN “users” ON “users”.“id” = “approvals”.“user_id”
WHERE (approvals.merge_request_id = merge_requests.id
AND users.username = ‘root’)))
AND (EXISTS
(SELECT 1
FROM “approvals”
INNER JOIN “users” ON “users”.“id” = “approvals”.“user_id”
WHERE (approvals.merge_request_id = merge_requests.id
AND users.username = ‘reported_user_15’)))Explain: https://explain.depesz.com/s/oVR8
Sql query before this change
SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "approvals" ON "approvals"."merge_request_id" = "merge_requests"."id"
INNER JOIN "users" ON "users"."id" = "approvals"."user_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id))
AND "projects"."archived" = FALSE
AND "users"."username" IN ('root',
'suk')
GROUP BY "merge_requests"."id"
HAVING (COUNT(users.id) = 2)Filtering MRs via MR dashboard with Assignee(which includes Reviewer) + 2 Approvers
This generates massive SQL query which exceeds database-lab's string size limitation so I couldn't run it on there.
I have all ears to hear if anyone has any suggestion to reduce the size of this queries. However, it was already huge
Sql query with IN and UNION
SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND "merge_requests"."id" IN
(SELECT "merge_requests"."id"
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND EXISTS
(SELECT TRUE
FROM "merge_request_reviewers"
WHERE "merge_request_reviewers"."merge_request_id" = "merge_requests"."id"
AND "merge_request_reviewers"."user_id" = 1))) merge_requests)
AND "projects"."archived" = FALSE
AND "merge_requests"."id" IN
(SELECT "merge_requests"."id"
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id"
INNER JOIN "approval_merge_request_rules_users" ON "approval_merge_request_rules_users"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id"
INNER JOIN "users" ON "users"."id" = "approval_merge_request_rules_users"."user_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND "merge_requests"."id" IN
(SELECT "merge_requests"."id"
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND EXISTS
(SELECT TRUE
FROM "merge_request_reviewers"
WHERE "merge_request_reviewers"."merge_request_id" = "merge_requests"."id"
AND "merge_request_reviewers"."user_id" = 1))) merge_requests)
AND "projects"."archived" = FALSE
AND "users"."username" IN ('root',
'suk')
GROUP BY merge_requests.id
HAVING (COUNT(users.id) = 2))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id"
INNER JOIN "approval_merge_request_rules_groups" ON "approval_merge_request_rules_groups"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "approval_merge_request_rules_groups"."group_id"
AND "namespaces"."type" = 'Group'
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND "merge_requests"."id" IN
(SELECT "merge_requests"."id"
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND EXISTS
(SELECT TRUE
FROM "merge_request_reviewers"
WHERE "merge_request_reviewers"."merge_request_id" = "merge_requests"."id"
AND "merge_request_reviewers"."user_id" = 1))) merge_requests)
AND "projects"."archived" = FALSE
AND "users"."username" IN ('root',
'suk')
GROUP BY merge_requests.id
HAVING (COUNT(users.id) = 2))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id"
INNER JOIN "approval_project_rules_users" ON "approval_project_rules_users"."approval_project_rule_id" = "approval_project_rules"."id"
INNER JOIN "users" ON "users"."id" = "approval_project_rules_users"."user_id"
LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND "merge_requests"."id" IN
(SELECT "merge_requests"."id"
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND EXISTS
(SELECT TRUE
FROM "merge_request_reviewers"
WHERE "merge_request_reviewers"."merge_request_id" = "merge_requests"."id"
AND "merge_request_reviewers"."user_id" = 1))) merge_requests)
AND "projects"."archived" = FALSE
AND "approval_merge_request_rules"."id" IS NULL
AND "users"."username" IN ('root',
'suk')
GROUP BY merge_requests.id
HAVING (COUNT(users.id) = 2))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id"
INNER JOIN "approval_project_rules_groups" ON "approval_project_rules_groups"."approval_project_rule_id" = "approval_project_rules"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "approval_project_rules_groups"."group_id"
AND "namespaces"."type" = 'Group'
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND "merge_requests"."id" IN
(SELECT "merge_requests"."id"
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND EXISTS
(SELECT TRUE
FROM "merge_request_reviewers"
WHERE "merge_request_reviewers"."merge_request_id" = "merge_requests"."id"
AND "merge_request_reviewers"."user_id" = 1))) merge_requests)
AND "projects"."archived" = FALSE
AND "approval_merge_request_rules"."id" IS NULL
AND "users"."username" IN ('root',
'suk')
GROUP BY merge_requests.id
HAVING (COUNT(users.id) = 2))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND "merge_requests"."id" IN
(SELECT "merge_requests"."id"
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND EXISTS
(SELECT TRUE
FROM "merge_request_reviewers"
WHERE "merge_request_reviewers"."merge_request_id" = "merge_requests"."id"
AND "merge_request_reviewers"."user_id" = 1))) merge_requests)
AND "projects"."archived" = FALSE)Local Explain: https://explain.depesz.com/s/RTGD
Sql query with just UNION (previous approach)
EXPLAIN ANALYZE
SELECT "merge_requests".*
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
WHERE "projects"."archived" = FALSE
AND "merge_requests"."id" IN
(SELECT "merge_requests"."id"
FROM (
(SELECT "merge_requests".*
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
WHERE "projects"."archived" = FALSE
AND (EXISTS
(SELECT "approval_merge_request_rules".*
FROM "approval_merge_request_rules"
INNER JOIN "approval_merge_request_rules_users" ON "approval_merge_request_rules_users"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id"
INNER JOIN "users" ON "users"."id" = "approval_merge_request_rules_users"."user_id"
WHERE (approval_merge_request_rules.merge_request_id = merge_requests.id
AND users.username = 'root')))
AND (EXISTS
(SELECT "approval_merge_request_rules".*
FROM "approval_merge_request_rules"
INNER JOIN "approval_merge_request_rules_users" ON "approval_merge_request_rules_users"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id"
INNER JOIN "users" ON "users"."id" = "approval_merge_request_rules_users"."user_id"
WHERE (approval_merge_request_rules.merge_request_id = merge_requests.id
AND users.username = 'suk'))))
UNION
(SELECT "merge_requests".*
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
WHERE "projects"."archived" = FALSE
AND (EXISTS
(SELECT "approval_merge_request_rules".*
FROM "approval_merge_request_rules"
INNER JOIN "approval_merge_request_rules_groups" ON "approval_merge_request_rules_groups"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "approval_merge_request_rules_groups"."group_id"
AND "namespaces"."type" = 'Group'
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE (approval_merge_request_rules.merge_request_id = merge_requests.id
AND users.username = 'root')))
AND (EXISTS
(SELECT "approval_merge_request_rules".*
FROM "approval_merge_request_rules"
INNER JOIN "approval_merge_request_rules_groups" ON "approval_merge_request_rules_groups"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "approval_merge_request_rules_groups"."group_id"
AND "namespaces"."type" = 'Group'
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE (approval_merge_request_rules.merge_request_id = merge_requests.id
AND users.username = 'suk'))))
UNION
(SELECT "merge_requests".*
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id"
WHERE "projects"."archived" = FALSE
AND "approval_merge_request_rules"."id" IS NULL
AND (EXISTS
(SELECT "projects".*
FROM "projects"
INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id"
INNER JOIN "approval_project_rules_users" ON "approval_project_rules_users"."approval_project_rule_id" = "approval_project_rules"."id"
INNER JOIN "users" ON "users"."id" = "approval_project_rules_users"."user_id"
WHERE (projects.id = merge_requests.target_project_id
AND users.username = 'root')))
AND (EXISTS
(SELECT "projects".*
FROM "projects"
INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id"
INNER JOIN "approval_project_rules_users" ON "approval_project_rules_users"."approval_project_rule_id" = "approval_project_rules"."id"
INNER JOIN "users" ON "users"."id" = "approval_project_rules_users"."user_id"
WHERE (projects.id = merge_requests.target_project_id
AND users.username = 'suk'))))
UNION
(SELECT "merge_requests".*
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id"
WHERE "projects"."archived" = FALSE
AND "approval_merge_request_rules"."id" IS NULL
AND (EXISTS
(SELECT "projects".*
FROM "projects"
INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id"
INNER JOIN "approval_project_rules_groups" ON "approval_project_rules_groups"."approval_project_rule_id" = "approval_project_rules"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "approval_project_rules_groups"."group_id"
AND "namespaces"."type" = 'Group'
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE (projects.id = merge_requests.target_project_id
AND users.username = 'root')))
AND (EXISTS
(SELECT "projects".*
FROM "projects"
INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id"
INNER JOIN "approval_project_rules_groups" ON "approval_project_rules_groups"."approval_project_rule_id" = "approval_project_rules"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "approval_project_rules_groups"."group_id"
AND "namespaces"."type" = 'Group'
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE (projects.id = merge_requests.target_project_id
AND users.username = 'suk'))))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
WHERE "projects"."archived" = FALSE)Local Explain: https://explain.depesz.com/s/Mu42
Sql query before this change
SELECT "merge_requests".*
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
WHERE "projects"."archived" = FALSE
AND "merge_requests"."id" IN
(SELECT "merge_requests"."id"
FROM (
(SELECT "merge_requests".*
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
WHERE "projects"."archived" = FALSE
AND (EXISTS
(SELECT "approval_merge_request_rules".*
FROM "approval_merge_request_rules"
INNER JOIN "approval_merge_request_rules_users" ON "approval_merge_request_rules_users"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id"
INNER JOIN "users" ON "users"."id" = "approval_merge_request_rules_users"."user_id"
WHERE (approval_merge_request_rules.merge_request_id = merge_requests.id
AND users.username = 'root')))
AND (EXISTS
(SELECT "approval_merge_request_rules".*
FROM "approval_merge_request_rules"
INNER JOIN "approval_merge_request_rules_users" ON "approval_merge_request_rules_users"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id"
INNER JOIN "users" ON "users"."id" = "approval_merge_request_rules_users"."user_id"
WHERE (approval_merge_request_rules.merge_request_id = merge_requests.id
AND users.username = 'suk'))))
UNION
(SELECT "merge_requests".*
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
WHERE "projects"."archived" = FALSE
AND (EXISTS
(SELECT "approval_merge_request_rules".*
FROM "approval_merge_request_rules"
INNER JOIN "approval_merge_request_rules_groups" ON "approval_merge_request_rules_groups"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "approval_merge_request_rules_groups"."group_id"
AND "namespaces"."type" = 'Group'
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE (approval_merge_request_rules.merge_request_id = merge_requests.id
AND users.username = 'root')))
AND (EXISTS
(SELECT "approval_merge_request_rules".*
FROM "approval_merge_request_rules"
INNER JOIN "approval_merge_request_rules_groups" ON "approval_merge_request_rules_groups"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "approval_merge_request_rules_groups"."group_id"
AND "namespaces"."type" = 'Group'
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE (approval_merge_request_rules.merge_request_id = merge_requests.id
AND users.username = 'suk'))))
UNION
(SELECT "merge_requests".*
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id"
WHERE "projects"."archived" = FALSE
AND "approval_merge_request_rules"."id" IS NULL
AND (EXISTS
(SELECT "projects".*
FROM "projects"
INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id"
INNER JOIN "approval_project_rules_users" ON "approval_project_rules_users"."approval_project_rule_id" = "approval_project_rules"."id"
INNER JOIN "users" ON "users"."id" = "approval_project_rules_users"."user_id"
WHERE (projects.id = merge_requests.target_project_id
AND users.username = 'root')))
AND (EXISTS
(SELECT "projects".*
FROM "projects"
INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id"
INNER JOIN "approval_project_rules_users" ON "approval_project_rules_users"."approval_project_rule_id" = "approval_project_rules"."id"
INNER JOIN "users" ON "users"."id" = "approval_project_rules_users"."user_id"
WHERE (projects.id = merge_requests.target_project_id
AND users.username = 'suk'))))
UNION
(SELECT "merge_requests".*
FROM (
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN (1)
AND merge_request_id = merge_requests.id)))
UNION
(SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,
10,
20))
AND ("project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT TRUE
FROM merge_request_reviewers
WHERE user_id = 1
AND merge_request_id = merge_requests.id)))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id"
WHERE "projects"."archived" = FALSE
AND "approval_merge_request_rules"."id" IS NULL
AND (EXISTS
(SELECT "projects".*
FROM "projects"
INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id"
INNER JOIN "approval_project_rules_groups" ON "approval_project_rules_groups"."approval_project_rule_id" = "approval_project_rules"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "approval_project_rules_groups"."group_id"
AND "namespaces"."type" = 'Group'
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE (projects.id = merge_requests.target_project_id
AND users.username = 'root')))
AND (EXISTS
(SELECT "projects".*
FROM "projects"
INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id"
INNER JOIN "approval_project_rules_groups" ON "approval_project_rules_groups"."approval_project_rule_id" = "approval_project_rules"."id"
INNER JOIN "namespaces" ON "namespaces"."id" = "approval_project_rules_groups"."group_id"
AND "namespaces"."type" = 'Group'
INNER JOIN "members" ON "members"."source_type" = 'Namespace'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."access_level" != 5
AND "members"."source_id" = "namespaces"."id"
AND "members"."type" = 'GroupMember'
INNER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE (projects.id = merge_requests.target_project_id
AND users.username = 'suk'))))) merge_requests
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
WHERE "projects"."archived" = FALSE)Screenshots (strongly suggested)
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec -
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Edited by Sincheol (David) Kim