windowFunnel `strict_increase` behaves incorrectly if there is chain with events with the repeating subchain
#52.844 aperta il 31 lug 2023
Metriche repository
- Star
- (47.419 stelle)
- Metriche merge PR
- (Merge medio 2g 2h) (1000 PR mergiate in 30 g)
Descrizione
Does it reproduce on recent release?
Yes, 26.2
How to reproduce https://fiddle.clickhouse.com/db111d9b-14d0-41bc-bf56-310b6f1fa0ec
SELECT
recording_id,
windowFunnel(100000, 'strict_increase')(time, event_type = 'page_visit0', event_type = 'page_visit1', event_type = 'page_visit2', event_type = 'page_visit3') AS level
FROM
(
SELECT
number AS recording_id,
event_type,
time
FROM numbers(1)
ARRAY JOIN
['page_visit0', 'page_visit1', 'page_visit2', 'page_visit3', 'page_visit1', 'page_visit2', 'page_visit3', 'page_visit1', 'page_visit2', 'page_visit3'] AS event_type,
[1, 2, 2, 2, 3, 3, 3, 4, 4, 4] AS time
)
GROUP BY recording_id
Result:
┌─recording_id─┬─level─┐
│ 0 │ 2 │
└──────────────┴───────┘
Expected:
┌─recording_id─┬─level─┐
│ 0 │ 4 │
└──────────────┴───────┘
ClickHouse should return 4, because it can take this chain: page_visit0 at 1, page_visit1 at 2, page_visit2 at 3, page_visit3 at 4
Additional context
Basically here:
We iterate through matched event_id, (which is position of cond in windowFunnel(ts, 0, 1, 2, 3) function) And check if previous cond (event_idx - 1) have been matched earlier, than we replace latest match of current event_id with new time.
events_timestamp[event_idx] = std::make_pair(first_timestamp, timestamp);
Which seems incorrect at least for strict_increase modifier
May be something like:
else if (events_timestamp[event_idx - 1].has_value() && !events_timestamp[event_idx].has_value())
Should be here instead (do not update current event_idx if it already exists)