go.fuchsia.dev/infra@v0.0.0-20240507153436-9b593402251b/cmd/autogardener/queries/affected_test_results.sql (about)

     1  -- Copyright 2022 The Fuchsia Authors. All rights reserved.
     2  -- Use of this source code is governed by a BSD-style license that can be
     3  -- found in the LICENSE file.
     4  
     5  -- Given a ResultDB test ID, returns a list of changes within a time window
     6  -- that affected that test, based on build graph analysis.
     7  --
     8  -- Input parameters:
     9  --   test_id (string): Name of the test to search for.
    10  --   earliest_time (timestamp): Beginning of the time window.
    11  --   latest_time (timestamp): End of the time window.
    12  --
    13  -- Output columns:
    14  --   Change (GerritChange): Buildbucket-style GerritChange struct identifying
    15  --     the change against which the test ran.
    16  --   TestStatus (string): ResultDB status of the test run (e.g. "PASS", "FAIL",
    17  --     "SKIP").
    18  
    19  WITH
    20    resultdb AS (
    21      SELECT
    22        *,
    23        IF(exported.id LIKE 'build-%', CAST(SUBSTR(exported.id, 7) AS INT64), NULL) AS build_id,
    24        (SELECT value = 'true' FROM UNNEST(tags) WHERE key = 'affected' LIMIT 1) AS affected,
    25      FROM `fuchsia-infra.resultdb.try`
    26    ),
    27    buildbucket AS (
    28      SELECT
    29        *,
    30        input.gerrit_changes[OFFSET(0)] AS change,
    31      FROM `cr-buildbucket.fuchsia.builds`
    32      WHERE ARRAY_LENGTH(input.gerrit_changes) > 0
    33    )
    34  SELECT
    35    b.change AS Change,
    36    r.status AS TestStatus,
    37  FROM
    38    resultdb AS r
    39  JOIN buildbucket AS b
    40    ON b.id = r.build_id
    41  WHERE
    42    r.test_id = @test_id
    43    AND affected
    44    AND r.partition_time > @earliest_time
    45    AND r.partition_time < @latest_time