1# Copyright 2022 gRPC authors.
2#
3# Licensed under the Apache License, Version 2.0 (the "License");
4# you may not use this file except in compliance with the License.
5# You may obtain a copy of the License at
6#
7#     http://www.apache.org/licenses/LICENSE-2.0
8#
9# Unless required by applicable law or agreed to in writing, software
10# distributed under the License is distributed on an "AS IS" BASIS,
11# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12# See the License for the specific language governing permissions and
13# limitations under the License.
14
15QUERY = """
16#standardSQL
17
18-- See https://console.cloud.google.com/bigquery?sq=830293263384:5a8549832dfb48d9b2c04312a4ae3181 for the original query
19
20WITH
21
22  runs AS (
23  SELECT
24    RTRIM(LTRIM(REGEXP_REPLACE(test_target, r'(@poller=.+)', ''))) AS test_binary,
25    REGEXP_EXTRACT(test_target, r'poller=(\w+)') AS poll_strategy,
26    job_name,
27    test_target,
28    test_class_name,
29    CASE
30      # in case of timeout / retry / segfault the "test_case" fields will contain weird stuff
31      # e.g. "test_shard2_run0_attempt0" or "shard_2/20"
32      # when aggregating, we want to display all of these as a single category of problems
33      WHEN test_case like 'test_shard_%_run%_attempt%' THEN 'CANNOT_DETERMINE'
34      WHEN test_case like '%shard_%/%' THEN 'CANNOT_DETERMINE'
35      # when test_case looks suspiciously like test_target
36      # its value is probably meaningless and it means that the entire target has failed
37      # e.g. test_target="//test/cpp/client:destroy_grpclb_channel_with_active_connect_stress_test" and test_case="test/cpp/client/destroy_grpclb_channel_with_active_connect_stress_test.exe"
38      WHEN STRPOS(test_case, REPLACE(SUBSTR(test_target, 3), ":", "/")) != 0 THEN 'CANNOT_DETERMINE'
39      ELSE test_case
40    END AS test_case,
41    result,
42    build_id,
43    timestamp
44  FROM
45    `grpc-testing.jenkins_test_results.rbe_test_results`
46  WHERE
47    DATETIME_DIFF(CURRENT_DATETIME(),
48      dateTIME(timestamp),
49      HOUR) < {lookback_hours}
50    ),
51
52  results_counts_per_build AS (
53  SELECT
54    test_binary,
55    #test_target, # aggregate data over all pollers
56    test_class_name,
57    test_case,
58    SUM(SAFE_CAST(result != 'PASSED'
59        AND result != 'SKIPPED' AS INT64)) AS runs_failed,
60    SUM(SAFE_CAST(result != 'SKIPPED' AS INT64)) AS runs_total,
61    job_name,
62    build_id
63  FROM
64    runs
65  GROUP BY
66    test_binary,
67    test_class_name,
68    test_case,
69    job_name,
70    build_id),
71
72  builds_with_missing_cannot_determine_testcase_entry AS (
73    SELECT
74      test_binary,
75      job_name,
76      build_id,
77    FROM
78      results_counts_per_build
79    GROUP BY
80      test_binary,
81      job_name,
82      build_id
83    HAVING COUNTIF(test_case = 'CANNOT_DETERMINE') = 0
84  ),
85
86  # for each test target and build, generate a fake entry with "CANNOT_DETERMINE" test_case
87  # if not already present.
88  # this is because in many builds, there will be no "CANNOT_DETERMINE" entry
89  # and we want to avoid skewing the statistics
90  results_counts_per_build_with_fake_cannot_determine_test_case_entries AS (
91    (SELECT * FROM results_counts_per_build)
92    UNION ALL
93    (SELECT
94      test_binary,
95      '' AS test_class_name,  # when test_case is 'CANNOT_DETERMINE', test class is empty string
96      'CANNOT_DETERMINE' AS test_case,  # see table "runs"
97      0 AS runs_failed,
98      1 AS runs_total,
99      job_name,
100      build_id
101    FROM
102      builds_with_missing_cannot_determine_testcase_entry)
103  ),
104
105  results_counts AS (
106  SELECT
107    test_binary,
108    test_class_name,
109    test_case,
110    job_name,
111    SUM(runs_failed) AS runs_failed,
112    SUM(runs_total) AS runs_total,
113    SUM(SAFE_CAST(runs_failed > 0 AS INT64)) AS builds_failed,
114    COUNT(build_id) AS builds_total,
115    STRING_AGG(CASE
116        WHEN runs_failed > 0 THEN 'X'
117        ELSE '_' END, ''
118    ORDER BY
119      build_id ASC) AS build_failure_pattern,
120    FORMAT("%T", ARRAY_AGG(build_id
121      ORDER BY
122        build_id ASC)) AS builds
123  FROM
124    #results_counts_per_build
125    results_counts_per_build_with_fake_cannot_determine_test_case_entries
126  GROUP BY
127    test_binary,
128    test_class_name,
129    test_case,
130    job_name
131  HAVING
132    runs_failed > 0)
133
134SELECT
135  ROUND(100*builds_failed / builds_total, 2) AS pct_builds_failed,
136  ROUND(100*runs_failed / runs_total, 2) AS pct_runs_failed,
137  test_binary,
138  test_class_name,
139  test_case,
140  job_name,
141  build_failure_pattern
142
143FROM
144  results_counts
145ORDER BY
146  pct_builds_failed DESC
147"""
148