Extend the supported range of Date32 to [0000-01-01, 9999-12-31] - #111534
Conversation
`Date32` was limited to `[1900-01-01, 2299-12-31]` - the span covered by
the `DateLUTImpl` lookup table - while `DateTime64` was already extended
to `[0000-01-01, 9999-12-31]`. The mismatch was inconsistent on its own:
parsing `toDate32('0079-08-24')` silently clamped to `1900-01-01` (so
e.g. `age` returned a plausible-looking wrong answer), while the cast
`toDate32(toDateTime64('0079-08-24 13:00:00', 0))` produced the correct
out-of-range value, because the `Int32` day number has room for it and
the extended formatting path is shared with `DateTime64`.
`DateLUTImpl::makeDayNum` and `tryToMakeDayNum` now fall back to the
cctz-based `makeDayNumOutOfRange` for valid calendar dates outside
`[DATE_LUT_MIN_YEAR, DATE_LUT_MAX_YEAR]`, exactly like `makeDateTime`
already does since the `DateTime64` extension. That single change covers
text parsing everywhere: SQL casts from `String`, CSV/JSON/TSV/`Values`
input formats, and `YYYYMMDDToDayNum`. Computations over the extended
values were already handled by the `DateTime64` infrastructure, which
gates every `ExtendedDayNum` argument with an out-of-range escape path.
`Date` behavior is unchanged: its clamps happen downstream, on the
day-number result.
The LUT-derived `DATE_LUT_MAX_EXTEND_DAY_NUM` (120530, an exclusive
bound) is replaced by inclusive `DATE_LUT_MIN_EXTEND_DAY_NUM` (-719528,
`0000-01-01`) and `DATE_LUT_MAX_EXTEND_DAY_NUM` (2932896, `9999-12-31`),
tied to the internal representable window by static asserts.
In the numeric conversion `toDate32(N)`, the day-number/timestamp
disambiguation threshold moves accordingly: values up to 2932896 are day
numbers, larger values are Unix timestamps capped at the new
`MAX_DATE32_TIMESTAMP` (`9999-12-31 23:59:59` instead of `2299-12-31
23:59:59`); values below the minimum saturate to `0000-01-01` instead of
`1900-01-01`. The monotonicity analysis threshold is kept in sync, and
the Parquet, Arrow, Arrow IPC and native ORC readers validate `Date32`
input against the new bounds (also fixing an off-by-one that accepted
day number 120530). `makeDate32`, `YYYYMMDDToDate32` and `changeDate`
accept years `[0, 9999]`. The default value of `Date32` and the result
of `toDate32OrZero` on unparseable input remain `1900-01-01`.
Closes #111524
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
|
Workflow [PR], commit [549f6a7] Summary: ✅
AI ReviewSummaryThis PR extends Findings❌ Blockers
Final VerdictChanges are close, but the backward-incompatible LLVM Coverage Report
Changed lines: Changed C/C++ lines covered: 410/433 (94.69%) · Uncovered code |
clickhouse-gh
Bot
added
the
pr-backward-incompatible
|
Preview deployment for your docs. Learn more about Mintlify Previews.
|
![clickhouse-gh[bot]](https://avatars.githubusercontent.com/in/928874?s=60&v=4){kind=small}
|
Preview deployment for your docs. Learn more about Mintlify Previews.
|
On Darwin, `time_t` is `long` while `DateLUTImpl::Time` is `Int64` (`long long`) - distinct types - so `may_be_out_of_lut_range` was false for the `time_t` arguments that `ToDate32TransformFromSecondsOrDays` passed to `toDayNum`, the cctz escape path was compiled out, and timestamps beyond the LUT end saturated to `2299-12-31` instead of `9999-12-31`. Pass `DateLUTImpl::Time` instead. Fixes the `Fast test (arm_darwin)` failures in 04626_extend_date32_range, 02477_age_date32, 03212_variant_dynamic_cast_or_default and 03604_to_date_casts: https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=111534&sha=cb0accdedb4b3cfdb9373d1f34f18df23add0c02&name_0=PR&name_1=Fast%20test%20%28arm_darwin%29 #111534 Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
`toDate32('2999-12-31')` and `toDate32('1000-12-31')` no longer clamp to the old
`[1900-01-01, 2299-12-31]` boundaries - both dates are now within the supported range.
#111534
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
ToYearImpl::getPreimage and ToYYYYMMImpl::getPreimage still refused years outside 1900..2299, so predicates like toYear(d) = 1500 on a table ordered by d lost primary key pruning over the newly valid Date32 range. Widen the check to DATE_LUT_MAX_REPRESENTABLE_YEAR. The year 9999 (and month 9999-12) stays excluded because the exclusive upper endpoint of its preimage would be the first moment of the next year/month, which is not representable. Addresses the AI review on #111534 Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
The reference file for `04641_date32_extended_range_preimage` was committed empty. Generated the output: `toYear`/`toYYYYMM` predicates over the extended `Date32` range are rewritten to raw key ranges, and the year 9999 boundary stays unrewritten but returns the correct result. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
|
The Upgrade check failure ( The Fast test (arm_darwin) failures and |
…f `toDate32OrZero`, not the lower boundary of `Date32`
|
Update (
|
|
Update ( Status of the reds on the previous commit
The @groeneai, please investigate the |
|
A fix is already in progress, and it is your own #111691 ("Pin Mechanism, for the record. The failing assertions in this test are plan shape ones of the form SELECT count() > 0 FROM (EXPLAIN actions = 1 ... ) WHERE explain ILIKE '%BuildRuntimeFilter%';which is why the diff is if (optimization_settings.join_runtime_filter_min_probe_rows > 0)
{
auto probe_size = join_step->getInputRowsEstimation(JoinTableSide::Left);
if (probe_size && *probe_size <= optimization_settings.join_runtime_filter_min_probe_rows)
return false;
}Two settings the test runner randomizes have to line up for that
So the probe side estimate becomes a random number, and when it lands at or below the randomized threshold the runtime filter is not built and the 8842 against a randomized estimate, rather than the default 1000 against a real one. Why #111691 closes it completely: the guard is wrapped in One correction to something I want to avoid leaving on the record: this is not a join side swap. The statements already carry a per query Your observation that it is unrelated to |
# Conflicts: # src/Functions/FunctionsConversion_reg.cpp
…UT dates The review asked `isMakeDateOutOfRange` to reject calendar-invalid dates such as `1899-02-30` before taking the `cctz` escape path. That would make years outside `[1900, 2299]` stricter than years inside it: the lookup table has always normalized an overlong day of month, so `makeDate32(1999, 2, 30)` is `1999-03-02` and `makeDate32(1984, 2, 30)` is `1984-03-01` (pinned in `02243_make_date32` long before this change). Rejecting the same input only for pre-1900 and post-2299 years would mean the same expression has a different meaning depending on the year. Add `04651_date32_out_of_lut_calendar_normalization` which checks, for `toDate32`, `makeDate32` and `YYYYMMDDToDate32`, that February 30 and April 31 normalize identically inside and outside the lookup table window, and that truly malformed components (month or day outside `1..12` / `1..31`) still take the error path everywhere.
|
🕵 Status update: CI on Fixed the remaining AI-review blocker in The other unresolved thread is the compatibility-setting request, which stays dismissed — the |
…eference was truncated after the first Parquet block The test outputs 47 lines (a CAST section plus five format blocks), but only the first 13 lines were committed, so the test failed with "result differs with reference" in every stateless configuration. https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=111534&sha=6dfd4596a33b2490827d34ee3788805fdf5dc2ae&name_0=PR Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
…he date range checks of the Arrow and ORC readers The Arrow and native ORC readers dispatch their `DATE`-column handling on the requested header type, but the hint arrived unstripped, so a `LowCardinality(Date)` or `LowCardinality(DateTime)` target skipped the new range-check branches: Arrow fell back to the plain `Date32` path (the later context-less cast then clamped or wrapped with the default overflow mode instead of honoring `date_time_overflow_behavior`), and ORC fell clear through to the raw `Int32` branch, so even an in-range day count was cast as unix seconds instead of midnight of that day. Strip `LowCardinality` and `Nullable` from the hint first, the same way `readColumnWithTimestampData` and the Arrow IPC `stripHint` already do. The Parquet and Avro readers already strip or recurse into the wrapper. `LowCardinality(DateTime64(...))` from the review example turned out to be not constructible (`DataTypeLowCardinality` only accepts numbers, strings, `Date` and `DateTime`), so `Date` and `DateTime` cover all reachable wrapped targets. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
|
🕵 Merged CI red on Both AI-Review blockers on the All touched format tests ( |
The embedded documentation of `toDate32` and `toDate32OrZero` in
`src/Functions/FunctionsConversion_reg.cpp` was updated for the extended
range, but the published copy in
`docs/reference/functions/regular-functions/type-conversion-functions.mdx`
was still the stale autogenerated text: it described `1900-01-01` as the
lower boundary of `Date32` and showed `toDate32('1899-01-01')` saturating
to `1900-01-01`.
Also fixed the same claim in the source documentation of
`toDate32OrDefault` (`src/Functions/castOrDefault.cpp`): its default value
is `1900-01-01`, which is the historical default of the type, not its lower
boundary, which is now `0000-01-01`.
`master` added `04838_in_subquery_lowcardinality_result_type` and `04844_type_evolution_matrix_widen`, which clash with the numbers used by this branch. Renumbered `04838_date32_to_datetime64_scale_overflow` to `04848_...` and `04844_orc_arrow_lowcardinality_date_targets` to `04849_...` (the temporary file names inside the tests too).
|
🕵 Merged Addressed the AI-Review Major (stale generated docs). The embedded documentation of Test renumbering. All 18 touched tests pass locally against a fresh build. Both reds on
@groeneai, please investigate the |
|
The harness half is already in flight: #111934, "AST fuzzer: bound post-fuzz stages and detect a memory-stuck server". It bounds the teardown wait you hit, plus the client reap and the liveness-probe window on the same path. One refinement from re-reading the job log: The expensive part is that #111934 replaces the wait with a 180 s poll, then SIGKILLs and writes a teardown watchdog marker, so the run reports the stall with its logs attached in about 33 min instead of being cancelled at 5 h. It does not make the query interruptible: yours is the It is open and unreviewed since 2026-08-05. If you would prefer the teardown bound alone, split from the memory-stuck detection it carries, I will do that. |
robot-ch-test-poll3
added
the
pr-synced-to-cloud
…s-out-of-column-type-range Conflict in `src/Interpreters/convertFieldToType.cpp`: both sides range-check an exact integer `Field` converted to a `Date32` target and cover both integer carriers. Master's version (from #111534) is the stronger one - it rejects day numbers outside the representable calendar `[DATE_LUT_MIN_EXTEND_DAY_NUM, DATE_LUT_MAX_EXTEND_DAY_NUM]` = `[0000-01-01, 9999-12-31]`, a strict subset of the `Int32` storage range this branch checked - so keep it and drop this branch's variant. The `WITH FILL` bound checks in `FillingTransform` only need `convertFieldToType` to return Null for a value the column type cannot hold, which the kept version still does.
…acros from master Master now defines these two macros as the day numbers of `0000-01-01` and `9999-12-31` (#111534), with static assertions tying them to `min_representable_day_index` and `max_representable_day_index` - exactly the expressions the accessors added on this branch returned. Drop the accessors, use the macros the rest of the codebase already uses, and note in `FillingTransform` that `convertFieldToType` now rejects out-of-calendar `Date32` day numbers too, while the window is still needed here for the dedicated `INTERVAL`-step message and for `DateTime64`.
…erge The `Date32` range extension on master (#111534) made `toDate32('9995-06-01')` parse to the actual date instead of clamping to `2299-12-31`, so the anchor of SELECT count() > 0 FROM (SELECT toDate32('9995-06-01') AS d ORDER BY d ASC WITH FILL TO 2932896 STEP INTERVAL 1 YEAR) now sits four steps below the calendar boundary and its sequence steps *over* `TO` = `9999-12-31` instead of onto it: `9999-06-01` + 1 year would leave the representable calendar, so the `INTERVAL` step returns its input unchanged and the filling stagnates strictly below `TO` forever. That is the data-dependent shape the pull request lists as not fixed - without a `FROM`, the anchor, and hence the stagnation, is only known at execution time - so it is the test query that has to be picked so that it terminates. Use an anchor whose sequence lands exactly on the boundary (`toDate32('9995-12-31')`), which keeps what the query asserts: a `TO` at exactly the calendar boundary is accepted and reachable. The output is unchanged, so the reference file stays as is.
…by master Master's `NumericToDate32` suite (from #111534) already covers exactly what the `Date32` assertions added here did - both integer carriers, the exact boundaries, and a value that does not fit the underlying `Int32` - and does so against the narrower representable calendar rather than the storage range. Keep only the `Date` and `DateTime` assertions, which nothing else covers, and add the `Int64` carrier for `DateTime`: the branch this pull request changes accepts it, while the previous code handled `UInt64` alone.
Master extended Date32 to [0000-01-01, 9999-12-31] in ClickHouse#111534, which touches the same transforms and field-coercion helpers this PR rewrites. Conflict resolution: - ToDate32TransformFromSecondsOrDays: take master's DATE_LUT_MIN_EXTEND_DAY_NUM lower bound (daynum_min_offset no longer exists) and keep this PR's formatOutOfBoundsValue helper on the throw path. Master's separate is_nan check is dropped as redundant: the isFinite early throw above it already rejects NaN and inf for every mode. - convertFieldToType: keep this PR's overflow-aware coercion helpers, which subsume master's inline Date32 range check, and retarget their bounds to the extended range. - toDate32 documentation: state the new range alongside the setting-dependent behaviour. Two defects the moved bounds exposed, both fixed here: - DATE32_MAX_TIMESTAMP_FIELD still mirrored the old 2299-12-31 timestamp, so the field path saturated three centuries below CAST. - The day-number/timestamp predicate in the Date32 field helper was non-strict while the transform's is strict, so the boundary day number 2932896 materialized as 1970-02-03 through INSERT ... VALUES while CAST returned 9999-12-31. Both now use the strict form. The fractional-boundary test carriers were chosen against the old boundary (120530) and no longer straddle it, so they were retargeted to 2932896 and an integer arm was added for the boundary day number itself. Verified that arm fails against a binary without the predicate fix. Submodule working trees were resynced to the merged pins (six had stale checkouts, which broke the build on NuRaft's entry_at_ext).
…e_binary` The Apache Arrow library reader for the `Arrow` formats was removed in ClickHouse#111996, so `input_format_arrow_use_native_reader` is an obsolete no-op and the two loops over both readers now ran the same reader twice. Each file is read once, and the notes about what the library reader could not read are gone: unions and NULL list/map slots spanning a non-empty range are no longer special. `04512` also moves its out-of-range day number to the end of the `Date32` range that ClickHouse#111534 extended, since `2299-12-31 + 100` is a valid date now. `04513` gains the `fixed_size_binary` leaves the ClickHouse writer uses for `UUID`, `IPv6` and the 128/256-bit integers, including the self-describing Arrow `uuid` extension type, which the review asked for. These are crafted inline with `pyarrow` (as `04613` does) instead of shipping three more binary fixtures - the layout is easier to review as code, and it doubles as documentation of how the committed fixtures were made.
DateTime64 (PR ClickHouse#107907, 26.7.1) and Date32 (PR ClickHouse#111534, 26.7.4) independently extended their supported range from [1900-01-01, 2299-12-31] to [0000-01-01, 9999-12-31], but the docs only ever showed the new range. A customer hit this gap: querying toDateTime64('1899-01-01', 9) on an older version returns a silently-clamped 1900-01-01 with no indication anything was cut off. Add a "Before version X" note to both types' embedded doc source (and the generated .mdx mirrors, since autogenerate_docs.py needs a built binary to regenerate them) so the old behavior is discoverable. Date and DateTime (32-bit) are unaffected -- their ranges are fixed by storage width, not by the date-lookup-table extension -- so left unchanged. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
4 participants
Changelog category (leave one):
Changelog entry (a user-readable short description of the changes that goes into CHANGELOG.md):
Extended the supported range of
Date32from[1900-01-01, 2299-12-31]to[0000-01-01, 9999-12-31], matchingDateTime64. Parsing and conversions now accept the extended range instead of silently clamping to the old boundaries. Backward compatibility notes: in the numeric conversiontoDate32(N), values in[120530, 2932896]are now interpreted as day numbers (dates from2300-01-01to9999-12-31) instead of Unix timestamps in early 1970, matching the rule that a number that fits into the day-number range is a day number; numbers below the day number of0000-01-01and timestamps after9999-12-31saturate to the new boundaries.Date32was documented as[1900-01-01, 2299-12-31]— the span covered by theDateLUTImpllookup table — while #107907 extendedDateTime64to[0000-01-01, 9999-12-31]. The mismatch was also internally inconsistent: parsing clamped silently while casting from the extendedDateTime64produced out-of-rangeDate32values that rendered correctly:Now all three agree: the parse returns
0079-08-24andagereturns 1946.Implementation:
DateLUTImpl::makeDayNum/tryToMakeDayNumfall back to the cctz-basedmakeDayNumOutOfRangefor valid calendar dates outside the LUT years, exactly likemakeDateTimealready does since #107907 — this single change covers text parsing everywhere (SQL casts fromString, CSV/JSON/TSV/Valuesformats,YYYYMMDDToDayNum). Computations over extended values were already handled by the #107907 infrastructure, which gates everyExtendedDayNumargument with a cctz escape path;Datesemantics are unchanged (its clamps happen downstream on the day-number result). The LUT-derivedDATE_LUT_MAX_EXTEND_DAY_NUM(120530, exclusive) is replaced by inclusiveDATE_LUT_MIN_EXTEND_DAY_NUM/DATE_LUT_MAX_EXTEND_DAY_NUM(−719528 / 2932896) tied to the internal representable window by static asserts.MAX_DATE32_TIMESTAMPis lifted to9999-12-31 23:59:59. The monotonicity analysis threshold is kept in sync, the Parquet / Arrow / Arrow IPC / native ORC readers validateDate32against the new bounds (also fixing an off-by-one that accepted day number 120530), andmakeDate32/YYYYMMDDToDate32/changeDateaccept years[0, 9999]. The default value ofDate32and the result oftoDate32OrZeroon unparseable input remain1900-01-01.Closes #111524
Related: #107907
Documentation entry for user-facing changes