Fix with_fix_available treating empty solution as fix-available

What does this MR do and why?

Fix Vulnerabilities::Finding.with_fix_available treating an empty-string solution column value as fix-available.

Root cause

The scope previously tested only IS NOT NULL / IS NULL on vulnerability_occurrences.solution. An empty string ('') is NOT NULL, so findings whose solution was stored as '' were incorrectly classified as fix-available.

This caused merge request approval policy rules using vulnerability_attributes.fix_available: true to keep container_scanning findings that have no real fix (e.g. from Trivy's old "No solution provided" placeholder, which the fixed analyzer now emits as an empty string).

Fix

Change the solution predicate in Vulnerabilities::Finding.with_fix_available to:

• fix_available true: solution IS NOT NULL AND solution <> ''
• fix_available false: (solution IS NULL OR solution = '')

This matches the behaviour of Security::Finding.fix_available / .no_fix_available (the newly-detected path), which already uses COALESCE((finding_data->>'solution')::text, '') <> ''.

Why this form (not COALESCE)?

db/structure.sql has no index on vulnerability_occurrences.solution (confirmed by inspection). The plain sargable predicates are preferred over COALESCE(solution, '') <> '' because a COALESCE wrapper is not index-usable if an index on that column is added in the future.

Retroactive fix

This is a read-side change and is retroactive. Rows already stored with solution = '' are corrected at read time without any backfill, re-scan, or migration needed.

Consistency

This makes the pre-existing-state path (Vulnerabilities::Finding -> Vulnerability.with_fix_available -> VulnerabilitiesFinder) consistent with the newly-detected path (Security::Finding.fix_available).

References

• Issue: #612864
• Analyzer fix: gitlab-org/security-products/analyzers/container-scanning!3342 (merged)

Queries

Before

explain SELECT "vulnerability_occurrences".* FROM "vulnerability_occurrences" WHERE "vulnerability_occurrences"."project_id" = 278964 AND (vulnerability_occurrences.solution IS NOT NULL OR (EXISTS (SELECT 1 FROM "vulnerability_findings_remediations" WHERE "vulnerability_findings_remediations"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id")))

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/55293/commands/158837

 Seq Scan on public.vulnerability_occurrences  (cost=0.00..0.00 rows=1 width=418) (actual time=0.011..0.012 rows=0 loops=1)
   Filter: (((vulnerability_occurrences.solution IS NOT NULL) OR EXISTS(SubPlan 1)) AND (vulnerability_occurrences.project_id = 278964))
   I/O Timings: read=0.000 write=0.000
   SubPlan 1
     ->  Seq Scan on public.vulnerability_findings_remediations  (cost=0.00..0.00 rows=1 width=0) (never executed)
           Filter: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
           I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5'
Query ID: 2419586954794014069

After

explain SELECT "vulnerability_occurrences".* FROM "vulnerability_occurrences" WHERE "vulnerability_occurrences"."project_id" = 278964 AND (vulnerability_occurrences.solution IS NOT NULL AND vulnerability_occurrences.solution <> '' OR (EXISTS (SELECT 1 FROM "vulnerability_findings_remediations" WHERE "vulnerability_findings_remediations"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id")))

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/55293/commands/158838

 Seq Scan on public.vulnerability_occurrences  (cost=0.00..0.00 rows=1 width=418) (actual time=0.005..0.006 rows=0 loops=1)
   Filter: ((((vulnerability_occurrences.solution IS NOT NULL) AND (vulnerability_occurrences.solution <> ''::text)) OR EXISTS(SubPlan 1)) AND (vulnerability_occurrences.project_id = 278964))
   I/O Timings: read=0.000 write=0.000
   SubPlan 1
     ->  Seq Scan on public.vulnerability_findings_remediations  (cost=0.00..0.00 rows=1 width=0) (never executed)
           Filter: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
           I/O Timings: read=0.000 write=0.000
Settings: seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', work_mem = '230MB'
Query ID: 8334517369186116772

How to set up and validate locally

1. In a Rails console, create a finding with an empty solution and verify the fix:

finding = Vulnerabilities::Finding.create!(solution: '', ...)
Vulnerabilities::Finding.with_fix_available(true).where(id: finding.id).exists?
# => false (CORRECT after fix; was true before)
Vulnerabilities::Finding.with_fix_available(false).where(id: finding.id).exists?
# => true (CORRECT)

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.