Parse unquoted JSON numbers for DateTime64 and DateTime as Unix timestamps by alexey-milovidov · Pull Request #108091 · ClickHouse/ClickHouse

alexey-milovidov · 2026-06-22T01:21:15Z

A JSON number with a fractional part such as 1703363853.035 was rejected when inserted into a DateTime64 column via JSONEachRow, raising CANNOT_PARSE_INPUT_ASSERTION_FAILED at the decimal point — even though the same number is accepted by the Values format, and the docs advertise that floating-point epochs cast to DateTime64.

The unquoted-number branch of SerializationDateTime64 (and SerializationDateTime) used readIntText, which stops at the . and leaves the fractional part unparsed. For DateTime64 it also read a bare integer straight into the raw underlying (scaled) value, so 1703363853 was interpreted as ticks (1970-01-20 ...) instead of seconds — inconsistent with Values, CAST and toDateTime64.

Now an unquoted number is parsed as a decimal value at the column scale: a Unix timestamp (seconds since the epoch) with optional sub-second precision, matching Values, CAST, toDateTime64 and the Decimal type (DateTime64 is a Decimal underneath). One more digit than Decimal's precision is allowed so that a 10-digit second count is still valid at scale 9. DateTime truncates the fractional part to whole seconds, also matching CAST.

Backward incompatible note: a bare unquoted integer fed to a DateTime64 column in JSONEachRow (and the other JSON/Quoted text paths) is now interpreted as seconds since the epoch instead of the raw scaled value. For example 1703363853 for DateTime64(3) now yields 2023-12-23 20:37:33.000 instead of 1970-01-20 17:09:23.853. This only affects unquoted numbers; quoted strings and ClickHouse's own JSON output (which is always quoted) are unaffected.

Closes: #59443

Changelog category (leave one):

Backward Incompatible Change

Changelog entry (a user-readable short description of the changes that goes into CHANGELOG.md):

An unquoted JSON number for a DateTime64/DateTime column in JSONEachRow and similar formats is now interpreted as a Unix timestamp (seconds since the epoch) with optional sub-second precision, consistent with the Values format, CAST and toDateTime64. Previously a number with a fractional part (e.g. 1703363853.035) was rejected with CANNOT_PARSE_INPUT_ASSERTION_FAILED, and a bare integer (e.g. 1703363853) was read into the raw scaled value of a DateTime64, producing a 1970-... timestamp. This is a backward incompatible change for unquoted integers fed to DateTime64 columns; quoted strings and ClickHouse's own (always quoted) JSON output are unaffected.

Documentation entry for user-facing changes

Documentation is written (mandatory for new features)

…tamps An unquoted JSON number such as `1703363853.035` was rejected when inserted into a `DateTime64` column via `JSONEachRow` (and the other JSON/Quoted text paths), because the unquoted-number branch used `readIntText`, which stops at the decimal point and leaves `.035` unparsed, raising `CANNOT_PARSE_INPUT_ASSERTION_FAILED`. The same number is accepted by the `Values` format. For `DateTime64`, `readIntText` also read a bare integer straight into the raw underlying (scaled) value, so `1703363853` was interpreted as ticks (`1970-01-20 ...`) rather than seconds, inconsistent with `Values`, `CAST` and `toDateTime64`. Now an unquoted number is parsed as a decimal value at the column scale, i.e. a Unix timestamp (seconds since the epoch) with optional sub-second precision, matching `Values`, `CAST`, `toDateTime64` and the `Decimal` type (`DateTime64` is a `Decimal` underneath). One more digit than `Decimal`'s precision is allowed so that a 10-digit second count is still valid at scale 9. `DateTime` truncates the fractional part to whole seconds, also matching `CAST`. Closes: #59443 Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

clickhouse-gh · 2026-06-22T01:21:47Z

Workflow [PR], commit [2c90b96]

AI Review

Summary

This PR changes unquoted numeric DateTime / DateTime64 input in JSON and Quoted-style paths to Unix-timestamp semantics, adds input_format_read_datetime_number_as_raw_value as the rollback knob, and aligns the JSONExtract / typed-JSON DOM path with the new contract. Most of the earlier correctness gaps are fixed, but one Quoted-format surface is still inconsistent: Regexp can silently truncate a fractional compatibility-mode timestamp to its integer prefix instead of rejecting it.

Findings

❌ Blockers

[src/Core/FormatFactorySettings.h:780] The setting/docs now say the Quoted contract also covers Regexp, but RegexpRowInputFormat still feeds a standalone field buffer into deserializeFieldByEscapingRule and never checks that the whole field was consumed (src/Processors/Formats/Impl/RegexpRowInputFormat.cpp:111-114). With input_format_read_datetime_number_as_raw_value = 1, the raw-value readers stop at . / e, so a row like 1703363853.035 under format_regexp_escaping_rule = 'Quoted' is accepted after reading only the 1703363853 prefix instead of matching the documented Values / MySQLDump / CustomSeparated behavior.
Suggested fix: require full field consumption on the Regexp Quoted path, or narrow the setting/docs to exclude Regexp. Add a focused Regexp regression alongside 04622_datetime_number_quoted_escaping_formats.

Final Verdict

Status: ❌ Block

…time64-float

