Data truncation error occurs in update statement.

[yanghy233]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. schema.

create table t1(c1 CHAR(16));
insert into t1 values('G');

2. sql statement.

UPDATE t1 SET c1="b+F6rUuv?" WHERE (("-1433326829") LIKE (1909913287)) IN ((0), ((1.790079046E9)IS FALSE), (c1), (-1512320610));

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

MySQL and TIDB have different execution results in the above case.

The normal result in MySQL 8:

mysql> UPDATE t1 SET c1="b+F6rUuv?" WHERE (("-1433326829") LIKE (1909913287)) IN ((0), ((1.790079046E9)IS FALSE), (c1), (-1512320610));
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+-----------+
| c1        |
+-----------+
| b+F6rUuv? |
+-----------+
1 row in set (0.00 sec)

TiDB should also return the same result, but failed.

3. What did you see instead (Required)

In TiDB v8.1.1:

MySQL [test]> UPDATE t1 SET c1="b+F6rUuv?" WHERE (("-1433326829") LIKE (1909913287)) IN ((0), ((1.790079046E9)IS FALSE), (c1), (-1512320610));
ERROR 1292 (22007): Truncated incorrect INTEGER value: 'G'

Afterwards, I wondered if the error was caused by predicate, so I constructed the same predicate using a select statement.

The following result is correct:

MySQL [test]> select * from t1 WHERE (("-1433326829") LIKE (1909913287)) IN ((0), ((1.790079046E9)IS FALSE), (c1), (-1512320610));
+------+
| c1   |
+------+
| G    |
+------+
1 row in set, 1 warning (0.02 sec)

The predicate is the same, but the results of the select and update statements are different in TiDB.

4. What is your TiDB version? (Required)

Tidb v8.1.1

[yanghy233]

/label affects-8.1

[yanghy233]

Also affects TiDB v8.4
/label affects-8.4

[yibin87]

The select statement executes with a warning:
mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | evaluation failed: Truncated incorrect INTEGER value: 'G' |
+---------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)
That's the reason why update statement failed. For dml statement, in strict sql mode, any warnings will fail the whole statement execution.
MySQL doesn't report any warnings or errors for this case. However, tidb's behavior seems reasonable for this case.

[yibin87]

/severity moderate