search

smi2 / phpClickHouse

php ClickHouse wrapper
MIT License
750 stars 139 forks source link

CountAll() giving different values for same query #186

Closed ElGabbu closed 1 year ago

ElGabbu commented 1 year ago

We are running a fairly simple query:

SELECT service_name, table_name, event_id, event_date, action_type, `updated_values.field`, `updated_values.old_value`, `updated_values.new_value`, context_user_id, context_sapi, context_user_app, pg_session_user_name FROM change_tracking WHERE ((service_name = 'user' AND table_name = 'users' AND `primary_keys.value` = ['fbe7762b-028d-481a-8ff4-cc2d4a4247f9'] ) OR (service_name = 'user' AND table_name = 'user_address' AND `primary_keys.value` = ['cb0a23c1-5570-4e1a-91a4-d44adb042a30'] )) LIMIT 5 OFFSET 0 FORMAT JSON

However we are getting different different values from the countAll function.

ElGabbu commented 1 year ago

Update, I have run the query with another client directly against the clickhouse server and noticed that the property rows_before_limit_at_least (which is read by the countAll function) changes sporadically.

The behaviour seems related to the OR part of the query

ElGabbu commented 1 year ago

So turns out that this is by design from clickhouse. It is however possible to enforce that the rows_before_limit_at_least has the exact number of records by apply the setting exact_rows_before_limit=1