← Documentation / SQL Reference

DOI 1.21 — A higher-precision pipeline for measuring σ_gt/μ_gt's distinct-value count

View DOI 1.21 on Zenodo: https://doi.org/10.5281/zenodo.23271571

New DBBuilt on a newly constructed database per model (*_notta_tta_sigma.duckdb, storing raw σ_notta/μ_notta), alongside the existing *_notta_tta_comp.duckdb files from Technical Report v1.12.

σ_gt/μ_gt take only finitely many distinct values because of the GT image's 8-bit quantization — that property itself is correct, and was already established in DOI 1.16. However, the specific figures reported in Technical Reports v1.16/v1.18/v1.19 (approximately 2.3 million distinct values for σ_gt, 68,649 for μ_gt) turn out to have been inflated by about 1.95× and 4.2× respectively, due to a separate issue unrelated to quantization: floating-point addition's non-associativity producing “ghost duplication” — the same true value recorded as two slightly different bit patterns, depending on the exact order floating-point operations happened to execute in. This page walks through three of the report's verification queries; the rest are listed, without a full walkthrough, in the SQL reference.

Part 1: what ghost duplication looks like — ghost_duplication_concrete_example_4model.sql

Two unrelated blocks in one image, 00000001_002 (at row=3/col=107 and row=8/col=29), share the exact same true σ_gt — about 0.0004863513439456968. If the database stored that value identically both times, COUNT(DISTINCT sigma_gt) would count them as one value. This query checks whether it actually does, for all four independently-recomputed model databases:

.open --readonly output_std/std_notta_tta_sigma.duckdb
SELECT 'Standard' AS model, image_id, row, col, sigma_gt
FROM blocks
WHERE image_id = '00000001_002'
  AND ((row = 3 AND col = 107) OR (row = 8 AND col = 29));

WITH pair AS (
    SELECT sigma_gt FROM blocks
    WHERE image_id = '00000001_002'
      AND ((row = 3 AND col = 107) OR (row = 8 AND col = 29))
)
SELECT 'Standard' AS model, MAX(sigma_gt) - MIN(sigma_gt) AS gap_between_the_two_blocks FROM pair;

-- Repeats identically for SwinIR, High-Fidelity, and Digital-Art,
-- each preceded by its own .open line.

Both blocks print the same σ_gt to the precision normally displayed — but the exact subtraction shows they are not quite equal: the gap is 5.421010862427522e-20, exactly 2^-64, a clean IEEE 754 machine constant. This is the signature of floating-point “ghost duplication”, not a real difference in content. The gap is identical, byte-for-byte, across all four models.

Part 2: the corrected count — DOI1.21_verify_against_published_comp_db_4model.sql

This query re-runs against the exact same database and population Technical Reports v1.16/v1.18/v1.19 used at publication time, lining up the originally published COUNT(DISTINCT) figures alongside a tolerance-based adjacent-gap recount (the “LAG method”): sort the distinct values, measure the gap to each value's predecessor, and treat any gap under 1e-9 as a ghost duplicate rather than a genuinely different value.

.open --readonly output_std/std_notta_tta_comp.duckdb

WITH sigma_distinct AS (
    SELECT DISTINCT sigma_gt AS v FROM blocks WHERE sigma_gt > 1e-6
),
sigma_gaps AS (
    SELECT v - LAG(v) OVER (ORDER BY v) AS gap_to_prev FROM sigma_distinct
),
sigma_summary AS (
    SELECT
        COUNT(*) AS n_naive_distinct,
        SUM(CASE WHEN gap_to_prev < 1e-9 THEN 1 ELSE 0 END) AS n_ghost_gaps
    FROM sigma_gaps
),
mu_distinct AS (
    SELECT DISTINCT mu_gt AS v FROM blocks WHERE mu_gt > 1e-6
),
mu_gaps AS (
    SELECT v - LAG(v) OVER (ORDER BY v) AS gap_to_prev FROM mu_distinct
),
mu_summary AS (
    SELECT
        COUNT(*) AS n_naive_distinct,
        SUM(CASE WHEN gap_to_prev < 1e-9 THEN 1 ELSE 0 END) AS n_ghost_gaps
    FROM mu_gaps
)
SELECT 'Standard' AS model, 'sigma_gt' AS variable,
    n_naive_distinct AS naive_distinct_count_as_published,
    n_ghost_gaps AS n_ghost_duplicates,
    (n_naive_distinct - n_ghost_gaps) AS true_distinct_count_corrected,
    ROUND(n_naive_distinct * 1.0 / (n_naive_distinct - n_ghost_gaps), 4) AS inflation_ratio
