Skip to content

Avoid using distinct with join to avoid issues with order by

Sincheol (David) Kim requested to merge 373878-activerecord-statementinvalid-pg-invalidcolumnreference-error-for-select-distinct-order-by-2 into master

What does this MR do and why?

Using DISTINCT with ORDER BY is troublesome as one would need to specify the column used in ORDER BY in SELECT as well.

Related to #373878 (closed)

How to set up and validate locally

  1. Filter merge requests by Release = Any with order by non merge request attribute such as Milestone Due Date
  2. It should return expected merge request without any error -->

Database

filter by ?scope=all&state=merged&assignee_username=dskim_gitlab&release_tag=any BEFORE

explain SELECT
  DISTINCT "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
  JOIN milestone_releases ON merge_requests.milestone_id = milestone_releases.milestone_id
  JOIN releases ON milestone_releases.release_id = releases.id
WHERE
  (
    EXISTS (
      SELECT
        1
      FROM
        "project_authorizations"
      WHERE
        "project_authorizations"."user_id" = 4901936
        AND (project_authorizations.project_id = projects.id)
        AND (project_authorizations.access_level >= 20)
    )
    OR projects.visibility_level IN (10, 20)
  )
  AND (
    "project_features"."merge_requests_access_level" IS NULL
    OR "project_features"."merge_requests_access_level" IN (20, 30)
    OR (
      "project_features"."merge_requests_access_level" = 10
      AND EXISTS (
        SELECT
          1
        FROM
          "project_authorizations"
        WHERE
          "project_authorizations"."user_id" = 4901936
          AND (
            project_authorizations.project_id = project_features.project_id
          )
          AND (project_authorizations.access_level >= 20)
      )
    )
  )
  AND ("merge_requests"."state_id" IN (3))
  AND EXISTS (
    SELECT
      "merge_request_assignees".*
    FROM
      "merge_request_assignees"
    WHERE
      "merge_request_assignees"."user_id" = 4901936
      AND (merge_request_id = merge_requests.id)
  )
  AND "projects"."archived" = FALSE
ORDER BY
  "merge_requests"."created_at" DESC,
  "merge_requests"."id" DESC
LIMIT
  20 OFFSET 0

EXPLAIN: Time: 160.177 ms, https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13727/commands/48215

filter by ?scope=all&state=merged&assignee_username=dskim_gitlab&release_tag=any AFTER

explain 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" = 4901936
        AND (project_authorizations.project_id = projects.id)
        AND (project_authorizations.access_level >= 20)
    )
    OR projects.visibility_level IN (10, 20)
  )
  AND (
    "project_features"."merge_requests_access_level" IS NULL
    OR "project_features"."merge_requests_access_level" IN (20, 30)
    OR (
      "project_features"."merge_requests_access_level" = 10
      AND EXISTS (
        SELECT
          1
        FROM
          "project_authorizations"
        WHERE
          "project_authorizations"."user_id" = 4901936
          AND (
            project_authorizations.project_id = project_features.project_id
          )
          AND (project_authorizations.access_level >= 20)
      )
    )
  )
  AND ("merge_requests"."state_id" IN (3))
  AND EXISTS (
    SELECT
      "merge_request_assignees".*
    FROM
      "merge_request_assignees"
    WHERE
      "merge_request_assignees"."user_id" = 4901936
      AND (merge_request_id = merge_requests.id)
  )
  AND "projects"."archived" = FALSE
  AND (
    EXISTS (
      SELECT
        "milestones".*
      FROM
        "milestones"
        INNER JOIN "milestone_releases" ON "milestone_releases"."milestone_id" = "milestones"."id"
        INNER JOIN "releases" ON "releases"."id" = "milestone_releases"."release_id"
      WHERE
        (merge_requests.milestone_id = milestones.id)
    )
  )
ORDER BY
  "merge_requests"."created_at" DESC,
  "merge_requests"."id" DESC
LIMIT
  20 OFFSET 0

EXPLAIN: Time: 87.864 ms, https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13727/commands/48213

filter by ?scope=all&state=merged&assignee_username=dskim_gitlab&release_tag=v15.6.0-ee BEFORE

explain SELECT
  DISTINCT "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
  JOIN milestone_releases ON merge_requests.milestone_id = milestone_releases.milestone_id
  JOIN releases ON milestone_releases.release_id = releases.id
WHERE
  (
    EXISTS (
      SELECT
        1
      FROM
        "project_authorizations"
      WHERE
        "project_authorizations"."user_id" = 4901936
        AND (project_authorizations.project_id = projects.id)
        AND (project_authorizations.access_level >= 20)
    )
    OR projects.visibility_level IN (10, 20)
  )
  AND (
    "project_features"."merge_requests_access_level" IS NULL
    OR "project_features"."merge_requests_access_level" IN (20, 30)
    OR (
      "project_features"."merge_requests_access_level" = 10
      AND EXISTS (
        SELECT
          1
        FROM
          "project_authorizations"
        WHERE
          "project_authorizations"."user_id" = 4901936
          AND (
            project_authorizations.project_id = project_features.project_id
          )
          AND (project_authorizations.access_level >= 20)
      )
    )
  )
  AND ("merge_requests"."state_id" IN (3))
  AND EXISTS (
    SELECT
      "merge_request_assignees".*
    FROM
      "merge_request_assignees"
    WHERE
      "merge_request_assignees"."user_id" = 4901936
      AND (merge_request_id = merge_requests.id)
  )
  AND "projects"."archived" = FALSE
  AND "releases"."tag" = 'v15.6.0-ee'
  AND "releases"."project_id" = 278964
ORDER BY
  "merge_requests"."created_at" DESC,
  "merge_requests"."id" DESC
LIMIT
  20 OFFSET 0

