Time-range predicates on messages.timestamp silently drop rows from older files (stats pushdown pruning)

[tenequm]

Summary

Comparing the raw timestamp column against a literal in pond_sql_query /
SQL silently under-returns: rows ingested before roughly 2026-06 are pruned
out even though they match the range. No error is raised - the result just
looks smaller than reality. Wrapping the column so pushdown can't run returns
the correct rows.

Repro (against a store with pre-2026-06 history)

-- 1. Raw string literal: WRONG (166 rows)
SELECT COUNT(*) FROM messages
WHERE timestamp >= '2026-06-16' AND timestamp < '2026-06-20';

-- 2. Typed literal: identically WRONG (166 rows)
SELECT COUNT(*) FROM messages
WHERE timestamp >= TIMESTAMP '2026-06-16 00:00:00'
  AND timestamp <  TIMESTAMP '2026-06-20 00:00:00';

-- 3. Column wrapped, pushdown neutralized: CORRECT (117,827 rows)
SELECT COUNT(*) FROM messages
WHERE timestamp + INTERVAL '0 seconds' >= TIMESTAMP '2026-06-16 00:00:00'
  AND timestamp + INTERVAL '0 seconds' <  TIMESTAMP '2026-06-20 00:00:00';

-- 4. Day-granularity wrap also CORRECT (117,827 rows)
SELECT COUNT(*) FROM messages
WHERE CAST(timestamp AS DATE) >= '2026-06-16'
  AND CAST(timestamp AS DATE) <= '2026-06-19';

Wider month-scale check, same pattern: raw predicate over June 2026 returned
28,565 rows where the wrapped form returns 413,836.

Diagnostics

• arrow_typeof(timestamp) is uniform across old and new rows:
  Timestamp(µs, "UTC") - the logical schema is consistent.
• Returned timestamp VALUES from old rows are correct once they survive
  pruning (they print as proper ISO instants), so the data itself is intact.
• Recent data is unaffected: over a July 2026 range, raw and wrapped
  predicates return identical counts.
• The matching/non-matching split is not a clean date boundary in the data -
  it follows ingestion batches (some mid-June rows match, most don't), which
  points at per-file storage differences rather than the row values.

Hypothesis

Files written by an older writer version carry parquet column
statistics/physical encoding for timestamp that the DataFusion pruner
misinterprets, so stats-based row-group/file pruning skips them entirely.
Anything that blocks pushdown (CAST, arithmetic on the column) bypasses the
bad stats and reads the files fine.

Impact

Every time-scoped query over older history silently under-returns - counts,
windows, gap analyses all come back plausible-but-wrong with no signal that
files were skipped. This one cost a downstream analysis several days of
"missing" history before it was caught.

Workarounds (verified)

• CAST(timestamp AS DATE) for day-granularity filters
• timestamp + INTERVAL '0 seconds' >= TIMESTAMP '...' for sub-day filters

Possible fixes

1. Rewrite/compact pre-boundary files with the current writer (normalizes
   stats) - also fixes the files at rest.
2. Until then, disable stats-based pruning on timestamp in the DataFusion
   session config, or validate file stats at open and fall back to scan.