Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
13.1
-
None
-
Not for Release Notes
Description
Item_func_xxh3::val_int() feeds the argument through Item::hash_val_str() (the collation-aware interface designed for GROUP BY/DISTINCT grouping), so the hash is computed over collation sortkeys rather than raw string bytes. Under a case-insensitive collation, 'ABC' and 'abc' have identical sortkeys and therefore identical hashes (XXH3('ABC') = XXH3('abc') returns 1). Hash-based equality lookups on ci columns falsely match case-variant rows (demo: WHERE XXH3(v) = XXH3('abc') matches 3 of 4 rows abc/ABC/Abc). MD5/SHA2 in the same scenario behave correctly (byte semantics), confirming this is an XXH implementation error, not intended behavior.
SELECT XXH3('ABC') = XXH3('abc'); -- 1 (should be 0) |
SELECT XXH32('ABC') = XXH32('abc'); -- 1 (should be 0) |
|
|
-- User scenario: hash equality lookup on a ci column
|
CREATE TABLE uh(v VARCHAR(10) COLLATE utf8mb4_uca1400_ai_ci); |
INSERT INTO uh VALUES ('abc'),('ABC'),('Abc'),('abd'); |
SELECT COUNT(*) FROM uh WHERE XXH3(v) = XXH3('abc'); |
-- Returns 3 (abc/ABC/Abc all match; hash equality lookup semantics completely wrong) |
-- Reference: SELECT COUNT(*) FROM uh WHERE MD5(v) = MD5('abc'); -- 1 (correct) |
|
|
SELECT XXH3('abc') AS plain; -- 5167207402487786768 (utf8mb4) |
SELECT XXH3(_latin1'abc') AS latin1; -- 2615927343983396622 |
SELECT XXH3(BINARY 'abc') AS binary_; -- 8696274497037089104 |
-- All three have identical byte content (HEX 616263), yet three hash values |
|
|
-- User scenario: cross-collation hash JOIN
|
CREATE TABLE uh2(v VARCHAR(10) COLLATE utf8mb4_bin); |
INSERT INTO uh2 VALUES ('abc'); |
SELECT COUNT(*) FROM uh a JOIN uh2 b ON XXH3(a.v) = XXH3(b.v);
|
-- Returns 0 (both tables contain 'abc', JOIN matches 0 rows — silent data loss) |
-- Reference: MD5(BINARY'abc')=MD5('abc') and MD5(_latin1'abc')=MD5('abc') both hold |