Skip to content

Support LAG and LEAD window functions#82108

Merged
novikd merged 13 commits intomasterfrom
support-lag-and-lead-functions
Jun 19, 2025
Merged

Support LAG and LEAD window functions#82108
novikd merged 13 commits intomasterfrom
support-lag-and-lead-functions

Conversation

@novikd
Copy link
Copy Markdown
Member

@novikd novikd commented Jun 18, 2025 •
edited
Loading

Changelog category (leave one):

  • New Feature

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

Support lag and lead window functions. Closes #9887.

Documentation entry for user-facing changes

  • Documentation is written (mandatory for new features)

@clickhouse-gh
Copy link
Copy Markdown
Contributor

clickhouse-gh bot commented Jun 18, 2025 •
edited
Loading

Workflow [PR], commit [4bd5dfc]

Summary: ❌

job_name test_name status info comment
Stateless tests (amd_tsan, s3 storage, 1/3) failure
01515_mv_and_array_join_optimisation_bag SERVER_DIED
03442_json_duplicate_path SERVER_DIED
02992_analyzer_group_by_const SERVER_DIED
02458_insert_select_progress_tcp SERVER_DIED
Server died FAIL

@clickhouse-gh clickhouse-gh bot added the pr-feature Pull request with new product feature label Jun 18, 2025
@alexey-milovidov alexey-milovidov self-assigned this Jun 18, 2025
@novikd
Copy link
Copy Markdown
Member Author

novikd commented Jun 18, 2025

@Blargian, could you please review the documentation I wrote?

Blargian
Blargian approved these changes Jun 18, 2025
View reviewed changes
Copy link
Copy Markdown
Member

@Blargian Blargian left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM. I made some suggestions for a few small changes.

novikd and others added 9 commits June 18, 2025 20:03
@novikd @Blargian
Update docs/en/sql-reference/window-functions/lag.md
5942ef3 
Co-authored-by: Shaun Struwig <[email protected]>
@novikd @Blargian
Update docs/en/sql-reference/window-functions/lag.md
14166a3 
Co-authored-by: Shaun Struwig <[email protected]>
@novikd @Blargian
Update docs/en/sql-reference/window-functions/lag.md
7be0827 
Co-authored-by: Shaun Struwig <[email protected]>
@novikd @Blargian
Update docs/en/sql-reference/window-functions/lag.md
c3e8c73 
Co-authored-by: Shaun Struwig <[email protected]>
@novikd @Blargian
Update docs/en/sql-reference/window-functions/lead.md
cbe706f 
Co-authored-by: Shaun Struwig <[email protected]>
@novikd @Blargian
Update docs/en/sql-reference/window-functions/lead.md
84c6b9d 
Co-authored-by: Shaun Struwig <[email protected]>
@novikd @Blargian
Update docs/en/sql-reference/window-functions/lead.md
86fad1f 
Co-authored-by: Shaun Struwig <[email protected]>
@novikd @Blargian
Update docs/en/sql-reference/window-functions/lead.md
950e366 
Co-authored-by: Shaun Struwig <[email protected]>
@novikd
Merge remote-tracking branch 'origin/master' into support-lag-and-lea…
4bd5dfc 
…d-functions
@novikd
Copy link
Copy Markdown
Member Author

novikd commented Jun 19, 2025

Stateless tests (amd_tsan, s3 storage, 1/3) — Failed: 4, Passed: 1788, Skipped: 39

2025.06.19 11:02:12.751814 [ 374779 ] {} <Error> DiskLocal: Cannot gain read access of the disk directory: disk1_02961/
2025.06.19 11:02:13.312938 [ 374779 ] {} <Error> DiskLocal: Disk disk1_02961 is marked as broken during startup: Code: 481. DB::ErrnoException: Cannot check read access to file: disk1_02961/: , errno: 2, strerror: No such file or directory. (PATH_ACCESS_DENIED), Stack trace (when copying this message, always include the lines below):

