MySQL Bugs: #79857: select cast(100 as unsigned) between 1 and -1 returns true

Bug #79857	select cast(100 as unsigned) between 1 and -1 returns true
Submitted:	6 Jan 2016 2:41	Modified:	19 Jan 2016 17:31
Reporter:	Su Dylan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.7.8, 5.7.10	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Output:
=======
mysql> select cast(100 as unsigned) between 1 and -1;
+----------------------------------------+
| cast(100 as unsigned) between 1 and -1 |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========

How to repeat:
select cast(100 as unsigned) between 1 and -1;

Suggested fix:
select cast(100 as unsigned) between 1 and -1 returns 0.

Hello Su Dylan,

Thank you for the report and test case.
Verified as described with 5.7.10.

Thanks,
Umesh

// regression - only 5.7 builds affected

// 5.7.10

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select cast(100 as unsigned) between 1 and -1;
+----------------------------------------+
| cast(100 as unsigned) between 1 and -1 |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql>

Noted in 5.8.0 changelog.

Some comparisons between unsigned values and negative upper limits
could return incorrect results (for example, CAST(100 AS UNSIGNED)
BETWEEN 1 AND -1).