[BUG] Data skipping order-preserving whitelist: unix_timestamp parity gap and format-monotonicity holes in live arms
- Dominant language
- Java
- Stars
- 6.2k
- Forks
- 2.5k
- Avg merge
- 2d 8h
- Merged PRs (30d)
- 111
Description
**Describe the problem you faced**
Follow-up umbrella from the #19474 review; related to #19445 (order-breaking casts).
1. **unix_timestamp parity gap (missed pruning).** The whitelist matches `ToUnixTimestamp` but not `UnixTimestamp`, so `to_unix_timestamp(B, fmt) > x` prunes while the far more common spelling `unix_timestamp(B, fmt) > x` does not. `ExpressionIndexSupport` handles `unix_timestamp`, so the two matchers disagree. Both classes extend `UnixTime`; a `case ut: UnixTime => unapply(ut.left)` arm restores parity -- but it must carry the same soundness gates as the `GetTimestamp` arm from #19474 (literal, order-preserving format; string parsing is partial and format-sensitive).
2. **Format-monotonicity holes in live arms (wrong results, pre-existing).** Several arms re-apply a format-parameterized function to min/max stats and accept ANY format, but the translation `f(min) <= B AND B <= f(max)` is only sound when f is monotonic:
- `DateFormatClass` (timestamp -> string): `date_format(ts, 'MM/dd/yyyy') = '03/06/2022'` plants non-monotone string output over timestamp stats -- can prune files containing matching rows on master today.
- `FromUnixTime` (long -> string): same class of problem.
- `ToUnixTimestamp` (string -> long with format): same problem in the other direction, plus null-on-unparseable partiality.
- Plain `Cast(StringType -> Timestamp/Date)`: `isCastPreservingOrdering` returns true, but lexicographic order only matches chronological order for canonical fixed-width strings; sloppy values like `2022-3-6` break it, and unparseable values null out the bound.
**Suggested fix**
Reuse the two mechanisms introduced in #19474: the fixed-width year-first format allowlist (gate `DateFormatClass`/`FromUnixTime`/`ToUnixTimestamp`/`UnixTime` arms on it) and the null-tolerant `Coalesce(bound, true)` wrapping of transformed bounds (already generic). For the String->Timestamp/Date cast, either drop it from `isCastPreservingOrdering` or keep it with the null-tolerant bound documented as best-effort. Overlaps the #19445 theme (reject non-order-preserving shapes); consider folding into that work.
**Environment**: master (found during review of #19474; DateFormatClass/FromUnixTime wrong-results cases reasoned from the translation semantics, UnixTimestamp gap verified with a live optimizer probe).
Contributor guide
No contributing guide indexed for this repository
Research direction
Start in the ExpressionIndexSupport whitelist matcher and compare the UnixTimestamp and ToUnixTimestamp arms with the GetTimestamp changes from #19474. Review the DateFormatClass, FromUnixTime, ToUnixTimestamp, UnixTime, and String-to-Timestamp/Date cast paths for the stated monotonicity and null-handling conditions. Done means equivalent timestamp spellings have sound, gated pruning and unsafe format or cast cases no longer produce wrong results.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, spark
- Domain
- data-engineering, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100