← Documentation / SQL Reference

DOI 1.5 — Viewer systems and a corrected collapse-type classification

View DOI 1.5 on Zenodo: https://doi.org/10.5281/zenodo.19040901

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.

DOI1_5_Silent_sql.txt

Classifies near-zero-Δssim blocks into Silent/Near-Silent (by how close Δssim is to exactly zero) crossed with None/Single/Multiple_or_Dual (by how many comp_* features dominate the change).

This file has two queries: an original classification, and the corrected version this report documents.

WITH comps AS (
    SELECT
        ABS(delta_ssim) AS adelta,
        GREATEST(
            ABS(comp_sharpness_processed_block), ABS(comp_edges_processed_block),
            ABS(comp_artifact_ringing_processed_block), ABS(comp_artifact_block_processed_block),
            ABS(comp_contrast_processed_block), ABS(comp_texture_processed_block),
            ABS(comp_entropy_processed_block), ABS(comp_noise_processed_block)
        ) AS max_comp,
        ( ABS(comp_sharpness_processed_block) + ABS(comp_edges_processed_block)
        + ABS(comp_artifact_ringing_processed_block) + ABS(comp_artifact_block_processed_block)
        + ABS(comp_contrast_processed_block) + ABS(comp_texture_processed_block)
        + ABS(comp_entropy_processed_block) + ABS(comp_noise_processed_block)
        ) AS total
    FROM blocks
    WHERE ABS(delta_ssim) < 0.001
),
classified AS (
    SELECT
        CASE WHEN adelta < 1e-12 THEN 'Silent' ELSE 'Near-Silent' END AS layer1,
        CASE
            WHEN max_comp < 0.01 THEN 'None'
            WHEN total = 0 THEN 'None'
            WHEN (max_comp / total) > 0.5 THEN 'Single'
            ELSE 'Multiple_or_Dual'
        END AS layer2
    FROM comps
)
SELECT layer1, layer2, COUNT(*) AS cnt
FROM classified
GROUP BY layer1, layer2
ORDER BY layer1, layer2;

Restricts to blocks whose overall score barely moved (|Δssim| < 0.001) — candidates for a "silent" collapse: no visible score movement, but possibly real internal change. GREATEST(...) finds the single largest |comp_*| value among the 8 features for that block (max_comp); the parenthesized sum adds all 8 together (total). classified then applies two independent labels: layer1 splits by whether Δssim is truly, exactly 0 (adelta < 1e-12, essentially floating-point zero) versus just very small ("Silent" vs. "Near-Silent"); layer2 classifies how concentrated the internal comp_* change is — "None" if nothing moved meaningfully, "Single" if one feature accounts for over half the total movement, otherwise "Multiple_or_Dual" as a catch-all. The final query counts how many blocks fall into each layer1×layer2 combination.

WITH comps AS (
  SELECT
      ABS(delta_ssim) AS adelta,
      GREATEST( ... ) AS max_comp,
      ( ... ) AS total,
      ARRAY_SORT(ARRAY[
          ABS(comp_sharpness_processed_block), ABS(comp_edges_processed_block),
          ABS(comp_artifact_ringing_processed_block), ABS(comp_artifact_block_processed_block),
          ABS(comp_contrast_processed_block), ABS(comp_texture_processed_block),
          ABS(comp_entropy_processed_block), ABS(comp_noise_processed_block)
      ])[-1]
      + ARRAY_SORT(ARRAY[ ... same 8 values ... ])[-2] AS top2_sum
  FROM blocks
  WHERE ABS(delta_ssim) < 0.001
),
classified AS (
  SELECT
      CASE WHEN adelta < 1e-12 THEN 'Silent' ELSE 'Near-Silent' END AS layer1,
      CASE
          WHEN max_comp < 0.01 THEN 'None'
          WHEN total = 0 THEN 'None'
          WHEN (max_comp / total) > 0.5 THEN 'Single'
          WHEN (top2_sum / total) > 0.7 THEN 'Dual'
          ELSE 'Multiple'
      END AS layer2
  FROM comps
)
SELECT layer1, layer2, COUNT(*) AS cnt
FROM classified
GROUP BY layer1, layer2
ORDER BY layer1, layer2;

The corrected classification. ARRAY_SORT(ARRAY[...]) sorts the 8 |comp_*| values from smallest to largest, so [-1] is the largest (the same value GREATEST found above) and [-2] is the second-largest; top2_sum adds those two together. layer2 can now distinguish a genuine "Dual" case — the top two features together account for over 70% of the movement, but neither alone crosses 50% — from a true "Multiple" case (change spread across three or more features), a distinction the original version's "Multiple_or_Dual" bucket couldn't make.