SecurityFinding.by_build_ids joining through `ci_builds` during `job_artifacts` destroy
Security::Finding.by_build_ids
is doing a cross-join through ci_builds
. This will not be allowed when ci_*
tables moves to a new database.
This is only used in JobArtifacts::DestroyBatchService
and this is (luckily) already not using a subquery.
Query:
SELECT "security_findings".* FROM "security_findings"
INNER JOIN "security_scans" ON "security_scans"."id" = "security_findings"."scan_id"
INNER JOIN "ci_builds" ON "ci_builds"."id" = "security_scans"."build_id"
AND "ci_builds"."type" = 'Ci::Build'
WHERE "ci_builds"."id" = 71 /*application:test,correlation_id:6eb43de267535bf9a0e73a89af48579b,db_config_name:main*/
Solution
This is a redundant join anyway since security_scans
table already has the build_id
so just simplify the query to reduce the extra join to ci_builds
.
If the by_build_ids
is ever used in future with a relation that has a subjoin then this will create a new cross join but today it's only used in one place with an array of ids so we don't need the cross-join.
Edited by Dylan Griffith