Partitions for some MySQL tables are not migrated to ClickHouse

[Selfeer]

When creating a table in MySQL with the following structure, in some cases it seems that the partition is not migrated to the ClickHouse.

For example if we create a partitioned table in MySQL,

mysql> CREATE TABLE test3 (
    ->     order_id INT AUTO_INCREMENT,
    ->     product_name VARCHAR(255),
    ->     quantity INT,
    ->     order_date DATE,
    ->     PRIMARY KEY (order_id, order_date)
    -> ) 
    -> ENGINE = InnoDB
    -> PARTITION BY RANGE( YEAR(order_date) ) (
    ->     PARTITION p2020 VALUES LESS THAN (2021),
    ->     PARTITION p2021 VALUES LESS THAN (2022),
    ->     PARTITION p2022 VALUES LESS THAN (2023),
    ->     PARTITION p2023 VALUES LESS THAN (2024),
    ->     PARTITION p2024 VALUES LESS THAN (2025)
    -> );

In ClickHouse we get a table that is not PARTITIONED BY

SHOW CREATE TABLE test3;

CREATE TABLE test.test3
(
    `order_id` Int32,
    `product_name` Nullable(String),
    `quantity` Nullable(Int32),
    `order_date` Date32,
    `_sign` Int8,
    `_version` UInt64
)
ENGINE = ReplacingMergeTree(_version)
PRIMARY KEY (order_id, order_date)
ORDER BY (order_id, order_date)
SETTINGS index_granularity = 8192

[Selfeer]

Related to: https://github.com/Altinity/clickhouse-sink-connector/issues/332

[aadant]

@Selfeer actually it would work if you use range columns which is recommended in MySQL

[Selfeer]

@aadant but shouldn't it still work in the given example?

[aadant]

The partitioning support is limited to the RANGE COLUMN case with date / datetime. Any other scheme is skipped. It should be easy to support though. You can start with the python code if you wish to contribute or just alter this table.

YEAR(order_date) can return NULL, there is no future partition. MySQL will probably scan the first partition as a result …