bug: UNSIGNED bigint data, return incorrect result set

[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> SELECT t1.c4, t2.c4 FROM st1 AS t1 INNER JOIN st2 t2 ON t1.c4=t2.c4 ORDER BY t1.c4;
+----------------------+---------------------+
| c4                   | c4                  |
+----------------------+---------------------+
| -9223372036854775806 | 9223372036854775810 |
|  9223372036854775806 | 9223372036854775806 |
+----------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT t1.c4, t2.c4 FROM st1 AS t1 LEFT JOIN st2 t2 ON t1.c4=t2.c4 ORDER BY t1.c4;
+----------------------+---------------------+
| c4                   | c4                  |
+----------------------+---------------------+
| -9223372036854775806 | 9223372036854775810 |
|  9223372036854775806 | 9223372036854775806 |
+----------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT t1.c4, t2.c4 FROM st1 AS t1 RIGHT JOIN st2 t2 ON t1.c4=t2.c4 ORDER BY t2.c4;
+----------------------+---------------------+
| c4                   | c4                  |
+----------------------+---------------------+
| -9223372036854775806 | 9223372036854775810 |
|                 NULL | 9223372036854775805 |
|  9223372036854775806 | 9223372036854775806 |
+----------------------+---------------------+
3 rows in set (0.00 sec)

Expected behavior

mysql> SELECT t1.c4, t2.c4 FROM st1 AS t1 INNER JOIN st2 t2 ON t1.c4=t2.c4 ORDER BY t1.c4;
+---------------------+---------------------+
| c4                  | c4                  |
+---------------------+---------------------+
| 9223372036854775806 | 9223372036854775806 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT t1.c4, t2.c4 FROM st1 AS t1 LEFT JOIN st2 t2 ON t1.c4=t2.c4 ORDER BY t1.c4;
+----------------------+---------------------+
| c4                   | c4                  |
+----------------------+---------------------+
| -9223372036854775806 |                NULL |
|  9223372036854775806 | 9223372036854775806 |
+----------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT t1.c4, t2.c4 FROM st1 AS t1 RIGHT JOIN st2 t2 ON t1.c4=t2.c4 ORDER BY t2.c4;
+---------------------+---------------------+
| c4                  | c4                  |
+---------------------+---------------------+
|                NULL | 9223372036854775805 |
| 9223372036854775806 | 9223372036854775806 |
|                NULL | 9223372036854775810 |
+---------------------+---------------------+

How To Reproduce

CREATE TABLE st1 (c1 TINYINT DEFAULT NULL, 
                       c2 SMALLINT DEFAULT NULL, 
                       c3 INT DEFAULT NULL, 
                       c4 BIGINT DEFAULT NULL
);
CREATE TABLE st2 (c1 TINYINT UNSIGNED DEFAULT NULL, 
                       c2 SMALLINT UNSIGNED DEFAULT NULL, 
                       c3 INT UNSIGNED DEFAULT NULL, 
                       c4 BIGINT UNSIGNED DEFAULT NULL
);
INSERT INTO st1 (c1, c2, c3, c4) VALUES (-126, -32766, -2147483646, -9223372036854775806);
INSERT INTO st2 (c1, c2, c3, c4) VALUES (130, 32770, 2147483650, 9223372036854775810);
INSERT INTO st1 (c1, c2, c3, c4) VALUES (127, 32766, 2147483646, 9223372036854775806);
INSERT INTO st2 (c1, c2, c3, c4) VALUES (127, 32766, 2147483646, 9223372036854775806);
INSERT INTO st2 (c1, c2, c3, c4) VALUES (125, 32765, 2147483645, 9223372036854775805);

SELECT t1.c4, t2.c4 FROM st1 AS t1 INNER JOIN st2 t2 ON t1.c4=t2.c4 ORDER BY t1.c4;
SELECT t1.c4, t2.c4 FROM st1 AS t1 LEFT JOIN st2 t2 ON t1.c4=t2.c4 ORDER BY t1.c4;
SELECT t1.c4, t2.c4 FROM st1 AS t1 RIGHT JOIN st2 t2 ON t1.c4=t2.c4 ORDER BY t2.c4;

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/hustjieke/stonedb.git:feat_unsigned_support
        Branch name: feat_unsigned_support
        Last commit ID: 2fb620d93
        Last commit time: Date:   Thu Jan 5 12:51:05 2023 +0000
        Build time: Date: Fri Jan  6 18:16:48 CST 2023
root@ub01:~# cat /etc/issue
Ubuntu 20.04.5 LTS \n \l

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

• Yes, I will!

[hustjieke]

Simplify the sql:

mysql> create table big(a bigint default null, b bigint unsigned default null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into big values(-9223372036854775806, 9223372036854775810);
Query OK, 1 row affected (0.01 sec)

mysql> select * from big;
+----------------------+---------------------+
| a                    | b                   |
+----------------------+---------------------+
| -9223372036854775806 | 9223372036854775810 |
+----------------------+---------------------+
1 row in set (0.01 sec)

mysql> select * from big where a=b;
+----------------------+---------------------+
| a                    | b                   |
+----------------------+---------------------+
| -9223372036854775806 | 9223372036854775810 |
+----------------------+---------------------+
1 row in set (0.01 sec)

[hustjieke]

The range of unsigned data had been limited to signed type, so this problem should check again after we fully support unsigned types.

[hustjieke]

Related to #1266
ping @davidshiz