A forbidden cast 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 '1.0' << 1;

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

The UPDATE statement should fail, because cast '1.0' into an INTEGER is not allowable in MySQL.

3. What did you see instead (Required)

UPDATE succeeded.

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]

tidb without tikv will report error

MySQL root@127.0.0.1:test> DROP TABLE IF EXISTS t0;
                       ->  CREATE TABLE t0 (c0 INT);
                       ->  INSERT INTO t0 VALUES(1);
                       ->  UPDATE t0 SET c0 = 2 WHERE '1.0' << 1;
Query OK, 0 rows affected
Time: 0.002s

Query OK, 0 rows affected
Time: 0.012s

Query OK, 1 row affected
Time: 0.001s
(1292, "Truncated incorrect INTEGER value: '1.0'")

a cluster with tikv will execute success

MySQL root@127.0.0.1:test> DROP TABLE IF EXISTS t0;
                       ->  CREATE TABLE t0 (c0 INT);
                       ->  INSERT INTO t0 VALUES(1);
                       ->  UPDATE t0 SET c0 = 2 WHERE '1.0' << 1;
Query OK, 0 rows affected
Time: 0.002s

Query OK, 0 rows affected
Time: 0.126s

Query OK, 1 row affected
Time: 0.005s

Query OK, 1 row affected
Time: 0.002s
MySQL root@127.0.0.1:test> select * from t0;
+----+
| c0 |
+----+
| 2  |
+----+
MySQL root@127.0.0.1:test> explain UPDATE t0 SET c0 = 2 WHERE '1.0' << 1;
                       ->
+-----------------------+----------+-----------+---------------+---------------------------------------------+
| id                    | estRows  | task      | access object | operator info                               |
+-----------------------+----------+-----------+---------------+---------------------------------------------+
| Update_4              | N/A      | root      |               | N/A                                         |
| └─TableReader_8       | 8000.00  | root      |               | data:Selection_7                            |
|   └─Selection_7       | 8000.00  | cop[tikv] |               | leftshift(cast("1.0", bigint(3) BINARY), 1) |
|     └─TableFullScan_6 | 10000.00 | cop[tikv] | table:t0      | keep order:false, stats:pseudo              |
+-----------------------+----------+-----------+---------------+---------------------------------------------+

MySQL root@127.0.0.1:test> select '1.0' << 1;
+------------+
| '1.0' << 1 |
+------------+
| 2          |
+------------+

will success on both tidb and mysql