pingcap / pingcap/tidb

COUNT vs SUM inconsistency under utf8mb4_general_ci collation

Open
#64,372 1 comment 1 reaction 0 assignees View on GitHub
contribution first-time-contributor sig/execution type/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.