FROM sigma_summary
UNION ALL
SELECT 'Standard', 'mu_gt', n_naive_distinct, n_ghost_gaps,
    (n_naive_distinct - n_ghost_gaps),
    ROUND(n_naive_distinct * 1.0 / (n_naive_distinct - n_ghost_gaps), 4)
FROM mu_summary;

-- Repeats identically for SwinIR, High-Fidelity, and Digital-Art,
-- each preceded by its own .open line.

σ_gt: published 2,308,415 distinct values — 1,123,775 of those turn out to be ghost duplicates — true count 1,184,640 (the published figure is 1.9486× too high). μ_gt: published 68,649 — 52,329 ghost duplicates — true count 16,320 (4.2064× too high). All eight figures agree byte-for-byte across all four models.

Part 3: why 1e-9 — DOI1.21_section8_noise_signal_gap_4model.sql

The 1e-9 threshold used above isn't arbitrary. This query measures the largest gap ever seen between two values that really are the same (the “noise ceiling”) against the smallest gap ever seen between two values that really are different (the “signal minimum gap”) — if there's a completely empty band between them, any threshold inside that band gives the identical, correct answer.

.open --readonly output_std/std_notta_tta_sigma.duckdb

SELECT 'Standard' AS model, COUNT(*) AS total_blocks_in_db FROM blocks;

WITH sigma_distinct AS (
    SELECT DISTINCT sigma_gt AS v FROM blocks WHERE sigma_gt > 1e-6 AND sigma_notta > 1e-6
),
sigma_gaps AS (
    SELECT v - LAG(v) OVER (ORDER BY v) AS gap_to_prev FROM sigma_distinct
),
mu_distinct AS (
    SELECT DISTINCT mu_gt AS v FROM blocks WHERE mu_gt > 1e-6 AND mu_notta > 1e-6
),
mu_gaps AS (
    SELECT v - LAG(v) OVER (ORDER BY v) AS gap_to_prev FROM mu_distinct
)
SELECT 'Standard' AS model,
    'sigma_gt: noise_ceiling' AS metric, CAST(MAX(gap_to_prev) AS VARCHAR) AS value
FROM sigma_gaps WHERE gap_to_prev < 1e-9
UNION ALL
SELECT 'Standard', 'sigma_gt: signal_floor', CAST(MIN(gap_to_prev) AS VARCHAR)
FROM sigma_gaps WHERE gap_to_prev >= 1e-9
UNION ALL
SELECT 'Standard', 'mu_gt: noise_ceiling', CAST(MAX(gap_to_prev) AS VARCHAR)
FROM mu_gaps WHERE gap_to_prev < 1e-9
UNION ALL
SELECT 'Standard', 'mu_gt: signal_floor', CAST(MIN(gap_to_prev) AS VARCHAR)
FROM mu_gaps WHERE gap_to_prev >= 1e-9;

-- Repeats identically for SwinIR, High-Fidelity, and Digital-Art,
-- each preceded by its own .open line.

σ_gt: noise ceiling 1.11e-16, signal minimum gap 4.084696e-9 — identical across all four models, an empty band spanning roughly seven orders of magnitude. μ_gt: noise ceiling 2.22e-16, signal minimum gap 6.127451e-5 — also identical, with zero ambiguous cases (gaps that could plausibly be either) in every model.

Results

The qualitative conclusions this corrects the precision of remain unchanged, or are strengthened: σ_gt/μ_gt are still strongly discretized by 8-bit quantization (now σ_gt≈1.18 million true values, μ_gt≈16,320), μ_gt is still more sharply quantized than σ_gt (the ratio actually widens, from about 33.6× to about 72.6×), and both quantities still depend only on the GT image, confirmed in its strongest form — byte-for-byte identical across all four models even in these corrected figures. Only the absolute distinct-value counts themselves needed correcting.