Incompatible with MySQL 8.0 about insert over length string into CHAR column

[lonng]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

➜  ~ tidiff 'select @@sql_mode'
MySQL(127.0.0.1:3306)> select @@sql_mode
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

TiDB(127.0.0.1:4000)> select @@sql_mode
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.003 sec)

➜  ~ tidiff 'create table vctt (v varchar(4), c char(4))'
MySQL(127.0.0.1:3306)> create table vctt (v varchar(4), c char(4))
Query OK (0.006 sec)

TiDB(127.0.0.1:4000)> create table vctt (v varchar(4), c char(4))
Query OK (0.007 sec)

➜  ~ tidiff 'insert into vctt values ("ab  ", "ab   ")'
MySQL(127.0.0.1:3306)> insert into vctt values ("ab  ", "ab   ")
Query OK (0.003 sec)

TiDB(127.0.0.1:4000)> insert into vctt values ("ab  ", "ab   ")
Error 1406: Data too long for column 'c' at row 1

➜  ~ tidiff 'select * from vctt'
MySQL(127.0.0.1:3306)> select * from vctt
+-----------+
| v    | c  |
+-----------+
| ab   | ab |
+-----------+
1 row in set (0.001 sec)

TiDB(127.0.0.1:4000)> select * from vctt
0 row in set (0.107 sec)

2. What did you expect to see?

Truncate over length string successfully.

3. What did you see instead?

Got error Error 1406: Data too long for column 'c' at row 1

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v2.1.8-24-g7b36cdfb6-dirty
Git Commit Hash: 7b36cdfb6e8a4c279fc360ed79cf1c499b553688
Git Branch: failpoint-2.1
UTC Build Time: 2019-04-30 08:26:17
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @XuHuaiyu

[ghost]

Confirming this bug still exists in master (you don't need to change TiDB's sql modes - the original testcase is unclear there):

drop table if exists vctt;
create table vctt (v varchar(4), c char(4));
insert into vctt values ("ab  ", "ab   ");

..

mysql> insert into vctt values ("ab  ", "ab   ");
ERROR 1406 (22001): Data too long for column 'c' at row 1
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
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)

[lzmhhh123]

duplicated with #4323

[wjhuang2016]

I think it's a compatibility problem. TiDB truncates the tailing spaces, insert "ab "(three tail spaces) to char(4) is ok. But MySQL would pads "ab " to char(4), then fails. @nullnotnil What do you think about this? Should we need to fix it?

[ghost]

Yes, I don't think it is high priority, but I expect there could be applications that depend on this.

[wjhuang2016]

Yes, I don't think it is high priority, but I expect there could be applications that depend on this.

Ok, I hope some contributors can help us with this issue. It's not hard to fix.

[ichn-hu]

/assign @ichn-hu

[ichn-hu]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions