bug: insert bigint data close to the boundary range, the result set is wrong

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

mysql> CREATE TABLE int8_tbl(q1 int8, q2 int8);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO int8_tbl VALUES('4567890123456789','123');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO int8_tbl VALUES ('9223372036854775807','9223372036854775806');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO int8_tbl VALUES ('-9223372036854775805','-9223372036854775806');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM int8_tbl;
+----------------------+----------------------+
| q1                   | q2                   |
+----------------------+----------------------+
|     4567890123456789 |                  123 |
|  9223372036854775807 |  9223372036854775806 |
| -9223372036854775805 | -9223372036854775806 |
+----------------------+----------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM int8_tbl WHERE q2 < 4567890123456789;
Empty set (0.00 sec)

Expected behavior

mysql> SELECT * FROM int8_tbl WHERE q2 < 4567890123456789;
+----------------------+----------------------+
| q1                   | q2                   |
+----------------------+----------------------+
|     4567890123456789 |                  123 |
| -9223372036854775805 | -9223372036854775806 |
+----------------------+----------------------+
2 rows in set (0.00 sec)

How To Reproduce

CREATE TABLE int8_tbl(q1 int8, q2 int8);
INSERT INTO int8_tbl VALUES('4567890123456789','123');
INSERT INTO int8_tbl VALUES ('9223372036854775807','9223372036854775806');
INSERT INTO int8_tbl VALUES ('-9223372036854775805','-9223372036854775806');
SELECT * FROM int8_tbl WHERE q2 < 4567890123456789;

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB-v1.0.2 for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 0f4250589
        Last commit time: Date:   Tue Feb 21 06:09:37 2023 +0000
        Build time: Date: Wed Feb 22 17:50:51 CST 2023

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[RingsC]

@davidshiz, pls, give out the resul of select * from int8_tbl . Thanks.

[davidshiz]

@davidshiz, pls, give out the resul of select * from int8_tbl . Thanks.

ok, It has been updated, view the above content

[hustjieke]

It seems like the similar problem with #1344

[hustjieke]

Simplify the sql:

mysql> create table t(a int8);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t where a < 3;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into t values(-9223372036854775806);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t where a < 3;
+----------------------+
| a                    |
+----------------------+
|                    1 |
| -9223372036854775806 |
+----------------------+
2 rows in set (0.00 sec)

mysql> insert into t values(9223372036854775806);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t where a < 3;
Empty set (0.00 sec)

[hustjieke]

error msg should be [-9223372036854775806, 9223372036854775807]:

[davidshiz]

same question

mysql> select * from int8_tbl;
+---------------------+---------------------+
| q1                  | q2                  |
+---------------------+---------------------+
| 9223372036854775807 | 9223372036854775806 |
|                NULL |                NULL |
|                 123 |                 456 |
|                 123 |    4567890123456789 |
|    4567890123456789 |                 123 |
|    4567890123456789 |    4567890123456789 |
|    4567890123456789 |   -4567890123456789 |
+---------------------+---------------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM int8_tbl WHERE q2 >= 4567890123456789;
Empty set (0.00 sec)

[El-even-11]

another similar problem

mysql> select * from int8_tbl;
+----------------------+----------------------+
| q1                   | q2                   |
+----------------------+----------------------+
| -9223372036854775805 | -9223372036854775806 |
+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> select * from int8_tbl where q2 = -9223372036854775807;
+----------------------+----------------------+
| q1                   | q2                   |
+----------------------+----------------------+
| -9223372036854775805 | -9223372036854775806 |
+----------------------+----------------------+
1 row in set (0.00 sec)