EnvironmentsFinder may not be ordering environments properly
Summary
(Summarize the bug encountered concisely)
From !21789 (comment 260669938), @ahegyi suspects there may be some incorrectness with the ORDER BY
in:
SELECT
"environments".*
FROM
"environments"
WHERE
"environments"."project_id" = 278964
AND ("environments"."state" IN ('available'))
AND "environments"."id" IN (
SELECT
"deployments"."environment_id"
FROM
"deployments"
WHERE
"deployments"."project_id" = 278964
AND (ref = 'sh-optimize-commit-is-ancestor-env')
GROUP BY
"deployments"."environment_id"
)
ORDER BY
(
SELECT
MAX("deployments"."id")
FROM
"deployments"
WHERE
"deployments"."environment_id" = "environments"."id"
) ASC NULLS FIRST
I'm not sure that the order expression is correct. We order by the last deployment record for the given environment_id
, which might not be the same as the matching deployment record from the IN
subselect.
If there is a newer deployment record with different ref
for the same environment, then the ordering will be based on that record.
Steps to reproduce
(How one can reproduce the issue - this is very important)
Order Environments by ID they should be sorted by AVAILABLE
and IDs with an associated deployment in that project.
Example Project
(If possible, please create an example project here on GitLab.com that exhibits the problematic behavior, and link to it here in the bug report)
(If you are using an older version of GitLab, this will also determine whether the bug is fixed in a more recent version)
Example:
- environment:
gprd
- deployments filter in subselect:
environment = gprd
andproject_id = 278964
andref = refs/merge-requests/21790/head
- deployments in order by:
environment = gprd
, take the MAX(id)
What is the current bug behavior?
(What actually happens)
Ordering is based on newer the deployment record with different ref
for the same environment, rather than all available environments.
What is the expected correct behavior?
(What you should see instead)
Relevant logs and/or screenshots
(Paste any relevant logs - please use code blocks (```) to format console output, logs, and code as it's tough to read otherwise.)
Output of checks
(If you are reporting a bug on GitLab.com, write: This bug happens on GitLab.com)
Results of GitLab environment info
Expand for output related to GitLab environment info
(For installations with omnibus-gitlab package run and paste the output of: `sudo gitlab-rake gitlab:env:info`) (For installations from source run and paste the output of: `sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)
Results of GitLab application Check
Expand for output related to the GitLab application check
(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true
)(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true
)(we will only investigate if the tests are passing)
Possible fixes
(If you can, link to the line of code that might be responsible for the problem)
We can use INNER JOIN
with window aggregation to get the latest deployment id
, second query (first query could be also altered this way):
SELECT
"environments"."id", "deployments"."id" as did
FROM
"environments"
INNER JOIN (
SELECT environment_id, id, row_number() over (partition by environment_id order by id DESC) as row_id
FROM deployments
WHERE project_id = 278964 and (ref = 'master' OR tag is true)
) deployments on deployments.environment_id = environments.id
WHERE
"environments"."project_id" = 278964 AND "environments"."state" IN ('available') AND deployments.row_id = 1
ORDER BY deployments.id ASC NULLS FIRST
``