ClickHouse / ClickHouse/ClickHouse
`toDayOfWeek` with mode 2 or 3 falsely claims monotonicity within a Monday-based week — primary-key pruning silently drops matching rows
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**Describe what's wrong**
`toDayOfWeek` accepts a mode argument: mode 0 (default) numbers Monday=1..Sunday=7, modes 2 and 3 number Sunday-first (Sunday=1/0). The monotonicity machinery is mode-blind: `ToDayOfWeekImpl` in `src/Functions/DateTimeTransforms.h` declares `hasMonotonicity` true with `FactorTransform = ToMondayImpl`, meaning "monotonic within a Monday-based week". That claim is correct for modes 0 and 1, but false for modes 2 and 3: within a Monday-based week the Sunday-first numbering jumps down at Sunday, so the function is not monotonic on the factor's interval.
`KeyCondition` consults this monotonicity for the two-argument form via `FunctionWithOptionalConstArg` (`src/Storages/MergeTree/KeyCondition.cpp`), so a range predicate on `toDayOfWeek(key, 2)` over a date key prunes granules that contain matching rows: silent row loss at pure default settings.
**Does it reproduce on the most recent release?**
Yes. Verified wrong on the 24.8, 25.8 and 26.8 releases and on a 26.9 development build; `ToDayOfWeekImpl`'s factor is unchanged on current master. Long-standing.
**How to reproduce**
Pure default settings (small granules only to make the loss deterministic on a tiny table; any table where week boundaries cross granule boundaries is affected):
```sql
CREATE TABLE t (d Date) ENGINE = MergeTree ORDER BY d SETTINGS index_granularity = 1;
INSERT INTO t SELECT toDate('2026-08-03') + number FROM numbers(14); -- two full Mon-Sun weeks
SELECT count() FROM t WHERE toDayOfWeek(d, 2) >= 5; -- 1, wrong
SELECT countIf(toDayOfWeek(d, 2) >= 5) FROM t; -- 4, correct (full scan)
SELECT count() FROM t WHERE toDayOfWeek(d, 3) >= 5; -- 3, wrong
SELECT countIf(toDayOfWeek(d, 3) >= 5) FROM t; -- 6, correct
SELECT count() FROM t WHERE toDayOfWeek(d, 1) >= 5; -- 4, correct (mode 1 is Monday-based)
SELECT count() FROM t WHERE toDayOfWeek(d) >= 5; -- 6, correct (default mode)
```
With mode 2, days 5..7 are Thursday, Friday, Saturday; each week contains three such days plus Sunday=1, and the index analysis maps the predicate to a sub-range of each week that excludes some of them.
**Expected behavior**
The filtered count equals the full-scan count for every mode: monotonicity over the enclosing Monday-based week must only be claimed for modes whose numbering is actually monotonic within that week, or the factor must be mode-aware.
**Error message and/or stacktrace**
No error — rows are silently missing from the result.
**Additional context**
The pending #117246 fixes the same class of false monotonicity claim for the subsecond extractors (`toMillisecond` and friends) by giving them a correct factor transform; `toDayOfWeek`'s two-argument form needs the analogous treatment (decline monotonicity for modes 2 and 3, or use a week transform matching the mode). `toWeek` already opted out of the claim entirely for similar reasons.
Contributor guide
Assessment
This issue has not been assessed yet.