← Documentation / SQL Reference

DOI 1.9 — Live SQL verification of Failure Fingerprint values (High-Fidelity / Digital-Art)

View DOI 1.9 on Zenodo: https://doi.org/10.5281/zenodo.21433900

Old DBBuilt on the earlier, JOIN-based pipeline. Not every model was affected by its data-integrity bug — Standard and High-Fidelity needed correction, SwinIR and Digital-Art were confirmed unaffected. See Superseded Files.

verification_queries.md

The dataset-scale and "Merutan condition" block-count queries run live on camera against the High-Fidelity and Digital-Art databases, annotated with explanatory notes.

SELECT
    COUNT(DISTINCT image_id) AS total_images,
    COUNT(*)                 AS total_blocks
FROM blocks;

Step 1. How many distinct source images the database covers, and the total block count across all of them.

SELECT
    COUNT(DISTINCT image_id) AS merutan_images,
    COUNT(*)                 AS merutan_blocks
FROM blocks
WHERE is_comp_zero = TRUE
  AND delta_ssim != 0;

Step 2. The same count, restricted to the "Merutan condition" — the flat-reconstruction, score-still-changed population this series uses as the μ_stat source (see is_comp_zero in the Glossary).

SELECT
    ROUND(mu_gt / 0.01) * 0.01            AS mu_bin,
    COUNT(*)                              AS block_count,
    ROUND(STDDEV_POP(ABS(delta_ssim)), 6) AS std_abs_delta_ssim
FROM blocks
WHERE is_comp_zero = TRUE
  AND delta_ssim != 0
GROUP BY ROUND(mu_gt / 0.01) * 0.01
HAVING COUNT(*) > 20
ORDER BY mu_bin
LIMIT 20;

Step 3. The phase-transition curve: the same μ_gt-binned |Δssim| spread as DOI 1.4's first query, printed here to actually look at (rather than only feeding into a later calculation).

WITH bins AS ( ... GROUP BY ROUND(mu_gt / 0.01) * 0.01 ... ),
ordered AS ( SELECT ..., ROW_NUMBER() OVER (ORDER BY mu_bin) AS rn, COUNT(*) OVER () AS n FROM bins ),
split_cost AS (
    SELECT k.rn AS k, k.mu_bin AS mu_stat,
        VAR_POP(l.std_val) FILTER (WHERE l.rn <= k.rn) * k.rn
        + VAR_POP(l.std_val) FILTER (WHERE l.rn >  k.rn) * (k.n - k.rn) AS cost
    FROM ordered k CROSS JOIN ordered l
    WHERE k.rn BETWEEN 2 AND k.n - 2
    GROUP BY k.rn, k.mu_bin, k.n
)
SELECT PRINTF('%.4f', mu_stat) AS mu_stat
FROM split_cost ORDER BY cost ASC LIMIT 1;

Step 4. μ_stat itself, via the Two-Segment Optimal Split pattern — explained in full in How to read SQL. In short: re-bins the Step 3 curve, tries every possible split point, and returns the bin boundary that best divides it into two flat segments.

WITH bins AS ( SELECT round(sigma_gt, 3) AS sigma_bin, AVG(ssim_s) AS avg_s
               FROM blocks WHERE is_comp_zero = FALSE AND sigma_gt > 0
               GROUP BY round(sigma_gt, 3) HAVING COUNT(*) > 10 ),
ordered AS ( ... ROW_NUMBER() OVER (ORDER BY sigma_bin) ... ),
split_cost AS ( ... VAR_POP(l.avg_s) FILTER (...) ... ),
best_k AS ( SELECT k FROM split_cost ORDER BY cost ASC LIMIT 1 )
SELECT PRINTF('%.4f', sigma_bin) AS sigma_stat_A
FROM ordered, best_k WHERE rn = best_k.k + 1;

Step 5 (Approach A). The same Two-Segment Split, on σ_gt bins of avg_s (mean structure-term value) instead of μ_gt bins of a standard deviation. This is the approach used for the published σ_stat value throughout this series.

WITH bins AS ( SELECT round(sigma_gt, 2) AS sigma_bin, STDDEV_POP(ABS(delta_ssim)) AS std_val
               FROM blocks WHERE sigma_gt > 0 AND delta_ssim != 0
               GROUP BY round(sigma_gt, 2) HAVING COUNT(*) > 20 ),
-- ...same Two-Segment Split machinery as Step 5...
SELECT PRINTF('%.4f', AVG(std_val)) AS sigma_stat_B
FROM ordered, best_k WHERE rn <= best_k.k;

Step 5b (Approach B). An alternative σ_stat definition — coarser bins, and instead of picking the boundary bin directly, it averages the |Δssim| spread across every bin before the split point. Documented as a comparison; not the value used elsewhere in this series.

Step 6 re-derives both μ_stat and σ_stat(A) in one combined query (the same two CTE chains from Steps 4 and 5, renamed to coexist), then subtracts the theoretical critical points μ_c=√(C1/2) and σ_c=√C3 to get the deviation coordinate (Δμ, Δσ) — the Failure Fingerprint itself. A final supplement query just prints the raw C1/C2/C3 constants and the critical points computed from them, as a reference row.

source_script_en.md

The same script with no annotation added — the literal text pasted into the terminal in both videos, visible in each recording's paste-confirmation dialog.