The query result of the distributed table groupBitmapOr is inconsistent with the expected result

[kingruning]

Company or project name

No response

Question

Hello, could you please help me see why the same SQL query results are different on different nodes( the result of shard 1, replica 1 is 10000000, but the result of shard 2, replica 1 is 0). The SQL is as follows: SELECT groupBitmapOr(user_code)　from　user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion　where　tag_name = 'tag1'　and dt='2024-06-01'

In addition, using subqueries can result in correct results, but will performance become slower. The SQL is as follows SELECT groupBitmapOr(user_code) from (select user_code FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion　WHERE (tag_name = 'tag1') AND (dt = '2024-06-01'))

Detailed records are as follows: clickhouse version: 21.8.12.29 clickhouse-01-01: shard 1, replica 1 clickhouse-02-01: shard 2, replica 1

clickhouse-01-01 :) select bitmapCardinality(user_code)　from　user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion　where　tag_name = 'tag1'　and dt='2024-06-01'

SELECT bitmapCardinality(user_code) FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion WHERE (tag_name = 'tag1') AND (dt = '2024-06-01')

Query id: 4a72d136-acc4-408f-b6db-b31cbb754cfe

┌─bitmapCardinality(user_code)─┐ │ 1000037 │ │ 1000496 │ │ 999968 │ │ 1000890 │ │ 1000063 │ │ 999782 │ │ 999603 │ │ 998788 │ │ 1001269 │ │ 999104 │ └──────────────────────────────┘

10 rows in set. Elapsed: 0.024 sec.

clickhouse-01-01 :) select　groupBitmapOr(user_code)　from　user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion　where　tag_name = 'tag1'　and dt='2024-06-01'

SELECT groupBitmapOr(user_code) FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion WHERE (tag_name = 'tag1') AND (dt = '2024-06-01')

Query id: 29e2dd4b-6ea4-457b-92df-809603787247

┌─groupBitmapOr(user_code)─┐ │ 10000000 │ └──────────────────────────┘

1 rows in set. Elapsed: 0.025 sec.

clickhouse-02-01 :) SELECT bitmapCardinality(user_code)　FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion　WHERE (tag_name = 'tag1') AND (dt = '2024-06-01')

SELECT bitmapCardinality(user_code) FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion WHERE (tag_name = 'tag1') AND (dt = '2024-06-01')

Query id: cfa977fa-0661-49fc-a41d-9cf8a7e7cddc

┌─bitmapCardinality(user_code)─┐ │ 1000037 │ │ 1000496 │ │ 999968 │ │ 1000890 │ │ 1000063 │ │ 999782 │ │ 999603 │ │ 998788 │ │ 1001269 │ │ 999104 │ └──────────────────────────────┘

10 rows in set. Elapsed: 0.028 sec.

clickhouse-02-01 :) SELECT groupBitmapOr(user_code)　FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion　WHERE (tag_name = 'tag1') AND (dt = '2024-06-01')

SELECT groupBitmapOr(user_code) FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion WHERE (tag_name = 'tag1') AND (dt = '2024-06-01')

Query id: 1e88fc0d-be7e-4458-97ac-08e2aa3c6c4a

┌─groupBitmapOr(user_code)─┐ │ 0 │ └──────────────────────────┘

1 rows in set. Elapsed: 0.028 sec.

clickhouse-02-01 :) SELECT groupBitmapOr(user_code) from (select user_code FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion　WHERE (tag_name = 'tag1') AND (dt = '2024-06-01'))

SELECT groupBitmapOr(user_code) FROM ( SELECT user_code FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion WHERE (tag_name = 'tag1') AND (dt = '2024-06-01') )

Query id: 0864b5c0-061c-49fe-96af-d2593fa47093

┌─groupBitmapOr(user_code)─┐ │ 10000000 │ └──────────────────────────┘

1 rows in set. Elapsed: 0.132 sec.

[yariks5s]

Hi, @kingruning!

Firstly you can try to synchronise replicas manually:

SYSTEM SYNC REPLICA user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion;

Run this on all replicas and see if the results become consistent.

Please ensure the data ingestion process correctly handles data distribution and replication. If there are any failures or inconsistencies during data ingestion, it could lead to such issues.

Also, good to check this problem on the newer versions of ClickHouse as the version you're using is quite old + it would be better to have a working repro of this problem if this problem is still on newer versions of ClickHouse

[kingruning]

We have upgraded to 23.12.5.81, The above statement is fine, but there are new issues. The results obtained through these three methods are different(groupBitmapMerge, groupBitmapOr() + distributed table subqueries, groupBitmapOr()) The results of the execution are as follows:

1. SELECT groupBitmapMerge(user_code) FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion WHERE (tag_name = 'tag3') AND ((dt >= '2024-01-01') AND (dt <= '2024-03-30')) AND (float_tag_value > 500000) ┌─groupBitmapMerge(user_code)─┐ │ 5102282 │ └─────────────────────────────┘

2. SELECT groupBitmapOr(user_code) FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion WHERE (tag_name = 'tag3') AND ((dt >= '2024-01-01') AND (dt <= '2024-03-30')) AND (float_tag_value > 500000) ┌─groupBitmapOr(user_code)─┐ │ 5102153 │ └──────────────────────────┘

3. SELECT groupBitmapOr(user_code) FROM ( SELECT user_code FROM user_profile_bitmap_data.new_tag_bitmap_table_all_withCollapseVersion WHERE (tag_name = 'tag3') AND ((dt >= '2024-01-01') AND (dt <= '2024-03-30')) AND (float_tag_value > 500000) ) ┌─groupBitmapOr(user_code)─┐ │ 5102282 │ └──────────────────────────┘ Is there a bug in executing groupBitmapOr in a distributed table

[yariks5s]

1. Please ensure data is consistently replicated and distributed across all nodes. Use SYSTEM SYNC REPLICA to synchronize replicas.

2. Are results of second query execution consistent or change every time?

The execution plan for groupBitmapOr directly on the distributed table versus using a subquery might be different, causing variations in intermediate aggregation steps.