Goal and boundaries

Build a small evidence table for regression triage: how many attempts does each build contain, which attempts failed, and which tags describe those failures? The complete listing below targets Python 3.12 with sqlite3 available. It uses only the standard library, embeds every record, opens an in-memory database, and makes no network requests. The inserts construct the fictional exercise; they are not instructions to seed or modify any course or production database. Running the listing is an optional learner activity; the outputs below are expected values derived by inspection.

Read the five runs before reading the SQL. B1 has one PASS and two FAIL attempts; B2 has one PASS and one ERROR attempt; B3 has none. Both failures use fifo_stress, but their seeds differ. R2 has two tags, which makes it especially useful for finding an accidental overcount. These deliberately small records let you check every result without trusting either an assistant's explanation or a database dashboard.

Complete runnable listing

Learner run command for this listing as an independent scratch script: python3 regression_lab.py. Use a Python 3.12 interpreter with sqlite3 support. No project checkout, simulator, or database service is needed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.executescript("""
CREATE TABLE builds (
    build_id TEXT PRIMARY KEY NOT NULL, revision TEXT NOT NULL
);
CREATE TABLE runs (
    run_id TEXT PRIMARY KEY NOT NULL,
    build_id TEXT NOT NULL REFERENCES builds(build_id),
    test_name TEXT NOT NULL, seed INTEGER NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('PASS', 'FAIL', 'ERROR'))
);
CREATE TABLE failure_tags (
    run_id TEXT NOT NULL REFERENCES runs(run_id),
    tag TEXT NOT NULL, PRIMARY KEY (run_id, tag)
);
""")
con.executemany("INSERT INTO builds VALUES (?, ?)", [
    ("B1", "rev_a"), ("B2", "rev_b"), ("B3", "rev_c"),
])
con.executemany("INSERT INTO runs VALUES (?, ?, ?, ?, ?)", [
    ("R1", "B1", "reset_smoke", 11, "PASS"),
    ("R2", "B1", "fifo_stress", 22, "FAIL"),
    ("R3", "B1", "fifo_stress", 23, "FAIL"),
    ("R4", "B2", "reset_smoke", 11, "PASS"),
    ("R5", "B2", "fifo_stress", 22, "ERROR"),
])
con.executemany("INSERT INTO failure_tags VALUES (?, ?)", [
    ("R2", "timeout"), ("R2", "reset"),
    ("R3", "timeout"), ("R5", "compile"),
])
con.commit()
con.execute("PRAGMA query_only = ON")

summary = con.execute("""
SELECT b.build_id, COUNT(r.run_id) AS attempts,
       SUM(CASE WHEN r.status = 'FAIL' THEN 1 ELSE 0 END) AS fails,
       SUM(CASE WHEN r.status = 'ERROR' THEN 1 ELSE 0 END) AS errors
FROM builds AS b LEFT JOIN runs AS r ON r.build_id = b.build_id
GROUP BY b.build_id ORDER BY b.build_id
""").fetchall()
failed = con.execute("""
SELECT r.run_id, b.revision, r.test_name, r.seed
FROM runs AS r JOIN builds AS b ON b.build_id = r.build_id
WHERE r.status = ? ORDER BY r.run_id
""", ("FAIL",)).fetchall()
counts = con.execute("""
SELECT COUNT(*) AS tag_rows, COUNT(DISTINCT r.run_id) AS failed_attempts
FROM runs AS r JOIN failure_tags AS t ON t.run_id = r.run_id
WHERE r.status = ?
""", ("FAIL",)).fetchone()
assert summary == [("B1", 3, 2, 0), ("B2", 2, 0, 1), ("B3", 0, 0, 0)]
assert failed == [
    ("R2", "rev_a", "fifo_stress", 22),
    ("R3", "rev_a", "fifo_stress", 23),
]
assert counts == (3, 2)
print("builds:", summary)
print("failed:", failed)
print("joined counts:", counts)
con.close()
print("PASS: synthetic regression joins and counts")

Expected checks, not a recorded execution

1
2
3
4

builds: [('B1', 3, 2, 0), ('B2', 2, 0, 1), ('B3', 0, 0, 0)]
failed: [('R2', 'rev_a', 'fifo_stress', 22), ('R3', 'rev_a', 'fifo_stress', 23)]
joined counts: (3, 2)
PASS: synthetic regression joins and counts

The summary preserves B3 by starting at builds. COUNT(r.run_id) ignores its unmatched child, while each CASE contributes zero. The second query binds FAIL as a value and returns the revision associated with each failed attempt. The final query sees three matching tag rows but only two distinct failed run IDs. R5's compile tag is excluded because ERROR is a separate status. A label such as timeout is a triage observation, not proof of a root cause.

Exercise

For a second exercise, draft a per-test summary without joining tags. Expected groups are fifo_stress with three attempts, two FAIL and one ERROR, and reset_smoke with two attempts, both PASS. Specify ORDER BY test_name for stable presentation. If your counts exceed five total attempts, inspect whether a one-to-many join expanded the records before aggregation. Keep the original query beside a proposed revision so the reviewer can see which population changed.

Limits and source

The lab omits ingestion, concurrency, authentication, timing corners, and retry selection. query_only protects this connection's later statements from ordinary data writes; it is not a security boundary for arbitrary untrusted Python. Real access should use the data owner's approved read-only mechanism. Reference: Python 3.12 sqlite3 documentation, including placeholders and in-memory connections.

Related topics

• Up: Engineering Data

• Next: Queries versus search