Skip to content

Fix compliance violations mergedBefore/mergedAfter filters so the filtering date is included properly

Robert Hunt requested to merge 356389-bug-compliance-violations-merged_before-merged_after-filters-excluding-the-filter-dates into master

What does this MR do and why?

This MR fixes the compliance violations mergedBefore/mergedAfter filters so it properly includes the filtering date. Previously it was comparing a date string to a datetime column which was causing the date string to have 00:00:00 appended for comparisons. This resulted in the filtering date data being mostly excluded.

So this MR wraps the query in DATE() to make sure we're comparing like for like.

GraphQL query

Query 
query getComplianceViolations($fullPath: ID!, $filters: ComplianceViolationInput, $sort: ComplianceViolationSort) {
  group(fullPath: $fullPath) {
    id
    mergeRequestViolations(filters: filters, sort: sort) {
      nodes {
        id
        severityLevel
        reason
        violatingUser {
          id
          name
          username
          state
          avatarUrl
          webUrl
        }
        mergeRequest {
          id
          title
          mergedAt
          milestone {
            id
            title
          }
          webUrl
          author {
            id
            name
            username
            state
            avatarUrl
            webUrl
          }
          mergeUser {
            id
            name
            username
            state
            avatarUrl
            webUrl
          }
          committers {
            nodes {
              id
              name
              username
              state
              avatarUrl
              webUrl
            }
          }
          participants {
            nodes {
              id
              name
              username
              state
              avatarUrl
              webUrl
            }
          }
          approvedBy {
            nodes {
              id
              name
              username
              state
              avatarUrl
              webUrl
            }
          }
          ref: reference
          fullRef: reference(full: true)
          sourceBranch
          sourceBranchExists
          targetBranch
          targetBranchExists
          headPipeline {
            detailedStatus {
              id
              icon
              favicon
              text
              label
              group
              tooltip
              hasDetails
              detailsPath
            }
          }
          project {
            id
            avatarUrl
            name
            webUrl
            complianceFrameworks {
              nodes {
                id
                name
                description
                color
              }
            }
          }
        }
      }
    }
  }
}

Database explain plans

Filter by merged before

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(current_user: current_user, group: group, params: { merged_before: '2022-03-01' }).execute
Plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9391/commands/33277
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    INNER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
    INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE
    "projects"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS ((
                SELECT
                    "namespaces"."id",
                    "namespaces"."name",
                    "namespaces"."path",
                    "namespaces"."owner_id",
                    "namespaces"."created_at",
                    "namespaces"."updated_at",
                    "namespaces"."type",
                    "namespaces"."description",
                    "namespaces"."avatar",
                    "namespaces"."membership_lock",
                    "namespaces"."share_with_group_lock",
                    "namespaces"."visibility_level",
                    "namespaces"."request_access_enabled",
                    "namespaces"."ldap_sync_status",
                    "namespaces"."ldap_sync_error",
                    "namespaces"."ldap_sync_last_update_at",
                    "namespaces"."ldap_sync_last_successful_update_at",
                    "namespaces"."ldap_sync_last_sync_at",
                    "namespaces"."description_html",
                    "namespaces"."lfs_enabled",
                    "namespaces"."parent_id",
                    "namespaces"."shared_runners_minutes_limit",
                    "namespaces"."repository_size_limit",
                    "namespaces"."require_two_factor_authentication",
                    "namespaces"."two_factor_grace_period",
                    "namespaces"."cached_markdown_version",
                    "namespaces"."project_creation_level",
                    "namespaces"."runners_token",
                    "namespaces"."file_template_project_id",
                    "namespaces"."saml_discovery_token",
                    "namespaces"."runners_token_encrypted",
                    "namespaces"."custom_project_templates_group_id",
                    "namespaces"."auto_devops_enabled",
                    "namespaces"."extra_shared_runners_minutes_limit",
                    "namespaces"."last_ci_minutes_notification_at",
                    "namespaces"."last_ci_minutes_usage_notification_level",
                    "namespaces"."subgroup_creation_level",
                    "namespaces"."emails_disabled",
                    "namespaces"."max_pages_size",
                    "namespaces"."max_artifacts_size",
                    "namespaces"."mentions_disabled",
                    "namespaces"."default_branch_protection",
                    "namespaces"."unlock_membership_to_ldap",
                    "namespaces"."max_personal_access_token_lifetime",
                    "namespaces"."push_rule_id",
                    "namespaces"."shared_runners_enabled",
                    "namespaces"."allow_descendants_override_disabled_shared_runners",
                    "namespaces"."traversal_ids"
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."id" = 9970)
            UNION (
                SELECT
                    "namespaces"."id",
                    "namespaces"."name",
                    "namespaces"."path",
                    "namespaces"."owner_id",
                    "namespaces"."created_at",
                    "namespaces"."updated_at",
                    "namespaces"."type",
                    "namespaces"."description",
                    "namespaces"."avatar",
                    "namespaces"."membership_lock",
                    "namespaces"."share_with_group_lock",
                    "namespaces"."visibility_level",
                    "namespaces"."request_access_enabled",
                    "namespaces"."ldap_sync_status",
                    "namespaces"."ldap_sync_error",
                    "namespaces"."ldap_sync_last_update_at",
                    "namespaces"."ldap_sync_last_successful_update_at",
                    "namespaces"."ldap_sync_last_sync_at",
                    "namespaces"."description_html",
                    "namespaces"."lfs_enabled",
                    "namespaces"."parent_id",
                    "namespaces"."shared_runners_minutes_limit",
                    "namespaces"."repository_size_limit",
                    "namespaces"."require_two_factor_authentication",
                    "namespaces"."two_factor_grace_period",
                    "namespaces"."cached_markdown_version",
                    "namespaces"."project_creation_level",
                    "namespaces"."runners_token",
                    "namespaces"."file_template_project_id",
                    "namespaces"."saml_discovery_token",
                    "namespaces"."runners_token_encrypted",
                    "namespaces"."custom_project_templates_group_id",
                    "namespaces"."auto_devops_enabled",
                    "namespaces"."extra_shared_runners_minutes_limit",
                    "namespaces"."last_ci_minutes_notification_at",
                    "namespaces"."last_ci_minutes_usage_notification_level",
                    "namespaces"."subgroup_creation_level",
                    "namespaces"."emails_disabled",
                    "namespaces"."max_pages_size",
                    "namespaces"."max_artifacts_size",
                    "namespaces"."mentions_disabled",
                    "namespaces"."default_branch_protection",
                    "namespaces"."unlock_membership_to_ldap",
                    "namespaces"."max_personal_access_token_lifetime",
                    "namespaces"."push_rule_id",
                    "namespaces"."shared_runners_enabled",
                    "namespaces"."allow_descendants_override_disabled_shared_runners",
                    "namespaces"."traversal_ids"
                FROM
                    "namespaces",
                    "base_and_descendants"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."parent_id" = "base_and_descendants"."id"))
            SELECT
                "namespaces"."id"
            FROM
                "base_and_descendants" AS "namespaces")
        AND ("merge_request_metrics"."merged_at" <= '2022-03-01')
