Use disable join to avoid doing cross-join via `ci_builds` in `Security::Finding.build`

What does this MR do and why?

• adds disable_joins behind a feature flag for Security::Finding.build
• adds new feature flags enabled by default to enable us to turn the functionality off if necessary

This has_one association between non ci_* tables and ci_* tables which will not be allowed when ci_* tables are moved to a separate database. This MR is using the disable_joins feature that was backported in !66400 (merged).

Comparison of queries:

Without disable join:

has_one :build, through: :scan, disable_joins: true

SELECT "ci_builds"."status", "ci_builds"."finished_at", "ci_builds"."trace", "ci_builds"."created_at", "ci_builds"."updated_at", "ci_builds"."started_at", "ci_builds"."runner_id", "ci_builds"."coverage", "ci_builds"."commit_id", "ci_builds"."name", "ci_builds"."options", "ci_builds"."allow_failure", "ci_builds"."stage", "ci_builds"."trigger_request_id", "ci_builds"."stage_idx", "ci_builds"."tag", "ci_builds"."ref", "ci_builds"."user_id", "ci_builds"."type", "ci_builds"."target_url", "ci_builds"."description", "ci_builds"."project_id", "ci_builds"."erased_by_id", "ci_builds"."erased_at", "ci_builds"."artifacts_expire_at", "ci_builds"."environment", "ci_builds"."when", "ci_builds"."yaml_variables", "ci_builds"."queued_at", "ci_builds"."token", "ci_builds"."lock_version", "ci_builds"."coverage_regex", "ci_builds"."auto_canceled_by_id", "ci_builds"."retried", "ci_builds"."protected", "ci_builds"."failure_reason", "ci_builds"."scheduled_at", "ci_builds"."token_encrypted", "ci_builds"."upstream_pipeline_id", "ci_builds"."resource_group_id", "ci_builds"."waiting_for_resource_at", "ci_builds"."processed", "ci_builds"."scheduling_type", "ci_builds"."id", "ci_builds"."stage_id" FROM "ci_builds" INNER JOIN "security_scans" ON "ci_builds"."id" = "security_scans"."build_id" WHERE "ci_builds"."type" = 'Ci::Build' AND "security_scans"."id" = 1 LIMIT 1

scope :with_pipeline_entities, -> { preload(build: [:job_artifacts, :pipeline]) }

SELECT "security_findings".* FROM "security_findings"

