I'm trying to understand how is working the ReplacingMergeTree engine.
I have the following table configured with such engine.
┌─brand─┬─country─┬─id─────┬────updated─┬─version─┐
│ IM │ FR │ 1 │ 2017-09-29 │ 2 │
│ IM │ FR │ 2 │ 2017-09-29 │ 0 │
│ IM │ FR │ 3 │ 2017-09-29 │ 1 │
└───────┴─────────┴────────┴────────────┴─────────┘
At this point everything is ok.
Then I execute the following INSERT.
INSERT INTO table(brand, country, id, updated, version) VALUES ('IM', 'FR', 1, '2017-10-29', 3);
As expected, there are 2 rows with id 1:
┌─brand─┬─country─┬─id─────┬────updated─┬─version─┐
│ IM │ FR │ 1 │ 2017-09-29 │ 2 │
│ IM │ FR │ 2 │ 2017-09-29 │ 0 │
│ IM │ FR │ 3 │ 2017-09-29 │ 1 │
└───────┴─────────┴────────┴────────────┴─────────┘
┌─brand─┬─country─┬─id─────┬────updated─┬─version─┐
│ IM │ FR │ 1 │ 2017-10-29 │ 3 │
└───────┴─────────┴────────┴────────────┴─────────┘
As the primary key for this table is (brand, country, id), I would expect that a merge on this table would replace the row with id=1 having the lower version 2.
Triggering a merge with OPTIMIZE TABLE table to check that, it seems that it didn't work in this way, and that both rows are surprisingly kept.
┌─brand─┬─country─┬─id─────┬────updated─┬─version─┐
│ IM │ FR │ 1 │ 2017-10-29 │ 3 │
└───────┴─────────┴────────┴────────────┴─────────┘
┌─brand─┬─country─┬─id─────┬────updated─┬─version─┐
│ IM │ FR │ 1 │ 2017-09-29 │ 2 │
│ IM │ FR │ 2 │ 2017-09-29 │ 0 │
│ IM │ FR │ 3 │ 2017-09-29 │ 1 │
└───────┴─────────┴────────┴────────────┴─────────┘