generated columns do not wrap unsigned values to signed

[morgo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a BIGINT UNSIGNED NOT NULL, b BIGINT AS (a) NOT NULL);
SELECT cast(9223372036854775808 as signed); # works; no warnings or errors
INSERT INTO t1 (a) VALUES (9223372036854775808); # error in TiDB, works in MySQL
SELECT * FROM t1;

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

mysql [localhost:8022] {msandbox} (test) > DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8022] {msandbox} (test) > CREATE TABLE t1 (a BIGINT UNSIGNED NOT NULL, b BIGINT AS (a) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8022] {msandbox} (test) > SELECT cast(9223372036854775808 as signed); # works; no warnings or errors
+-------------------------------------+
| cast(9223372036854775808 as signed) |
+-------------------------------------+
|                -9223372036854775808 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8022] {msandbox} (test) > INSERT INTO t1 (a) VALUES (9223372036854775808); # error in TiDB, works in MySQL
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8022] {msandbox} (test) > SELECT * FROM t1;
+---------------------+----------------------+
| a                   | b                    |
+---------------------+----------------------+
| 9223372036854775808 | -9223372036854775808 |
+---------------------+----------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (a BIGINT UNSIGNED NOT NULL, b BIGINT AS (a) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT cast(9223372036854775808 as signed); # works; no warnings or errors
+-------------------------------------+
| cast(9223372036854775808 as signed) |
+-------------------------------------+
|                -9223372036854775808 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 (a) VALUES (9223372036854775808); # error in TiDB, works in MySQL
ERROR 1690 (22003): constant 9223372036854775808 overflows bigint
mysql> SELECT * FROM t1;
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-1770-g51fbf415c
Edition: Community
Git Commit Hash: 51fbf415c904ad77b2a4c29fbfb3ff513ed60f8c
Git Branch: master
UTC Build Time: 2020-12-09 14:19:36
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[morgo]

I figured out how to permit the insert and correctly read back the value with:

+++ b/executor/insert_common.go
@@ -599,8 +599,8 @@ func (e *InsertValues) fillRow(ctx context.Context, row []types.Datum, hasValue
                if e.handleErr(gCol, &val, 0, err) != nil {
                        return nil, err
                }
-               row[colIdx], err = table.CastValue(e.ctx, val, gCol.ToInfo(), false, false)
-               if err != nil {
+               row[colIdx], err = table.CastValue(e.ctx, val, gCol.ToInfo(), true, false)
+               if err != nil && !types.ErrOverflow.Equal(err) { // gcols permit overflow errors
                        return nil, err
                }
                // Handle the bad null error.

But there is another bug with generated columns. The value generated is incorrect:

mysql> SELECT * FROM t1;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 9223372036854775808 | 9223372036854775807 |
+---------------------+---------------------+
1 row in set, 1 warning (0.00 sec)

[wjhuang2016]

I think it's a bug in MySQL:

mysql> CREATE TABLE t1 (a BIGINT UNSIGNED NOT NULL, b INT AS (a) NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 (a) VALUES (9223372036854775808);
ERROR 1264 (22003): Out of range value for column 'b' at row 1

MySQL fails to check the overflow when the generated column is BIT INT, but check it for INT.
If the sql_mode is empty, MySQL would round to the upper limit:

mysql> set sql_mode="";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 (a) VALUES (9223372036854775808);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t1;
+---------------------+------------+
| a                   | b          |
+---------------------+------------+
| 9223372036854775808 | 2147483647 |
+---------------------+------------+
1 row in set (0.00 sec)

[wjhuang2016]

Interesting in MySQL:

mysql> CREATE TABLE t2 (a INT UNSIGNED NOT NULL, b INT AS (a) NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t2 (a) VALUES (4294967295);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+------------+----+
| a          | b  |
+------------+----+
| 4294967295 | -1 |
+------------+----+
1 row in set (0.00 sec)

mysql> INSERT INTO t2 (a) VALUES (4294967296);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

[morgo]

Yeah, I think you are right - I will report it as a MySQL bug tomorrow and see what they think.

[XuHuaiyu]

@morgo If this is a bug in MySQL, we can close this issue, or remove the bug label and add a compatibility label for it.

[morgo]

This is filed as https://bugs.mysql.com/bug.php?id=101976 - I will close this issue as soon as the MySQL bug is marked verified.

[morgo]

Alright, it's been verified as a MySQL bug. Closing this issue.