… detect Int64 overflow The unquoted-number branch read the value straight into the `Int64` ticks of `DateTime64` with one extra digit of precision allowed. As the AI review noted, `readDecimalText` accumulates into the destination type, whose `Decimal` operators wrap, so a 19-digit input just above the `Int64` boundary (e.g. `9223372036854775808` for `DateTime64(0)`) could wrap during accumulation and then pass the final scale-multiplication check as a bogus, often negative, timestamp. Read into a `Decimal128` temporary instead, apply the remaining scale in `Int128`, then range-check the result against the `Int64` ticks of `DateTime64` and raise `DECIMAL_OVERFLOW` if it does not fit. A 128-bit accumulator holds up to `max_precision<Decimal128>` (38) digits, which spans the whole valid range at every scale, so no in-range value is rejected and no out-of-range value wraps. This also makes a too-large value report `DECIMAL_OVERFLOW` rather than `ARGUMENT_OUT_OF_BOUND`, matching the error the `Values` expression fallback produces (fixes the `03797_convert_field_to_type_decimal_overflow` regression). Adds boundary coverage to `04402_json_datetime64_number_input`: `Int64` max/min are stored exactly, and values one step outside the range are rejected instead of silently wrapped. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

…mantics A bare unquoted integer for a `DateTime64` column in the `Values`/Quoted text path is now a Unix timestamp in seconds rather than the raw scaled value, so tests that fed such an integer to a `DateTime64` column need their inputs (or references) adjusted: - `01516_date_time_output_format`: `1602709200123` (raw ms ticks) becomes `1602709200.123`, which is the same instant under the new rule, keeping the reference unchanged. - `02519_monotonicity_fuzz`: `1000` (one second of ticks) becomes `1`, one second since the epoch, keeping the reference unchanged. - `01732_more_consistent_datetime64_parsing`: the integer row is now whole seconds (`1111111111`); the other forms are unchanged. - `00927_asof_join_other_types`: the `DateTime64(3)` rows now read `1`, `3`, `5` as seconds, which incidentally makes them consistent with the `DateTime` rows in the same test. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

The backward-incompatible parser change makes a bare unquoted integer for a `DateTime64` column a Unix timestamp in seconds (matching `DateTime`, the `Values` format, `CAST` and `toDateTime64`) instead of the raw scaled ticks. The user doc `docs/en/sql-reference/data-types/datetime64.md` and the matching source documentation in `src/DataTypes/registerDataTypeDateTime.cpp` still described the old contract (an integer interpreted as scaled ticks, e.g. `1546300800000` at precision 3), which would now parse to a different instant. Update both to describe a number as a Unix timestamp in seconds with optional sub-second precision, change the example insert from `1546300800123` to `1546300800`, and add a backward-incompatible-change note pointing out that a bare integer used to be interpreted as raw ticks before version 26.7. Quoted strings and ClickHouse's own (always quoted) output are unaffected. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

…time64-float

The 04402 prefix now has many tests in master after the merge; move the new DateTime64 number-input test to the free 04489 slot. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

…time64-float

alexey-milovidov · 2026-06-30T04:09:49Z

Pushed the work that addresses the AI Review ❌ Block (the commits were prepared but not yet on the branch): 109c5da1fa6..8e2c74edb06.

Minimum required actions from the verdict, all done:

DateTime64 19-digit overflow (164348fad66): the unquoted number is now read into a Decimal128 temporary (readDecimalText with precision max_precision<Decimal128> = 38 digits), the remaining scale is applied in Int128 with common::mulOverflow, and the result is range-checked against the Int64 ticks of DateTime64 before storing. Out-of-range values raise DECIMAL_OVERFLOW instead of wrapping. A 128-bit accumulator covers the whole valid DateTime64 range at every scale, so no in-range value is rejected and no out-of-range value wraps. This also makes a too-large value report DECIMAL_OVERFLOW (matching the Values fallback), which fixes the 03797_convert_field_to_type_decimal_overflow regression.
Boundary tests (04489_json_datetime64_number_input): Int64 max/min are stored exactly; 9223372036854775808 / -9223372036854775809 and the scale-multiplied DateTime64(3) case are rejected with DECIMAL_OVERFLOW. Also covers exponent notation, negative epochs, fractional truncation, multi-column delimiters, and the Nullable/Variant try-paths.
Docs (6a3c426b376): docs/en/sql-reference/data-types/datetime64.md and the source doc in src/DataTypes/registerDataTypeDateTime.cpp now describe an inserted number as a Unix timestamp in seconds (fractional part = sub-second precision), with a Backward incompatible change note.

The existing tests broken by the behavior change were updated to keep their original intent: 00927_asof_join_other_types, 01516_date_time_output_format, 01732_more_consistent_datetime64_parsing, 02519_monotonicity_fuzz.

Also merged the latest master (clean, net diff unchanged) and resolved the two open review threads. The two changed serialization translation units pass -fsyntax-only.

The sentence "Contrary to inserting, the `toDateTime64` function will treat all values as the decimal variant" described the pre-26.7 behavior, where an inserted number was the raw scaled value while `toDateTime64` interpreted a number as seconds. Now that an inserted number is also a Unix timestamp in seconds, the contrast no longer holds and the sentence contradicted the new insertion contract. Reword it in both `docs/en/sql-reference/data-types/datetime64.md` and the mirrored source documentation in `src/DataTypes/registerDataTypeDateTime.cpp`. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

