Skip to content

Remove unnecessary ORDER BY clause from `forked_to_project_id` subquery

M. Hasbini requested to merge 0xbsec/gitlab-ce:forked-subquery-order into master

What does this MR do?

Remove unnecessary ORDER BY clause from forked_to_project_id subquery.

Original query (forked_from_project_id: 1 & owner_id: 1):

SELECT "forked_project_links".*
FROM "forked_project_links"
WHERE "forked_project_links"."forked_from_project_id" = 1
  AND "forked_project_links"."forked_to_project_id" IN
    (SELECT "projects"."id"
     FROM "projects"
     INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id"
     WHERE "projects"."pending_delete" = 'f'
       AND "namespaces"."deleted_at" IS NULL
       AND "namespaces"."owner_id" = 1
       AND "namespaces"."type" IS NULL
     ORDER BY "namespaces"."id" DESC)

Updated query:

SELECT "forked_project_links".*
FROM "forked_project_links"
WHERE "forked_project_links"."forked_from_project_id" = 1
  AND "forked_project_links"."forked_to_project_id" IN
    (SELECT "projects"."id"
     FROM "projects"
     INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id"
     WHERE "projects"."pending_delete" = 'f'
       AND "namespaces"."deleted_at" IS NULL
       AND "namespaces"."owner_id" = 1
       AND "namespaces"."type" IS NULL)

The query could be fired by visiting a project page as a logged in user (e.g. as an admin visit http://localhost:3000/gitlab-org/gitlab-test)


links.first will order the result by "forked_project_links"."id" ASC:

Updated:

SELECT "forked_project_links".*
FROM "forked_project_links"
WHERE "forked_project_links"."forked_from_project_id" = 1
  AND "forked_project_links"."forked_to_project_id" IN
    (SELECT "projects"."id"
     FROM "projects"
     INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id"
     WHERE "projects"."pending_delete" = 'f'
       AND "namespaces"."deleted_at" IS NULL
       AND "namespaces"."owner_id" = 1
       AND "namespaces"."type" IS NULL)
ORDER BY "forked_project_links"."id" ASC
LIMIT 1

Original:

SELECT "forked_project_links".*
FROM "forked_project_links"
WHERE "forked_project_links"."forked_from_project_id" = 1
  AND "forked_project_links"."forked_to_project_id" IN
    (SELECT "projects"."id"
     FROM "projects"
     INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id"
     WHERE "projects"."pending_delete" = 'f'
       AND "namespaces"."deleted_at" IS NULL
       AND "namespaces"."owner_id" = 1
       AND "namespaces"."type" IS NULL
     ORDER BY "namespaces"."id" DESC)
ORDER BY "forked_project_links"."id" ASC
LIMIT 1

Are there points in the code the reviewer needs to double check?

Why was this MR needed?

Screenshots (if relevant)

Does this MR meet the acceptance criteria?

What are the relevant issue numbers?

Merge request reports