Inconsistent count results

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: count 结果不一致

| username: GreenGuan

Today, while investigating an issue where a compute node’s CPU was fully utilized, I logged into the node to check the slow logs and found a strange problem: the results of count(*) and count(digest) were inconsistent. Has anyone encountered this before?

TiDB version v5.4.2

image
image1339×341 13.4 KB

2
| username: h5n1 | Original post link

Take a look at the execution plans of the two using EXPLAIN ANALYZE.

3
| username: Kongdom | Original post link

I have the same issue here. The execution plan is the same, so it should be a syntax problem. According to Baidu, there is a difference between count(*) and count(col) in terms of data retrieval.

4
| username: songxuecheng | Original post link

count(1)

5
| username: Kongdom | Original post link

count(1) and count(*) are the same.

6
| username: 胡杨树旁 | Original post link

count(*) is not querying the total number of rows in the table. It should be more than the count(digest), right? Why is it showing less? Have I always misunderstood?

7
| username: Kongdom | Original post link

I also found inconsistencies on my end, but count(*) is greater than count(digest).

8
| username: buddyyuan | Original post link

Check if it’s this issue

9
| username: Kongdom | Original post link

:+1: :+1: :+1: This should be it.

10
| username: forever | Original post link

Is this column a non-null column?

11
| username: Kongdom | Original post link

Checked, there are no null or empty values.

12
| username: forever | Original post link

Then that’s not the issue. count(*) will include null values, while count(col) does not count nulls, resulting in fewer data :smile:

13
| username: GreenGuan | Original post link

Now it always feels unreliable to perform aggregation operations like group by, count, and sum on the slow_query table.

14
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.