Bug #95957 IN operator issue when comparing signed column and the column cast to unsigned
| Submitted: | 24 Jun 2019 16:26 | Modified: | 25 Jun 2019 5:13 |
|---|---|---|---|
| Reporter: | Manuel Rigger | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.16, 5.7.26, 5.6.44 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | x86 |
[25 Jun 2019 5:13]
MySQL Verification Team
Hello Manuel Rigger, Thank you for the report. regards, Umesh
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.046 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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