Skip to content

Fix incorrect monotonicity for toWeek, toYearWeek, toStartOfWeek, toLastDayOfWeek, toDayOfWeek causing wrong pruning#94816

Merged
alexey-milovidov merged 2 commits intoClickHouse:masterfrom
nihalzp:fix-to-year-week-monotonicity
Jan 30, 2026
Merged

Fix incorrect monotonicity for toWeek, toYearWeek, toStartOfWeek, toLastDayOfWeek, toDayOfWeek causing wrong pruning#94816
alexey-milovidov merged 2 commits intoClickHouse:masterfrom
nihalzp:fix-to-year-week-monotonicity

Conversation

@nihalzp
Copy link
Copy Markdown
Member

@nihalzp nihalzp commented Jan 22, 2026 •
edited
Loading

Wrong answer:

CREATE TABLE t (s String) ENGINE = MergeTree ORDER BY s;
INSERT INTO t VALUES ('2020-01-10 00:00:00'), ('2020-01-2 00:00:00');
SELECT count() FROM t WHERE toYearWeek(s) = toYearWeek('2020-01-2 00:00:00'); -- should be 1
CREATE TABLE t (d Date32) ENGINE = MergeTree ORDER BY d;
INSERT INTO t VALUES ('2020-12-31'), ('2021-01-01');
SELECT count() FROM t WHERE toWeek(d) = toWeek(toDate32('2020-12-31')); -- should be 1

Exception on valid query:

CREATE TABLE t (s LowCardinality(String)) ENGINE = MergeTree ORDER BY s;
INSERT INTO t VALUES ('2020-01-10 00:00:00'), ('2020-01-2 00:00:00');
SELECT * FROM t WHERE toYearWeek(s) = toYearWeek('2020-01-2 00:00:00');

Changelog category (leave one):

  • Bug Fix (user-visible misbehavior in an official stable release)

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

Fix incorrect primary key and skip index pruning for predicates involving toWeek, toYearWeek, toStartOfWeek, toLastDayOfWeek, and toDayOfWeek, and fix exceptions in some of these functions for valid queries with LowCardinality(String).

Documentation entry for user-facing changes

  • Documentation is written (mandatory for new features)

@clickhouse-gh
Copy link
Copy Markdown
Contributor

clickhouse-gh bot commented Jan 22, 2026 •
edited
Loading

Workflow [PR], commit [af1a8e5]

Summary: ❌

job_name test_name status info comment
AST fuzzer (amd_ubsan) failure
UndefinedBehaviorSanitizer: undefined behavior (STID: 4924-babb) FAIL cidb
BuzzHouse (amd_debug) failure
Logical error: 'Inconsistent AST formatting: the query: (STID: 1941-1bfa) FAIL cidb, issue

