Expand MR security pipeline comparison to check older commits

What does this MR do and why?

Expand MR security pipeline comparison to check last 10 commits

Previously we only checked the latest commit on the target branch for a valid pipeline with security scans.

References

Issue: #547898

New Query

MergeRequest#target_branch_pipelines_for_comparison

WITH commits(sha) AS (
    VALUES
        ('a95d23f540d0ed7554a1da0446d4342c3b850a0b'),
        ('62b0095efdd64af731c440f00ae811edba837223'),
        ('98ef9c5406c5a5511b14bb8442e02331908b7650'),
        ('1c3f0a06f4972cf7c33d29af30e560a2d5781133'),
        ('dc7d519cd7fc963d5897e784dfa80e6008a2c12a'),
        ('0bb2d492c9838395eeb8de8d2a6b7ca27f57b867'),
        ('8a7699d401b5da6782d84203c2dba84de68f9661'),
        ('c2af2a08cffbc175fa4191a3c3030b55408aa1a3'),
        ('b0fcd4ec97237b30b2d46a2cd11a87d11bb96867'),
        ('da25e7a2f7dd1c0aae14b10190bed37b96f3a0e2'),
        ('9a456e71492f3f3dd01e2088f2d84459fa7d32f7'),
        ('f74cc73fa54750ababc70616098aa84c6919b8a1'),
        ('6829a251f8b8fff1ce9c4aa7da6c6c985ec38357')
)
SELECT
    ranked_pipelines.*
FROM
    commits
    JOIN LATERAL (
        SELECT
            p_ci_pipelines.*
        FROM
            "p_ci_pipelines"
        WHERE
            "p_ci_pipelines"."sha" = commits.sha
            AND "p_ci_pipelines"."project_id" = 278964
            AND (
                "p_ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11)
                OR "p_ci_pipelines"."source" IS NULL
            )
            AND "p_ci_pipelines"."ref" = 'master'
        ORDER BY id DESC
        LIMIT 10
    ) ranked_pipelines ON TRUE
ORDER BY
    ranked_pipelines.sha,
    ranked_pipelines.id DESC;

Query Plan: https://explain.depesz.com/s/XAQjc

Screenshots or screen recordings

Before	After

How to set up and validate locally

1. Ensure the default branch (main or develop) has an existing known vulnerability (e.g., vulnerable dependency in package-lock.json):

   {
     "name": "vuln-demo",
     "version": "1.0.0",
     "lockfileVersion": 1,
     "dependencies": {
       "minimist": {
         "version": "0.0.8",
         "resolved": "https://registry.npmjs.org/minimist/-/minimist-0.0.8.tgz",
         "integrity": "sha512-Nu4YOVnAOFDXB/E6m++rRvd8WPoS9K5lYdBQnqJ2bYz+gUMW//LKHEx1h+ZdTJmjRyTckfS+UTfW1REy6+vJvQ=="
       }
     }
   }

2. Push this to the default branch and trigger a normal pipeline with Dependency Scanning enabled:

   git checkout -b main
   git add package-lock.json
   git commit -m "Add vulnerable package"
   git push -u origin main

3. Confirm that the default branch pipeline runs and gl-dependency-scanning-report.json is uploaded.

4. Now, simulate the bug trigger by pushing a commit to the default branch with the pipeline skipped:

   git commit --allow-empty -m "skip the scan"
   git push -o ci.skip

5. Create a new feature branch from that skipped commit:

   git checkout -b feature/trigger-the-bug
   git commit --allow-empty -m "no-op change to see the bug"
   git push -u origin feature/trigger-the-bug

6. Open a Merge Request from feature/trigger-the-bug into main.

7. Let the MR pipeline complete. Observe the MR security widget or Security tab:

   • Before Change: It will show existing vulnerabilities (e.g., minimist) as new, despite them being already present in a previous scan on main.
   • After Change (checkout this branch): It should show "Security scanning detected no new potential vulnerabilities"

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #547898