SELECT "security_scans"."id" AS t0_r0, "security_scans"."created_at" AS t0_r1, "security_scans"."updated_at" AS t0_r2, "security_scans"."build_id" AS t0_r3, "security_scans"."scan_type" AS t0_r4, "security_scans"."info" AS t0_r5, "security_scans"."project_id" AS t0_r6, "security_scans"."pipeline_id" AS t0_r7, "security_scans"."latest" AS t0_r8, "security_scans"."status" AS t0_r9, "ci_builds"."status" AS t1_r0, "ci_builds"."finished_at" AS t1_r1, "ci_builds"."trace" AS t1_r2, "ci_builds"."created_at" AS t1_r3, "ci_builds"."updated_at" AS t1_r4, "ci_builds"."started_at" AS t1_r5, "ci_builds"."runner_id" AS t1_r6, "ci_builds"."coverage" AS t1_r7, "ci_builds"."commit_id" AS t1_r8, "ci_builds"."name" AS t1_r9, "ci_builds"."options" AS t1_r10, "ci_builds"."allow_failure" AS t1_r11, "ci_builds"."stage" AS t1_r12, "ci_builds"."trigger_request_id" AS t1_r13, "ci_builds"."stage_idx" AS t1_r14, "ci_builds"."tag" AS t1_r15, "ci_builds"."ref" AS t1_r16, "ci_builds"."user_id" AS t1_r17, "ci_builds"."type" AS t1_r18, "ci_builds"."target_url" AS t1_r19, "ci_builds"."description" AS t1_r20, "ci_builds"."project_id" AS t1_r21, "ci_builds"."erased_by_id" AS t1_r22, "ci_builds"."erased_at" AS t1_r23, "ci_builds"."artifacts_expire_at" AS t1_r24, "ci_builds"."environment" AS t1_r25, "ci_builds"."when" AS t1_r26, "ci_builds"."yaml_variables" AS t1_r27, "ci_builds"."queued_at" AS t1_r28, "ci_builds"."token" AS t1_r29, "ci_builds"."lock_version" AS t1_r30, "ci_builds"."coverage_regex" AS t1_r31, "ci_builds"."auto_canceled_by_id" AS t1_r32, "ci_builds"."retried" AS t1_r33, "ci_builds"."protected" AS t1_r34, "ci_builds"."failure_reason" AS t1_r35, "ci_builds"."scheduled_at" AS t1_r36, "ci_builds"."token_encrypted" AS t1_r37, "ci_builds"."upstream_pipeline_id" AS t1_r38, "ci_builds"."resource_group_id" AS t1_r39, "ci_builds"."waiting_for_resource_at" AS t1_r40, "ci_builds"."processed" AS t1_r41, "ci_builds"."scheduling_type" AS t1_r42, "ci_builds"."id" AS t1_r43, "ci_builds"."stage_id" AS t1_r44 FROM "security_scans" LEFT OUTER JOIN "ci_builds" ON "ci_builds"."id" = "security_scans"."build_id" AND "ci_builds"."type" = 'Ci::Build' WHERE "ci_builds"."type" = 'Ci::Build' AND "security_scans"."id" IN (13, 1, 2, 14, 39, 15, 65, 40, 78, 66, 3, 5, 4, 79, 6, 7, 80, 8, 81, 82, 9, 10, 16, 11, 12, 17, 18, 19, 20, 21, 22, 25, 23, 24, 41, 42, 83, 43, 47, 44, 45, 46, 84, 50, 48, 49, 51, 87, 85, 86, 67, 68, 69, 70, 71, 72, 74, 73, 75, 76, 77)

SELECT "ci_job_artifacts"."project_id", "ci_job_artifacts"."file_type", "ci_job_artifacts"."size", "ci_job_artifacts"."created_at", "ci_job_artifacts"."updated_at", "ci_job_artifacts"."expire_at", "ci_job_artifacts"."file", "ci_job_artifacts"."file_store", "ci_job_artifacts"."file_sha256", "ci_job_artifacts"."file_format", "ci_job_artifacts"."file_location", "ci_job_artifacts"."id", "ci_job_artifacts"."job_id", "ci_job_artifacts"."locked" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."job_id" IN (209, 214, 104, 105, 106, 107, 110, 111, 112, 113, 114, 115, 116, 117, 118, 120, 121, 122, 123, 126, 127, 128, 129, 130, 131, 132, 133, 163, 164, 165, 166, 167, 170, 171, 172, 173, 174, 175, 176, 177, 193, 194, 195, 196, 197, 200, 201, 202, 203, 204, 205, 206, 207, 210, 215, 216, 217, 218, 219, 220, 221) 

SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" IN (16, 8, 9, 10, 13, 15)

Security::FindingsFinder.new(pipeline, {}).send(:all_security_findings)