@clickhouse-gh clickhouse-gh bot added the pr-bugfix Pull request with bugfix, not backported by default label Jan 22, 2026
@nihalzp nihalzp added pr-must-backport Pull request should be backported intentionally. Use this label with great care! pr-bugfix Pull request with bugfix, not backported by default and removed pr-bugfix Pull request with bugfix, not backported by default labels Jan 22, 2026
@alexey-milovidov alexey-milovidov self-assigned this Jan 30, 2026
@alexey-milovidov alexey-milovidov merged commit dc2c0f4 into ClickHouse:master Jan 30, 2026
127 of 132 checks passed
@robot-clickhouse-ci-1 robot-clickhouse-ci-1 added the pr-synced-to-cloud The PR is synced to the cloud repo label Jan 30, 2026
@robot-ch-test-poll robot-ch-test-poll added the pr-must-backport-synced The `*-must-backport` labels are synced into the cloud Sync PR label Jan 30, 2026
@robot-ch-test-poll2 robot-ch-test-poll2 mentioned this pull request Jan 30, 2026
Merged
robot-ch-test-poll2 added a commit that referenced this pull request Jan 30, 2026
@robot-ch-test-poll2
Merge pull request #95635 from ClickHouse/cherrypick/25.3/94816
214de8c 
Cherry pick #94816 to 25.3: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
robot-clickhouse added a commit that referenced this pull request Jan 30, 2026
@robot-clickhouse
Backport #94816 to 25.3: Fix incorrect monotonicity for toWeek, `to…
aa42d13 
…YearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
This was referenced Jan 30, 2026
Merged
Merged
robot-ch-test-poll2 added a commit that referenced this pull request Jan 30, 2026
@robot-ch-test-poll2
Merge pull request #95637 from ClickHouse/cherrypick/25.8/94816
10e42fa 
Cherry pick #94816 to 25.8: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
robot-clickhouse added a commit that referenced this pull request Jan 30, 2026
@robot-clickhouse
Backport #94816 to 25.8: Fix incorrect monotonicity for toWeek, `to…
cca9124 
…YearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
This was referenced Jan 30, 2026
Merged
Merged
robot-ch-test-poll2 added a commit that referenced this pull request Jan 30, 2026
@robot-ch-test-poll2
Merge pull request #95639 from ClickHouse/cherrypick/25.10/94816
10976b4 
Cherry pick #94816 to 25.10: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
@robot-ch-test-poll2 robot-ch-test-poll2 mentioned this pull request Jan 30, 2026
Merged
robot-clickhouse added a commit that referenced this pull request Jan 30, 2026
@robot-clickhouse
Backport #94816 to 25.10: Fix incorrect monotonicity for toWeek, `t…
ca6449a 
…oYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
@robot-ch-test-poll2 robot-ch-test-poll2 mentioned this pull request Jan 30, 2026
Merged
robot-ch-test-poll2 added a commit that referenced this pull request Jan 30, 2026
@robot-ch-test-poll2
Merge pull request #95641 from ClickHouse/cherrypick/25.11/94816
7c6dab1 
Cherry pick #94816 to 25.11: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
robot-clickhouse added a commit that referenced this pull request Jan 30, 2026
@robot-clickhouse
Backport #94816 to 25.11: Fix incorrect monotonicity for toWeek, `t…
c45d515 
…oYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
This was referenced Jan 30, 2026
Merged
Merged
robot-ch-test-poll2 added a commit that referenced this pull request Jan 30, 2026
@robot-ch-test-poll2
Merge pull request #95643 from ClickHouse/cherrypick/25.12/94816
8521523 
Cherry pick #94816 to 25.12: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
robot-clickhouse added a commit that referenced this pull request Jan 30, 2026
@robot-clickhouse
Backport #94816 to 25.12: Fix incorrect monotonicity for toWeek, `t…
087f173 
…oYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
This was referenced Jan 30, 2026
Merged
Merged
robot-ch-test-poll2 added a commit that referenced this pull request Jan 30, 2026
@robot-ch-test-poll2
Merge pull request #95645 from ClickHouse/cherrypick/26.1/94816
6ca80b6 
Cherry pick #94816 to 26.1: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
robot-clickhouse added a commit that referenced this pull request Jan 30, 2026
@robot-clickhouse
Backport #94816 to 26.1: Fix incorrect monotonicity for toWeek, `to…
55d3450 
…YearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
@robot-ch-test-poll2 robot-ch-test-poll2 mentioned this pull request Jan 30, 2026
Merged
@robot-ch-test-poll3 robot-ch-test-poll3 added the pr-backports-created Backport PRs are successfully created, it won't be processed by CI script anymore label Jan 30, 2026
clickhouse-gh bot added a commit that referenced this pull request Jan 30, 2026
@clickhouse-gh
Merge pull request #95638 from ClickHouse/backport/25.8/94816
9b34e38 
Backport #94816 to 25.8: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
nihalzp added a commit that referenced this pull request Jan 30, 2026
@nihalzp
Merge pull request #95646 from ClickHouse/backport/26.1/94816
b7dad7d 
Backport #94816 to 26.1: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
nihalzp added a commit that referenced this pull request Jan 30, 2026
@nihalzp
Merge pull request #95640 from ClickHouse/backport/25.10/94816
678d872 
Backport #94816 to 25.10: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
nihalzp added a commit that referenced this pull request Jan 30, 2026
@nihalzp
Merge pull request #95642 from ClickHouse/backport/25.11/94816
23161d1 
Backport #94816 to 25.11: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
nihalzp added a commit that referenced this pull request Jan 30, 2026
@nihalzp
Merge pull request #95636 from ClickHouse/backport/25.3/94816
84d6b30 
Backport #94816 to 25.3: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
clickhouse-gh bot added a commit that referenced this pull request Jan 30, 2026
@clickhouse-gh
Merge pull request #95644 from ClickHouse/backport/25.12/94816
9a2a394 
Backport #94816 to 25.12: Fix incorrect monotonicity for `toWeek`, `toYearWeek`, `toStartOfWeek`, `toLastDayOfWeek`, `toDayOfWeek` causing wrong pruning
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@alexey-milovidov alexey-milovidov alexey-milovidov approved these changes

Assignees

@al13n321 al13n321

@alexey-milovidov alexey-milovidov

Labels

pr-backports-created Backport PRs are successfully created, it won't be processed by CI script anymore pr-bugfix Pull request with bugfix, not backported by default pr-must-backport Pull request should be backported intentionally. Use this label with great care! pr-must-backport-synced The `*-must-backport` labels are synced into the cloud Sync PR pr-synced-to-cloud The PR is synced to the cloud repo

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

7 participants

@nihalzp @alexey-milovidov @al13n321 @robot-ch-test-poll @robot-ch-test-poll3 @pnovitskiy @robot-clickhouse-ci-1