Inconsisitent with Mysql When a varchar column only have empty string

[DQinYuan]

Bug Report

1. What did you do?

create table t (a varchar(30));
insert into t values ('');
delete from t where a=10;

2. What did you expect to see?

the same behavour with mysql.

3. What did you see instead?

TiDB Result:

ERROR 1265 (01000): Data Truncated

Mysql Result（5.7 and 8.0）:

Query OK, 0 rows affected (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();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-111-g266ff4b6f
Git Commit Hash: 266ff4b6f37e721cf3d777a4e1d94b692f235d60
Git Branch: master
UTC Build Time: 2019-02-22 10:50:48
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 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[qw4990]

@DQinYuan Thanks for your feedback and I am looking into this problem now.

[ghost]

Confirming this issue can still be reproduced:

drop table if exists t;
create table t (a varchar(30));
insert into t values ('');
delete from t where a=10;

..

mysql> delete from t where a=10;
ERROR 1292 (22007): Truncated incorrect INTEGER value: ''
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-870-g2a8b96845
Edition: Community
Git Commit Hash: 2a8b968453520e4fcf9d6ff46c9f23b4ad23feee
Git Branch: master
UTC Build Time: 2020-07-31 08:45:35
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)

[morgo]

I can still reproduce this against master, but it requires a tikv server present:

mysql> delete from t where a=10;
ERROR 1292 (22007): Truncated incorrect INTEGER value: ''
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-1790-g052d71695
Edition: Community
Git Commit Hash: 052d7169553ef7c12e5204cde80c09a9303527aa
Git Branch: master
UTC Build Time: 2020-12-10 13:18:52
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)

[guo-shaoge]

Maybe we should fix it also in coprocessor. The filter is pushed to tikv. Wroking on it.

mysql> explain delete from t where a=10;
+---------------------------+---------+-----------+---------------+--------------------------------+
| id                        | estRows | task      | access object | operator info                  |
+---------------------------+---------+-----------+---------------+--------------------------------+
| Delete_4                  | N/A     | root      |               | N/A                            |
| └─TableReader_8           | 0.80    | root      |               | data:Selection_7               |
|   └─Selection_7           | 0.80    | cop[tikv] |               | eq(cast(test.t.a), 10)         |
|     └─TableFullScan_6     | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+---------------+--------------------------------+
4 rows in set (0.01 sec)

[guo-shaoge]

This issue is fixed in TiKV. Maybe we can close it.