Fix group filter pagination bug
What does this MR do and why?
When using filtering, some items persisted across pages i.e. even though some items have been shown on page 1, they would reappear on page 2. A spec has been added that covers this case. An important point to note is if a parent group and a subgroup match the filter then the parent group would still appear twice in the list. Once as itself and once as the ancestor of the subgroup.
You can watch the below video to see this quirky behavior on the master branch.
Screenshots or screen recordings
Query plans
Before
Raw Query
-- Before
WITH "base_ancestors_cte" AS MATERIALIZED (SELECT "namespaces"."traversal_ids"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN (SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
AND "namespaces"."id" != 9970
AND ("namespaces"."visibility_level" IN
(10, 20) OR EXISTS (SELECT 1
FROM (SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM ((WITH "descendants_base_cte"
AS MATERIALIZED (SELECT "namespaces"."id", "namespaces"."traversal_ids"
FROM "namespaces"
INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group'
AND "members"."user_id" = 10327656
AND "members"."requested_at" IS NULL
AND (access_level >= 10)),
"superset"
AS (SELECT d1.traversal_ids
FROM descendants_base_cte d1
WHERE NOT EXISTS (SELECT 1
FROM descendants_base_cte d2
WHERE d2.id = ANY (d1.traversal_ids)
AND d2.id <> d1.id))
SELECT DISTINCT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM "superset",
"namespaces"
WHERE "namespaces"."type" = 'Group'
AND
next_traversal_ids_sibling("superset"."traversal_ids") >
"namespaces"."traversal_ids"
AND "superset"."traversal_ids" <= "namespaces"."traversal_ids")
UNION
(WITH "base_ancestors_cte"
AS MATERIALIZED (SELECT "namespaces"."traversal_ids"
FROM ((WITH "direct_groups"
AS MATERIALIZED (SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM ((SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM "namespaces"
INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group'
AND "members"."user_id" = 10327656
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
UNION
(SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND
"namespaces"."id" IN
(SELECT "projects"."namespace_id"
FROM "projects"
INNER JOIN "project_authorizations"
ON "projects"."id" = "project_authorizations"."project_id"
WHERE "project_authorizations"."user_id" = 10327656))) namespaces
WHERE "namespaces"."type" = 'Group')
SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM ((SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM "direct_groups" "namespaces"
WHERE "namespaces"."type" = 'Group')
UNION
(SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM "namespaces"
INNER JOIN "group_group_links"
ON "group_group_links"."shared_group_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND
"group_group_links"."shared_with_group_id" IN
(SELECT "namespaces"."id"
FROM "direct_groups" "namespaces"
WHERE "namespaces"."type" = 'Group'))) namespaces
WHERE "namespaces"."type" = 'Group')
UNION
(SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM "namespaces"
INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group'
AND "members"."user_id" = 10327656
AND "members"."access_level" = 5)) namespaces
WHERE "namespaces"."type" = 'Group')
SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids",
"namespaces"."organization_id"
FROM "namespaces"
INNER JOIN (SELECT DISTINCT UNNEST("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors(ancestor_id)
ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group')) namespaces
WHERE "namespaces"."type" = 'Group'
ORDER BY "namespaces"."id" DESC) authorized
WHERE authorized."id" = "namespaces"."id"))
AND "namespaces"."type" != 'Project'
AND ("namespaces"."path" ILIKE '%GitLab%' OR
"namespaces"."name" ILIKE '%GitLab%')
ORDER BY "namespaces"."created_at" ASC))
SELECT namespaces.*,
(SELECT COUNT(*) AS preloaded_project_count
FROM "projects"
WHERE "projects"."namespace_id" = "namespaces"."id"
AND "projects"."archived" != TRUE) AS preloaded_project_count,
(SELECT COUNT(*) AS preloaded_member_count
FROM "members"
WHERE "members"."source_type" = 'Namespace'
AND "members"."source_id" = "namespaces"."id"
AND "members"."requested_at" IS NULL
AND "members"."access_level" > 5) AS preloaded_member_count,
(SELECT COUNT(*) AS preloaded_subgroup_count
FROM "namespaces" "children"
WHERE "children"."parent_id" = "namespaces"."id"
AND "children"."type" IS DISTINCT FROM 'Project') AS preloaded_subgroup_count
FROM "namespaces"
INNER JOIN (SELECT DISTINCT UNNEST("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors(ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" NOT IN
(SELECT UNNEST(traversal_ids) FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970);Raw Query
-- Before
WITH "base_ancestors_cte" AS MATERIALIZED (SELECT "namespaces"."traversal_ids"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN (SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN
(SELECT "projects"."namespace_id"
FROM "projects"
WHERE (EXISTS (SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 10327656
AND (project_authorizations.project_id = projects.id)) OR
projects.visibility_level IN (10,
20))
AND "projects"."pending_delete" = FALSE
AND "projects"."id" IN
(SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN
(SELECT namespaces.traversal_ids[ARRAY_LENGTH(namespaces.traversal_ids, 1)] AS id
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))))
AND (
("projects"."path" ILIKE '%GitLab%' OR "projects"."name" ILIKE '%GitLab%') OR
"projects"."description" ILIKE
'%GitLab%')
AND "projects"."archived" = FALSE
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE
AND "projects"."hidden" = FALSE
AND "projects"."namespace_id" != 9970
ORDER BY "projects"."created_at" ASC)))
SELECT namespaces.*,
(SELECT COUNT(*) AS preloaded_project_count
FROM "projects"
WHERE "projects"."namespace_id" = "namespaces"."id"
AND "projects"."archived" != TRUE) AS preloaded_project_count,
(SELECT COUNT(*) AS preloaded_member_count
FROM "members"
WHERE "members"."source_type" = 'Namespace'
AND "members"."source_id" = "namespaces"."id"
AND "members"."requested_at" IS NULL
AND "members"."access_level" > 5) AS preloaded_member_count,
(SELECT COUNT(*) AS preloaded_subgroup_count
FROM "namespaces" "children"
WHERE "children"."parent_id" = "namespaces"."id"
AND "children"."type" IS DISTINCT FROM 'Project') AS preloaded_subgroup_count
FROM "namespaces"
INNER JOIN (SELECT DISTINCT UNNEST("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors(ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" NOT IN
(SELECT UNNEST(traversal_ids) FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970);After
Raw Query
-- After
WITH "base_ancestors_cte" AS MATERIALIZED (SELECT "namespaces"."traversal_ids"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" IN
(3345373, 3910297, 5387503, 6511126, 9988194, 10375385, 14005604,
15587832, 53051354, 53991625, 54077923, 60118208, 63143558, 68520095,
68520411, 70172854, 71255377, 71876660, 72563107, 78228052))
SELECT namespaces.*,
(SELECT COUNT(*) AS preloaded_project_count
FROM "projects"
WHERE "projects"."namespace_id" = "namespaces"."id"
AND "projects"."archived" != TRUE) AS preloaded_project_count,
(SELECT COUNT(*) AS preloaded_member_count
FROM "members"
WHERE "members"."source_type" = 'Namespace'
AND "members"."source_id" = "namespaces"."id"
AND "members"."requested_at" IS NULL
AND "members"."access_level" > 5) AS preloaded_member_count,
(SELECT COUNT(*) AS preloaded_subgroup_count
FROM "namespaces" "children"
WHERE "children"."parent_id" = "namespaces"."id"
AND "children"."type" IS DISTINCT FROM 'Project') AS preloaded_subgroup_count
FROM "namespaces"
INNER JOIN (SELECT DISTINCT UNNEST("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors(ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" NOT IN
(SELECT UNNEST(traversal_ids) FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970);Raw Query
-- After
WITH "base_ancestors_cte" AS MATERIALIZED (SELECT "namespaces"."traversal_ids"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND 1 = 0)
SELECT namespaces.*,
(SELECT COUNT(*) AS preloaded_project_count
FROM "projects"
WHERE "projects"."namespace_id" = "namespaces"."id"
AND "projects"."archived" != TRUE) AS preloaded_project_count,
(SELECT COUNT(*) AS preloaded_member_count
FROM "members"
WHERE "members"."source_type" = 'Namespace'
AND "members"."source_id" = "namespaces"."id"
AND "members"."requested_at" IS NULL
AND "members"."access_level" > 5) AS preloaded_member_count,
(SELECT COUNT(*) AS preloaded_subgroup_count
FROM "namespaces" "children"
WHERE "children"."parent_id" = "namespaces"."id"
AND "children"."type" IS DISTINCT FROM 'Project') AS preloaded_subgroup_count
FROM "namespaces"
INNER JOIN (SELECT DISTINCT UNNEST("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors(ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" NOT IN
(SELECT UNNEST(traversal_ids) FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970);How to set up and validate locally
- Create nested groups using the script here Script to reproduce the issue. Update the top level group id and PAT in the script before running it.
- On the master branch, go to the group home page and search for
GitLabin the Search field. - Browse through the page and notice that some pages contain items from the preceding pages.
- Check out this branch and see that the items from the preceding pages are not repeated on the current page.
- Run the newly added spec spec/finders/group_descendants_finder_spec.rb. On master, this spec should fail and on this branch, it should pass.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #418749 (closed)
Edited by Abdul Wadood