Description:
When a BIGINT UNSIGNED column is compared against a negative value inside an IN (...) list, MySQL correctly determines the comparison can never be true and returns no matching rows — this holds both for a plain negative literal and for CAST(<literal> AS SIGNED) applied to a literal, since both are constant-foldable at optimization time.
However, when the same negative value is instead produced by CAST((<scalar subquery>) AS SIGNED) — i.e. a non-constant expression that MySQL cannot fold at optimization time, even though it deterministically evaluates to the identical value — the optimizer skips whatever logic prevents an unsigned column from matching a negative constant, and the value appears to be reinterpreted using its two's-complement bit pattern as an unsigned value instead. This causes a row to incorrectly match.
In the reproduction below, the table contains a single row with a = 18446744073709551615 (0xFFFFFFFFFFFFFFFF), which is exactly the two's-complement unsigned representation of -1.
How to repeat:
CREATE TABLE t1 (a BIGINT UNSIGNED);
INSERT INTO t1 VALUES (18446744073709551615);
-- Correct: 0 rows (constant, folded at optimize time)
SELECT DISTINCT * FROM t1 WHERE a IN (-1, -2);
-- Correct: 0 rows (CAST of a literal is still constant-foldable)
SELECT DISTINCT * FROM t1 WHERE a IN (-CAST(1 AS SIGNED), -2);
-- Incorrect: returns 1 row, should also return 0
SELECT DISTINCT * FROM t1
WHERE a IN (-CAST((SELECT COUNT(*) FROM (SELECT ST_ASTEXT(ST_CONVEXHULL(
ST_GEOMFROMTEXT('MULTILINESTRING((0 10,10 0),(10 0,0 0),(0 0,10 10))')
)) AS col1) AS ssub) AS SIGNED), -2);
The subquery deterministically evaluates to 1 (verified independently via SELECT COUNT(*) FROM (...)), so -CAST((subquery) AS SIGNED) is numerically identical to -1 in all three queries above
Expected result: All three queries should return 0 rows
Description: When a BIGINT UNSIGNED column is compared against a negative value inside an IN (...) list, MySQL correctly determines the comparison can never be true and returns no matching rows — this holds both for a plain negative literal and for CAST(<literal> AS SIGNED) applied to a literal, since both are constant-foldable at optimization time. However, when the same negative value is instead produced by CAST((<scalar subquery>) AS SIGNED) — i.e. a non-constant expression that MySQL cannot fold at optimization time, even though it deterministically evaluates to the identical value — the optimizer skips whatever logic prevents an unsigned column from matching a negative constant, and the value appears to be reinterpreted using its two's-complement bit pattern as an unsigned value instead. This causes a row to incorrectly match. In the reproduction below, the table contains a single row with a = 18446744073709551615 (0xFFFFFFFFFFFFFFFF), which is exactly the two's-complement unsigned representation of -1. How to repeat: CREATE TABLE t1 (a BIGINT UNSIGNED); INSERT INTO t1 VALUES (18446744073709551615); -- Correct: 0 rows (constant, folded at optimize time) SELECT DISTINCT * FROM t1 WHERE a IN (-1, -2); -- Correct: 0 rows (CAST of a literal is still constant-foldable) SELECT DISTINCT * FROM t1 WHERE a IN (-CAST(1 AS SIGNED), -2); -- Incorrect: returns 1 row, should also return 0 SELECT DISTINCT * FROM t1 WHERE a IN (-CAST((SELECT COUNT(*) FROM (SELECT ST_ASTEXT(ST_CONVEXHULL( ST_GEOMFROMTEXT('MULTILINESTRING((0 10,10 0),(10 0,0 0),(0 0,10 10))') )) AS col1) AS ssub) AS SIGNED), -2); The subquery deterministically evaluates to 1 (verified independently via SELECT COUNT(*) FROM (...)), so -CAST((subquery) AS SIGNED) is numerically identical to -1 in all three queries above Expected result: All three queries should return 0 rows