Bug #121100 Merged `CAST(col AS CHAR) COLLATE utf8mb4_0900_bin` makes col = UPPER(col) return wrong result

• Submitted: 16 Aug 22:04
• Modified: 19 Aug 5:22
• Reporter: Junwen An
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.46, 8.4.11, 9.7.2
• OS: Linux
• CPU Architecture: ARM

[16 Aug 22:04] Junwen An

Description:
A merged `CAST(col AS CHAR) COLLATE utf8mb4_0900_bin` makes `col = UPPER(col)` true while `UPPER(col)` is a different string

How to repeat:
CREATE TABLE b (c_chr VARCHAR(255) COLLATE utf8mb4_0900_bin);
INSERT INTO b VALUES ('a');
CREATE VIEW t AS
  SELECT CAST(c_chr AS CHAR(255)) COLLATE utf8mb4_0900_bin AS c_chr FROM b;

SELECT c_chr,
       HEX(c_chr),
       UPPER(c_chr),
       HEX(UPPER(c_chr)),
       c_chr = UPPER(c_chr) AS eq_upper,   -- Expected 0, actual 1
       c_chr = 'A' AS eq_lit,              -- 0 on both sides (control)
       c_chr <= UPPER(c_chr) AS le_upper,  -- Expected 0, actual 1
       COLLATION(c_chr)
FROM t;
-- Expected: ('a','61','A','41', 0, 0, 0, utf8mb4_0900_bin)
-- Actual:   ('a','61','A','41', 1, 0, 1, utf8mb4_0900_bin)

However, the following is correct:
CREATE TABLE t (c_chr VARCHAR(255) COLLATE utf8mb4_0900_bin);
INSERT INTO t VALUES ('a');
SELECT c_chr = UPPER(c_chr) FROM t;  -- 0

[19 Aug 5:22] Chaithra Marsur Gopala Reddy

Hi Junwen An,

Thank you for the test case. Verified as described.