[MDEV-12044] Unsigned bigint overflow problem when using an index Created: 2017-02-10  Updated: 2021-11-03

Status: Confirmed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.0.21, 10.1.19, 10.2.3
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Claudio Nanni Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: bigint, unsigned

Attachments: Text File test-case-unsigned-bug.txt    

 Description   

When using an index a query may present the following strange behaviour:

1) A constant value is wrongly returned

2) Results are not found
2.b) Using >= =< instead of BETWEEN returns the results, but still constant is wrongly returned

3) ERROR 1690 (22003): BIGINT value is out of range is not consistent

- select count(*),9223372036854775807+4 from t1  where b>1;  ERROR
 
- select count(*),9223372036854775808+4 from t1  where b>1;  OK

And I think there are many other related side effects.

Test case attached.

 Comments   
Comment by Elena Stepanova [ 2017-02-13 ]

Constant value wrongly returned:

MariaDB [test]> select (0xffffffffffffffff DIV 8) *5-1;
 
+---------------------------------+
 
| (0xffffffffffffffff DIV 8) *5-1 |
 
+---------------------------------+
 
|            11529215046068469754 |
 
+---------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select count(*), (0xffffffffffffffff DIV 8)*4, (0xffffffffffffffff DIV 8) *5-1 from t1 
where b between 9223372036854775804 and 11529215046068469754;
 
+----------+------------------------------+---------------------------------+
 
| count(*) | (0xffffffffffffffff DIV 8)*4 | (0xffffffffffffffff DIV 8) *5-1 |
 
+----------+------------------------------+---------------------------------+
 
|        0 |          9223372036854775804 |             9223372036854775807 |
 
+----------+------------------------------+---------------------------------+
 
1 row in set (0.05 sec)

Results are not found:

MariaDB [test]> select count(*) from t1 where b between 9223372036854775804 and 11529215046068469754;
 
+----------+
 
| count(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.04 sec)
 
 
 
MariaDB [test]> select count(*) from t1 where b > 9223372036854775804 and b < 11529215046068469754;
 
+----------+
 
| count(*) |
 
+----------+
 
|    12421 |
 
+----------+
 
1 row in set (0.05 sec)

Comment by Alexander Barkov [ 2017-06-14 ]

A smaller script demonstrating the problem.
I create a populate a table:

CREATE OR REPLACE TABLE t1 (
 
  b bigint(20) unsigned PRIMARY KEY
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19);
 
INSERT INTO t1 VALUES (9223372036854775804+1),(9223372036854775804+2);

Problem N1

This query returns a correct result:

SELECT count(*), (0xffffffffffffffff DIV 8)*4, (0xffffffffffffffff DIV 8) *5-1 FROM t1;


+----------+------------------------------+---------------------------------+
 
| count(*) | (0xffffffffffffffff DIV 8)*4 | (0xffffffffffffffff DIV 8) *5-1 |
 
+----------+------------------------------+---------------------------------+
 
|       12 |          9223372036854775804 |            11529215046068469754 |
 
+----------+------------------------------+---------------------------------+

Now I run the same query with a WHERE condition added, it returns a wrong result for the last column:

SELECT count(*), (0xffffffffffffffff DIV 8)*4, (0xffffffffffffffff DIV 8) *5-1 FROM t1 
WHERE b BETWEEN 9223372036854775804 AND 11529215046068469754;


+----------+------------------------------+---------------------------------+
 
| count(*) | (0xffffffffffffffff DIV 8)*4 | (0xffffffffffffffff DIV 8) *5-1 |
 
+----------+------------------------------+---------------------------------+
 
|        0 |          9223372036854775804 |             9223372036854775807 |
 
+----------+------------------------------+---------------------------------+

Problem N2

This query returns a correct result:

SELECT count(*) FROM t1 WHERE b > 9223372036854775804 AND b < 11529215046068469754;


+----------+
 
| count(*) |
 
+----------+
 
|        2 |
 
+----------+

Now if I change the WHERE condition to use an equivalent BETWEEN predicate, it erroneously returns 0:

SELECT count(*) FROM t1 WHERE b BETWEEN 9223372036854775804 AND 11529215046068469754;


+----------+
 
| count(*) |
 
+----------+
 
|        0 |
 
+----------+

Problem N3

This query correctly returns a result:

SELECT count(*),9223372036854775808+4 FROM t1 WHERE b>1;


+----------+-----------------------+
 
| count(*) | 9223372036854775808+4 |
 
+----------+-----------------------+
 
|       12 |   9223372036854775807 |
 
+----------+-----------------------+

This query unexpectedly returns an error:

SELECT count(*),9223372036854775807+4 FROM t1 WHERE b>1;


ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 4)'

Generated at Thu Feb 08 07:54:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.