CVE fixes and complexity queries¶
These queries put the two halves of aveloxis-cve together: they place each CVE fix on the default branch in its complexity window, and compare the code that fixes touch with the rest of the repository.
Read the analysis guide first. The series step is the one described in the complexity queries. Each query is tested against a seeded schema by TestAnalysisQueriesRun (internal/db).
Labor windows cover the default branch only, so these queries consider fixes as they landed on the default branch. Fixes that reached only maintenance branches have no complexity windows.
X-01 The window around each CVE fix¶
Question: For each CVE fixed on the default branch, in which window did the fix land, and how did complexity move in that step?
Windows overlap, so a landing falls in about two windows. This query uses the first window whose end is at or after the landing: the earliest snapshot that contains the fix.
WITH series AS (
SELECT w.*
FROM aveloxis_cve.repo_labor_windows w
WHERE w.repo_id = :repo_id
AND w.window_days = :window_days AND w.step_months = :step_months
AND NOT w.needs_recompute
AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), steps AS (
SELECT window_id, window_start, window_end, code_complexity, code_lines,
code_complexity - lag(code_complexity) OVER (ORDER BY window_end) AS complexity_delta,
code_lines - lag(code_lines) OVER (ORDER BY window_end) AS code_lines_delta
FROM series
), landings AS (
SELECT cve_id, first_landed_at
FROM aveloxis_cve.cve_fix_propagation
WHERE repo_id = :repo_id
AND branch_id = (SELECT branch_id FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
)
SELECT l.cve_id, l.first_landed_at, s.window_end::date AS first_window_end_after_fix,
s.code_complexity, s.complexity_delta, s.code_lines_delta
FROM landings l
LEFT JOIN LATERAL (
SELECT * FROM steps st WHERE st.window_end >= l.first_landed_at ORDER BY st.window_end LIMIT 1
) s ON TRUE
ORDER BY l.first_landed_at, l.cve_id;
Reading the result: the window columns are NULL for a fix that landed after the last complete window. The step delta includes every other change of that month. X-02 narrows it to the fixed files.
X-02 Complexity of the fixed files before and after¶
Question: For each file a CVE fix touched on the default branch, what was its complexity at the last window end before the fix, and at the first window end after it?
WITH series AS (
SELECT w.*
FROM aveloxis_cve.repo_labor_windows w
WHERE w.repo_id = :repo_id
AND w.window_days = :window_days AND w.step_months = :step_months
AND NOT w.needs_recompute
AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), default_branch AS (
SELECT branch_id
FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC
LIMIT 1
), fix_files AS (
SELECT f.cve_id, cf.file_path, min(bc.landed_at) AS landed_at
FROM aveloxis_cve.cve_fix_commits f
JOIN aveloxis_cve.branch_commits bc ON bc.commit_id = f.commit_id
AND bc.branch_id = (SELECT branch_id FROM default_branch)
JOIN aveloxis_cve.commit_files cf ON cf.commit_id = f.commit_id
WHERE f.repo_id = :repo_id
GROUP BY f.cve_id, cf.file_path
)
SELECT ff.cve_id, ff.file_path, ff.landed_at,
b.window_end::date AS before_end, rb.code_complexity AS complexity_before,
a.window_end::date AS after_end, ra.code_complexity AS complexity_after,
ra.code_complexity - rb.code_complexity AS complexity_delta,
ra.code_lines - rb.code_lines AS code_lines_delta
FROM fix_files ff
LEFT JOIN LATERAL (SELECT window_id, window_end FROM series
WHERE window_end < ff.landed_at ORDER BY window_end DESC LIMIT 1) b ON TRUE
LEFT JOIN LATERAL (SELECT window_id, window_end FROM series
WHERE window_end >= ff.landed_at ORDER BY window_end LIMIT 1) a ON TRUE
LEFT JOIN aveloxis_cve.repo_labor_rolling rb ON rb.window_id = b.window_id AND rb.file_path = ff.file_path
LEFT JOIN aveloxis_cve.repo_labor_rolling ra ON ra.window_id = a.window_id AND ra.file_path = ff.file_path
ORDER BY ff.landed_at, ff.cve_id, ff.file_path;
Reading the result:
The two snapshots are one step apart (a month by default) and include every other commit to the file in between. Check LAB-07 for a file with other activity.
complexity_beforeis NULL when the fix added the file, or when the fix landed before the first window end.
X-03 Are fixed files more complex than the rest?¶
Question: At the latest window end, do files that CVE fixes have touched carry more complexity than other files?
WITH series AS (
SELECT w.*
FROM aveloxis_cve.repo_labor_windows w
WHERE w.repo_id = :repo_id
AND w.window_days = :window_days AND w.step_months = :step_months
AND NOT w.needs_recompute
AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), latest AS (
SELECT window_id FROM series ORDER BY window_end DESC LIMIT 1
), fixed_paths AS (
SELECT DISTINCT cf.file_path
FROM aveloxis_cve.cve_fix_commits f
JOIN aveloxis_cve.commit_files cf ON cf.commit_id = f.commit_id
WHERE f.repo_id = :repo_id
)
SELECT (fp.file_path IS NOT NULL) AS touched_by_cve_fix,
count(*) AS files,
sum(r.code_complexity) AS total_complexity,
round(avg(r.code_complexity), 2) AS mean_complexity,
percentile_cont(0.5) WITHIN GROUP (ORDER BY r.code_complexity) AS median_complexity,
round(1000.0 * sum(r.code_complexity) / NULLIF(sum(r.code_lines), 0), 2) AS complexity_per_kloc,
round(avg(r.code_lines), 1) AS mean_code_lines
FROM aveloxis_cve.repo_labor_rolling r
JOIN latest l ON l.window_id = r.window_id
LEFT JOIN fixed_paths fp ON fp.file_path = r.file_path
GROUP BY (fp.file_path IS NOT NULL)
ORDER BY touched_by_cve_fix DESC;
Reading the result:
Fix paths are taken from fixes on every branch, matched by path at the latest snapshot. A file renamed since its fix is counted with the untouched files.
Larger files attract more changes of every kind, fixes included. Compare
complexity_per_klocas well as the means.
X-04 CVE fixes and complexity, window by window¶
Question: For each window, how many CVE fixes landed on the default branch, next to the window's complexity?
WITH series AS (
SELECT w.*
FROM aveloxis_cve.repo_labor_windows w
WHERE w.repo_id = :repo_id
AND w.window_days = :window_days AND w.step_months = :step_months
AND NOT w.needs_recompute
AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), default_branch AS (
SELECT branch_id
FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC
LIMIT 1
), fix_landings AS (
SELECT f.cve_id, f.commit_id, bc.landed_at
FROM aveloxis_cve.cve_fix_commits f
JOIN aveloxis_cve.branch_commits bc ON bc.commit_id = f.commit_id
AND bc.branch_id = (SELECT branch_id FROM default_branch)
WHERE f.repo_id = :repo_id
)
SELECT s.window_end::date AS window_end, s.code_complexity,
s.code_complexity - lag(s.code_complexity) OVER (ORDER BY s.window_end) AS complexity_delta,
count(DISTINCT fl.cve_id) AS cves_fixed_in_window,
count(DISTINCT fl.commit_id) AS fix_commits_in_window,
array_agg(DISTINCT fl.cve_id) FILTER (WHERE fl.cve_id IS NOT NULL) AS cve_ids
FROM series s
LEFT JOIN fix_landings fl
ON fl.landed_at <= s.window_end
AND (fl.landed_at > s.window_start
OR (s.window_end = (SELECT min(f.window_end) FROM aveloxis_cve.repo_labor_windows f
WHERE f.repo_id = s.repo_id AND f.branch_name = s.branch_name
AND f.window_days = s.window_days AND f.step_months = s.step_months)
AND fl.landed_at = s.window_start))
GROUP BY s.window_id, s.window_end, s.code_complexity
ORDER BY s.window_end;
Reading the result: windows overlap, so one fix is counted in each window it lands in. Do not sum the counts across windows.
X-05 Languages of the code CVE fixes touch¶
Question: In which languages is the code that CVE fixes touched, as of the latest window end?
WITH series AS (
SELECT w.*
FROM aveloxis_cve.repo_labor_windows w
WHERE w.repo_id = :repo_id
AND w.window_days = :window_days AND w.step_months = :step_months
AND NOT w.needs_recompute
AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), latest AS (
SELECT window_id FROM series ORDER BY window_end DESC LIMIT 1
), fix_paths AS (
SELECT DISTINCT f.cve_id, cf.file_path
FROM aveloxis_cve.cve_fix_commits f
JOIN aveloxis_cve.commit_files cf ON cf.commit_id = f.commit_id
WHERE f.repo_id = :repo_id
), located AS (
SELECT fp.cve_id, fp.file_path, r.code_complexity,
COALESCE(r.programming_language, '(not in latest snapshot)') AS programming_language
FROM fix_paths fp
LEFT JOIN aveloxis_cve.repo_labor_rolling r
ON r.window_id = (SELECT window_id FROM latest) AND r.file_path = fp.file_path
)
SELECT l.programming_language,
count(DISTINCT l.cve_id) AS cves,
count(DISTINCT l.file_path) AS files,
(SELECT sum(d.code_complexity)
FROM (SELECT DISTINCT file_path, code_complexity FROM located x
WHERE x.programming_language = l.programming_language) d) AS complexity_now
FROM located l
GROUP BY l.programming_language
ORDER BY cves DESC, l.programming_language;
Reading the result: (not in latest snapshot) collects paths deleted or renamed since the fix, and files the scc walk skips or scc does not count (documentation, build files, binaries).