Description:
When comparing a signed with an unsigned integer in an IN operator, the comparison unexpectedly yields true.
How to repeat:
CREATE TABLE t0(c0 INT);
INSERT INTO t0(c0) VALUES(-1);
SELECT t0.c0 IN (1, CAST(t0.c0 AS UNSIGNED)) from t0; -- expected: 0, actual: 1
According to the documentation, the conversion should work in the same way for the binary comparison operators, "but applied to all the arguments". As expected, a binary equality comparison yields 0:
SELECT t0.c0 = CAST(t0.c0 AS UNSIGNED) FROM t0; -- 0
It seems that more than one argument of any type (including another unsigned value) can be used to trigger the bug:
SELECT t0.c0 IN (NULL, CAST(t0.c0 AS UNSIGNED)) from t0; -- expected: NULL, actual: 1
SELECT t0.c0 IN (CAST(0 AS UNSIGNED), CAST(t0.c0 AS UNSIGNED)) from t0; -- expected: 0, actual: 1
SELECT t0.c0 IN ("asdf", CAST(t0.c0 AS UNSIGNED)) from t0; -- expected: 0, actual: 1
When removing the first argument, the operation behaves as expected:
SELECT t0.c0 IN (CAST(t0.c0 AS UNSIGNED)) from t0; -- 0
This also seems to work as expected when using constants rather than column references:
SELECT -1 IN (1, CAST(-1 AS UNSIGNED)) from t0; -- 0
Description: When comparing a signed with an unsigned integer in an IN operator, the comparison unexpectedly yields true. How to repeat: CREATE TABLE t0(c0 INT); INSERT INTO t0(c0) VALUES(-1); SELECT t0.c0 IN (1, CAST(t0.c0 AS UNSIGNED)) from t0; -- expected: 0, actual: 1 According to the documentation, the conversion should work in the same way for the binary comparison operators, "but applied to all the arguments". As expected, a binary equality comparison yields 0: SELECT t0.c0 = CAST(t0.c0 AS UNSIGNED) FROM t0; -- 0 It seems that more than one argument of any type (including another unsigned value) can be used to trigger the bug: SELECT t0.c0 IN (NULL, CAST(t0.c0 AS UNSIGNED)) from t0; -- expected: NULL, actual: 1 SELECT t0.c0 IN (CAST(0 AS UNSIGNED), CAST(t0.c0 AS UNSIGNED)) from t0; -- expected: 0, actual: 1 SELECT t0.c0 IN ("asdf", CAST(t0.c0 AS UNSIGNED)) from t0; -- expected: 0, actual: 1 When removing the first argument, the operation behaves as expected: SELECT t0.c0 IN (CAST(t0.c0 AS UNSIGNED)) from t0; -- 0 This also seems to work as expected when using constants rather than column references: SELECT -1 IN (1, CAST(-1 AS UNSIGNED)) from t0; -- 0