ORDER BY
    "merge_requests_compliance_violations"."severity_level" DESC,
    "merge_requests_compliance_violations"."id" DESC;
Filter by merged after

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(current_user: current_user, group: group, params: { merged_after: '2022-03-01' }).execute
Plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9391/commands/33278
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    INNER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
    INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE
    "projects"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS ((
                SELECT
                    "namespaces"."id",
                    "namespaces"."name",
                    "namespaces"."path",
                    "namespaces"."owner_id",
                    "namespaces"."created_at",
                    "namespaces"."updated_at",
                    "namespaces"."type",
                    "namespaces"."description",
                    "namespaces"."avatar",
                    "namespaces"."membership_lock",
                    "namespaces"."share_with_group_lock",
                    "namespaces"."visibility_level",
                    "namespaces"."request_access_enabled",
                    "namespaces"."ldap_sync_status",
                    "namespaces"."ldap_sync_error",
                    "namespaces"."ldap_sync_last_update_at",
                    "namespaces"."ldap_sync_last_successful_update_at",
                    "namespaces"."ldap_sync_last_sync_at",
                    "namespaces"."description_html",
                    "namespaces"."lfs_enabled",
                    "namespaces"."parent_id",
                    "namespaces"."shared_runners_minutes_limit",
                    "namespaces"."repository_size_limit",
                    "namespaces"."require_two_factor_authentication",
                    "namespaces"."two_factor_grace_period",
                    "namespaces"."cached_markdown_version",
                    "namespaces"."project_creation_level",
                    "namespaces"."runners_token",
                    "namespaces"."file_template_project_id",
                    "namespaces"."saml_discovery_token",
                    "namespaces"."runners_token_encrypted",
                    "namespaces"."custom_project_templates_group_id",
                    "namespaces"."auto_devops_enabled",
                    "namespaces"."extra_shared_runners_minutes_limit",
                    "namespaces"."last_ci_minutes_notification_at",
                    "namespaces"."last_ci_minutes_usage_notification_level",
                    "namespaces"."subgroup_creation_level",
                    "namespaces"."emails_disabled",
                    "namespaces"."max_pages_size",
                    "namespaces"."max_artifacts_size",
                    "namespaces"."mentions_disabled",
                    "namespaces"."default_branch_protection",
                    "namespaces"."unlock_membership_to_ldap",
                    "namespaces"."max_personal_access_token_lifetime",
                    "namespaces"."push_rule_id",
                    "namespaces"."shared_runners_enabled",
                    "namespaces"."allow_descendants_override_disabled_shared_runners",
                    "namespaces"."traversal_ids"
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."id" = 9970)
            UNION (
                SELECT
                    "namespaces"."id",
                    "namespaces"."name",
                    "namespaces"."path",
                    "namespaces"."owner_id",
                    "namespaces"."created_at",
                    "namespaces"."updated_at",
                    "namespaces"."type",
                    "namespaces"."description",
                    "namespaces"."avatar",
                    "namespaces"."membership_lock",
                    "namespaces"."share_with_group_lock",
                    "namespaces"."visibility_level",
                    "namespaces"."request_access_enabled",
                    "namespaces"."ldap_sync_status",
                    "namespaces"."ldap_sync_error",
                    "namespaces"."ldap_sync_last_update_at",
                    "namespaces"."ldap_sync_last_successful_update_at",
                    "namespaces"."ldap_sync_last_sync_at",
                    "namespaces"."description_html",
                    "namespaces"."lfs_enabled",
                    "namespaces"."parent_id",
                    "namespaces"."shared_runners_minutes_limit",
                    "namespaces"."repository_size_limit",
                    "namespaces"."require_two_factor_authentication",
                    "namespaces"."two_factor_grace_period",
                    "namespaces"."cached_markdown_version",
                    "namespaces"."project_creation_level",
                    "namespaces"."runners_token",
                    "namespaces"."file_template_project_id",
                    "namespaces"."saml_discovery_token",
                    "namespaces"."runners_token_encrypted",
                    "namespaces"."custom_project_templates_group_id",
                    "namespaces"."auto_devops_enabled",
                    "namespaces"."extra_shared_runners_minutes_limit",
                    "namespaces"."last_ci_minutes_notification_at",
                    "namespaces"."last_ci_minutes_usage_notification_level",
                    "namespaces"."subgroup_creation_level",
                    "namespaces"."emails_disabled",
                    "namespaces"."max_pages_size",
                    "namespaces"."max_artifacts_size",
                    "namespaces"."mentions_disabled",
                    "namespaces"."default_branch_protection",
                    "namespaces"."unlock_membership_to_ldap",
                    "namespaces"."max_personal_access_token_lifetime",
                    "namespaces"."push_rule_id",
                    "namespaces"."shared_runners_enabled",
                    "namespaces"."allow_descendants_override_disabled_shared_runners",
                    "namespaces"."traversal_ids"
                FROM
                    "namespaces",
                    "base_and_descendants"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."parent_id" = "base_and_descendants"."id"))
            SELECT
                "namespaces"."id"
            FROM
                "base_and_descendants" AS "namespaces")
        AND ("merge_request_metrics"."merged_at" >= '2022-03-01')
