Skip to content

Fix scan filters at the edges of the date and float ranges - #4406

Open
Anurag-S1ngh wants to merge 4 commits into
apache:mainfrom
Anurag-S1ngh:fix/4404-date-inf-scan-edges
Open

Anurag-S1ngh wants to merge 4 commits into
apache:mainfrom
Anurag-S1ngh:fix/4404-date-inf-scan-edges

Conversation

@Anurag-S1ngh

@Anurag-S1ngh Anurag-S1ngh commented Nov 17, 2026 •

Copy link
Copy Markdown
Contributor

Summary

Filtered scans raised or returned wrong rows at two kinds of edge values.

  • Year and month partitions raised OverflowError when a filter projected past 9999-12-31 or before 0001-01-01, because the transform converted the day count to a datetime.date. 9999-12-31 is a common "no end date" value.
  • EqualTo, In, NotEqualTo, and the range comparisons (<, <=, >, >=) on inf returned wrong rows for float columns. A Python float becomes a DoubleLiteral, and converting that to a float column turned inf into FloatAboveMax or FloatBelowMin, which bound the predicate to AlwaysFalse or AlwaysTrue.

Changes

  • Compute years and months from a day count with integer arithmetic, so a projection just past the last representable date yields year 10000 and matches no partition instead of raising. Results inside the representable range are unchanged.
  • Convert inf and -inf to a FloatLiteral for float columns, like any other float that fits. For float columns this also changes how the range comparisons bind: < and <= with inf no longer match every row, and > and >= with -inf no longer match every row. For example, table.delete(LessThan("x", math.inf)) used to delete every row, including the inf, NaN and null rows, and now leaves exactly those rows.

Notes

  • Iceberg-Java applies the same range check in DoubleLiteral.to(FLOAT), so Java binds float_col = <double infinity> to alwaysFalse. This change intentionally diverges from that.
  • StringLiteral.to(FloatType) still turns values such as "Infinity" into FloatAboveMax, so EqualTo("x", "Infinity") returns no rows. That is the same bug through the string path and is left for a follow-up.

Testing

  • Added unit tests for the day and microsecond conversions at and beyond the date range, the year projection at both date limits, and the infinity literal conversion.
  • Added scan and delete tests for inf, In and the range comparisons on a float column, and a year-partitioned table scanned at 9999-12-31.
  • tests/utils/test_datetime.py, tests/expressions/test_literals.py, tests/expressions/test_projection.py and the non-cloud tests in tests/io/test_pyarrow.py pass (493 passed, 1 skipped).

Closes #4404

@rahulsmahadev rahulsmahadev left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Thanks! I ran this locally against main (33b4ce5):

  • The new tests fail on main and pass here (the parametrized cases inside the date range pass on both, as expected).
  • _civil_from_days matches datetime.date for every day from 0001-01-01 to 9999-12-31, so results in that range are unchanged.
  • The float change also fixes deletes and range filters with an infinity. On main, x < inf binds to AlwaysTrue, so table.delete(LessThan("x", math.inf)) on a float column deletes every row, including the inf, NaN and null rows; here it keeps exactly those three. <, <=, > and >= with an infinity all change this way, so they are worth listing in the description, with a test.

The one bug I found is in the negative-era branch of _civil_from_days (inline comment). A few more points:

  • Java's DoubleLiteral.to(FLOAT) in Literals.java has the same range check, so Java binds float_col = <double infinity> to alwaysFalse as well. Worth saying in the description that this differs from Java.
  • StringLiteral.to(FloatType) applies the same check, so EqualTo("x", "Infinity") still returns no rows and In("x", ["Infinity", "1.5"]) only 1.5. Fine as a follow-up, but it is the same bug.
  • Nit: 2932897 * day_nanos in test_years_and_months_beyond_the_date_range is beyond int64, so no timestamp_ns can hold it (they end in 2262); that assert pins an unreachable case.
  • This conflicts with #4407 (and #4401, #4402) in tests/io/test_pyarrow.py, since they all add tests right after test_append_to_table_bucketed_by_time and edit the same import block.

