APEX CNO

APEX Daily Pulse — 2026-11-15

Joined Rows Are Not Finding Counts

[✅ Verified] Pillar: Analysis and classification

[❓ Unverified] Estimated duration: 4–6 minutes

Reactivation target: Detect a severity metric that changes when tags change, although no finding changes.

Prerequisites

- Python 3.12+ with standard-library sqlite3.

- A Bash-compatible terminal on owned equipment.

- No privileges or network access. The database exists only in memory.

Actions

  1. 1. Predict the critical-finding percentage for two findings: one critical and one high. Then run the complete fixture:
python3 - <<'PY'
import sqlite3

def measure(db: sqlite3.Connection) -> tuple[int, int, int, int]:
 """Return joined rows, unique findings, and critical counts."""
 row = db.execute("""
 SELECT
 count(*),
 count(DISTINCT f.id),
 sum(CASE WHEN f.severity = 'critical' THEN 1 ELSE 0 END),
 count(DISTINCT CASE
 WHEN f.severity = 'critical' THEN f.id
 END)
 FROM findings AS f
 JOIN tags AS t ON t.finding_id = f.id
 """).fetchone()
 if row is None:
 raise RuntimeError("aggregate result missing")
 return tuple(int(value) for value in row)

db = sqlite3.connect(":memory:")
try:
 db.executescript("""
 CREATE TABLE findings (
 id TEXT PRIMARY KEY,
 severity TEXT NOT NULL
 );
 CREATE TABLE tags (
 finding_id TEXT NOT NULL,
 tag TEXT NOT NULL,
 PRIMARY KEY (finding_id, tag)
 );
 INSERT INTO findings VALUES
 ('F1', 'critical'), ('F2', 'high');
 INSERT INTO tags VALUES
 ('F1', 'external'), ('F1', 'production'),
 ('F2', 'internal');
 """)

 before = measure(db)

 # Metadata-only change: no new finding or severity change.
 db.execute("INSERT INTO tags VALUES ('F1', 'reviewed')")
 after = measure(db)

 for label, result in (("before", before), ("after", after)):
 rows, findings, critical_rows, critical_findings = result
 print(
 f"{label}: joined_rows={rows} findings={findings} "
 f"naive_critical_pct={100 * critical_rows / rows:.1f} "
 f"guarded_critical_pct={100 * critical_findings / findings:.1f}"
 )

 passed = (
 before == (3, 2, 2, 1)
 and after == (4, 2, 3, 1)
 )
 print(f"drill_passed={passed}")
 raise SystemExit(0 if passed else 1)
finally:
 db.close()
PY
  1. 2. Check the metadata-only control. Adding reviewed to F1 must not change either the number of findings or the critical-finding percentage.
  1. 3. Identify the counting unit in each calculation: the naive calculation counts finding–tag pairs; the guarded calculation counts unique finding identities.

Observable signal

[❓ Unverified] Expected output; this fixture was not executed in this run:

before: joined_rows=3 findings=2 naive_critical_pct=66.7 guarded_critical_pct=50.0
after: joined_rows=4 findings=2 naive_critical_pct=75.0 guarded_critical_pct=50.0
drill_passed=True

[✅ Verified] Mechanism: a one-to-many join emits one row per matching tag. More tags give a finding more weight in count(*) and row-based severity sums. Counting distinct finding IDs removes that extra weight for these counts.

Success condition

The tag change increases joined rows from three to four. Unique findings remain two, and the critical share remains 50.0%. All tuple assertions pass.

Failure signature

[✅ Verified] The reported critical-finding percentage changes from 66.7% to 75.0% after a tag-only change. The metric measures tag multiplicity, not the declared finding population.

Python unavailable: sqlite3 CLI alternative

Run this self-contained in-memory query:

sqlite3 :memory: <<'SQL'
WITH
findings(id, severity) AS (
 VALUES ('F1', 'critical'), ('F2', 'high')
),
tags(finding_id, tag) AS (
 VALUES ('F1', 'external'), ('F1', 'production'),
 ('F1', 'reviewed'), ('F2', 'internal')
)
SELECT
 'joined_rows=' || count(*),
 'findings=' || count(DISTINCT f.id),
 'critical_findings=' || count(DISTINCT CASE
 WHEN f.severity = 'critical' THEN f.id
 END)
FROM findings AS f
JOIN tags AS t ON t.finding_id = f.id;
SQL

[❓ Unverified] Expected output:

joined_rows=4|findings=2|critical_findings=1

Boundary check

[✅ Verified] Hidden assumption: every finding has at least one tag. This inner join excludes untagged findings, even with DISTINCT. For an all-findings metric, aggregate the findings table directly. If tags define scope, use an explicit EXISTS filter or deduplicate the scoped IDs before aggregation.

[✅ Verified] Distinct counts are not a general repair for sums or averages. Define the population and counting unit first. Keep the tag-only mutation as a regression control so operators do not need to inspect every dashboard query manually.

Adaptation question

Which APEX finding or benchmark metrics change when labels, evidence links, or reviewer records are added without changing the underlying result?