Add support for Fractional limits and offsets by 0xgouda · Pull Request #88755 · ClickHouse/ClickHouse

0xgouda · 2025-10-17T19:01:16Z

Changelog category (leave one):

New Feature

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

Add support for fractional LIMIT and OFFSET for selecting a fraction of a table. Closes #81892

Documentation entry for user-facing changes

Documentation is written (mandatory for new features)

Details

Add support for fractional limits and offset to retrieve percentages of a table for new and old analyzers.

WITH TIES is also supported.

Examples:

# get the median of the table
SELECT * FROM table ORDER BY id LIMIT 1 OFFSET 0.5

# returns first 20% of the result
SELECT * FROM numbers(10) LIMIT 0.2;
   ┌─number─┐
1. │      0 │
2. │      1 │
   └────────┘

# returns 3rd quartile of the result
SELECT * FROM numbers(12) LIMIT 0.25 OFFSET 0.5;
   ┌─number─┐
1. │      6 │
2. │      7 │
3. │      8 │
   └────────┘

# returns first 10% of the result with ties
SELECT * FROM test ORDER BY id LIMIT 0.1 WITH TIES;
   ┌─id─┐
1. │  0 │
2. │  1 │
3. │  1 │
4. │  1 │
   └────┘

Closes #81892

…or limit and offset - Used `DataTypeDecimal32(2,1)` for type conversion for now because for some reason it doesn't recognise `DataTypeFloat32()`.

…mit values.

…ctional[Limit/Offset]Step`

…6` values of limit/offset.

…et` signature

… limit in the pipe

…aryLimitStep` as well

…Offset}

nihalzp

I noticed that during transforms, the algorithm first caches all chunks; afterwards, when all pulling is done, only then it starts processing the chunks. The issue is that it will have slow and high memory usage and would not work if the entire table size is bigger than available memory.

For example:

SELECT *
FROM numbers(1e9)
OFFSET 0.99999999

10 rows in set. Elapsed: 3.646 sec. Processed 990.88 million rows, 7.93 GB (271.76 million rows/s., 2.17 GB/s.)
Peak memory usage: 7.45 GiB.

Here we are caching the entire table of 1e9 rows and that's why memory usage is high. If we used a bigger table whose size is bigger than available RAM, the query would fail.

But if we keep evicting front chunks which will never be part of the final result, we can get very low memory footprint:

SELECT *
FROM numbers(1e9)
LIMIT -10

10 rows in set. Elapsed: 0.328 sec. Processed 919.59 million rows, 7.36 GB (2.80 billion rows/s., 22.41 GB/s.)
Peak memory usage: 17.27 KiB.

Offset version:

SELECT *
FROM numbers(1e9)
OFFSET 1e9 - 10

10 rows in set. Elapsed: 0.320 sec. Processed 943.98 million rows, 7.55 GB (2.95 billion rows/s., 23.61 GB/s.)
Peak memory usage: 9.23 KiB.

For context, we can do some inference about already cache chunks and either discard or push them to free up memory. For example, suppose we have a query with OFFSET 0.5. At one point in time, before we have pulled all the data, we have cached chunks which have a total of 100 rows. Now, even if we do not yet know the total number of rows in the table, we can safely infer that the first 50 rows can be discarded, and we can discard cached complete chunks within the first 50 rows.

Similar for LIMIT, instead of discard, we would push to output ports.

This will require some extra bookkeeping but it can make the feature really high quality and blazingly fast!

nihalzp · 2025-11-07T06:54:23Z

It could be very tricky for FractionalLimitTransform and it will not help much because for fractional limit, especially if small, we would need to keep the most of the table in memory anyway.

However, I think optimizing for FractionalOffsetTransform should be simple change. For this initial version, I think this should be okay. And, later we could optimize FractionalLimitTransform in a separate PR.

Let me know what you think.

…ractional limit/offset.

- add additinoal `getLimimtLengthAndOffset()` parsing step to ensure fractional limit and offset are zero before adding a prelimit.