Comment thread pyiceberg/utils/datetime.py Outdated
https://howardhinnant.github.io/date_algorithms.html#civil_from_days.
"""
z = days + 719468
era = (z if z >= 0 else z - 146096) // 146097

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This is the C++ trick for floor division, but Python's // already floors, so for z < 0 (days before 0000-03-01) era comes out one too small. About 3% of those days get the wrong month, and some the wrong year. For example, -0001-12-31 (days=-719529) gives year -1970 and month -23640 instead of -1971 and -23641, while the writer puts that date in year partition -1971. So EqualTo("d", -719529) on a year-partitioned table projects to d_year == -1970 and prunes the file that holds the row; main raises OverflowError there instead.

Suggested change
era = (z if z >= 0 else z - 146096) // 146097
era = z // 146097

With this change the function matches datetime on the whole representable range and gives the same values as Java's DateTimeUtil.daysToYears / daysToMonths before year 1, and the tests in this PR still pass. None of the new test days has z < 0; adding (-719529, -1971, -23641) to test_days_to_years_and_months would pin it.

@Anurag-S1ngh Anurag-S1ngh Nov 18, 2026 •

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Fixed in 16903cf: era = z // 146097. Added (-719529, -1971, -23641) to test_days_to_years_and_months, so the z < 0 branch is pinned now.

@Fokko Fokko left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Thanks @Anurag-S1ngh! Two real user-facing bugs fixed here, and the 9999-12-31 "no end date" case is exactly the kind of value that shows up in production data.

I pulled the branch and verified:

  • tests/test_transforms.py, tests/expressions/test_projection.py, tests/utils/test_datetime.py, tests/expressions/test_literals.py all pass
  • Brute-forced _civil_from_days against Python's datetime over 20k random days in the valid range: identical year/month everywhere
  • Compared old vs new days_to_months / micros_to_months / nanos_to_* over 5k random samples: results identical inside the representable range, so no behavior change for existing data
  • Reproduced both scan scenarios end to end: EqualTo("x", inf) on a float column now returns the right row, and a year-partitioned table with 9999-12-31 scans correctly with LessThanOrEqual and GreaterThan
  • Checked pre-epoch timestamps (e.g. 1969-12-31 23:59:59.999999): months/years come out the same as before

One small question, non-blocking: in pyiceberg/utils/datetime.py, micros_to_months(micros) now does micros // 86_400_000_000 first. Since that's floor division, negative micros (pre-1970) floor toward negative infinity, which matches the old day-boundary behavior. Just double-checked it, all good.

CI is fully green. LGTM!

@Anurag-S1ngh

Anurag-S1ngh commented Nov 18, 2026 •

Copy link
Copy Markdown
Contributor Author

Thanks for the review. Addressed in 16903cf, 60759e1 and eb30c12:

  • era is now z // 146097, and (-719529, -1971, -23641) is in the parametrized test.
  • Added test_scan_and_delete_filters_on_infinity for <, <=, >, >= and delete(LessThan("x", math.inf)).
  • The description now lists the range and delete behavior change and the divergence from Java's DoubleLiteral.to(FLOAT).
  • Dropped the unreachable nanos_to_* asserts.

StringLiteral with "Infinity" is left as a follow-up. Rebased onto main to resolve the conflict with #4388 in tests/utils/test_datetime.py.

@Anurag-S1ngh

Copy link
Copy Markdown
Contributor Author

Thanks for checking. Nothing to change there, floor division for negative micros matches the previous day-boundary behavior.

Year and month partitions raised OverflowError when a filter projected
past 9999-12-31 or before 0001-01-01, because the transform converted the
day count to a datetime.date. Compute years and months from the day count
with integer arithmetic, so projections just past the last representable
date produce year 10000 and match no partition instead of raising.

A Python float became a DoubleLiteral, and converting that to a float
column turned inf into FloatAboveMax or FloatBelowMin, which bound the
predicate to AlwaysFalse or AlwaysTrue. inf is a valid float value, so
convert it to a FloatLiteral like any other float that fits.
The C++ algorithm truncates toward zero and compensates with z - 146096, but Python's // already floors. The correction made the era one too small for days before 0000-03-01, so year and month partitions before year 1 projected to the wrong partition.
A timestamp_ns cannot hold 2932897 days, so the asserts covered an input no table can contain.
A double infinity on a float column now binds as a float literal, so <, <=, > and >= match rows instead of binding to always true or always false.
@Anurag-S1ngh
Anurag-S1ngh force-pushed the fix/4404-date-inf-scan-edges branch from 68dc05b to eb30c12 Compare November 18, 2026 11:34

This branch has not been deployed

No deployments
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

Scans raise on year and month partitions at 9999-12-31, and float inf filters return wrong rows

3 participants