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:
None 
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

[24 Jun 2019 16:26] Manuel Rigger
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
[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.