Loading
Fix epics milestone filter combined with title sort
What does this MR do and why?
Filtering epics by milestone with a title sort fails with PG::InvalidColumnReference, so the roadmap shows "Something went wrong while fetching epics". Epic.in_milestone adds DISTINCT to a join on issues, and the title sort's LOWER(epics.title) is outside the select list.
Matching through an EXISTS subquery on epic_issues, as !105197 (merged) did for the release filter, returns the same epics, each once, without DISTINCT. The scope's other caller, EE::Milestones::UpdateService, passes it to Epics::UpdateDatesService, which reads select(:issue_id) from it inside an IN subquery, so it gets the same ids.
Path
sequenceDiagram
participant UI as roadmap_app.vue
participant R as Resolvers::EpicsResolver
participant F as EpicsFinder
participant S as Epic.in_milestone
participant PG as PostgreSQL
UI->>R: group.epics(milestoneTitle, sort: TITLE_ASC)
R->>F: execute
F->>S: by_milestone
Note over S: before: joins(:issues).distinct<br/>after: where_exists on epic_issues
S-->>F: relation
F->>PG: ORDER BY LOWER(epics.title), epics.id DESC
Note over PG: before: SELECT DISTINCT epics.* is rejected<br/>after: SELECT epics.* WHERE EXISTS returns rows
PG-->>UI: before: Internal server error, after: epics in title orderDatabase
-- before
SELECT DISTINCT "epics".* FROM "epics"
INNER JOIN "epic_issues" ON "epic_issues"."epic_id" = "epics"."id"
INNER JOIN "issues" ON "issues"."id" = "epic_issues"."issue_id"
WHERE ... AND "issues"."milestone_id" IN (...)
-- after
SELECT "epics".* FROM "epics"
WHERE ... AND (EXISTS (SELECT 1 FROM "epic_issues"
INNER JOIN "issues" ON "issues"."id" = "epic_issues"."issue_id"
WHERE "issues"."milestone_id" IN (...) AND "epic_issues"."epic_id" = "epics"."id"))Raw SQL and query plans
The roadmap's group.epics query for gitlab-org (9970), milestone 19.4, user gitlab-qa (1614863), and first: 100, with the membership subqueries a user without confidential epic access gets. Warm runs.
The join over every child issue and its Unique step become a semi-join that stops at the first issue in the milestone, through index_epic_issues_on_epic_id_and_issue_id (index-only) and issues_pkey. No plan has a sequential scan. Most of the time in each plan is the unchanged milestone and group lookups.
Before, Start date sort
SELECT DISTINCT
"epics".*
FROM
"epics"
INNER JOIN "epic_issues" ON "epic_issues"."epic_id" = "epics"."id"
INNER JOIN "issues" ON "issues"."id" = "epic_issues"."issue_id"
WHERE
"epics"."group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 10)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 10)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
UNION
(
SELECT
unnest(traversal_ids)
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"projects"."namespace_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (project_authorizations.project_id = projects.id)
)
OR projects.visibility_level IN (10, 20)
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
AND (visibility_level > 0)
)
)
)
)
AND (
"epics"."confidential" = FALSE
OR "epics"."confidential" = TRUE
AND "epics"."group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 15)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 15)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
)
)
)
)
AND (
"epics"."start_date" IS NOT NULL
OR "epics"."end_date" IS NOT NULL
)
AND (
"epics"."start_date" IS NULL
OR "epics"."start_date" <= '2027-01-02'
)
AND (
"epics"."end_date" IS NULL
OR "epics"."end_date" >= '2026-09-27'
)
AND "issues"."milestone_id" IN (
SELECT
"milestones"."id"
FROM
(
(
SELECT
"milestones".*
FROM
"milestones"
WHERE
"milestones"."project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 10)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 10)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
UNION
(
SELECT
unnest(traversal_ids)
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"projects"."namespace_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (project_authorizations.project_id = projects.id)
)
OR projects.visibility_level IN (10, 20)
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
AND (visibility_level > 0)
)
)
)
)
AND (
"project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20, 30)
OR (
"project_features"."issues_access_level" = 10
AND EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (
project_authorizations.project_id = project_features.project_id
)
AND (project_authorizations.access_level >= 10)
)
)
)
)
)
UNION ALL
(
SELECT
"milestones".*
FROM
"milestones"
WHERE
"milestones"."group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 10)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 10)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
UNION
(
SELECT
unnest(traversal_ids)
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"projects"."namespace_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (project_authorizations.project_id = projects.id)
)
OR projects.visibility_level IN (10, 20)
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
AND (visibility_level > 0)
)
)
)
)
)
) milestones
WHERE
"milestones"."title" = '19.4'
)
ORDER BY
"epics"."start_date" ASC NULLS LAST,
"epics"."id" DESC
LIMIT
101https://console.postgres.ai/gitlab/gitlab-production-main/sessions/61727/commands/166913 (497 ms, 438,948 shared buffer hits)
Before, Title sort
The same query ordered by LOWER("epics"."title") ASC, "epics"."id" DESC fails with ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list.
After, Title sort
SELECT
"epics".*
FROM
"epics"
WHERE
"epics"."group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 10)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 10)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
UNION
(
SELECT
unnest(traversal_ids)
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"projects"."namespace_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (project_authorizations.project_id = projects.id)
)
OR projects.visibility_level IN (10, 20)
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
AND (visibility_level > 0)
)
)
)
)
AND (
"epics"."confidential" = FALSE
OR "epics"."confidential" = TRUE
AND "epics"."group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 15)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 15)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
)
)
)
)
AND (
"epics"."start_date" IS NOT NULL
OR "epics"."end_date" IS NOT NULL
)
AND (
"epics"."start_date" IS NULL
OR "epics"."start_date" <= '2027-01-02'
)
AND (
"epics"."end_date" IS NULL
OR "epics"."end_date" >= '2026-09-27'
)
AND (
EXISTS (
SELECT
1
FROM
"epic_issues"
INNER JOIN "issues" ON "issues"."id" = "epic_issues"."issue_id"
WHERE
"issues"."milestone_id" IN (
SELECT
"milestones"."id"
FROM
(
(
SELECT
"milestones".*
FROM
"milestones"
WHERE
"milestones"."project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 10)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 10)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
UNION
(
SELECT
unnest(traversal_ids)
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"projects"."namespace_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (project_authorizations.project_id = projects.id)
)
OR projects.visibility_level IN (10, 20)
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
AND (visibility_level > 0)
)
)
)
)
AND (
"project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20, 30)
OR (
"project_features"."issues_access_level" = 10
AND EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (
project_authorizations.project_id = project_features.project_id
)
AND (project_authorizations.access_level >= 10)
)
)
)
)
)
UNION ALL
(
SELECT
"milestones".*
FROM
"milestones"
WHERE
"milestones"."group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 10)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 10)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
UNION
(
SELECT
unnest(traversal_ids)
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"projects"."namespace_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (project_authorizations.project_id = projects.id)
)
OR projects.visibility_level IN (10, 20)
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
AND (visibility_level > 0)
)
)
)
)
)
) milestones
WHERE
"milestones"."title" = '19.4'
)
AND "epic_issues"."epic_id" = "epics"."id"
)
)
ORDER BY
LOWER("epics"."title") ASC,
"epics"."id" DESC
LIMIT
101https://console.postgres.ai/gitlab/gitlab-production-main/sessions/61727/commands/167148 (474 ms, 417,451 shared buffer hits)
After, Start date sort
SELECT
"epics".*
FROM
"epics"
WHERE
"epics"."group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 10)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 10)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
UNION
(
SELECT
unnest(traversal_ids)
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"projects"."namespace_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (project_authorizations.project_id = projects.id)
)
OR projects.visibility_level IN (10, 20)
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
AND (visibility_level > 0)
)
)
)
)
AND (
"epics"."confidential" = FALSE
OR "epics"."confidential" = TRUE
AND "epics"."group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 15)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 15)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
)
)
)
)
AND (
"epics"."start_date" IS NOT NULL
OR "epics"."end_date" IS NOT NULL
)
AND (
"epics"."start_date" IS NULL
OR "epics"."start_date" <= '2027-01-02'
)
AND (
"epics"."end_date" IS NULL
OR "epics"."end_date" >= '2026-09-27'
)
AND (
EXISTS (
SELECT
1
FROM
"epic_issues"
INNER JOIN "issues" ON "issues"."id" = "epic_issues"."issue_id"
WHERE
"issues"."milestone_id" IN (
SELECT
"milestones"."id"
FROM
(
(
SELECT
"milestones".*
FROM
"milestones"
WHERE
"milestones"."project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 10)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 10)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
UNION
(
SELECT
unnest(traversal_ids)
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"projects"."namespace_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (project_authorizations.project_id = projects.id)
)
OR projects.visibility_level IN (10, 20)
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
AND (visibility_level > 0)
)
)
)
)
AND (
"project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20, 30)
OR (
"project_features"."issues_access_level" = 10
AND EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (
project_authorizations.project_id = project_features.project_id
)
AND (project_authorizations.access_level >= 10)
)
)
)
)
)
UNION ALL
(
SELECT
"milestones".*
FROM
"milestones"
WHERE
"milestones"."group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
id IN (
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"members"."source_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (access_level >= 10)
AND "members"."user_id" = 1614863 /* allow_cross_joins_across_databases */
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids && ARRAY(
SELECT
"group_group_links"."shared_group_id"
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (group_access >= 10)
AND "group_group_links"."shared_with_group_id" IN (
SELECT
"members"."source_id"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" = 1614863
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
)
)::integer[]
)
)
UNION
(
SELECT
unnest(traversal_ids)
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" IN (
SELECT
"projects"."namespace_id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
)
AND (
EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 1614863
AND (project_authorizations.project_id = projects.id)
)
OR projects.visibility_level IN (10, 20)
)
)
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
AND (visibility_level > 0)
)
)
)
)
)
) milestones
WHERE
"milestones"."title" = '19.4'
)
AND "epic_issues"."epic_id" = "epics"."id"
)
)
ORDER BY
"epics"."start_date" ASC NULLS LAST,
"epics"."id" DESC
LIMIT
101https://console.postgres.ai/gitlab/gitlab-production-main/sessions/61727/commands/167149 (482 ms, 417,451 shared buffer hits)
References
- Closes #632624
How to set up and validate locally
-
On a roadmap, sort by Title and filter by a milestone holding the epics' child issues.
mastershows the alert; this branch lists the epics by title. -
The new examples fail on
master:bundle exec rspec ee/spec/models/epic_spec.rb:186 ee/spec/finders/epics_finder_spec.rb:555 ee/spec/requests/api/graphql/group/epics_spec.rb:343
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Claude Code wrote this change. It ran RuboCop and the specs above on a source checkout of master, reproduced the bug on self-managed 19.2.6 and 18.8.9 test instances, and ran the plans on Database Lab.