ClickHouse / ClickHouse/ClickHouse

TTL on table does not work properly

Open
#94,550 0 comments 0 reactions 0 assignees View on GitHub
comp-ttl external unexpected behaviour
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Company or project name

_No response_

### Describe the unexpected behaviour

we have a table we call it fake_db.test_table_name, the table has 206863173626 rows in total, and total bytes is 3.56 TiB, total data_compressed_bytes is 3.52 TiB, total data_uncompressed_bytes is 115.56 TiB. we add ttl to the table, we expect to remove 1 day data, but seems it is going remove all data .

then we quickly stop the ttl by stop merge , and then remove ttl on the table .
and after serval days , we add ttl to the table and try to reproduce the issue, and we reproduced the issue : it removed more day's data than we expected .

while when we try to reproduce on another table ,and it can not be reproduced .

the value of dw_load_dt is DateTime, and format value is like '2020-07-22 10:30:11.000'

table schema is like below:
`create table fake_db.test_table_name on cluster 'fake_cluster'
(
xxx
...
`dw_load_dt` DateTime
...
)
ENGINE = ReplicatedMergeTree
PARTITION BY dt
ORDER BY (fake_order_by_list)
SETTINGS index_granularity = 8192, max_suspicious_broken_parts = 400`

table data size :

add ttl ddl :

`ALTER TABLE fake_db.test_table_name on cluster 'fake_cluster' MODIFY TTL dw_load_dt + toIntervalDay(2062);`

### Which ClickHouse versions are affected?

24.1.5

### How to reproduce

can not be reproduced on other tables

### Expected behavior

if dw_load_dt is 2020-07-22 00:30:00.000, and when we set ttl expression to dw_load_dt + toIntervalDay(2), then after 2020-07-24 00:30:00.000, all data with 2020-07-22 00:30:00.000 will be removed, and no data whose dw_load_dt bigger than 2020-07-22 00:30:00.000 will not be removed

### Error message and/or stacktrace

_No response_

### Additional context

_No response_

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.