BIGINT out of range error code/message incorrect

[exialin]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

Record two cases remain unresolved in PR #8544 . Here is some analysis #8544 (comment)

create table t(a bigint unsigned);
insert into t values (18446744073709551616);
insert into t value (18446744073709554616);

set sql_mode='';
insert into t value (-9223372036854775809);
insert into t value (-18446744073709551615);

2. What did you expect to see?

MySQL:

mysql> create table t(a bigint unsigned);
Query OK, 0 rows affected (0.54 sec)

-- Case 1
mysql> insert into t values (18446744073709551616);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into t values (18446744073709554616);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

-- Case 2
mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values (-9223372036854775809);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> insert into t values (-18446744073709551615);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> select * from t;
+------+
| a    |
+------+
|    0 |
|    0 |
+------+
2 rows in set (0.00 sec)

3. What did you see instead?

TiDB:

tidb> create table t(a bigint unsigned);
Query OK, 0 rows affected (0.02 sec)

-- Case 1
tidb> insert into t values (18446744073709551616);
Query OK, 1 row affected (0.00 sec)

tidb> select * from t;
+---------------------+
| a                   |
+---------------------+
| 9223372036854775808 |
+---------------------+
1 row in set (0.00 sec)

tidb> insert into t values (18446744073709554616);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

-- Case 2
tidb> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

tidb> insert into t values (-9223372036854775809);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
tidb> insert into t values (-18446744073709551615);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

mysql> select tidb_version() \G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.0-rc.3-456-g8ac79f3f6-dirty
Git Commit Hash: 8ac79f3f6e3b1dfd97c02b124256dd613a7284fa
Git Branch: master
UTC Build Time: 2019-01-13 02:21:58
GoVersion: go version go1.11.4 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

[XuHuaiyu]

hi, @exialin
Thanks for your help.

• For Case 1:
  As you mentioned in the comment, 18446744073709551616 is regarded as the type MyDecimal when be read from the client. And TiDB calls convertFloatToUint to do the type converting. But I'm not sure when will this happen: Due to precision loss of float, it doesn't cause an overflow error.
• For Case 2:
  Can we check the error and the SQL_MODE in InsertValues.handleErr to fix this problem? The usage of OverflowAsWarning may help you understand what I mean.

[exialin]

@XuHuaiyu

• For Case 1:
  When the literal value is slightly larger than MaxUint64, namely in range [18446744073709551616, 18446744073709553664] (manually tested). Precision loss of float seems unavoidable in Golang. (Did I understand your question?)

• For Case 2:
  Thanks for the hint. I'll check it.

[XuHuaiyu]

@exialin
For Case 1:
I think maybe we should implement function ConvertMyDecimalToInt/ConvertMyDecimalToUint to solve this.
For now, we call ConvertFloat64ToInt/Uint when the type is Decimal or Float, but it will cause precision loss and further cause wrong result.

@jackysp What do you think about this issue?

[XuHuaiyu]

After discussed with @jackysp , we may fix Case 1 as I mentioned in this comment.

[ghost]

This is now fixed in master. The warning messages differ between MySQL and TiDB:

set sql_mode=DEFAULT;
drop table if exists t;
create table t(a bigint unsigned);
insert into t values (18446744073709551616);
insert into t value (18446744073709554616);

set sql_mode='';
insert into t value (-9223372036854775809);
SHOW WARNINGS;
insert into t value (-18446744073709551615);
SHOW WARNINGS;

SELECT * FROM t;

..

mysql> insert into t value (-9223372036854775809);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1690 | constant -9223372036854775809 overflows bigint |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t value (-18446744073709551615);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1690 | constant -18446744073709551615 overflows bigint |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT * FROM t;
+------+
| a    |
+------+
|    0 |
|    0 |
+------+
2 rows in set (0.00 sec)

Vs MySQL:

mysql [localhost:5731] {msandbox} (test) > insert into t value (-9223372036854775809);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql [localhost:5731] {msandbox} (test) > SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:5731] {msandbox} (test) > insert into t value (-18446744073709551615);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql [localhost:5731] {msandbox} (test) > SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:5731] {msandbox} (test) > 
mysql [localhost:5731] {msandbox} (test) > SELECT * FROM t;
+------+
| a    |
+------+
|    0 |
|    0 |
+------+
2 rows in set (0.00 sec)

[ghost]

Note: the errors actually match between TiDB and MySQL, but the warnings differ. Because the warnings only occur with a non-default sql-mode, I think this one is lower priority.