The `YTsaurus` engine reads YT data via `JSONEachRow` and maps the YT `timestamp`/`timestamp64` types (microseconds since the epoch) to `DateTime64(6)`, relying on an unquoted JSON number being stored as the raw underlying value (ticks). This PR changed an unquoted number to mean a Unix timestamp in seconds, so a real YT timestamp such as `1703363853000000` would be interpreted as seconds and rejected with `DECIMAL_OVERFLOW`. Add a `read_datetime64_number_as_raw_value` JSON format setting that restores the raw-ticks interpretation of an unquoted number for `DateTime64` columns, and enable it in `YTsaurusSource`. The setting is internal (set only by the `YTsaurus` reader) and defaults to off, so the user-facing JSON/Quoted parsing behavior is unchanged. Fixes the integration test `test_ytsaurus/test_tables.py::test_ytsaurus_primitive_types[timestamp0]` and `[timestamp1]`. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

An unquoted number for a `DateTime64` column is now interpreted as a Unix timestamp in seconds. The `VALUES` insert in this test used raw millisecond ticks (`1639872000000`, `1639872891123`, `42`) which under the new semantics overflow or shift to a different instant. Use the equivalent seconds form (`1639872000`, `1639872891.123`, `0.042`) so the stored ticks, the `MsgPack` round-trip and the reference output are all unchanged. `readDecimalText` parses these exactly, so the stored values are bit-identical to before. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

alexey-milovidov · 2026-06-30T16:47:23Z

Pushed 8e2c74edb06..d2145f5ce59 addressing the CI failures and the AI Review ⚠️ Request changes verdict.

1. Docs (the sole blocking action / unresolved thread, 1dab91a80a1). Reworded the stale sentence "Contrary to inserting, the toDateTime64 function will treat all values as the decimal variant" in both docs/en/sql-reference/data-types/datetime64.md and the mirrored source documentation in src/DataTypes/registerDataTypeDateTime.cpp. It now reads that toDateTime64 treats a numeric argument as a number of seconds, like inserting, so it no longer contradicts the new contract. Thread resolved.

2. YTsaurus DateTime64 regression (0ab5d2f4c3c) — fixes integration test test_ytsaurus/test_tables.py::test_ytsaurus_primitive_types[timestamp0]/[timestamp1]. The YTsaurus engine reads YT data via JSONEachRow and maps timestamp/timestamp64 (microseconds since the epoch) to DateTime64(6), relying on an unquoted JSON number being stored as the raw underlying value (ticks). With this PR an unquoted number means seconds, so a real YT timestamp such as 1703363853000000 would overflow with DECIMAL_OVERFLOW. Added an internal read_datetime64_number_as_raw_value JSON format setting (default off) that restores the raw-ticks reading, enabled only by YTsaurusSource; user-facing JSON/Quoted parsing is unchanged.

3. 01098_msgpack_format (d2145f5ce59) — its VALUES insert used raw millisecond ticks (1639872000000, 1639872891123, 42) which now shift/overflow. Replaced with the equivalent seconds form (1639872000, 1639872891.123, 0.042); readDecimalText parses these exactly, so the stored ticks, the MsgPack round-trip and the .reference are bit-identical.

Remaining red — Stress test (amd_tsan/arm_tsan)/Hung check failed, possible deadlock found is the known open flake #107941; this PR has no concurrency/threading changes.

Verification: the three changed translation units pass -fsyntax-only against the codebase. A full build/integration run was not possible in this environment (submodules are not initialized in the worktree), so the 01098/test_ytsaurus fixes rest on the exact-decimal arithmetic above and on CI re-running.

… `DateTime64` The `DateTime` unquoted-number path used `readIntText` followed by a manual `.`-only fraction skip, so a valid JSON number in exponent form such as `1.703363853e9` or `1703363853.0e0` left the `e...` suffix unread and the row was rejected by `JSONEachRow` - even though the `DateTime64` path (which uses `readDecimalText`) already accepts it and the PR contract promises unquoted JSON numbers behave like Unix timestamps matching `CAST`. Parse the `DateTime` number with the same decimal-aware `readDecimalText` logic used by `DateTime64`, but at scale 0: `DateTime` has whole-second resolution, so any sub-second part (from a fraction or a net-negative exponent) is truncated to whole seconds, matching `CAST`, `toDateTime` and the `Values` format. The value is accumulated in a 128-bit temporary and clamped to the `DateTime` range, as before for out-of-range numbers. Addresses the AI Review finding on `SerializationDateTime.cpp`.

Add exponent-notation cases (`1.703363853e9`, `1703363853.0e0`) for an unquoted `DateTime` number to `04489_json_datetime64_number_input`, alongside the existing `DateTime64` exponent case, and assert they match `CAST(1.703363853e9 AS DateTime)`. Renumber the test from `04489` to `04492`: `04489` is now taken on `master` by `04489_max_threads_auto_parsing_compat`, and `04490`/`04491` are also taken, so `04492` is the next free prefix.

alexey-milovidov · 2026-06-30T20:13:52Z

Pushed d2145f5ce59..e2bc6a5e9c7 addressing the AI Review ⚠️ Request changes verdict (the single remaining DateTime finding).

1. DateTime exponent-form numbers (b30316944aa). The DateTime unquoted-number path used readIntText followed by a manual .-only fraction skip, so a valid JSON number in exponent form such as 1.703363853e9 or 1703363853.0e0 left the e... suffix unread and JSONEachRow rejected the row — even though the DateTime64 path already accepts it and the PR contract promises unquoted JSON numbers behave like Unix timestamps matching CAST. It now parses the number with the same decimal-aware readDecimalText logic as DateTime64, but at scale 0: DateTime has whole-second resolution, so any sub-second part (from a fraction or a net-negative exponent) is truncated to whole seconds, matching CAST, toDateTime and the Values format. The value is accumulated in a 128-bit temporary and clamped to the DateTime range, as before for out-of-range numbers.

