Fix the vulnerability statistic calculation logic
What does this MR do and why?
Account for vulnerabilities transition to detected
from resolved
while updating the vulnerability statistics data.
Related to Vulnerability statistics flaw in security repor... (#429730 - closed).
Database review
This MR changes an UPDATE query which can be seen below.
Old Query
UPDATE
"vulnerabilities"
SET
"state" = 1
WHERE
"vulnerabilities"."state" = 3
AND "vulnerabilities"."id" IN (
SELECT
"vulnerabilities"."id"
FROM
"vulnerabilities"
WHERE
"vulnerabilities"."state" = 3
AND "vulnerabilities"."id" IN (18891225, 18891226, 18891227, 18891228, 18891229, 18891230, 18891231, 18891232, 18891233, 18891234, 18891235, 18891236, 18891237, 18891238, 18891239, 18891240, 18891241, 18891242, 18891243, 18891244, 18891245, 18891246, 18891247, 18891248, 18891249, 18891250, 18891251, 18891252, 18891253, 19214230, 19219189, 19219190, 20448722, 20857375, 21491479, 21491480, 22542616, 35388194, 37684798, 37684799, 37684800, 38226949, 38226950, 38226951, 38226953, 38400777, 39689098, 41211016, 41211017, 44285095, 44753773, 44753774, 45300669, 45300670, 45923663, 45923664, 46614368, 46614369, 46614370, 46614371, 46614372, 47366909, 47366910, 47366911, 47808244, 47808245, 47808246, 47808247, 47808248, 47808249, 47808250, 48266313, 50603877, 50603878, 50983758, 15782738, 18889624, 18889625, 18889626, 18889627, 18889628, 18889629, 18889630, 18889631, 18889632, 18889633, 18889634, 18889635, 18889636, 18889637, 18889638, 18889639, 18889640, 18889641, 18889642, 18889643, 18889644, 18889645, 18889646, 18889647))
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24662/commands/79563
New Query
UPDATE
"vulnerabilities"
SET
"state" = 1
WHERE
"vulnerabilities"."state" = 3
AND "vulnerabilities"."id" IN (18891225, 18891226, 18891227, 18891228, 18891229, 18891230, 18891231, 18891232, 18891233, 18891234, 18891235, 18891236, 18891237, 18891238, 18891239, 18891240, 18891241, 18891242, 18891243, 18891244, 18891245, 18891246, 18891247, 18891248, 18891249, 18891250, 18891251, 18891252, 18891253, 19214230, 19219189, 19219190, 20448722, 20857375, 21491479, 21491480, 22542616, 35388194, 37684798, 37684799, 37684800, 38226949, 38226950, 38226951, 38226953, 38400777, 39689098, 41211016, 41211017, 44285095, 44753773, 44753774, 45300669, 45300670, 45923663, 45923664, 46614368, 46614369, 46614370, 46614371, 46614372, 47366909, 47366910, 47366911, 47808244, 47808245, 47808246, 47808247, 47808248, 47808249, 47808250, 48266313, 50603877, 50603878, 50983758, 15782738, 18889624, 18889625, 18889626, 18889627, 18889628, 18889629, 18889630, 18889631, 18889632, 18889633, 18889634, 18889635, 18889636, 18889637, 18889638, 18889639, 18889640, 18889641, 18889642, 18889643, 18889644, 18889645, 18889646, 18889647)
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24662/commands/79562
Edited by Mehmet Emin INAC