Confusing behavior happened in UPDATE statement

[where2me]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 INT);
INSERT INTO t0 VALUES(1);
UPDATE t0 SET c0 = 2 WHERE CAST(CAST(0.4 AS CHAR)^1 AS CHAR);

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

I think there is a data truncate error in UPDATE statement, and UPDATE should fail.

3. What did you see instead (Required)

UPDATE succeeded without throwing an error.

mysql> UPDATE t0 SET c0 = 2 WHERE CAST(CAST(0.4 AS CHAR)^1 AS CHAR);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()

                                                                   |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.4.0
Edition: Community
Git Commit Hash: 55f3b24
Git Branch: heads/refs/tags/v5.4.0
UTC Build Time: 2022-01-25 08:39:26
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[aytrack]

mysql 8.0.28 and tidb with tikv hava the same result, tidb use unistore will report a truncate error.

mysql root@172.16.4.131:test> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set
Time: 0.009s
mysql root@172.16.4.131:test> UPDATE t0 SET c0 = 2 WHERE CAST(CAST(0.4 AS CHAR)^1 AS CHAR);
Query OK, 0 rows affected
Time: 0.003s
mysql root@172.16.4.131:test> select * from t0;
+----+
| c0 |
+----+
| 2  |
+----+

tidb with tikv

MySQL root@127.0.0.1:test> UPDATE t0 SET c0 = 2 WHERE CAST(CAST(0.4 AS CHAR)^1 AS CHAR);
Query OK, 0 rows affected
Time: 0.002s
MySQL root@127.0.0.1:test> select * from t0;
+----+
| c0 |
+----+
| 2  |
+----+

tidb without tikv

MySQL root@127.0.0.1:test>  UPDATE t0 SET c0 = 2 WHERE CAST(CAST(0.4 AS CHAR)^1 AS CHAR);
                       ->
(1292, "Truncated incorrect INTEGER value: '0.4'")
MySQL root@127.0.0.1:test>

[aytrack]

the expression of both tidb and mysql have the same results.

MySQL root@172.16.4.131:test> select CAST(CAST(0.4 AS CHAR)^1 AS CHAR);
+-----------------------------------+
| CAST(CAST(0.4 AS CHAR)^1 AS CHAR) |
+-----------------------------------+
| 1                                 |
+-----------------------------------+