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)
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: all
• OS: Any
• Assigned to: Tor Didriksen
• CPU Architecture: Any
• Tags: between_and, BIGINT, longlong

[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] MySQL Verification Team

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.