SELECT "security_findings"."id" AS t0_r0, "security_findings"."scan_id" AS t0_r1, "security_findings"."scanner_id" AS t0_r2, "security_findings"."severity" AS t0_r3, "security_findings"."confidence" AS t0_r4, "security_findings"."project_fingerprint" AS t0_r5, "security_findings"."deduplicated" AS t0_r6, "security_findings"."position" AS t0_r7, "security_findings"."uuid" AS t0_r8, "security_findings"."overridden_uuid" AS t0_r9, "scans_security_findings"."id" AS t1_r0, "scans_security_findings"."created_at" AS t1_r1, "scans_security_findings"."updated_at" AS t1_r2, "scans_security_findings"."build_id" AS t1_r3, "scans_security_findings"."scan_type" AS t1_r4, "scans_security_findings"."info" AS t1_r5, "scans_security_findings"."project_id" AS t1_r6, "scans_security_findings"."pipeline_id" AS t1_r7, "scans_security_findings"."latest" AS t1_r8, "scans_security_findings"."status" AS t1_r9, "vulnerability_scanners"."id" AS t2_r0, "vulnerability_scanners"."created_at" AS t2_r1, "vulnerability_scanners"."updated_at" AS t2_r2, "vulnerability_scanners"."project_id" AS t2_r3, "vulnerability_scanners"."external_id" AS t2_r4, "vulnerability_scanners"."name" AS t2_r5, "vulnerability_scanners"."vendor" AS t2_r6 FROM "security_findings" INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id" INNER JOIN "security_scans" "scans_security_findings" ON "scans_security_findings"."id" = "security_findings"."scan_id" LEFT OUTER JOIN "vulnerability_scanners" ON "vulnerability_scanners"."id" = "security_findings"."scanner_id" WHERE "security_scans"."pipeline_id" = 8 AND "security_findings"."deduplicated" = TRUE AND "security_scans"."latest" = TRUE AND "security_scans"."status" = 1 ORDER BY "security_findings"."severity" DESC, "security_findings"."confidence" DESC, "security_findings"."id" ASC LIMIT 20 OFFSET 0

pipeline.security_findings.with_pipeline_entities.with_scan

SELECT "security_findings".* FROM "security_findings" INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id" WHERE "security_scans"."pipeline_id" = 8 

SELECT "security_scans"."id" AS t0_r0, "security_scans"."created_at" AS t0_r1, "security_scans"."updated_at" AS t0_r2, "security_scans"."build_id" AS t0_r3, "security_scans"."scan_type" AS t0_r4, "security_scans"."info" AS t0_r5, "security_scans"."project_id" AS t0_r6, "security_scans"."pipeline_id" AS t0_r7, "security_scans"."latest" AS t0_r8, "security_scans"."status" AS t0_r9, "ci_builds"."status" AS t1_r0, "ci_builds"."finished_at" AS t1_r1, "ci_builds"."trace" AS t1_r2, "ci_builds"."created_at" AS t1_r3, "ci_builds"."updated_at" AS t1_r4, "ci_builds"."started_at" AS t1_r5, "ci_builds"."runner_id" AS t1_r6, "ci_builds"."coverage" AS t1_r7, "ci_builds"."commit_id" AS t1_r8, "ci_builds"."name" AS t1_r9, "ci_builds"."options" AS t1_r10, "ci_builds"."allow_failure" AS t1_r11, "ci_builds"."stage" AS t1_r12, "ci_builds"."trigger_request_id" AS t1_r13, "ci_builds"."stage_idx" AS t1_r14, "ci_builds"."tag" AS t1_r15, "ci_builds"."ref" AS t1_r16, "ci_builds"."user_id" AS t1_r17, "ci_builds"."type" AS t1_r18, "ci_builds"."target_url" AS t1_r19, "ci_builds"."description" AS t1_r20, "ci_builds"."project_id" AS t1_r21, "ci_builds"."erased_by_id" AS t1_r22, "ci_builds"."erased_at" AS t1_r23, "ci_builds"."artifacts_expire_at" AS t1_r24, "ci_builds"."environment" AS t1_r25, "ci_builds"."when" AS t1_r26, "ci_builds"."yaml_variables" AS t1_r27, "ci_builds"."queued_at" AS t1_r28, "ci_builds"."token" AS t1_r29, "ci_builds"."lock_version" AS t1_r30, "ci_builds"."coverage_regex" AS t1_r31, "ci_builds"."auto_canceled_by_id" AS t1_r32, "ci_builds"."retried" AS t1_r33, "ci_builds"."protected" AS t1_r34, "ci_builds"."failure_reason" AS t1_r35, "ci_builds"."scheduled_at" AS t1_r36, "ci_builds"."token_encrypted" AS t1_r37, "ci_builds"."upstream_pipeline_id" AS t1_r38, "ci_builds"."resource_group_id" AS t1_r39, "ci_builds"."waiting_for_resource_at" AS t1_r40, "ci_builds"."processed" AS t1_r41, "ci_builds"."scheduling_type" AS t1_r42, "ci_builds"."id" AS t1_r43, "ci_builds"."stage_id" AS t1_r44 FROM "security_scans" LEFT OUTER JOIN "ci_builds" ON "ci_builds"."id" = "security_scans"."build_id" AND "ci_builds"."type" = 'Ci::Build' WHERE "ci_builds"."type" = 'Ci::Build' AND "security_scans"."id" IN (2, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) 

 Ci::JobArtifact Load (2.0ms)  SELECT "ci_job_artifacts"."project_id", "ci_job_artifacts"."file_type", "ci_job_artifacts"."size", "ci_job_artifacts"."created_at", "ci_job_artifacts"."updated_at", "ci_job_artifacts"."expire_at", "ci_job_artifacts"."file", "ci_job_artifacts"."file_store", "ci_job_artifacts"."file_sha256", "ci_job_artifacts"."file_format", "ci_job_artifacts"."file_location", "ci_job_artifacts"."id", "ci_job_artifacts"."job_id", "ci_job_artifacts"."locked" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."job_id" IN (104, 105, 106, 107, 110, 111, 112, 113, 114, 115, 116, 117) 

SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 8

SELECT "security_scans".* FROM "security_scans" WHERE "security_scans"."id" IN (2, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)

With disable join:

has_one :build, through: :scan, disable_joins: true

SELECT "security_scans"."build_id" FROM "security_scans" WHERE "security_scans"."id" = 1 

Ci::Build Load (0.5ms)  SELECT "ci_builds"."status", "ci_builds"."finished_at", "ci_builds"."trace", "ci_builds"."created_at", "ci_builds"."updated_at", "ci_builds"."started_at", "ci_builds"."runner_id", "ci_builds"."coverage", "ci_builds"."commit_id", "ci_builds"."name", "ci_builds"."options", "ci_builds"."allow_failure", "ci_builds"."stage", "ci_builds"."trigger_request_id", "ci_builds"."stage_idx", "ci_builds"."tag", "ci_builds"."ref", "ci_builds"."user_id", "ci_builds"."type", "ci_builds"."target_url", "ci_builds"."description", "ci_builds"."project_id", "ci_builds"."erased_by_id", "ci_builds"."erased_at", "ci_builds"."artifacts_expire_at", "ci_builds"."environment", "ci_builds"."when", "ci_builds"."yaml_variables", "ci_builds"."queued_at", "ci_builds"."token", "ci_builds"."lock_version", "ci_builds"."coverage_regex", "ci_builds"."auto_canceled_by_id", "ci_builds"."retried", "ci_builds"."protected", "ci_builds"."failure_reason", "ci_builds"."scheduled_at", "ci_builds"."token_encrypted", "ci_builds"."upstream_pipeline_id", "ci_builds"."resource_group_id", "ci_builds"."waiting_for_resource_at", "ci_builds"."processed", "ci_builds"."scheduling_type", "ci_builds"."id", "ci_builds"."stage_id" FROM "ci_builds" WHERE "ci_builds"."id" = 105 AND "ci_builds"."type" = 'Ci::Build' ORDER BY "ci_builds"."id" ASC LIMIT 1

scope :with_pipeline_entities, -> { preload(build: [:job_artifacts, :pipeline]) }

SELECT "security_findings".* FROM "security_findings" 

SELECT "security_scans".* FROM "security_scans" WHERE "security_scans"."id" IN (13, 1, 2, 14, 39, 15, 65, 40, 78, 66, 3, 5, 4, 79, 6, 7, 80, 8, 81, 82, 9, 10, 16, 11, 12, 17, 18, 19, 20, 21, 22, 25, 23, 24, 41, 42, 83, 43, 47, 44, 45, 46, 84, 50, 48, 49, 51, 87, 85, 86, 67, 68, 69, 70, 71, 72, 74, 73, 75, 76, 77) 

