Clickhouse: TTL doen't work

[qietingfengling]

Which ClickHouse server version to use
19.11.5.28

Which interface to use, if matters
clickhouse-client

Non-default settings, if any
default settings in place

CREATE TABLE :

CREATE TABLE test_database."test_table" (date Date, timestamp UInt64, String name) ENGINE = ReplicatedMergeTree('xxx', 'xxx') PARTITION BY (toDayOfYear(toDateTime(timestamp)),toHour(toDateTime(timestamp))) ORDER BY (name,timestamp)  TTL date + INTERVAL 10 DAY;

result

select date from "test_table" group by date;

┌───────date─┐
│ 2019-09-28 │
│ 2019-09-29 │
│ 2019-09-30 │
│ 2019-10-01 │
│ 2019-10-02 │
│ 2019-10-03 │
│ 2019-10-04 │
│ 2019-10-05 │
└────────────┘

select count(1) from system.parts where active and database='test_database' and toDate(modification_time)='2019-11-11' limit 10 

┌─count(1)─┐
│      317 │
└──────────┘

select count(1) from system.parts where active and database='test_database' and toDate(modification_time)='2019-10-10' limit 10 

┌─count(1)─┐
│        0 │
└──────────┘

[honmaple]

Confirm it. If partition key is created as toDayOfYear(timestamp), the TTL would be failure. But toYYYYMM(timestamp) will success.

My test code:

CREATE DATABASE IF NOT EXISTS test_database;

CREATE TABLE IF NOT EXISTS test_database.test_table (
    timestamp DateTime,
    test      UInt32)
    ENGINE = MergeTree()
    PARTITION BY toYYYYMM(timestamp)
    ORDER BY (timestamp)
    TTL timestamp + INTERVAL 3 DAY
    SETTINGS storage_policy = 'moving_from_ssd_to_hdd', merge_with_ttl_timeout = 60;

CREATE TABLE IF NOT EXISTS test_database.test_table1 (
    timestamp DateTime,
    test      UInt32)
    ENGINE = MergeTree()
    PARTITION BY toDayOfYear(timestamp)
    ORDER BY (timestamp)
    TTL timestamp + INTERVAL 3 DAY
    SETTINGS storage_policy = 'moving_from_ssd_to_hdd', merge_with_ttl_timeout = 60;

INSERT INTO test_database.test_table
    SELECT
        NOW() - INTERVAL number HOUR,
        rand()
    FROM numbers(1000);

INSERT INTO test_database.test_table1
    SELECT
        NOW() - INTERVAL number HOUR,
        rand()
    FROM numbers(1000);

SELECT * FROM test_database.test_table LIMIT 3;
SELECT * FROM test_database.test_table1 LIMIT 3;

[vvchistiakov]

It looks like the bug for me too.
I have few tables with

PARTITION BY toMonday(timestamp)
TTL timestamp + toIntervalDay(90)

And, the old data is not deleted also =(
My version is 19.14.7.15

[alex-zaitsev]

@vvchistiakov TTLs has been significantly improved since 19.14. Please try with 19.16.16 or 20.1.x release.

Also look at merge tree settings related to TTLs:

select * from system.merge_tree_settings where name like '%ttl%';

[alexey-milovidov]

@CurtizJ Could you please clarify the status of this issue?