0xgouda · 2025-11-07T13:55:45Z

I think optimizing for FractionalOffsetTransform should be simple change

I am not sure if optimizing FractionalOffsetTransform is going to be that simple, because we will remove the push phase entirely and in the pull phase every couple of chunks apply the offset on them as you proposed and at the very end push all remaining chunks, but the point is (i am not very sure) there might be scenarios where we still want to offset in the remaining chunks (the ones to be pushed at the end) then maintaining the offset integer till that final phase is going to be tricky i guess.

I would prefer working on these + some more optimizations like limit pushdown, etc. in a separate PR This one already got too big.

0xgouda · 2025-11-07T13:59:26Z

I really enjoyed working on this. You are a very good reviewer, thanks.

I was planning to implement WITH TIES for the negative limit processor next, but I think I should go for the optimizations you proposed first.

nihalzp · 2025-11-07T14:06:10Z

I see what you mean. I underestimated the complexity. Okay, let's go with your plan then!

nihalzp · 2025-11-07T14:07:16Z

I was planning to implement WITH TIES for the negative limit processor next, but I think I should go for the optimizations you proposed first.

Anything that interests you would be very much welcome :)

nihalzp

Looks good to me!

Just few small doc additions I thought would be better if we are more precise.

docs/en/sql-reference/statements/select/limit.md

docs/en/sql-reference/statements/select/offset.md

…sult.

…mit-offset

nihalzp · 2025-11-08T12:16:29Z

Failed integration tests:

Failure in test_s3_access_headers/test.py::test_custom_access_header[test_access_over_custom_header] #89758

0xgouda added 30 commits October 15, 2025 15:56

Update Query Analyzer to ccept float numbers in the range 0.1-0.9 f…

1b815bb

…or limit and offset - Used `DataTypeDecimal32(2,1)` for type conversion for now because for some reason it doesn't recognise `DataTypeFloat32()`.

Update the Limit optimization to check if type is UInt64.

1e6eabc

Update QueryAnalysisResult to parse both UInt64 and Fractional Li…

169991e

…mit values.

Use fractional limit/offset values from query analysis to create `Fra…

2ec3db1

…ctional[Limit/Offset]Step`

Update getLimitLengthAndOffset to return both UInt64 and `BFloat1…

4f326a4

…6` values of limit/offset.

Add Fractional[Limit/Offset]Step creation logic.

afd3e85

Create new FractionalLimitStep step header file.

92a00bb

Implement FractionalLimitStep methods

237261d

Create new FractionalOffsetStep class.

3e4f30a

Implement FractionalOffsetStep class methods

a6924dd

Create new FractionalLimitTransform processor class

a07b6cd

Implement FractionalLimitTransform processor

6addf56

Create new FractionalOffsetTransform processors class

03008be

Implement FractionalOffsetTransform processor.

60f554b

Register new FractionalLimitStep and FractionalOffsetStep

12d345c

Update getLimitqueryInfo to adapt to the new `getLimitLengthAndOffs…

1fb8d1d

…et` signature

Update Exception error message

d4690e7

make function name plural

3a6dcb0

Update Exception error message

41d9940

Allow using up to 3 digits after the point in Fractional Limit.

f3f9eef

Update comment.

9096e4f

serialize offset field

b3f918f

Fix spacing

115c5a2

rename Action to "Fractional Offset"

00d5647

Update Exception message

fa6cbbc

ensure chunks_cache is not empty before processing

b29f176

ensure chunks_cache is not empty before processing

e77589e

If normal limit + fractional offset, add fractional offset before the…

55df8c7

… limit in the pipe

If normal limit + fractional offset, add fractional offset before the…

714281b

… limit in the pipe

if fractional offset add it before limit in the pipeline in `Prelimin…

a10e7f6

…aryLimitStep` as well

0xgouda added 4 commits November 6, 2025 17:21

Improve typing clarity in limit/offset docs.

b467c8c

