← Documentation / SQL Reference

DOI 1.14 — Four-model verification of the Failure Fingerprint statistics

View DOI 1.14 on Zenodo: https://doi.org/10.5281/zenodo.22012409

New DBBuilt on the current, join-free pipeline (build_notta_tta_comp_db.py), one independent database per model.

One self-contained script, run once per model against that model's own database (only the .open line changes). It extends DOI 1.12's consolidated script from two models to all four, computes both μ_stat and σ_stat directly in SQL with no external Python post-processing, and adds a new Part 5 checking the SSIM=1.0 pixel-identity corollary. Standard and SwinIR query the same databases already verified and evidenced in DOI 1.12/1.12.1, so only the two new models needed a fresh recording for this report.

Parts 1–2: dataset scale and population sizes

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

WITH merutan_pop AS (
    SELECT image_id
    FROM blocks
    WHERE comp_sharpness_notta = 0 AND comp_edges_notta = 0
      AND comp_artifact_ringing_notta = 0 AND comp_artifact_block_notta = 0
      AND comp_contrast_notta = 0 AND comp_texture_notta = 0
      AND comp_entropy_notta = 0 AND comp_noise_notta = 0
      AND (ssim_tta - ssim_notta) != 0
)
SELECT 'merutan_images' AS metric, CAST(COUNT(DISTINCT image_id) AS VARCHAR) AS value FROM merutan_pop
UNION ALL SELECT 'merutan_blocks', CAST(COUNT(*) AS VARCHAR) FROM merutan_pop
UNION ALL
SELECT 'sigma_pop_images', CAST(COUNT(DISTINCT image_id) AS VARCHAR)
FROM blocks WHERE sigma_gt > 0 AND sigma_gt <= 0.16
UNION ALL
SELECT 'sigma_pop_blocks', CAST(COUNT(*) AS VARCHAR)
FROM blocks WHERE sigma_gt > 0 AND sigma_gt <= 0.16;

A sanity check before the real computation: total blocks (the same 81,985,536 for every model, since all four share the same ground-truth grid), plus the sizes of the two populations the rest of the script depends on — the Merutan condition population (varies by model: Standard 599,388; SwinIR 2,948; High-Fidelity 8,987; Digital-Art 982,920) and the continuous-texture window σ_gt≤0.16 (identical across all four models at 79,102,938 blocks, since σ_gt depends only on the shared ground-truth image, not the model).

Part 3: μ_stat, σ_stat, and the deviation coordinate

WITH mu_pop AS (
    SELECT mu_gt, (ssim_tta - ssim_notta) AS delta_ssim
    FROM blocks
    WHERE comp_sharpness_notta = 0 AND comp_edges_notta = 0
      AND comp_artifact_ringing_notta = 0 AND comp_artifact_block_notta = 0
      AND comp_contrast_notta = 0 AND comp_texture_notta = 0
      AND comp_entropy_notta = 0 AND comp_noise_notta = 0
      AND (ssim_tta - ssim_notta) != 0
),
mu_bins AS (
    SELECT ROUND(mu_gt / 0.01) * 0.01 AS mu_bin, STDDEV_POP(ABS(delta_ssim)) AS std_val
    FROM mu_pop GROUP BY ROUND(mu_gt / 0.01) * 0.01 HAVING COUNT(*) > 20
),
mu_ordered AS (
    SELECT mu_bin, std_val, ROW_NUMBER() OVER (ORDER BY mu_bin) AS rn, COUNT(*) OVER () AS n
    FROM mu_bins
),
mu_split AS (
    SELECT k.mu_bin AS mu_stat
    FROM mu_ordered k CROSS JOIN mu_ordered l
    WHERE k.rn BETWEEN 2 AND k.n - 2
    GROUP BY k.rn, k.mu_bin, k.n
    ORDER BY 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)
    LIMIT 1
),
sigma_pop AS (
    SELECT sigma_gt, ssim_s_notta AS ssim_s FROM blocks WHERE sigma_gt > 0 AND sigma_gt <= 0.16
),
sigma_bins AS (
    SELECT ROUND(sigma_gt, 3) AS sigma_bin, AVG(ssim_s) AS avg_s
    FROM sigma_pop GROUP BY ROUND(sigma_gt, 3) HAVING COUNT(*) > 10
),
sigma_ordered AS (
    SELECT sigma_bin, avg_s, ROW_NUMBER() OVER (ORDER BY sigma_bin) AS rn, COUNT(*) OVER () AS n
    FROM sigma_bins
),
sigma_cost AS (
    SELECT k.rn AS k,
        VAR_POP(l.avg_s) FILTER (WHERE l.rn <= k.rn) * k.rn
        + VAR_POP(l.avg_s) FILTER (WHERE l.rn >  k.rn) * (k.n - k.rn) AS cost
    FROM sigma_ordered k CROSS JOIN sigma_ordered l
    WHERE k.rn BETWEEN 1 AND k.n - 2
    GROUP BY k.rn, k.n
),
sigma_split AS (
    SELECT sigma_bin AS sigma_stat
    FROM sigma_ordered, (SELECT k FROM sigma_cost ORDER BY cost ASC LIMIT 1) best
    WHERE rn = best.k + 1
)
SELECT
    PRINTF('%.4f', mu_stat)                       AS mu_stat,
    PRINTF('%.4f', sigma_stat)                    AS sigma_stat,
    PRINTF('%.4f', SQRT(0.0001 / 2.0))            AS mu_c,
    PRINTF('%.4f', SQRT(0.00045))                 AS sigma_c,
    PRINTF('%.4f', mu_stat    - SQRT(0.0001/2.0)) AS delta_mu,
    PRINTF('%.4f', sigma_stat - SQRT(0.00045))    AS delta_sigma
