Describe the unexpected behaviour

Using ReplacingMergeTree, PREWHERE hides data from FINAL, such that row replacements are not actually applied if the replacing row is filtered out by PREWHERE first.

To me this is confusing because PREWHERE is mostly described as an optimization over WHERE, however WHERE behaves differently here.

How to reproduce

• Run any Clickhouse version between 18 and 21 (one random one I've tested with is 20.3.9.70)
• Run this script:

drop table if exists test;
create table test (id int, version int) engine = ReplacingMergeTree order by id;

insert into test values (1, 1);
insert into test values (1, 2);

select * from test final where id = 1 and version = 1;  -- empty result
select * from test final prewhere id = 1 and version = 1;  -- returns (1, 1)

Expected behavior

I expect two things:

• Both select should yield 0 rows, as the second insert shadows the first.
• More generally, those two queries should behave the same.

Instead the two queries yield different results.

Additional context

We've recently come across this behavior and it appears to exist for a long time already. I think we would be fine if ClickHouse devs decide to close as wontfix/works-as-intended and update docs instead.