OpenMLDB icon indicating copy to clipboard operation
OpenMLDB copied to clipboard

Resolve the inconsistency problem of distinct count in offline mode

Open tobegit3hub opened this issue 2 years ago • 1 comments

Bug Description

Now we may get the inconsistent result for SQL with groupby.

tobegit3hub avatar Nov 27 '23 02:11 tobegit3hub

the SQL that can reproduce the incorrect result is

select
    reqId as reqId_42,
    distinct_count(`is_slry`) over bo_detail_new_user_id_ingestionTime_0s_5529601s_100 as bo_detail_is_slry_multi_unique_count_44,
from
    (select `eventTime` as `ingestionTime`, `new_user_id` as `new_user_id`, bigint(0) as `trx_ts`, "" as `trx_typ`, double(0) as `trx_amt`, "" as `is_slry`, reqId from `flattenRequest`)
    window bo_detail_new_user_id_ingestionTime_0s_5529601s_100 as (
UNION (select `ingestionTime`, `new_user_id`, `trx_ts`, `trx_typ`, `trx_amt`, `is_slry`, "" as reqId from `bo_detail`) partition by `new_user_id` order by `ingestionTime` rows_range between 5529600999 preceding and 0s preceding MAXSIZE 100 INSTANCE_NOT_IN_WINDOW)

lumianph avatar Jan 31 '24 07:01 lumianph