2. Test (e2bc6a5e9c7). Added the requested DateTime exponent cases (1.703363853e9, 1703363853.0e0) next to the existing DateTime64 exponent case, asserting they match CAST(1.703363853e9 AS DateTime). Also renumbered the test from 04489 to 04492: 04489_max_threads_auto_parsing_compat, 04490, and 04491 are now all taken on master, so 04492 is the next free prefix.

The unresolved review thread is resolved. The changed translation unit passes -fsyntax-only; the parsing reuses the exact readDecimalText machinery already exercised by the DateTime64 path. A full build was not possible in this worktree (submodules not initialized), so the behavior rests on that shared arithmetic and on CI re-running.

The unquoted-number paths of `SerializationDateTime64` and `SerializationDateTime` parse a Unix timestamp with `readDecimalText` and `digits_only = false`, which stops at the first non-numeric character so the `,`/`}` that follows the value in JSON is left for the format layer. That same leniency, however, also accepted a token with no digits at all: `.`, `-` and `e9` were each consumed as the value zero, so a malformed row such as `{"t":.}` was silently loaded as the Unix epoch instead of being rejected. The previous `readIntText`-based code left such suffixes unread, so those rows did not become data. `readDigits`/`readDecimalText` now optionally report whether at least one digit character was read. A bare `0` reads as zero but still has a digit, so the meaningful `digits` output (which is 0 for a leading zero) cannot be used for this. The `DateTime64`/`DateTime` readers pass this flag and raise `CANNOT_PARSE_DATETIME` (or fail the try-path) when no digit was present. The new out-parameter defaults to null, so every other `readDecimalText` caller is unaffected. Also drop the redundant `static` from `datetime_number_precision` in the anonymous namespace, which broke the `Build (arm_tidy)` check with `readability-static-definition-in-anonymous-namespace`. Adds malformed-token regression coverage (`{"t":.}`, `{"t":-}`, `{"t":e9}`) for both `DateTime64` and `DateTime` to `04492_json_datetime64_number_input`. CI report: https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=108091&sha=e2bc6a5e9c78758d3641312329512492d405c0ed&name_0=PR PR: #108091 Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

alexey-milovidov · 2026-06-30T22:42:47Z

Pushed e2bc6a5e9c7..4102d1178e6 addressing the AI Review ⚠️ Request changes verdict and the Build (arm_tidy) failure.

1. Malformed unquoted numeric tokens (the AI Major / unresolved thread). readDecimalText with digits_only = false stops at the first non-numeric character (so the ,/} after a JSON value is left for the format layer), but it also accepted a token with no digits at all — ., -, e9 were each consumed as the value zero, so a row such as {"t":.} was silently loaded as the Unix epoch. readDigits/readDecimalText now optionally report whether at least one digit character was read; the DateTime64/DateTime readers reject the value when none was (throwing path raises CANNOT_PARSE_DATETIME, try-path returns false). A bare 0 still parses (it has a digit even though the meaningful-digit count is 0), and the new out-parameter defaults to null so every other readDecimalText caller is unaffected.

2. Build (arm_tidy). Dropped the redundant static from datetime_number_precision in the anonymous namespace, which readability-static-definition-in-anonymous-namespace (warnings-as-errors) had failed on.

3. Tests. Added the requested malformed-token cases ({"t":.}, {"t":-}, {"t":e9}) for both DateTime64 and DateTime to 04492_json_datetime64_number_input, asserting CANNOT_PARSE_DATETIME.

The documentation checkbox is now checked (the DateTime64 docs were already updated in this PR). The unresolved review thread is resolved.

Verification: the two changed serialization translation units pass -fsyntax-only, and I confirmed that an exception thrown from deserializeTextJSON surfaces through format(JSONEachRow, …) with its original CANNOT_PARSE_DATETIME code (so the new test annotations are correct). A full build was not possible in this worktree (it is not configured), so the behavior rests on that shared decimal-parsing arithmetic and on CI re-running.

…tamp parsing change The `Fast test` failed on `03371_dynamic_values_parsing_templates` with `result differs with reference`. This fuzzer-generated test inserts a large heterogeneous set of values into a `Dynamic` column via the `VALUES` format, which uses the constant-expression template parser (hence the test name). For one garbage row whose field is the array literal `['你们', '认识你很高兴', x'E28228FF', '哪国人']`, the template deduced from an earlier row reads the field through the unquoted-number `DateTime` path. On `master` the old `readIntText`-based path read the leading `[` as zero digits and silently produced the epoch (`1970-01-01 00:00:00`). This PR's malformed-token rejection (`readDecimalText` now reports whether at least one digit was read, and the `DateTime`/`DateTime64` readers reject a token with none) correctly refuses the no-digit token, so the value falls back and is stored as `Float64` `0`. This is the intended, more-correct behavior (a token with no digits is not a valid number), and it is the sole difference in the output: the reference now drops the spurious epoch `DateTime` and gains a `0` in the `Float64` group, matching the exact diff produced by CI on the branch. CI report: https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=108091&sha=4102d1178e6f101e37d6df23d9a13853cb5b1644&name_0=PR&name_1=Fast%20test Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

…time64-float