SELECT "ci_builds"."status", "ci_builds"."finished_at", "ci_builds"."trace", "ci_builds"."created_at", "ci_builds"."updated_at", "ci_builds"."started_at", "ci_builds"."runner_id", "ci_builds"."coverage", "ci_builds"."commit_id", "ci_builds"."name", "ci_builds"."options", "ci_builds"."allow_failure", "ci_builds"."stage", "ci_builds"."trigger_request_id", "ci_builds"."stage_idx", "ci_builds"."tag", "ci_builds"."ref", "ci_builds"."user_id", "ci_builds"."type", "ci_builds"."target_url", "ci_builds"."description", "ci_builds"."project_id", "ci_builds"."erased_by_id", "ci_builds"."erased_at", "ci_builds"."artifacts_expire_at", "ci_builds"."environment", "ci_builds"."when", "ci_builds"."yaml_variables", "ci_builds"."queued_at", "ci_builds"."token", "ci_builds"."lock_version", "ci_builds"."coverage_regex", "ci_builds"."auto_canceled_by_id", "ci_builds"."retried", "ci_builds"."protected", "ci_builds"."failure_reason", "ci_builds"."scheduled_at", "ci_builds"."token_encrypted", "ci_builds"."upstream_pipeline_id", "ci_builds"."resource_group_id", "ci_builds"."waiting_for_resource_at", "ci_builds"."processed", "ci_builds"."scheduling_type", "ci_builds"."id", "ci_builds"."stage_id" FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."id" IN (193, 194, 195, 196, 197, 200, 201, 104, 105, 106, 107, 110, 111, 112, 113, 114, 115, 116, 117, 118, 209, 214, 120, 121, 122, 123, 126, 127, 128, 129, 130, 131, 132, 133, 163, 164, 165, 166, 167, 170, 171, 172, 173, 174, 175, 176, 177, 202, 203, 204, 205, 206, 207, 210, 215, 216, 217, 218, 219, 220, 221) 

SELECT "ci_job_artifacts"."project_id", "ci_job_artifacts"."file_type", "ci_job_artifacts"."size", "ci_job_artifacts"."created_at", "ci_job_artifacts"."updated_at", "ci_job_artifacts"."expire_at", "ci_job_artifacts"."file", "ci_job_artifacts"."file_store", "ci_job_artifacts"."file_sha256", "ci_job_artifacts"."file_format", "ci_job_artifacts"."file_location", "ci_job_artifacts"."id", "ci_job_artifacts"."job_id", "ci_job_artifacts"."locked" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."job_id" IN (209, 214, 104, 105, 106, 107, 110, 111, 112, 113, 114, 115, 116, 117, 118, 120, 121, 122, 123, 126, 127, 128, 129, 130, 131, 132, 133, 163, 164, 165, 166, 167, 170, 171, 172, 173, 174, 175, 176, 177, 193, 194, 195, 196, 197, 200, 201, 202, 203, 204, 205, 206, 207, 210, 215, 216, 217, 218, 219, 220, 221)

Security::FindingsFinder.new(pipeline, {}).send(:all_security_findings)

