Inconsistent Results Between COUNT and SUM with Common Logical Conditions in SQL Queries

[akuluasan]

Bug Report

The two SQL queries have a common sub-clause:v0.c1 >= v0.c0 OR v0.c1 LIKE 'z%'

Both queries essentially evaluate the same logical conditions. The first query directly counts the number of rows meeting the conditions, while the second query counts the number of TRUE evaluations from a subquery. If a row meets either condition, it is counted as 1 in the second query and is included in the count in the first query. Therefore, both statements should yield the same result, as they are counting the same set of rows based on the same conditions.

1. Minimal reproduce step (Required)

drop table  if exists t0;
drop view  if exists v0;
CREATE TABLE t0(c0 VARCHAR(255) COLLATE utf8mb4_general_ci);
INSERT INTO t0(c0) VALUES ('B');
CREATE VIEW v0(c0, c1) AS SELECT DISTINCT t0.c0, 'a' FROM t0;

SELECT COUNT(*) FROM v0 WHERE v0.c1 >= v0.c0 OR v0.c1 LIKE 'z%';
SELECT SUM(count) FROM (SELECT (v0.c1 >= v0.c0 OR v0.c1 LIKE 'z%') AS count FROM v0) AS asdf;

2. What did you expect to see? (Required)

return the same result.

3. What did you see instead (Required)

SELECT COUNT(*) FROM v0 WHERE v0.c1 >= v0.c0 OR v0.c1 LIKE 'z%';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

SELECT SUM(count) FROM (SELECT (v0.c1 >= v0.c0 OR v0.c1 LIKE 'z%') AS count FROM v0) AS asdf;
+------------+
| SUM(count) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v7.5.1 |
+--------------------+
1 row in set (0.00 sec)

[jebter]

The default configuration cluster of 7.5.1 failed to reproduce the issue. Could you provide the cluster settings?

TiDB root@127.0.0.1:test> SELECT COUNT(*) FROM v0 WHERE v0.c1 >= v0.c0 OR v0.c1 LIKE 'z%';
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

1 row in set
Time: 0.015s
TiDB root@127.0.0.1:test> SELECT SUM(count) FROM (SELECT (v0.c1 >= v0.c0 OR v0.c1 LIKE 'z%') AS count FROM v0) AS asdf;
+------------+
| SUM(count) |
+------------+
| 0          |
+------------+

1 row in set
Time: 0.008s
TiDB root@127.0.0.1:test> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v7.5.1 |
+--------------------+

[akuluasan]

This is the result I got from executing "show config;". Is this what you need? show config.txt