The `DateTime` DOM path (`JSONExtract`, typed `JSON`) rejected a negative numeric timestamp before it reached the shared numeric reader, while the row input serializer `SerializationDateTime::readAsIntText` parses such a token and clamps it into the `DateTime` range (the Unix epoch). So `{"t":-1}` and `{"t":-0.5}` became the epoch in `JSONEachRow` / `Values` but raised `INCORRECT_DATA` in typed `JSON`, and yielded the default value in `JSONExtract`. Remove the negative rejection from both the integer and the fractional branch of `DateTimeNode` and let the final `std::clamp` (integer path) and `timestampNumberToSeconds` inside `tryReadDateTimeAsNumber` (fractional path) map a negative value to the epoch, matching the row serializer and the adjacent `DateTime64` DOM path. Extend `04522_json_extract_datetime64_fractional_precision` with negative integer and fractional cases across `JSONExtract`, typed `JSON` and the row input path. Addresses the AI review finding on `src/Formats/JSONExtractTree.cpp`. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

…time64-float

The AI review left one Major on `SerializationDateTime.cpp`: the PR now makes an unquoted number for a `DateTime` column in the `JSON`, `Values`/`Quoted` and typed-`JSON` paths accept fractional and exponent forms (parsed via `readDecimalText` and truncated to whole seconds), and `input_format_read_datetime_number_as_raw_value` restores the legacy integer-only handling — but only the `DateTime64` documentation was updated for this contract, not `DateTime`. Update both `DateTime` documentation surfaces to match the `DateTime64` ones: `docs/en/sql-reference/data-types/datetime.md` and the mirrored source documentation in `src/DataTypes/registerDataTypeDateTime.cpp` now describe fractional/exponent numeric input, the truncation to whole seconds, and the scope of `input_format_read_datetime_number_as_raw_value`. Documentation only, no behavior change. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

…time64-float

…meric input The `DateTime` documentation said the 26.7 change also affects `JSONExtract` and the `JSON` data type but omitted the `Float64`-precision caveat that the matching `DateTime64` note already documents. `JSONExtract` and the `JSON` data type parse a fractional number through `Float64`, so a value close to a whole-second boundary can round to the adjacent second (for example `1703363853.9999999` is read as `1703363854`), while the row input formats truncate the original text exactly. This is pinned by `04522_json_extract_datetime64_fractional_precision`. Mirror the caveat from `datetime64.md` into `datetime.md` and into the source-registered `DateTime` docs in `registerDataTypeDateTime.cpp`, and add it to the `DateTime64` registration doc as well so all four documentation surfaces agree. Documentation-only; no behavior change.

…time64-float

clickhouse-gh · 2026-07-20T15:01:35Z

+    else /// Just 1504193808 or 1703363853.035 (a Unix timestamp, possibly with sub-second precision)
    {
- readIntText(x, istr);
+ readDateTime64AsNumber(x, scale, istr);


This quoted-path change is wider than the contract and test coverage around it. deserializeTextQuoted is not just the Values parser: MySQLDumpRowInputFormat::readField (src/Processors/Formats/Impl/MySQLDumpRowInputFormat.cpp:496-503) and the configurable quoted-escaping readers (Template, CustomSeparated, Regexp via deserializeFieldByEscapingRule) all route here too. That means a bare numeric DateTime64 literal in those formats now flips from raw ticks to seconds as well, and the mirrored SerializationDateTime::deserializeTextQuoted change also starts accepting bare fractional/exponent DateTime numbers there.

The setting help/history and the new tests only describe JSON, Values/Quoted, and JSONExtract/typed JSON, so this is currently an undocumented and unproven extra rollout surface. If that broader rollout is intentional, it needs explicit coverage and contract text for those extra callers; if it is not, this cannot live in the shared quoted serializer and needs a format-side flag so non-Values quoted readers keep the pre-26.7 behavior.

The broader rollout is intentional: the semantics of an unquoted number in a Quoted field should not depend on which format routes through the quoted serializer, so MySQLDump and the Template/CustomSeparated/Regexp formats configured with Quoted field escaping follow the same contract as Values. b52776f91dc makes that explicit: the setting description (FormatFactorySettings.h) and the docs (datetime.md, datetime64.md, the source docs in registerDataTypeDateTime.cpp) now name this surface, and the new regression test 04622_datetime_number_quoted_escaping_formats pins the MySQLDump and CustomSeparated (Quoted escaping) behavior for a bare integer and a fractional number under the default setting and under input_format_read_datetime_number_as_raw_value = 1 (which works there through the same FormatSettings member), plus the unchanged handling of quoted strings.

This still is not uniform for Regexp. In src/Processors/Formats/Impl/RegexpRowInputFormat.cpp, the matched subpattern is wrapped in a standalone field_buf and passed to deserializeFieldByEscapingRule, but the caller never checks that the whole field was consumed.

That matters in compatibility mode because the raw-value readers used by src/DataTypes/Serializations/SerializationDateTime64.cpp and src/DataTypes/Serializations/SerializationDateTime.cpp stop at . / e. So a Regexp row such as 1703363853.035 with format_regexp_escaping_rule = 'Quoted' and input_format_read_datetime_number_as_raw_value = 1 is accepted after reading only the 1703363853 prefix, instead of following the Values / MySQLDump / CustomSeparated contract described here.

Please either make the Regexp path require full field consumption for Quoted datetime numbers, or narrow the docs/setting text to exclude Regexp. A focused Regexp regression next to 04622_datetime_number_quoted_escaping_formats would pin the intended behavior.

The unquoted-number change for `DateTime`/`DateTime64` applies to every input path that parses fields with the `Quoted` escaping rule, because it lives in the shared quoted serializer: besides the `Values` format, this includes `MySQLDump` and the `Template`/`CustomSeparated`/`Regexp` formats configured with `Quoted` field escaping, and the `input_format_read_datetime_number_as_raw_value` rollback works there through the same `FormatSettings` member. Name that surface explicitly in the setting description and the docs, and add a regression test (`04622_datetime_number_quoted_escaping_formats`) that pins the `MySQLDump` and `CustomSeparated` (`Quoted` escaping) behavior for a bare integer and a fractional number under the default setting and under the compatibility setting. #108091 Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

…t slop The unquoted-number parsing for `DateTime`/`DateTime64` was implemented with local helpers inside `SerializationDateTime.cpp` and `SerializationDateTime64.cpp`, which is the wrong home for shared IO helpers, and each had a separate throwing and try-returning copy of the same body (plus a third duplicate of `tryReadDateTimeAsNumber`). Move the readers to `ReadHelpers` (declarations in `ReadHelpers.h`, definitions in `ReadHelpers.cpp` so no `readDecimalText.h` include is needed in the header) and collapse each throw/try pair into a single `ReturnType`-templated `readDateTimeAsNumber` / `readDateTime64AsNumber` (and the `AsRawValue` legacy variants), following the existing `readDateTime64Text` pattern. The serializations and `JSONExtractTree` now call these shared readers. Also trim the over-long explanatory comments to the non-obvious rationale. No behavior change: the throwing and try paths keep their distinct error codes (`ARGUMENT_OUT_OF_BOUND`, `DECIMAL_OVERFLOW`, `CANNOT_PARSE_DATETIME`, `CANNOT_PARSE_NUMBER`). All existing DateTime-number tests pass unchanged. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

Avogar

I refactored the code a bit - reduced code duplication, moved helper functions to ReadHelpers, shortened unnecessary big comments in the code. Hope it's ok.

…e mirrored DateTime64 docs The last refactoring commit reworded the `input_format_read_datetime_number_as_raw_value` help text back to the broad "an unquoted number" claim, while the implementation restores the legacy raw-value path only for a bare unquoted integer. Narrow the wording again and state explicitly what happens to a number with a fractional or exponent part in compatibility mode: the row input paths reject it, and `JSONExtract`/typed `JSON` still reads it as seconds for `DateTime64` and rejects it for `DateTime` — all matching the pre-26.7 behavior the setting restores. Also re-sync the mirrored `DateTime64` source documentation in `registerDataTypeDateTime.cpp` with `docs/en/sql-reference/data-types/datetime64.md`: point readers at the rollback setting, and say the tab-separated, CSV and other escaped text formats are not governed by it and keep their existing interpretation of an unquoted number (a large value is read as ticks) instead of the broader "keep that interpretation". Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

alexey-milovidov · 2026-07-22T18:52:02Z

Pushed e5bf6d6baf5..6832fafe6f1 addressing the AI Review ⚠️ Request changes verdict (both Majors / the two unresolved threads).

1. input_format_read_datetime_number_as_raw_value help text (src/Core/FormatFactorySettings.h). The refactoring commit e5bf6d6baf5 had reworded the opening line back to the broad "an unquoted number"; it is narrowed again to a bare unquoted integer, with the explicit compatibility-mode caveat: a number with a fractional or exponent part is rejected by the row input paths, while JSONExtract/typed JSON still reads it as seconds for DateTime64 and rejects it for DateTime — exactly the pre-26.7 behavior the setting restores, as the regression tests 04508_datetime_number_input_raw_value_compatibility and 04522_json_extract_datetime64_fractional_precision pin.

2. Mirrored DateTime64 source docs (src/DataTypes/registerDataTypeDateTime.cpp). Re-synced with docs/en/sql-reference/data-types/datetime64.md: the paragraph now points readers at the rollback setting and says the tab-separated, CSV and other escaped text input formats are not governed by it and keep their existing interpretation of an unquoted number (a large value is read as ticks). Both threads resolved.

Both changed translation units compile cleanly.

Triage of the one completed CI red at e5bf6d6baf5 — Stateless tests (amd_tsan, s3 storage, parallel, 1/3). Not caused by this PR: the server stopped responding globally at 13:44 (Hung check failed: server is not responding), and two unrelated tests running at that moment (00278_insert_already_sorted and 04058_json_all_values) both hit the same 300-second client SOCKET_TIMEOUT, followed by Server died. 04058_json_all_values exercises JSONAllValues over quoted-string JSON only and never touches the changed DateTime/DateTime64 number paths; the server error log has no sanitizer report, no fatal signal and no logical error — background threads kept logging for minutes after the hang, and dmesg shows no OOM. CIDB shows no other failure of this test in 90 days except one unrelated run on master. This PR contains no concurrency changes; the push above re-runs the whole suite. Report: https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=108091&sha=e5bf6d6baf532323eb5a36e14084fb069e2e31a8&name_0=PR&name_1=Stateless%20tests%20%28amd_tsan%2C%20s3%20storage%2C%20parallel%2C%201%2F3%29

…time64-float

alexey-milovidov · 2026-07-23T04:04:29Z

Merged master into the branch (6832fafe6f1..733d78122a9, clean merge, no conflicts; verified the merged branch still builds and the feature works: {"d": 1703363853.035} parses as a Unix timestamp and input_format_read_datetime_number_as_raw_value = 1 restores the raw-ticks behavior).

Triage of the three CI failures on 6832fafe6f1 — all unrelated to this PR:

Stress test (amd_asan_ubsan) — Cannot start clickhouse-server: part all_5_6_1_0 of test_3.t_plt_evict_reinsert intersects a previous part with unreadable transaction metadata. This is the known issue Stress tests: Cannot start clickhouse-server — corrupted txn_version.txt of fabricated parts from 04241_part_loading_tree_rollback_* tests #111053 (corrupted txn_version.txt of fabricated parts from the 04241_part_loading_tree_rollback_* tests), fix in progress: Detach tables permanently in part-loading-tree rollback tests #111055.
Upgrade check (amd_release) — Error message in clickhouse-server.log: DDLWorker(rdb_test_...) UUID-collision TABLE_ALREADY_EXISTS noise on restart. It hit 15 different PRs in the last day alone; allowlist fix in progress: Allowlist rdb_test_ DDLWorker UUID-collision upgrade-check restart noise #107126.
Stateless tests (amd_tsan, s3 storage, parallel, 1/3) — 01666_merge_tree_max_query_limit, labeled flaky by the harness itself; all 4 diagnostic reruns passed, and the test fails sporadically across many unrelated PRs. @groeneai, investigate the failure: https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=108091&sha=6832fafe6f18a63ed856735153f4e9187051646e&name_0=PR&name_1=Stateless%20tests%20%28amd_tsan%2C%20s3%20storage%2C%20parallel%2C%201%2F3%29 and provide a fix in a separate PR. If the fix is already in progress, link it here.

The merge triggers a fresh CI run. Review state is otherwise complete: Avogar approved, AI Review verdict is ✅ Approve, and there are no unresolved threads.

groeneai · 2026-07-23T04:50:07Z

Investigated. This is the chronic transient mode of 01666_merge_tree_max_query_limit, not a regression from this PR.

Evidence:

The CI harness's own randomized-settings diagnosis on 6832fafe6f1 reran the test 4 times with the identical settings: 4 passed, 0 failed ("not reproducible"). On master and across unrelated PRs the check passes at high frequency and fails only sporadically.
I traced the throttle path in source: the per-table concurrency counter is inserted at pipeline-build time (ReadFromMergeTree::AnalysisResult::checkLimits -> MergeTreeData::getQueryIdHolder -> insertQueryIdOrThrowNoLock), which runs before the query's read_rows becomes visible in system.processes. So wait_for_query_to_start (polling read_rows > 0) already guarantees the long-running query holds the slot before the first select * from simple, and the error-202 assertions are not racy on the registration side.

I could not reproduce a deterministic failure (a prior investigation ran it 30/30 locally, same conclusion), and no separate fix PR is currently open for this test.

I will follow up here shortly with either a narrow test-robustness change that does not weaken the 202 throttle checks, or, if it stays non-reproducible, the concrete findings.

groeneai · 2026-07-23T06:26:56Z

Fix for 01666_merge_tree_max_query_limit: #111528.

Root cause: the test's long-running holder query
(select sleepEachRow(0.1) from simple settings max_block_size = 1) pinned only
max_block_size and left max_threads to the settings randomizer. With a high
max_threads and a low merge_tree_min_rows_for_concurrent_read the single part is
split into many concurrent read streams, so the 5000 sleeping rows run in parallel and
the holder finishes early, releasing the max_concurrent_queries slot before a later
throttle assertion. That assertion then gets code 0 instead of 202 ("Expected error
code: 202 but got: 0").

Fix pins max_threads = 1 on the holder so it reads sequentially and stays alive for
the whole test. Verified with a deterministic both-directions repro plus 50/50 runs on
local disk and 50/50 on s3 storage, randomization on. The 202 assertions are unchanged.

…the JSON DOM path behind the compatibility setting With `input_format_read_datetime_number_as_raw_value = 1` (or `compatibility = '26.6'`), a negative integer for a `DateTime` column in `JSONExtract` and the typed `JSON` type is rejected again with `cannot convert negative integer value ... to DateTime`, as before 26.7, instead of being clamped to the epoch. The clamping relaxation now applies only on the new default path. The typed-JSON `-1` case is pinned in the compatibility test in both modes. #108091

clickhouse-gh · 2026-07-23T18:47:28Z

LLVM Coverage Report

Metric	Baseline	Current	Δ
Lines	86.20%	86.20%	+0.00%
Functions	92.00%	91.90%	-0.10%
Branches	78.20%	78.30%	+0.10%

Changed lines: Changed C/C++ lines covered: 254/294 (86.39%) · Uncovered code

Full report · Diff report

… a fractional number in compatibility mode With `input_format_read_datetime_number_as_raw_value = 1`, a fractional literal in the `Values` format is rejected by the streaming parser and falls back to SQL expression evaluation, which reads it as seconds. This is not a gap in the rollback contract: the same fallback produced the same result in versions up to 26.6 (verified on the official 26.7.1 build, where `(1703363853.035)` for `DateTime64(3)` yields `2023-12-23 20:37:33.035`), so `Values` behavior for a fractional number is identical in every configuration. Clarify this in the setting description, both data type docs and registration docs, and pin it with regression queries in `04508`.

clickhouse-gh Bot added the pr-bugfix Pull request with bugfix, not backported by default label Jun 22, 2026

clickhouse-gh Bot reviewed Jun 22, 2026

View reviewed changes

Comment thread src/DataTypes/Serializations/SerializationDateTime64.cpp Outdated

clickhouse-gh Bot added pr-backward-incompatible Pull request with backwards incompatible changes and removed pr-bugfix Pull request with bugfix, not backported by default labels Jun 22, 2026

clickhouse-gh Bot reviewed Jun 22, 2026

View reviewed changes

Comment thread tests/queries/0_stateless/04492_json_datetime64_number_input.sql

clickhouse-gh Bot reviewed Jun 22, 2026

View reviewed changes

Comment thread src/DataTypes/Serializations/SerializationDateTime64.cpp Outdated

alexey-milovidov and others added 7 commits June 29, 2026 16:14

Merge remote-tracking branch 'origin/master' into fix-59443-json-date…

d508022

…time64-float

Merge remote-tracking branch 'origin/master' into fix-59443-json-date…

cfb36aa

…time64-float

Renumber test 04402 -> 04489 to avoid collision with master

2fb4700

The 04402 prefix now has many tests in master after the merge; move the new DateTime64 number-input test to the free 04489 slot. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Merge remote-tracking branch 'origin/master' into fix-59443-json-date…

8e2c74e

…time64-float

clickhouse-gh Bot reviewed Jun 30, 2026

View reviewed changes

Comment thread docs/en/sql-reference/data-types/datetime64.md

alexey-milovidov and others added 3 commits June 30, 2026 16:45

clickhouse-gh Bot reviewed Jun 30, 2026

View reviewed changes

Comment thread src/DataTypes/Serializations/SerializationDateTime.cpp Outdated

alexey-milovidov added 2 commits June 30, 2026 20:12

clickhouse-gh Bot reviewed Jun 30, 2026

View reviewed changes

Comment thread src/DataTypes/Serializations/SerializationDateTime64.cpp Outdated

alexey-milovidov and others added 2 commits July 1, 2026 03:20

Merge remote-tracking branch 'origin/master' into fix-59443-json-date…

0cf9f46

…time64-float

clickhouse-gh Bot reviewed Jul 17, 2026

View reviewed changes

Comment thread src/Formats/JSONExtractTree.cpp Outdated

alexey-milovidov and others added 2 commits July 17, 2026 14:58

Merge remote-tracking branch 'origin/master' into fix-59443-json-date…

7013737

…time64-float

mintlify Bot deployed to staging - docs July 18, 2026 02:31 View deployment

clickhouse-gh Bot reviewed Jul 18, 2026

View reviewed changes

Comment thread src/DataTypes/Serializations/SerializationDateTime.cpp Outdated

alexey-milovidov and others added 2 commits July 18, 2026 05:45

Merge remote-tracking branch 'origin/master' into fix-59443-json-date…

5f6a682

…time64-float

mintlify Bot deployed to staging - docs July 18, 2026 14:08 View deployment

clickhouse-gh Bot reviewed Jul 18, 2026

View reviewed changes

Comment thread docs/en/sql-reference/data-types/datetime.md Outdated

alexey-milovidov added 2 commits July 18, 2026 16:39

Merge remote-tracking branch 'origin/master' into fix-59443-json-date…

d469049

…time64-float

mintlify Bot deployed to staging - docs July 20, 2026 14:45 View deployment

clickhouse-gh Bot reviewed Jul 20, 2026

View reviewed changes

alexey-milovidov and others added 2 commits July 20, 2026 22:19

Avogar approved these changes Jul 22, 2026

View reviewed changes

clickhouse-gh Bot reviewed Jul 22, 2026

View reviewed changes

Comment thread src/DataTypes/registerDataTypeDateTime.cpp Outdated

Merge remote-tracking branch 'origin/master' into fix-59443-json-date…

733d781

…time64-float

mintlify Bot deployed to staging - docs July 23, 2026 04:27 View deployment

clickhouse-gh Bot reviewed Jul 23, 2026

View reviewed changes

Comment thread src/Formats/JSONExtractTree.cpp

groeneai mentioned this pull request Jul 23, 2026

Fix flaky 01666_merge_tree_max_query_limit: pin max_threads=1 on the holder query #111528

Open

clickhouse-gh Bot reviewed Jul 23, 2026

View reviewed changes

Comment thread src/DataTypes/Serializations/SerializationDateTime64.cpp

Uh oh!

Conversation

alexey-milovidov commented Jun 22, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Changelog category (leave one):

Changelog entry (a user-readable short description of the changes that goes into CHANGELOG.md):

Documentation entry for user-facing changes

Uh oh!

clickhouse-gh Bot commented Jun 22, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

AI Review

Summary

Findings

Final Verdict

Uh oh!

Uh oh!

Uh oh!

Uh oh!

alexey-milovidov commented Jun 30, 2026

Uh oh!

Uh oh!

alexey-milovidov commented Jun 30, 2026

Uh oh!

Uh oh!

alexey-milovidov commented Jun 30, 2026

Uh oh!

Uh oh!

alexey-milovidov commented Jun 30, 2026

Uh oh!

Uh oh!

Uh oh!

Uh oh!

clickhouse-gh Bot Jul 20, 2026

Choose a reason for hiding this comment

Uh oh!

alexey-milovidov Jul 20, 2026

Choose a reason for hiding this comment

Uh oh!

clickhouse-gh Bot Jul 24, 2026

Choose a reason for hiding this comment

Uh oh!

Avogar left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

alexey-milovidov commented Jul 22, 2026

Uh oh!

alexey-milovidov commented Jul 23, 2026

Uh oh!

Uh oh!

groeneai commented Jul 23, 2026

Uh oh!

groeneai commented Jul 23, 2026

Uh oh!

Uh oh!

clickhouse-gh Bot commented Jul 23, 2026

LLVM Coverage Report

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

alexey-milovidov commented Jun 22, 2026 •

edited

Loading

clickhouse-gh Bot commented Jun 22, 2026 •

edited

Loading