FROM mu_split, sigma_split;

The same Two-Segment Optimal Split pattern used throughout this series, computed for both μ_stat and σ_stat in one pass, entirely inside SQL — DOI 1.12's version needed a short external script for the σ_stat half; this one doesn't. Results: Standard μ_stat=0.0500, σ_stat=0.1010; SwinIR 0.0100 / 0.0800; High-Fidelity 0.0100 / 0.0860; Digital-Art 0.0500 / 0.0750.

Part 4: stabilization constants (supplement)

SELECT
    PRINTF('%.5f', 0.0001)            AS C1,
    PRINTF('%.5f', 0.0009)            AS C2,
    PRINTF('%.5f', 0.00045)           AS C3,
    PRINTF('%.5f', SQRT(0.0001/2.0))  AS mu_c_theory,
    PRINTF('%.5f', SQRT(0.00045))     AS sigma_c_theory;

Just restates the corrected C1/C2/C3 constants (per DOI 1.10) and the theoretical critical points derived from them, for side-by-side comparison against Part 3's empirical values in the same output.

Part 5: the SSIM=1.0 pixel-identity corollary

WITH both_ssim1 AS (
    SELECT
        comp_sharpness_gt, comp_sharpness_notta, comp_sharpness_tta,
        comp_edges_gt, comp_edges_notta, comp_edges_tta,
        comp_artifact_ringing_gt, comp_artifact_ringing_notta, comp_artifact_ringing_tta,
        comp_artifact_block_gt, comp_artifact_block_notta, comp_artifact_block_tta,
        comp_contrast_gt, comp_contrast_notta, comp_contrast_tta,
        comp_texture_gt, comp_texture_notta, comp_texture_tta,
        comp_entropy_gt, comp_entropy_notta, comp_entropy_tta,
        comp_noise_gt, comp_noise_notta, comp_noise_tta
    FROM blocks
    WHERE (ssim_l_notta * ssim_c_notta * ssim_s_notta) = 1.0
      AND (ssim_l_tta   * ssim_c_tta   * ssim_s_tta)   = 1.0
)
SELECT 'n_both_ssim_eq_1' AS metric, CAST(COUNT(*) AS VARCHAR) AS value FROM both_ssim1
UNION ALL SELECT 'sharpness_gt_notta_exact_match_pct',
    CAST(100.0 * AVG(CASE WHEN comp_sharpness_gt = comp_sharpness_notta THEN 1 ELSE 0 END) AS VARCHAR) FROM both_ssim1
-- ...the same exact-match-percentage pair (vs. notta, vs. tta) repeated
-- for the other seven comp_* features (edges, ringing, artifact_block,
-- contrast, texture, entropy, noise).
;

Tests a corollary of the Pixel Identity Theorem: SSIM equals exactly 1.0 if and only if its three factors L, C, S are each individually 1 (every factor is ≤1, so a product of 1 forces all three to be 1). On every block where both the noTTA and TTA reconstructions hit SSIM=1.0 against the same ground truth, all eight comp_* features must therefore match exactly across GT, noTTA, and TTA — no is_comp_zero filter needed, this schema doesn't have that column. Result: n_both_ssim_eq_1 is 702,108 (Standard), 2,657,100 (SwinIR), 15 (High-Fidelity), 1,233,316 (Digital-Art) — and every single exact-match-percentage row comes back 100.0 for all four models, zero exceptions.