Description:
Under utf16_unicode_ci, the numeric patterns 10 and CAST(0 AS SIGNED) + 10 evaluate to the same value. However, substituting the computed expression changes the result of a NOT (a LIKE ...) filter. The discrepancy can be reproduced with one indexed integer column and a session collation change.
How to repeat:
Run each statement in the same MySQL session:
-- Setup: table created.
CREATE TABLE t1 (a INT, INDEX (a));
INSERT INTO t1 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
SET collation_connection = utf16_unicode_ci;
-- Actual: 1 row; verify the two LIKE values in the affected session.
SELECT a, a LIKE 10 AS literal_like, a LIKE (CAST(0 AS SIGNED) + 10) AS computed_like FROM t1 WHERE a = 10;
-- Actual: 10 rows; a = 10 is excluded.
SELECT a FROM t1 WHERE a BETWEEN 5 AND 15 AND NOT (a LIKE 10) ORDER BY a;
-- Actual: 11 rows, including a = 10. Expected: 10 rows, identical to the preceding query.
SELECT a FROM t1 WHERE a BETWEEN 5 AND 15 AND NOT (a LIKE (CAST(0 AS SIGNED) + 10)) ORDER BY a;
-- Actual: 1 row (a = 10). Expected: 0 rows.
SELECT a FROM t1 WHERE a = 10 AND NOT (a LIKE (CAST(0 AS SIGNED) + 10));
Expected result
Both range queries return the same 10 rows. The final query returns no rows.
Actual result
With utf16_unicode_ci, the computed-pattern query returns a different result from the literal-pattern query, despite both patterns having the numeric value 10. MySQL documents NOT LIKE as the negation of LIKE; the collation setting also changes the connection character set used in numeric-to-string conversion
Without SET collation_connection = utf16_unicode_ci, all four SELECT statements return the expected results; the discrepancy appears after this session setting is applied.