SELECT "security_findings"."id" AS t0_r0, "security_findings"."scan_id" AS t0_r1, "security_findings"."scanner_id" AS t0_r2, "security_findings"."severity" AS t0_r3, "security_findings"."confidence" AS t0_r4, "security_findings"."project_fingerprint" AS t0_r5, "security_findings"."deduplicated" AS t0_r6, "security_findings"."position" AS t0_r7, "security_findings"."uuid" AS t0_r8, "security_findings"."overridden_uuid" AS t0_r9, "scans_security_findings"."id" AS t1_r0, "scans_security_findings"."created_at" AS t1_r1, "scans_security_findings"."updated_at" AS t1_r2, "scans_security_findings"."build_id" AS t1_r3, "scans_security_findings"."scan_type" AS t1_r4, "scans_security_findings"."info" AS t1_r5, "scans_security_findings"."project_id" AS t1_r6, "scans_security_findings"."pipeline_id" AS t1_r7, "scans_security_findings"."latest" AS t1_r8, "scans_security_findings"."status" AS t1_r9, "vulnerability_scanners"."id" AS t2_r0, "vulnerability_scanners"."created_at" AS t2_r1, "vulnerability_scanners"."updated_at" AS t2_r2, "vulnerability_scanners"."project_id" AS t2_r3, "vulnerability_scanners"."external_id" AS t2_r4, "vulnerability_scanners"."name" AS t2_r5, "vulnerability_scanners"."vendor" AS t2_r6 FROM "security_findings" INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id" INNER JOIN "security_scans" "scans_security_findings" ON "scans_security_findings"."id" = "security_findings"."scan_id" LEFT OUTER JOIN "vulnerability_scanners" ON "vulnerability_scanners"."id" = "security_findings"."scanner_id" WHERE "security_scans"."pipeline_id" = 8 AND "security_findings"."deduplicated" = TRUE AND "security_scans"."latest" = TRUE AND "security_scans"."status" = 1 ORDER BY "security_findings"."severity" DESC, "security_findings"."confidence" DESC, "security_findings"."id" ASC LIMIT 20 OFFSET 0

pipeline.security_findings.with_pipeline_entities.with_scan

SELECT "security_findings".* FROM "security_findings" INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id" WHERE "security_scans"."pipeline_id" = 8 

SELECT "security_scans".* FROM "security_scans" WHERE "security_scans"."id" IN (2, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) 

SELECT "ci_builds"."status", "ci_builds"."finished_at", "ci_builds"."trace", "ci_builds"."created_at", "ci_builds"."updated_at", "ci_builds"."started_at", "ci_builds"."runner_id", "ci_builds"."coverage", "ci_builds"."commit_id", "ci_builds"."name", "ci_builds"."options", "ci_builds"."allow_failure", "ci_builds"."stage", "ci_builds"."trigger_request_id", "ci_builds"."stage_idx", "ci_builds"."tag", "ci_builds"."ref", "ci_builds"."user_id", "ci_builds"."type", "ci_builds"."target_url", "ci_builds"."description", "ci_builds"."project_id", "ci_builds"."erased_by_id", "ci_builds"."erased_at", "ci_builds"."artifacts_expire_at", "ci_builds"."environment", "ci_builds"."when", "ci_builds"."yaml_variables", "ci_builds"."queued_at", "ci_builds"."token", "ci_builds"."lock_version", "ci_builds"."coverage_regex", "ci_builds"."auto_canceled_by_id", "ci_builds"."retried", "ci_builds"."protected", "ci_builds"."failure_reason", "ci_builds"."scheduled_at", "ci_builds"."token_encrypted", "ci_builds"."upstream_pipeline_id", "ci_builds"."resource_group_id", "ci_builds"."waiting_for_resource_at", "ci_builds"."processed", "ci_builds"."scheduling_type", "ci_builds"."id", "ci_builds"."stage_id" FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."id" IN (104, 105, 106, 107, 110, 111, 112, 113, 114, 115, 116, 117) 

Ci::JobArtifact Load (0.4ms)  SELECT "ci_job_artifacts"."project_id", "ci_job_artifacts"."file_type", "ci_job_artifacts"."size", "ci_job_artifacts"."created_at", "ci_job_artifacts"."updated_at", "ci_job_artifacts"."expire_at", "ci_job_artifacts"."file", "ci_job_artifacts"."file_store", "ci_job_artifacts"."file_sha256", "ci_job_artifacts"."file_format", "ci_job_artifacts"."file_location", "ci_job_artifacts"."id", "ci_job_artifacts"."job_id", "ci_job_artifacts"."locked" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."job_id" IN (104, 105, 106, 107, 110, 111, 112, 113, 114, 115, 116, 117) 

Ci::Pipeline Load (0.3ms)  SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 8

Related to #342380 (closed)