Trivial count() optimization is applied to Iceberg tables with live delete files and trusts summary statistics over table data

[Selfeer]

Describe the situation

The trivial count() optimization is now applied to Iceberg v3 tables that have live position-delete files (deletion vectors) and equality-delete files. Previously the optimization failed closed in the presence of any live delete entries and count() fell back to a real scan, guaranteeing agreement with SELECT *. Now count() is computed from snapshot-summary statistics (total-records minus total-position-deletes), so whenever those statistics disagree with the actual delete content of the table, count() returns a different number than a scan of the very same snapshot.

Found by

Regression suite tests (rerun from the iceberg suite directory):

python3 regression.py --local --minio-root-user admin --minio-root-password password --clickhouse <build url or path> --only "/iceberg/deletion vectors/count paths/trivial count optimization/fails closed with deletes/*" -l fails_closed.log
python3 regression.py --local --minio-root-user admin --minio-root-password password --clickhouse <build url or path> --only "/iceberg/deletion vectors/coexistence/equality deletes/*" -l equality.log
python3 regression.py --local --minio-root-user admin --minio-root-password password --clickhouse <build url or path> --only "/iceberg/deletion vectors/vector shapes/all rows deleted/*" -l all_deleted.log
python3 regression.py --local --minio-root-user admin --minio-root-password password --clickhouse <build url or path> --only "/iceberg/deletion vectors/vector shapes/row group boundaries/*" -l boundaries.log

---

How to reproduce the behavior

Environment

• Version: 26.6.2.20000.altinityantalya
• Writer: Spark with Iceberg (e.g. tabulario/spark-iceberg), REST catalog, MinIO storage

Steps

1. In Spark, create an Iceberg v3 merge-on-read table with 100 rows and delete 10 of them, producing a live deletion vector:

CREATE TABLE demo.db.tbl (id BIGINT, data STRING)
USING iceberg
TBLPROPERTIES (
    'format-version' = '3',
    'write.delete.mode' = 'merge-on-read',
    'write.update.mode' = 'merge-on-read',
    'write.merge.mode' = 'merge-on-read'
);

INSERT INTO demo.db.tbl
SELECT id, concat('row-', CAST(id AS STRING)) FROM range(100);

DELETE FROM demo.db.tbl WHERE id % 10 = 0;

2. In ClickHouse, run count() with a log comment and check whether the trivial count optimization was applied:

SELECT count()
FROM icebergS3('http://minio:9000/warehouse/db/tbl', '<key>', '<secret>')
SETTINGS optimize_trivial_count_query = 1, log_comment = 'dv_trivial_count';

SYSTEM FLUSH LOGS;

SELECT ProfileEvents['IcebergTrivialCountOptimizationApplied']
FROM system.query_log
WHERE log_comment = 'dv_trivial_count' AND type = 'QueryFinish';

3. To see the wrong-results consequence, make the summary statistics disagree with the actual delete content. Any of these realistic paths works:

   • add an equality-delete file to the table with a writer that does not update the optional total-equality-deletes summary field (the field stays 0 from the earlier Spark commit), then compare count() with a scan; or
   • modify the current metadata JSON so total-position-deletes in the snapshot summary differs from the deletion vector's real cardinality (equivalently: any historical commit with inconsistent totals poisons all later snapshots, because the totals are maintained incrementally), then:

SYSTEM DROP ICEBERG METADATA CACHE;

SELECT count()
FROM icebergS3('http://minio:9000/warehouse/db/tbl', '<key>', '<secret>')
SETTINGS use_iceberg_metadata_files_cache = 0;

SELECT count()
FROM (SELECT * FROM icebergS3('http://minio:9000/warehouse/db/tbl', '<key>', '<secret>'))
SETTINGS use_iceberg_metadata_files_cache = 0;

---

Expected behavior

With any live delete files in the current snapshot, the trivial count optimization fails closed:

• IcebergTrivialCountOptimizationApplied is 0;
• count() is produced by the scan path and always equals SELECT count() FROM (SELECT * ...), regardless of what the optional snapshot-summary statistics claim.

This was the behavior of the previous build.

---

Actual behavior

The optimization is applied with a live deletion vector:

SELECT ProfileEvents['IcebergTrivialCountOptimizationApplied'] ...

┌─ProfileEvents…─┐
│              1 │
└────────────────┘

and count() follows the summary arithmetic instead of the data. Observed divergences on this build (same table, same snapshot, scan result shown by SELECT *):

Scenario	count()	actual rows in scan
live equality-delete file, summary total-equality-deletes = 0	90	89
deletion vector cardinality differs from summary total-position-deletes	140	50
same, small vector	9999	9992

In every case SELECT * returns the correct rows and count() returns total-records − total-position-deletes taken from the snapshot summary.

---

Question

Is applying the trivial count optimization with live delete files intentional? The summary fields are optional, writer-derived, incrementally-maintained statistics — the Iceberg spec does not require them to be consistent with the manifests, so any subtraction based on them can silently disagree with a scan. If the fast path for append-only tables is the goal, gating it on total-position-deletes being present and zero (in addition to the existing total-equality-deletes = 0 check) would keep tables with any live deletes on the fail-closed scan path.

This arose after changes in: #2183

[ianton-ru]

Must be fixed in #2183