ORDER BY
    "merge_requests_compliance_violations"."severity_level" DESC,
    "merge_requests_compliance_violations"."id" DESC;
Filter by all filters

Finder call: ComplianceManagement::MergeRequests::ComplianceViolationsFinder.new(current_user: current_user, group: group, params: { project_ids: [278964, 13083], merged_before: '2022-03-20', merged_after: '2022-03-01' }).execute
Plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9391/commands/33279
Raw SQL:

SELECT
    "merge_requests_compliance_violations".*
FROM
    "merge_requests_compliance_violations"
    INNER JOIN "merge_requests" ON "merge_requests"."id" = "merge_requests_compliance_violations"."merge_request_id"
    INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
    INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
    INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE
    "projects"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS ((
                SELECT
                    "namespaces"."id",
                    "namespaces"."name",
                    "namespaces"."path",
                    "namespaces"."owner_id",
                    "namespaces"."created_at",
                    "namespaces"."updated_at",
                    "namespaces"."type",
                    "namespaces"."description",
                    "namespaces"."avatar",
                    "namespaces"."membership_lock",
                    "namespaces"."share_with_group_lock",
                    "namespaces"."visibility_level",
                    "namespaces"."request_access_enabled",
                    "namespaces"."ldap_sync_status",
                    "namespaces"."ldap_sync_error",
                    "namespaces"."ldap_sync_last_update_at",
                    "namespaces"."ldap_sync_last_successful_update_at",
                    "namespaces"."ldap_sync_last_sync_at",
                    "namespaces"."description_html",
                    "namespaces"."lfs_enabled",
                    "namespaces"."parent_id",
                    "namespaces"."shared_runners_minutes_limit",
                    "namespaces"."repository_size_limit",
                    "namespaces"."require_two_factor_authentication",
                    "namespaces"."two_factor_grace_period",
                    "namespaces"."cached_markdown_version",
                    "namespaces"."project_creation_level",
                    "namespaces"."runners_token",
                    "namespaces"."file_template_project_id",
                    "namespaces"."saml_discovery_token",
                    "namespaces"."runners_token_encrypted",
                    "namespaces"."custom_project_templates_group_id",
                    "namespaces"."auto_devops_enabled",
                    "namespaces"."extra_shared_runners_minutes_limit",
                    "namespaces"."last_ci_minutes_notification_at",
                    "namespaces"."last_ci_minutes_usage_notification_level",
                    "namespaces"."subgroup_creation_level",
                    "namespaces"."emails_disabled",
                    "namespaces"."max_pages_size",
                    "namespaces"."max_artifacts_size",
                    "namespaces"."mentions_disabled",
                    "namespaces"."default_branch_protection",
                    "namespaces"."unlock_membership_to_ldap",
                    "namespaces"."max_personal_access_token_lifetime",
                    "namespaces"."push_rule_id",
                    "namespaces"."shared_runners_enabled",
                    "namespaces"."allow_descendants_override_disabled_shared_runners",
                    "namespaces"."traversal_ids"
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."id" = 9970)
            UNION (
                SELECT
                    "namespaces"."id",
                    "namespaces"."name",
                    "namespaces"."path",
                    "namespaces"."owner_id",
                    "namespaces"."created_at",
                    "namespaces"."updated_at",
                    "namespaces"."type",
                    "namespaces"."description",
                    "namespaces"."avatar",
                    "namespaces"."membership_lock",
                    "namespaces"."share_with_group_lock",
                    "namespaces"."visibility_level",
                    "namespaces"."request_access_enabled",
                    "namespaces"."ldap_sync_status",
                    "namespaces"."ldap_sync_error",
                    "namespaces"."ldap_sync_last_update_at",
                    "namespaces"."ldap_sync_last_successful_update_at",
                    "namespaces"."ldap_sync_last_sync_at",
                    "namespaces"."description_html",
                    "namespaces"."lfs_enabled",
                    "namespaces"."parent_id",
                    "namespaces"."shared_runners_minutes_limit",
                    "namespaces"."repository_size_limit",
                    "namespaces"."require_two_factor_authentication",
                    "namespaces"."two_factor_grace_period",
                    "namespaces"."cached_markdown_version",
                    "namespaces"."project_creation_level",
                    "namespaces"."runners_token",
                    "namespaces"."file_template_project_id",
                    "namespaces"."saml_discovery_token",
                    "namespaces"."runners_token_encrypted",
                    "namespaces"."custom_project_templates_group_id",
                    "namespaces"."auto_devops_enabled",
                    "namespaces"."extra_shared_runners_minutes_limit",
                    "namespaces"."last_ci_minutes_notification_at",
                    "namespaces"."last_ci_minutes_usage_notification_level",
                    "namespaces"."subgroup_creation_level",
                    "namespaces"."emails_disabled",
                    "namespaces"."max_pages_size",
                    "namespaces"."max_artifacts_size",
                    "namespaces"."mentions_disabled",
                    "namespaces"."default_branch_protection",
                    "namespaces"."unlock_membership_to_ldap",
                    "namespaces"."max_personal_access_token_lifetime",
                    "namespaces"."push_rule_id",
                    "namespaces"."shared_runners_enabled",
                    "namespaces"."allow_descendants_override_disabled_shared_runners",
                    "namespaces"."traversal_ids"
                FROM
                    "namespaces",
                    "base_and_descendants"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."parent_id" = "base_and_descendants"."id"))
            SELECT
                "namespaces"."id"
            FROM
                "base_and_descendants" AS "namespaces")
        AND "projects"."id" IN (278964, 13083)
    AND ("merge_request_metrics"."merged_at" <= '2022-03-20')
AND ("merge_request_metrics"."merged_at" >= '2022-03-01')
ORDER BY
    "merge_requests_compliance_violations"."severity_level" DESC,
    "merge_requests_compliance_violations"."id" DESC;

How to set up and validate locally

Setting up the violations

  1. Go to a projects general settings and make sure Prevent approval by author. is unticked under the Merge request approvals section
  2. Edit a file in the project and create a new merge request
  3. Use the merge requests author to approve the merge request and then merge it
  4. Wait for merging to be completed

Running the query

  1. Open your preferred GraphQL caller. You could use [GDK_HOST]/-/graphql-explorer
  2. Check that the violations you created above are shown using the query
  3. Test the different filters to check that they work as expected

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #356389 (closed)

Edited by Robert Hunt

Merge request reports