IFNULL produce different result with MySQL on bigint column with unsigned flag

[imtbkcat]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

MySQL [test]> create table t(x bigint unsigned);
Query OK, 0 rows affected (0.006 sec)

MySQL [test]> insert into t values( 9999999703771440633);
Query OK, 1 row affected (0.001 sec)

MySQL [test]> select ifnull(max(x), 0) from t
    -> ;
+----------------------+
| ifnull(max(x), 0)    |
+----------------------+
| -8446744369938110983 |
+----------------------+
1 row in set (0.001 sec)

2. What did you expect to see? (Required)

result should be 9999999703771440633

3. What did you see instead (Required)

-8446744369938110983

4. Affected version (Required)

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-rc.2-35-g8ab455575
Edition: Community
Git Commit Hash: 8ab45557504c922b240ab1aed56e66dc566c71be
Git Branch: release-4.0
UTC Build Time: 2020-06-03 09:30:35
GoVersion: go1.13.9
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

5. Root Cause Analysis

[zz-jason]

@shuke987 this happens only when the inserted data overflows the max value of signed int64, how about lower the severity to moderate or minor?

TiDB(root@127.0.0.1:test) > create table t(x bigint unsigned);
Query OK, 0 rows affected (0.01 sec)

TiDB(root@127.0.0.1:test) > insert into t values(9999999703771440633);
Query OK, 1 row affected (0.00 sec)

TiDB(root@127.0.0.1:test) > insert into t values(9223372036854775807);
Query OK, 1 row affected (0.00 sec)

TiDB(root@127.0.0.1:test) > select ifnull(max(x), 0) from t;
+----------------------+
| ifnull(max(x), 0)    |
+----------------------+
| -8446744369938110983 |
+----------------------+
1 row in set (0.01 sec)

TiDB(root@127.0.0.1:test) > select ifnull(x, 0) from t;
+----------------------+
| ifnull(x, 0)         |
+----------------------+
| -8446744369938110983 |
|  9223372036854775807 |
+----------------------+
2 rows in set (0.01 sec)

[XuHuaiyu]

Can be closed by
#21062

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions