go.fuchsia.dev/infra@v0.0.0-20240507153436-9b593402251b/cmd/autogardener/queries/nearby_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  -- Returns CI builds within a time window on which a given test ran.
     6  --
     7  -- TODO(olivernewman): Because this query returns only builds where the test
     8  -- ran, the blamelist analysis won't be correct for tefmocheck tests, which
     9  -- don't appear in the test results when they pass. *Any* commits in or before
    10  -- the first failure will be considered to have a distance of 1.
    11  --
    12  -- Input parameters:
    13  --   test_id (string): Name of the test to search for.
    14  --   earliest_time (timestamp): Beginning of the time window.
    15  --   latest_time (timestamp): End of the time window.
    16  --   repo (string): Only consider builds that triggered on changes to this git
    17  --     repo.
    18  --
    19  -- Output columns:
    20  --   Builder (string): Slash-separated full builder name (e.g.
    21  --     "fuchsia/try/core.x64-release").
    22  --   Failed (bool): Whether the test failed.
    23  --   CommitPosition (int): Index of the commit that triggered the build, within
    24  --     its repository.
    25  
    26  WITH
    27    resultdb AS (
    28      SELECT
    29        *,
    30        IF(exported.id LIKE 'build-%', CAST(SUBSTR(exported.id, 7) AS INT64), NULL) AS build_id,
    31      FROM
    32        (
    33          SELECT * FROM `fuchsia-infra.resultdb.ci`
    34          UNION ALL
    35          -- Dev builder test results are kept in a separate table since the data
    36          -- may be garbage, but the blamelists are very often still useful.
    37          SELECT * FROM `fuchsia-infra.resultdb.dev`
    38        )
    39      WHERE
    40        test_id = @test_id
    41        AND partition_time > @earliest_time
    42        AND partition_time < @latest_time
    43    ),
    44    buildbucket AS (
    45      SELECT
    46        *,
    47        CAST(
    48          CAST(JSON_QUERY(output.properties, '$.integration-revision-count') AS FLOAT64) AS INT64)
    49          AS commit_position,
    50        CONCAT(input.gitiles_commit.host, '/', input.gitiles_commit.project) AS repo,
    51      FROM `cr-buildbucket.fuchsia.builds`
    52    )
    53  SELECT
    54    CONCAT(b.builder.project, '/', b.builder.bucket, '/', b.builder.builder) AS Builder,
    55    NOT r.expected AS Failed,
    56    b.commit_position AS CommitPosition,
    57  FROM
    58    resultdb AS r
    59  JOIN (SELECT * FROM buildbucket WHERE commit_position IS NOT NULL) AS b
    60    ON b.id = r.build_id
    61  WHERE
    62    b.repo = @repo
    63  ORDER BY b.start_time DESC