COUNT vs SUM inconsistency under utf8mb4_general_ci collation
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
# Bug Report
Please answer these questions before submitting your issue. Thanks!
## 1. Minimal reproduce step (Required)
1. Run the following SQL statements in TiDB:
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;
SELECT SUM(count)
FROM (
SELECT CAST((v0.c1 >= v0.c0) IS TRUE AS UNSIGNED) AS count
FROM v0
) AS asdf;
2. Observe the outputs of the final two queries.
## 2. What did you expect to see? (Required)
Because `utf8mb4_general_ci` is case-insensitive, the comparison `'a' >= 'B'` should evaluate to false, so both queries should return `0`.
## 3. What did you see instead (Required)
`SELECT COUNT(*) ...` returns `0`, but `SELECT SUM(count) ...` returns `1`, meaning the same boolean expression yields different results under different aggregate queries.
## 4. What is your TiDB version? (Required)
ma ster
Contributor guide
Assessment
This issue has not been assessed yet.