← Documentation / SQL Reference

DOI 1.12 — sigma_stat correction

View DOI 1.12 on Zenodo: https://doi.org/10.5281/zenodo.21894585

New DBBuilt on the current, join-free pipeline (build_notta_tta_comp_db.py), used from DOI 1.12 onward.

These four queries re-derive μ_stat and σ_stat for two models (Standard and SwinIR) from scratch, against a newer database built directly from the images with no JOIN step and therefore no is_comp_zero flag to go wrong. Each query changes exactly one thing relative to how these statistics were originally computed — the unreliable flag is gone, replaced with the equivalent condition reconstructed directly from real comp_*_notta values — so any difference in the result isolates that one variable.

standard_mu_stat_exact_match_recheck.sql

Re-derives Standard's μ_stat from scratch against the from-scratch, no-join database, confirming it independently of the retired is_comp_zero flag.

WITH pop0 AS (
    SELECT image_id, mu_gt
    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 pop0
UNION ALL SELECT 'merutan_blocks', CAST(COUNT(*) AS VARCHAR) FROM pop0;

This first part is a sanity check, not the re-derivation itself: it counts how many images and blocks belong to the Merutan condition population — blocks whose reconstruction is flat by every one of the eight comp_* features, yet whose score still moved between noTTA and TTA. Instead of trusting a stored flag, the condition is spelled out directly: all eight comp_*_notta columns equal to exactly 0, and ssim_tta - ssim_notta not equal to 0.

WITH 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
),
bins AS (
    SELECT
        ROUND(mu_gt / 0.01) * 0.01  AS mu_bin,
        STDDEV_POP(ABS(delta_ssim)) AS std_val
    FROM pop
    GROUP BY ROUND(mu_gt / 0.01) * 0.01
    HAVING COUNT(*) > 20
),
ordered AS (
    SELECT
        mu_bin,
        std_val,
        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;

This is the actual re-derivation: the same Two-Segment Optimal Split pattern seen already in DOI 1.9, applied to the Merutan condition population from the query above. It re-bins μ_gt into 0.01-wide buckets, measures the spread of |Δssim| in each bucket, tries every possible split point, and returns the boundary that best divides the curve into two flat segments — that boundary is μ_stat. Running this against the from-scratch database reproduces the same value as the original, flag-based computation: this statistic was unaffected by the bug.

standard_sigma_stat_exact_match_recheck.sql

Re-derives Standard's σ_stat the same way — the query that found 0.101 instead of the originally published 0.0800.

COPY (
    SELECT
        ROUND(sigma_gt, 3) AS sigma_bin,
        COUNT(*) AS n,
        AVG(ssim_s_notta) AS avg_s,
        STDDEV_POP(ssim_s_notta) AS std_s
    FROM blocks
    WHERE sigma_gt > 0
      AND sigma_gt <= 0.16
    GROUP BY ROUND(sigma_gt, 3)
    HAVING COUNT(*) > 10
    ORDER BY sigma_bin
) TO 'standard_sigma_stat_exact_match_recheck.csv' (HEADER, DELIMITER ',');

Unlike the μ_stat query above, this one doesn't compute the Two-Segment Split inside SQL — it bins σ_gt into 0.001-wide buckets (restricted to the continuous-texture window σ_gt≤0.16), computes each bucket's mean and population standard deviation of the structure term S, and writes the result straight to a CSV with COPY (...) TO 'file.csv' (HEADER, DELIMITER ','). A short external script then runs the same Two-Segment Split logic over that CSV to find σ_stat. That split is what changed once the is_comp_zero=FALSE filter — present in the original computation, absent here because this database has no such flag to filter on — was removed: 0.101 instead of the originally published 0.0800, isolating that filter as the exact cause.

swinir_mu_stat_exact_match_recheck.sql

The same μ_stat re-derivation for SwinIR, confirming its value unaffected.

-- identical query text to standard_mu_stat_exact_match_recheck.sql
-- above, run against SwinIR's own database instead of Standard's.

Same query, same logic, same Two-Segment Split — only the database changes. SwinIR's μ_stat was already unaffected by the join-miss bug, and this from-scratch re-derivation confirms that independently: same value either way.

swinir_sigma_stat_exact_match_recheck.sql

The same σ_stat re-derivation for SwinIR, confirming its value unaffected.

-- identical query text to standard_sigma_stat_exact_match_recheck.sql
-- above, run against SwinIR's own database instead of Standard's.

Where Standard's σ_stat moved once the old filter was removed, SwinIR's did not: the from-scratch value matches the originally published one, meaning the join-miss bug happened to leave this particular model's σ_stat untouched even though the underlying flag it depended on was unreliable for both models.