Seems unrelated.

@novikd novikd added this pull request to the merge queue Jun 19, 2025
Merged via the queue into master with commit ef664dd Jun 19, 2025
119 of 122 checks passed
@novikd novikd deleted the support-lag-and-lead-functions branch June 19, 2025 13:49
@robot-ch-test-poll4 robot-ch-test-poll4 added the pr-synced-to-cloud The PR is synced to the cloud repo label Jun 19, 2025
@robot-ch-test-poll1 robot-ch-test-poll1 mentioned this pull request Jun 19, 2025
Merged
robot-ch-test-poll1 added a commit that referenced this pull request Jun 19, 2025
@robot-ch-test-poll1
Merge pull request #82217 from ClickHouse/cherrypick/25.6/82108
66116ad 
Cherry pick #82108 to 25.6: Support LAG and LEAD window functions
robot-clickhouse added a commit that referenced this pull request Jun 19, 2025
@robot-clickhouse
Backport #82108 to 25.6: Support LAG and LEAD window functions
0d57d94
@robot-ch-test-poll1 robot-ch-test-poll1 mentioned this pull request Jun 19, 2025
Merged
@robot-ch-test-poll robot-ch-test-poll added the pr-backports-created-cloud deprecated label, NOOP label Jun 19, 2025
@robot-ch-test-poll1 robot-ch-test-poll1 added the pr-backports-created Backport PRs are successfully created, it won't be processed by CI script anymore label Jun 19, 2025
clickhouse-gh bot added a commit that referenced this pull request Jun 19, 2025
@clickhouse-gh
Merge pull request #82218 from ClickHouse/backport/25.6/82108
cf578ce 
Backport #82108 to 25.6: Support LAG and LEAD window functions
@UnamedRus UnamedRus mentioned this pull request Jun 25, 2025
Closed
@robot-clickhouse robot-clickhouse added the pr-must-backport-synced The `*-must-backport` labels are synced into the cloud Sync PR label Jul 2, 2025
danthegoodman1
danthegoodman1 reviewed Jul 24, 2025
View reviewed changes
docs/en/sql-reference/window-functions/lead.md
Comment on lines +45 to +56
```sql title="Query"
SELECT
fullName,
lead(year, 1, year) OVER (PARTITION BY category ORDER BY year ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS year,
category,
motivation
FROM nobel_prize_laureates
WHERE category = 'physics'
ORDER BY year DESC
LIMIT 9
Copy link
Copy Markdown

@danthegoodman1 danthegoodman1 Jul 24, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Maybe I'm missing something, but isn't this example totally wrong/breaking a dataset?

This is pulling the year from the next row in the same category, which might not even be the same person. This is just shifting year that a prize in physics was awarded to the next year a prize was awarded, and has no relation to the current row other than being 1 offset within the category...

TLDR it's just mangling the dataset AFAICT, right? I did consult with o4-mini-high to make sure that I wasn't interpreting it wrong, but it seems like were just shifting the year and mangling the data for no reason. I'm not sure this is a useful example.

Copy link
Copy Markdown
Member

@Blargian Blargian Jul 25, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

@danthegoodman1 I think you are right. I will redo these examples.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@Blargian Blargian Blargian approved these changes

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

+1 more reviewer

@danthegoodman1 danthegoodman1 danthegoodman1 left review comments

Reviewers whose approvals may not affect merge requirements

Assignees

@alexey-milovidov alexey-milovidov

Labels

pr-backports-created Backport PRs are successfully created, it won't be processed by CI script anymore pr-backports-created-cloud deprecated label, NOOP pr-feature Pull request with new product feature 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 v25.6-must-backport

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

TPC-DS compatibility issues

8 participants

@novikd @danthegoodman1 @alexey-milovidov @Blargian @robot-clickhouse @robot-ch-test-poll @robot-ch-test-poll1 @robot-ch-test-poll4