EXPLAIN: Time: 390.612 ms, https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13727/commands/48222

filter by ?scope=all&state=merged&assignee_username=dskim_gitlab&release_tag=v15.6.0-ee AFTER

explain 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" = 4901936
        AND (project_authorizations.project_id = projects.id)
        AND (project_authorizations.access_level >= 20)
    )
    OR projects.visibility_level IN (10, 20)
  )
  AND (
    "project_features"."merge_requests_access_level" IS NULL
    OR "project_features"."merge_requests_access_level" IN (20, 30)
    OR (
      "project_features"."merge_requests_access_level" = 10
      AND EXISTS (
        SELECT
          1
        FROM
          "project_authorizations"
        WHERE
          "project_authorizations"."user_id" = 4901936
          AND (
            project_authorizations.project_id = project_features.project_id
          )
          AND (project_authorizations.access_level >= 20)
      )
    )
  )
  AND ("merge_requests"."state_id" IN (3))
  AND EXISTS (
    SELECT
      "merge_request_assignees".*
    FROM
      "merge_request_assignees"
    WHERE
      "merge_request_assignees"."user_id" = 4901936
      AND (merge_request_id = merge_requests.id)
  )
  AND "projects"."archived" = FALSE
  AND (
    EXISTS (
      SELECT
        "milestones".*
      FROM
        "milestones"
        INNER JOIN "milestone_releases" ON "milestone_releases"."milestone_id" = "milestones"."id"
        INNER JOIN "releases" ON "releases"."id" = "milestone_releases"."release_id"
      WHERE
        (merge_requests.milestone_id = milestones.id)
        AND "releases"."tag" = 'v15.6.0-ee'
        AND "releases"."project_id" = 278964
    )
  )
ORDER BY
  "merge_requests"."created_at" DESC,
  "merge_requests"."id" DESC
LIMIT
  20 OFFSET 0

EXPLAIN: Time: 282.852 ms, https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13727/commands/48225

filter by ?scope=all&state=merged&assignee_username=dskim_gitlab&not[release_tag]=v15.6.0-ee BEFORE

explain SELECT
  DISTINCT "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
  JOIN milestone_releases ON merge_requests.milestone_id = milestone_releases.milestone_id
  JOIN releases ON milestone_releases.release_id = releases.id
WHERE
  (
    EXISTS (
      SELECT
        1
      FROM
        "project_authorizations"
      WHERE
        "project_authorizations"."user_id" = 4901936
        AND (project_authorizations.project_id = projects.id)
        AND (project_authorizations.access_level >= 20)
    )
    OR projects.visibility_level IN (10, 20)
  )
  AND (
    "project_features"."merge_requests_access_level" IS NULL
    OR "project_features"."merge_requests_access_level" IN (20, 30)
    OR (
      "project_features"."merge_requests_access_level" = 10
      AND EXISTS (
        SELECT
          1
        FROM
          "project_authorizations"
        WHERE
          "project_authorizations"."user_id" = 4901936
          AND (
            project_authorizations.project_id = project_features.project_id
          )
          AND (project_authorizations.access_level >= 20)
      )
    )
  )
  AND ("merge_requests"."state_id" IN (3))
  AND EXISTS (
    SELECT
      "merge_request_assignees".*
    FROM
      "merge_request_assignees"
    WHERE
      "merge_request_assignees"."user_id" = 4901936
      AND (merge_request_id = merge_requests.id)
  )
  AND "projects"."archived" = FALSE
  AND NOT (
    "releases"."tag" = 'v15.6.0-ee'
    AND "releases"."project_id" = 278964
  )
ORDER BY
  "merge_requests"."created_at" DESC,
  "merge_requests"."id" DESC
LIMIT
  20 OFFSET 0

EXPLAIN: Time: 1.085 s, https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13727/commands/48227

filter by ?scope=all&state=merged&assignee_username=dskim_gitlab&not[release_tag]=v15.6.0-ee AFTER

explain 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" = 4901936
        AND (project_authorizations.project_id = projects.id)
        AND (project_authorizations.access_level >= 20)
    )
    OR projects.visibility_level IN (10, 20)
  )
  AND (
    "project_features"."merge_requests_access_level" IS NULL
    OR "project_features"."merge_requests_access_level" IN (20, 30)
    OR (
      "project_features"."merge_requests_access_level" = 10
      AND EXISTS (
        SELECT
          1
        FROM
          "project_authorizations"
        WHERE
          "project_authorizations"."user_id" = 4901936
          AND (
            project_authorizations.project_id = project_features.project_id
          )
          AND (project_authorizations.access_level >= 20)
      )
    )
  )
  AND ("merge_requests"."state_id" IN (3))
  AND EXISTS (
    SELECT
      "merge_request_assignees".*
    FROM
      "merge_request_assignees"
    WHERE
      "merge_request_assignees"."user_id" = 4901936
      AND (merge_request_id = merge_requests.id)
  )
  AND "projects"."archived" = FALSE
  AND (
    EXISTS (
      SELECT
        "milestones".*
      FROM
        "milestones"
        INNER JOIN "milestone_releases" ON "milestone_releases"."milestone_id" = "milestones"."id"
        INNER JOIN "releases" ON "releases"."id" = "milestone_releases"."release_id"
      WHERE
        (merge_requests.milestone_id = milestones.id)
        AND NOT (
          "releases"."tag" = 'v15.6.0-ee'
          AND "releases"."project_id" = 278964
        )
    )
  )
ORDER BY
  "merge_requests"."created_at" DESC,
  "merge_requests"."id" DESC
LIMIT
  20 OFFSET 0

EXPLAIN: Time: 97.375 ms, https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13727/commands/48229

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #373878 (closed)

Edited by Sincheol (David) Kim

Merge request reports