Bug #70622 error result when use between...and againest bigint unsigned
Submitted: 15 Oct 2013 10:33 Modified: 4 Mar 2014 16:07
Reporter: xiaobin lin (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:all OS:Any
Assigned to: Tor Didriksen CPU Architecture:Any
Tags: between_and, BIGINT, longlong
Triage: Needs Triage: D2 (Serious)

[15 Oct 2013 10:33] xiaobin lin
Description:
Inside function longlong Item_func_between::val_int(), when the type is int,the input value is treate as longlong. If the column type is bigint unsigned, some results seems buggy.

How to repeat:
drop table if exists t;
create table t (id bigint unsigned, b int);

insert into t values(8894754949779693574,1);
insert into t values(8894754949779693579,2);
insert into t values(17790886498483827171,3);

mysql> select count(*) from t where id>=8894754949779693574 and id <=17790886498483827171;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t where id between 8894754949779693574 and   17790886498483827171;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Suggested fix:
Check the unsigned_flag of the column and use ulonglong if necessary. Code in the patch file.
[15 Oct 2013 10:33] xiaobin lin
Based on 5.6.14

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: between_func_compare.diff (application/octet-stream, text), 3.61 KiB.

[15 Oct 2013 11:40] Miguel Solorzano
Thank you for the bug report. Verified as described.
[6 Feb 2014 15:12] Ståle Deraas
Hi Xiaobin Lin, Thank you for your contribution. It will be included in a future release.
[4 Mar 2014 16:07] Paul Dubois
Noted in 5.7.4 changelog.

Some BETWEEN expressions on unsigned values were evaluated using
signed arithmetic. Thanks to Xiaobin Lin for the patch.