Clickhouse: Unexepected query results because of corrupt table

[bartpeeters]

Table structure:

CREATE TABLE IF NOT EXISTS movement
(
  `time` DateTime DEFAULT now() CODEC(DoubleDelta, ZSTD),
  `configuration_id` UInt16 CODEC(ZSTD),
  `node_id` UInt16 CODEC(ZSTD)
)
ENGINE = MergeTree()
ORDER BY (configuration_id, lamp_id, time);

Query 1:

select configuration_id, count(*) from movement where configuration_id=67 group by configuration_id;
Result: 
configuration_id 67, count() 614 226

Query 2:

select configuration_id, count(*) from movement group by configuration_id;
Result contains:
configuration_id 67, count() 1 376 373

Same thing happens when doing min(time):
Query 1 2020-01-23 00:02:52
Query 2 2020-01-07 11:41:59

After creating a copy of this table using:

CREATE TABLE IF NOT EXISTS movement3
(
  `time` DateTime DEFAULT now() CODEC(DoubleDelta, ZSTD),
  `configuration_id` UInt16 CODEC(ZSTD),
  `lamp_id` UInt16 CODEC(ZSTD)
)
ENGINE = MergeTree()
ORDER BY (configuration_id, lamp_id, time)
insert into movement3 select time, configuration_id, lamp_id from movement;

I do get the same results for query 1 and 2. Does this mean that the original table is in a broken state?

If so, how did this happen and how can I detect and fix this?

[den-crane]

CH Version?

What shows:
select configuration_id, count(*) from movement
where identity(configuration_id)=67
group by configuration_id;

[bartpeeters]

Clickhouse version: 19.17.7.52
The query:

select configuration_id, count(*) from movement
where identity(configuration_id)=67
group by configuration_id;

Does return the expected configuration_id 67, count() 1 376 373

[den-crane]

Please try one more -- toUInt16(67)

select configuration_id, count(*) from movement
where configuration_id=toUInt16(67)
group by configuration_id;

[bartpeeters]

Above query using toUInt16(67) returns the incorrect value:
configuration_id 67, count() 614 226

[den-crane]

Have you run mutations (alter delete) against this table?

[bartpeeters]

Yes, a lot of alter delete's, even doing another alter delete before the first one is done. No updates though.

[den-crane]

Can you show a couple examples of delete?

[bartpeeters]

They are all of the same type:
alter table movement delete where configuration_id = x and time < y

[alesapin]

Maybe you can provide data (parts) from the broken table? You can do it privately if you send them to our mailing list [email protected].

[bartpeeters]

I sent the data to your mailing list.

[alesapin]

Reproduced the issue. But found more serious one https://github.com/ClickHouse/ClickHouse/issues/9034.

[alesapin]

The bug seems to be fixed in all supported versions (20.1, 20.3, 20.4, etc).