ClickHouse / ClickHouse/ClickHouse

`toDayOfWeek` with mode 2 or 3 falsely claims monotonicity within a Monday-based week — primary-key pruning silently drops matching rows

Open
#117,264 0 comments 0 reactions 1 assignee Claimed by @alexey-milovidov View on GitHub
comp-datetime comp-mergetree minor potential bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.