Insert data need to filter spaces

[zimulala]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

create table t (a char(10) not null, b char (10) binary not null);
insert into t values("hello ", "hello   ");
select b, length(b) from t;

2. What did you expect to see?

+-------+-----------+
| b     | length(b) |
+-------+-----------+
| hello |         5 |
+-------+-----------+
1 row in set (0.00 sec)

3. What did you see instead?

+----------+-----------+
| b        | length(b) |
+----------+-----------+
| hello    |         8 |
+----------+-----------+
1 row in set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V)?

[zz-jason]

Refer to: https://dev.mysql.com/doc/refman/5.7/en/char.html, the correct behavior of CHAR values is described as following:

When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

the behavior of char/varchar/tinytext/text/mediumtext/longtext is weird:

example 1:

create table t(a char(10), b varchar(10), c tinytext, d text, e mediumtext, f longtext);
insert into t (a, b, c, d, e, f) values ("a ", "b ", "c ", "d ", "e ", "f ");
select length(a), length(b), length(c), length(d), length(e), length(f) from t;
+-----------+-----------+-----------+-----------+-----------+-----------+
| length(a) | length(b) | length(c) | length(d) | length(e) | length(f) |
+-----------+-----------+-----------+-----------+-----------+-----------+
|         1 |         2 |         2 |         2 |         2 |         2 |
+-----------+-----------+-----------+-----------+-----------+-----------+

select repeat(a, 2), repeat(b, 2), repeat(c, 2), repeat(d, 2), repeat(e, 2), repeat(f, 2) from t;
+--------------+--------------+--------------+--------------+--------------+--------------+
| repeat(a, 2) | repeat(b, 2) | repeat(c, 2) | repeat(d, 2) | repeat(e, 2) | repeat(f, 2) |
+--------------+--------------+--------------+--------------+--------------+--------------+
| aa           | b b          | c c          | d d          | e e          | f f          |
+--------------+--------------+--------------+--------------+--------------+--------------+

it seems like char value's trailing spaces will be truncated while others trailing spaces will be retained, but the following example makes things complicated:

example 2:

select a="a  ", b="b  ", c="c  ", d="d  ", e="e  ", f="f  " from t;
+---------+---------+---------+---------+---------+---------+
| a="a  " | b="b  " | c="c  " | d="d  " | e="e  " | f="f  " |
+---------+---------+---------+---------+---------+---------+
|       1 |       1 |       1 |       1 |       1 |       1 |
+---------+---------+---------+---------+---------+---------+

[breezewish]

example 2 may relate to this:

All MySQL collations are of type PAD SPACE. This means that all CHAR, VARCHAR, and TEXT values are compared without regard to any trailing spaces.

https://dev.mysql.com/doc/refman/5.7/en/char.html

[zz-jason]

leave out ENUM and SET, mysql has these string types:

name	binary flag	tidb representation
CHAR[(M)]	0	TypeString
BINARY(M)	1	TypeString
VARCHAR(M)	0	TypeVarString/TypeVarchar
VARBINARY(M)	1	TypeVarString/TypeVarchar
TINYTEXT	0	TypeTinyBlob
TINYBLOB	1	TypeTinyBlob
TEXT[(M)]	0	TypeBlob
BLOB(M)	1	TypeBlob
MEDIUMTEXT	0	TypeMediumBlob
MEDIUMBLOB	1	TypeMediumBlob
LONGTEXT	0	TypeLongBlob
LONGBLOB	1	TypeLongBlob

1. TypeVarString, TypeTinyBlob, TypeBlob, TypeMediumBlob, TypeLongBlob
   for these types, no mater whether binary flag is set, we should store what user inserted, do not append spaces in the tail or truncate the trailing spaces
2. TypeString without binary flag, i.e CHAR[(M)]
   for this type, we should truncate the trailing spaces
3. TypeString with binary flag, i.e BINARY(M)
   for this type, we should append spaces in the tail.