Confusing behavior in the 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(0);
UPDATE t0 SET c0 = 1 WHERE 0^('0.5');

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

The column c0 should not be updated, as the result of 0^('0.5') is 0.

3. What did you see instead (Required)

mysql> UPDATE t0 SET c0 = 1 WHERE 0^('0.5');
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.3.0
Edition: Community
Git Commit Hash: 4a1b2e9
Git Branch: heads/refs/tags/v5.3.0
UTC Build Time: 2021-11-24 13:32:39
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)

mysql>

[morgo]

I can't reproduce against master:

tidb> DROP TABLE IF EXISTS t0;
Query OK, 0 rows affected (0.01 sec)

tidb> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.00 sec)

tidb> INSERT INTO t0 VALUES(0);
Query OK, 1 row affected (0.00 sec)

tidb> UPDATE t0 SET c0 = 1 WHERE 0^('0.5');
ERROR 1292 (22007): Truncated incorrect INTEGER value: '0.5'
tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.5.0-alpha-26-ge249588f5-dirty
Edition: Community
Git Commit Hash: e249588f5bca9424aaa6b37a1f2c523cf97eb9d0
Git Branch: experimental-instance-vars
UTC Build Time: 2022-01-14 17:33:34
GoVersion: go1.16.9
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

It looks like this might be a duplicate of a bug that has been fixed.

[morgo]

I was mistaken, it can be reproduced against master. However, this can only be reproduced when using TiKV. against unistore there is an error and the UPDATE statement will not run.

[morgo]

Confirming that this expression is executed in the coprocessor:

tidb> explain UPDATE t0 SET c0 = 1 WHERE 0^('0.3');
+---------------------------+----------+-----------+---------------+------------------------------------------+
| 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] |               | bitxor(0, cast("0.3", bigint(3) BINARY)) |
|     └─TableFullScan_6     | 10000.00 | cop[tikv] | table:t0      | keep order:false, stats:pseudo           |
+---------------------------+----------+-----------+---------------+------------------------------------------+
4 rows in set (0.00 sec)

tidb> explain UPDATE t0 SET c0 = 1 WHERE 0^('0.5');
+---------------------------+----------+-----------+---------------+------------------------------------------+
| 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] |               | bitxor(0, cast("0.5", bigint(3) BINARY)) |
|     └─TableFullScan_6     | 10000.00 | cop[tikv] | table:t0      | keep order:false, stats:pseudo           |
+---------------------------+----------+-----------+---------------+------------------------------------------+
4 rows in set (0.00 sec)

There are technically 2 bugs:

1. In MySQL, a cast of a floating point string would truncate the fractional part. i.e. SELECT cast("0.5" as unsigned) == 0.
2. MySQL refuses to cast a string representation of an integer that has a fractional part in the default SQL mode:

mysql [localhost:8027] {msandbox} (test) > insert into t2 VALUES (cast("1.0" as unsigned));
ERROR 1292 (22007): Truncated incorrect INTEGER value: '1.0'
mysql [localhost:8027] {msandbox} (test) > insert into t2 VALUES (cast("1" as unsigned));
Query OK, 1 row affected (0.00 sec)

For (2) tidb gets this right with unistore, but not with tikv.

[sayJason]

I try the same where clause in the DELETE statement as follows:

DROP TABLE IF EXISTS t0; 
CREATE TABLE t0 (c0 INT); 
INSERT INTO t0 VALUES (1);
DELETE FROM t0 WHERE 0^('0.5');

The DELETE statement also succeeded. I want to know whether the same reason as UPDATE cause this problem. Thanks!

[zanmato1984]

They are 90% for the same reason.

[guo-shaoge]

Will fix in tikv/tikv#13045

[guo-shaoge]

tikv/tikv#13046 merged