Description:
With a `utf8mb4` table containing `London` and `Paris`, run `SET NAMES binary` and compare `WHERE city = 'London' AND city = 'london'` with `WHERE CASE WHEN xxx THEN city = 'London' AND city = 'london' ELSE city = 'London' AND city = 'london' END`. The first query returns 0 rows, but the second returns `London`. Both `CASE` branches contain the same predicate, so both queries should return 0 rows.
How to repeat:
Run the following statements in the same MySQL connection:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (city CHAR(30)) CHARACTER SET=utf8mb4;
INSERT INTO t1 VALUES ('London'), ('Paris');
SET NAMES binary;
-- Actual: 0 rows.
SELECT DISTINCT *
FROM t1
WHERE city = 'London' AND city = 'london';
-- Actual: 1 row.
SELECT DISTINCT *
FROM t1
WHERE CASE
WHEN (SELECT count(@@collation_connection)) THEN city = 'London' AND city = 'london'
ELSE city = 'London' AND city = 'london'
END;
Expected result
Both queries should return 0 rows. The CASE expression uses the same predicate in both branches, so it should not change which rows satisfy WHERE. MySQL documents CASE WHEN as returning the selected branch’s result.
Actual result
The first query returns 0 rows; the second returns 1 row containing London. In the supplied mysql client output, that value appears as 0x4C6F6E646F6E. The mismatch reproduces only when SET NAMES binary is included;
Description: With a `utf8mb4` table containing `London` and `Paris`, run `SET NAMES binary` and compare `WHERE city = 'London' AND city = 'london'` with `WHERE CASE WHEN xxx THEN city = 'London' AND city = 'london' ELSE city = 'London' AND city = 'london' END`. The first query returns 0 rows, but the second returns `London`. Both `CASE` branches contain the same predicate, so both queries should return 0 rows. How to repeat: Run the following statements in the same MySQL connection: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (city CHAR(30)) CHARACTER SET=utf8mb4; INSERT INTO t1 VALUES ('London'), ('Paris'); SET NAMES binary; -- Actual: 0 rows. SELECT DISTINCT * FROM t1 WHERE city = 'London' AND city = 'london'; -- Actual: 1 row. SELECT DISTINCT * FROM t1 WHERE CASE WHEN (SELECT count(@@collation_connection)) THEN city = 'London' AND city = 'london' ELSE city = 'London' AND city = 'london' END; Expected result Both queries should return 0 rows. The CASE expression uses the same predicate in both branches, so it should not change which rows satisfy WHERE. MySQL documents CASE WHEN as returning the selected branch’s result. Actual result The first query returns 0 rows; the second returns 1 row containing London. In the supplied mysql client output, that value appears as 0x4C6F6E646F6E. The mismatch reproduces only when SET NAMES binary is included;