← Documentation / SQL Reference

DOI 1.10 — Correction of the C1/C2/C3 constant bug

View DOI 1.10 on Zenodo: https://doi.org/10.5281/zenodo.21658621

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_sql_final_en.md

The consolidated final verification script, already incorporating the corrected C1/C2/C3 constants and the σ_gt≤0.16 restriction from the start. Run once per model, four screen recordings total.

.open --readonly alignment_axis_images.duckdb

-- 1. Dataset scale
SELECT COUNT(DISTINCT image_id) AS total_images, COUNT(*) AS total_blocks FROM blocks;

-- 2. Merutan Condition population (luminance-axis source)
SELECT COUNT(DISTINCT image_id) AS merutan_images, COUNT(*) AS merutan_blocks
FROM blocks WHERE is_comp_zero = TRUE AND delta_ssim != 0;

-- 3. Non-degenerate block population (texture-axis source,
--    restricted to the continuous-texture regime sigma_gt<=0.16)
SELECT COUNT(DISTINCT image_id) AS texture_images, COUNT(*) AS texture_blocks
FROM blocks
WHERE is_comp_zero = FALSE AND sigma_gt > 0 AND sigma_gt <= 0.16;

Parts 1–3 are counts, same shape as DOI 1.9's first two steps, plus a new third population: blocks whose reconstruction isn't degenerate (is_comp_zero = FALSE) and whose ground truth isn't perfectly flat either (sigma_gt > 0), restricted to σ_gt≤0.16 — the range this report treats as the "continuous-texture regime" for σ_stat.

-- 4. Failure Fingerprint: mu_stat, sigma_stat, and the deviation
--    coordinate (Delta_mu, Delta_sigma), against the corrected
--    C1/C2/C3 and the sigma_gt<=0.16 restriction.
WITH mu_bins AS ( ... GROUP BY ROUND(mu_gt / 0.01) * 0.01 ... ),
mu_ordered AS ( ... ROW_NUMBER() OVER (ORDER BY mu_bin) ... ),
mu_split AS ( ... Two-Segment Optimal Split, same as DOI 1.9 Step 4 ... ),
sigma_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 AND sigma_gt <= 0.16
                GROUP BY ROUND(sigma_gt, 3) HAVING COUNT(*) > 10 ),
sigma_ordered AS ( ... ),
sigma_cost AS ( ... ),
sigma_split AS ( ... Two-Segment Optimal Split, same as DOI 1.9 Step 5 ... )
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;

-- 5. Supplement: SSIM stabilization constants and the theoretical
--    critical points derived from them.
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;

Part 4 is exactly the Two-Segment Optimal Split pattern from How to read SQL, run twice in one query (once for μ_gt bins, once for σ_gt bins — the same two CTE chains seen in DOI 1.9's Steps 4/5, just with the corrected C1=0.0001, C2=0.0009, C3=0.00045 baked into the critical-point formulas SQRT(0.0001/2.0) and SQRT(0.00045) this time, and the σ side additionally restricted to σ_gt≤0.16). Subtracting each stat value from its critical point gives the Failure Fingerprint coordinate (Δμ, Δσ) directly. Part 5 just prints the raw constants for reference.

sigma016_verification_sql.md

The query used to export the four models' σ_gt≤0.16 evidentiary CSVs (Section 6.4 of the report).

.open --readonly alignment_axis_images.duckdb

COPY (
    SELECT
        ROUND(sigma_gt, 3)    AS sigma_bin,
        COUNT(*)              AS block_count,
        ROUND(AVG(ssim_s), 4) AS avg_s
    FROM blocks
    WHERE is_comp_zero = FALSE AND sigma_gt > 0 AND sigma_gt <= 0.16
    GROUP BY ROUND(sigma_gt, 3)
    HAVING COUNT(*) > 10
    ORDER BY sigma_bin
) TO 'sigma016_check_<model>.csv' (HEADER, DELIMITER ',');

COPY (a query) TO 'file.csv' (HEADER, DELIMITER ',') runs the query in parentheses and writes its result straight to a CSV file instead of printing it to the terminal — the same binned σ_gt≤0.16 population as Part 4's σ side above, exported once per model as the raw evidence file backing the report's figures for that section.