Use (0, 1) instead of [0, 1) and 0.1 - 0.9 in Fractional{Limit,…

040b9f3

…Offset}

Through exceptions on invalid lim_info state for OFFSET executors.

1c21fa2

Fix style errors

0761e21

0xgouda force-pushed the add-fractional-limit-offset branch from a5d615e to 0761e21 Compare November 6, 2025 19:19

nihalzp reviewed Nov 7, 2025

View reviewed changes

0xgouda added 3 commits November 7, 2025 15:15

Add hyperlinks to Float64 type page

c0c5147

Add comment to explain why we shouldn't use prelimits if there is a f…

0fc9e59

…ractional limit/offset.

Don't enter executePreLimit() if there is a fractional limit/offset

7bd3b4a

- add additinoal `getLimimtLengthAndOffset()` parsing step to ensure fractional limit and offset are zero before adding a prelimit.

0xgouda force-pushed the add-fractional-limit-offset branch from c458be7 to 7bd3b4a Compare November 7, 2025 13:49

nihalzp approved these changes Nov 7, 2025

View reviewed changes

docs/en/sql-reference/statements/select/limit.md Show resolved Hide resolved

docs/en/sql-reference/statements/select/limit.md Show resolved Hide resolved

docs/en/sql-reference/statements/select/offset.md Show resolved Hide resolved

Add note about rounding up the fractional limit/offset calculation re…

635e83e

…sult.

0xgouda force-pushed the add-fractional-limit-offset branch from 2dfeda3 to 635e83e Compare November 7, 2025 15:51

nihalzp added 2 commits November 8, 2025 11:40

Merge remote-tracking branch 'upstream/master' into add-fractional-li…

4756023

…mit-offset

Rerun CI

f3ca2bc

0xgouda force-pushed the add-fractional-limit-offset branch from f073ef0 to f3ca2bc Compare November 8, 2025 07:22

nihalzp enabled auto-merge November 8, 2025 12:20

nihalzp disabled auto-merge November 8, 2025 12:23

nihalzp enabled auto-merge November 8, 2025 12:40

nihalzp added this pull request to the merge queue Nov 8, 2025

Merged via the queue into ClickHouse:master with commit f24b4ec Nov 8, 2025
126 of 131 checks passed

robot-ch-test-poll1 added the pr-synced-to-cloud The PR is synced to the cloud repo label Nov 8, 2025

0xgouda mentioned this pull request Nov 30, 2025

Optimize Fractional Limit/Offset memory usage #91167

Merged

1 task

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add support for Fractional limits and offsets#88755

Add support for Fractional limits and offsets#88755
nihalzp merged 121 commits intoClickHouse:masterfrom
0xgouda:add-fractional-limit-offset

0xgouda commented Oct 17, 2025 •

edited by fm4v

Loading

Uh oh!

nihalzp left a comment •

edited

Loading

Uh oh!

nihalzp commented Nov 7, 2025

Uh oh!

0xgouda commented Nov 7, 2025

Uh oh!

0xgouda commented Nov 7, 2025

Uh oh!

nihalzp commented Nov 7, 2025

Uh oh!

nihalzp commented Nov 7, 2025

Uh oh!

nihalzp left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

nihalzp commented Nov 8, 2025 •

edited

Loading

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

Conversation

0xgouda commented Oct 17, 2025 • edited by fm4v Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Changelog category (leave one):

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

Documentation entry for user-facing changes

Details

Uh oh!

nihalzp left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

nihalzp commented Nov 7, 2025

Uh oh!

0xgouda commented Nov 7, 2025

Uh oh!

0xgouda commented Nov 7, 2025

Uh oh!

nihalzp commented Nov 7, 2025

Uh oh!

nihalzp commented Nov 7, 2025

Uh oh!

nihalzp left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

nihalzp commented Nov 8, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

0xgouda commented Oct 17, 2025 •

edited by fm4v

Loading

nihalzp left a comment •

edited

Loading

nihalzp commented Nov 8, 2025 •

edited

Loading