error result when update bigint unsigned column to -1

[exialin]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

create table t (a bigint unsigned);
insert into t value (1);
update t set a = -1;

2. What did you expect to see?

mysql> insert into t value (1);
Query OK, 1 row affected (0.07 sec)

mysql> update t set a = -1;
ERROR 1264 (22003): Out of range value for column 'a' at row 1

3. What did you see instead?

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

mysql> update t set a = -1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----------------------+
| a                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.01 sec)

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-294-g6e2d6c7aa
Git Commit Hash: 6e2d6c7aa7eba3ac4f3a5201f1a36bf534fa6298
Git Branch: master
UTC Build Time: 2018-12-08 05:24:33
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @qw4990

[exialin]

This issue is similar to issue #6360, but the cause is different.

When evaluating the constant here:

tidb/executor/update.go

Line 215 in 6e2d6c7

val, err := assign.Expr.Eval(e.evalBuffer.ToRow())

val is a type types.Datum with val.k = 2 and val.i = -1, which means -1 is treated as KindUint64. So it goes to ConvertUintToUint instead of ConvertIntToUint, and it is the same as update t set a = 18446744073709551615;. I think here val.k should be 1, then it falls into the same routine as issue #6360.

[shenli]

@exialin Thanks for your feedback! Would you mind send a PR to fix it?

[exialin]

@shenli No problem.

[ghost]

I am not sure if it is a regression, but I can still reproduce this:

drop table if exists t;
create table t (a bigint unsigned);
insert into t value (1);
update t set a = -1;
select * from t;
select tidb_version()\G

...



mysql> update t set a = -1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-745-g2b0b34b88
Edition: Community
Git Commit Hash: 2b0b34b88e43ad20f4e5ab1a0b5daf7ae6ff6046
Git Branch: master
UTC Build Time: 2020-07-09 10:12:32
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)

[erwadba]

@nullnotnil
The bug was already fixed on tidb v4.0.6. I use tiup playground test like below.

MySQL [test]> drop table if exists t;
Query OK, 0 rows affected (0.19 sec)

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

MySQL [test]> insert into t value (1);
Query OK, 1 row affected (0.01 sec)

MySQL [test]> update t set a = -1;
ERROR 1264 (22003): Out of range value for column 'a' at row 1
MySQL [test]> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.6
Edition: Community
Git Commit Hash: 51d365fc45fdfc039eb204a96268c5bd1c55075f
Git Branch: heads/refs/tags/v4.0.6
UTC Build Time: 2020-09-15 09:50:30
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)

MySQL [test]> 

[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

v4.0.6

[ti-srebot]

( AffectedVersions ) fields are empty.
The values in ( FixedVersions ) fields are incorrect.