[Bug][refdiff] calculateDeploymentCommitsDiff recalculates every deployment without a previous successful deployment on every run (NULL vs '' comparison)

[chenwei791129]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

In short: refdiff calculates "which commits a deployment added since the previous one", records each calculated pair as finished, and skips it on the next run. For deployments without a previous successful deployment that skip never works, so they are recalculated against the full git history on every run.

Why the skip fails:

When marking as finished:   old_commit_sha = ''     <- column is NOT NULL, stored as empty string
When checking next time:    p.commit_sha   = NULL   <- LEFT JOIN finds no previous deployment

                            ''  =  NULL   ->  NULL (not TRUE)
                                          ->  treated as "not calculated yet"
                                          ->  recalculated again

Before / after:

Before: every run
+------------------------------------+
| deploy A (has previous deploy)     |--> finished, skipped
| deploy B (no previous deploy)      |--> '' = NULL fails --> recalculate full history
| deploy C (no previous deploy)      |--> '' = NULL fails --> recalculate full history
| ... (~144,000 rows)                |--> recalculated every run --> 5-6 hours
+------------------------------------+

After: compare against COALESCE(p.commit_sha, '')
+------------------------------------+
| deploy A (has previous deploy)     |--> finished, skipped
| deploy B (no previous deploy)      |--> '' = '' ok --> skipped
| deploy C (no previous deploy)      |--> '' = '' ok --> skipped
| new deploy                         |--> not recorded --> calculated once, then skipped
+------------------------------------+

	Before	After
Deployments without a previous success	Recalculated every run	Calculated once
calculateDeploymentCommitsDiff on the affected project	5–6 hours every run	Only new deployments
commits_diffs writes	Full history rewritten every run	Only new pairs

Projects most likely to hit this are GitLab projects with many manual deploy jobs: jobs that are never triggered leave blocked deployments, which never get a previous successful deployment, so all of them are recalculated on every run.

Details

On a GitLab project whose pipelines create many manual deploy jobs (one per target/environment, most of which are never triggered), refdiff → calculateDeploymentCommitsDiff takes 5–6 hours on every daily run, even when no new deployments were collected.

Root cause is in backend/plugins/refdiff/tasks/deployment_commit_diff_calculator.go (unchanged between v1.0.3-beta10 and v1.0.3-beta18). Step 1 selects the pairs to calculate:

dal.Select("dc.id, dc.commit_sha, p.commit_sha as prev_commit_sha"),
dal.From("cicd_deployment_commits dc"),
dal.Join("LEFT JOIN project_mapping pm ON (pm.table = 'cicd_scopes' AND pm.row_id = dc.cicd_scope_id)"),
dal.Join("LEFT JOIN cicd_deployment_commits p ON (dc.prev_success_deployment_commit_id = p.id)"),
dal.Where(`
    pm.project_name = ?
    AND NOT EXISTS (
        SELECT 1
        FROM _tool_refdiff_finished_commits_diffs fcd
        WHERE fcd.new_commit_sha = dc.commit_sha AND fcd.old_commit_sha = p.commit_sha
    )`, ...),

When a deployment commit has no previous successful deployment, the LEFT JOIN yields p.commit_sha = NULL. After the diff is calculated, the pair is marked as finished with OldCommitSha: pair.PrevCommitSha, which is the Go zero value '' (the column is varchar(40) NOT NULL). On the next run the guard evaluates '' = NULL, which is NULL, not TRUE, so NOT EXISTS is always true. Every such pair is selected and recalculated again on every run, forever.

Step 3 has no de-duplication either. Each of these rows recomputes the full ancestry of its commit (a diff against "nothing") and rewrites all of those rows into commits_diffs.

Numbers from our instance (single project, MySQL 8.4):

Metric	Value
cicd_deployment_commits rows for the project	~167,000
Rows with no previous successful deployment	~144,000 (only ~3,400 distinct commit_sha)
Of those, GitLab deployment status blocked (manual job never run) / skipped	~135,000 / ~7,500
_tool_refdiff_finished_commits_diffs rows with old_commit_sha = '' (instance-wide)	~94,000
commits_diffs rows per recalculated pair	~3,400 (full history)
calculateDeploymentCommitsDiff duration, every daily run	16,000–21,000 s

The same query also picks up deployments whose result is empty (blocked/skipped manual jobs). These can never be a previous successful deployment and never contribute to DORA metrics, but they still pay the full cost.

What do you expect to happen

A pair that has already been calculated is skipped on later runs, including pairs without a previous successful deployment. Once no new deployments are collected, the subtask should finish in seconds.

How to reproduce

1. Use a GitLab project whose .gitlab-ci.yml defines deploy jobs with environment: and when: manual, so most pipelines leave blocked deployments behind.
2. Add it to a project with the DORA and refdiff plugins enabled, and collect data.
3. Run the blueprint again without any new deployments.
4. The calculateDeploymentCommitsDiff progress total equals the number of deployment commits without a previous successful deployment, not 0. The log shows total N commits of difference found between [new][<sha>] and [old][(total:1)] for the same SHAs on every run.

Quick check (MySQL):

-- rows that will be re-selected on every run
SELECT COUNT(*)
FROM cicd_deployment_commits dc
LEFT JOIN cicd_deployment_commits p ON dc.prev_success_deployment_commit_id = p.id
WHERE dc.cicd_scope_id = '<scope id>' AND p.id IS NULL;

-- finished markers stored with an empty old sha
SELECT COUNT(*) FROM _tool_refdiff_finished_commits_diffs WHERE old_commit_sha = '';

Anything else

Possible fixes (not mutually exclusive):

1. Compare against the stored value: fcd.old_commit_sha = COALESCE(p.commit_sha, ''). MySQL's NULL-safe <=> alone is not enough, because the stored value is '', not NULL.
2. De-duplicate pairs by (commit_sha, prev_commit_sha) before step 3. Many deployment commits share the same SHA, e.g. one pipeline with many deploy jobs.
3. Optionally only calculate diffs for deployments with result = 'SUCCESS', since blocked/skipped deployments are never used as a previous successful deployment.

I'll open a PR for option 1 with an e2e regression test (running the subtask a second time must not recalculate anything). Option 2 can follow separately if desired.

Version

v1.0.3-beta10 (code path verified unchanged in v1.0.3-beta18)

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct