I wanted a cheap way to find real bugs in a large database, so I stopped reading code and compared answers instead. Two of the comparisons found genuine bugs in ClickHouse's date functions, both in time zones with a UTC offset that is not a whole number of hours. The other three comparisons found nothing, which is also worth knowing.
Both fixes are open pull requests, not merged. I say so up front because the title of a post like this tends to promise more than the state of the work.
The method: ask the same question two ways
A differential test needs no expected values. You run the same logical query through two routes that must agree, and print any disagreement:
- Against a reference. ClickHouse's date functions against Python's
zoneinfo. - Against itself. The same
JOINwith every join algorithm. The same filter with the primary key, skip indexes andPREWHEREon and off. The sameGROUP BYwith two-level, external and in-order aggregation.
Each probe is about 40-70 lines of Python that shells out to clickhouse local. They are public gists, with the setup in the header: date functions, JOIN algorithms, index analysis, aggregation settings.
Bug 1: toStartOfHour returns :30 on a day when the clocks move by 30 minutes
Australia/Lord_Howe shifts by 30 minutes twice a year. The probe compares toStartOfHour over thousands of timestamps with what Python says the wall clock hour is. Only two samples out of 4,000 disagreed, both on a transition day:
SELECT toStartOfHour(toDateTime('2024-04-07 03:10:00', 'Australia/Lord_Howe')); -- 2024-04-07 02:30:00 (expected 03:00:00) Every other sample, including all the other days of the year in that zone, was right. The test only found this because it sampled around transitions on purpose. The cause is that toStartOfHour ends in a function that rounds on a grid anchored at the local midnight. After a 30 minute shift, that grid no longer lines up with the clock. toStartOfInterval(..., INTERVAL 2 HOUR) already compensated for the shift, so the 2-hour answer was right and the 1-hour answer wrong for the same input.
The fix applies the same compensation to the 1-hour case, only when the day's shift is not a whole number of hours.
Two things I learned from the CI run:
- I could not build ClickHouse here, so CI was the first time the test ran. It passed.
- The performance comparison, which is part of the CI, flagged
date_time_longat +51.7% on my first push. I had added a second lookup of the day to the hot path. Looking the day up once removed it. A correct fix can still be a regression, and the benchmark caught it before a reviewer had to.
Pull request: ClickHouse #131620.
Bug 2: minute intervals before 1970 round to the wrong boundary
SET enable_extended_results_for_datetime_functions = 1; SELECT toStartOfFifteenMinutes(toDateTime64('1910-06-15 12:07:00', 0, 'Europe/Warsaw')); -- 12:00:00 SELECT toStartOfInterval(toDateTime64('1910-06-15 12:07:00', 0, 'Europe/Warsaw'), INTERVAL 15 MINUTE); -- 11:54:00 toStartOfInterval has an arithmetic shortcut that rounds the Unix timestamp. Before 1970 it was allowed whenever the offsets were whole minutes. The dedicated functions round from the local midnight there. The two agree only if the offset is a multiple of the interval, and Warsaw was at +01:24 until 1915. Checking the neighbours turned up Pacific/Chatham (+12:45), where even INTERVAL 10 MINUTE was wrong in 1960.
The fix narrows the condition: whole hours that divide into the interval. Other zones take the slower generic path before the epoch, which is what the dedicated functions use.
Pull request: ClickHouse #131915.
What found nothing
- JOIN algorithms (
hash,parallel_hash,grace_hash,full_sorting_merge,partial_merge,direct,auto) over NULL, NaN, tuple, array, map,LowCardinalityand decimal keys: no disagreement over 7 random seeds. - Index analysis (primary key, three skip index types,
PREWHERE, the old analyzer, the query condition cache) against a full scan over 14 key types: about 6,000 comparisons, no disagreement. - Aggregation settings against
GROUP BY,DISTINCT,ROLLUP, window functions on composite keys: about 3,200 comparisons, no disagreement.
That is a useful result about the database. It is also a reminder that most of a mature system is correct, and the bugs live in the corners where two correct-looking pieces of logic meet, here "round to the hour" and "the offset changed by 30 minutes today".
The probes lied to me first
Most of my debugging time went on the harness, not on ClickHouse:
- A first run reported thousands of mismatches.
DateTimeis unsigned, so negative timestamps clamp to 1970, and my Python side disagreed with that. I limited the range. -
toStartOfMonthreturns aDate, which has no time zone, so comparing its Unix timestamp with Python's local midnight is meaningless. I compared dates instead. -
-0.0and0.0group together but print differently, so the probe flagged a representative choice as a bug. I removed-0.0from the value pool. -
clickhouse localkeeps tables between runs, and running several seeds in parallel locked its directory. I gave each run its own path and aDROP TABLE IF EXISTS.
If your first differential test reports a flood of bugs, it is almost always the test.
Takeaways
- A reference implementation in another language is a good oracle for date and time code, because the two have different bugs.
- Sample around the edges on purpose. The Lord Howe bug showed up in 2 of 4,000 timestamps.
- When a fix touches a hot path, read the performance results, not only the tests.
- Say whether your fix is merged. Mine are not, yet.
