P2-[4.0 Bug Hunting]-[hex function]-missing padding zeros

[zhangysh1995]

Bug Report

1. What did you do?

create table table1 (col1 binary(4));
insert into table1 values ('a'),('a ');
select hex(col1) from table1;
alter table table1 modify col1 binary(10);
select hex(col1) from table1;
insert into table1 values ('b'),('b ');
select hex(col1) from table1;

2. What did you expect to see?

mysql> create table table1 (col1 binary(4));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into table1 values ('a'),('a ');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select hex(col1) from table1;
+-----------+
| hex(col1) |
+-----------+
| 61000000  |
| 61200000  |
+-----------+
2 rows in set (0.00 sec)

mysql> alter table table1 modify col1 binary(10);
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select hex(col1) from table1;
+----------------------+
| hex(col1)            |
+----------------------+
| 61000000000000000000 |
| 61200000000000000000 |
+----------------------+
2 rows in set (0.00 sec)

mysql> insert into table1 values ('b'),('b ');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select hex(col1) from table1;
+----------------------+
| hex(col1)            |
+----------------------+
| 61000000000000000000 |
| 61200000000000000000 |
| 62000000000000000000 |
| 62200000000000000000 |
+----------------------+
4 rows in set (0.00 sec)

3. What did you see instead?

mysql> create table table1 (col1 binary(4));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1 values ('a'),('a ');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select hex(col1) from table1;
+-----------+
| hex(col1) |
+-----------+
| 61000000  |
| 61200000  |
+-----------+
2 rows in set (0.00 sec)

mysql> alter table table1 modify col1 binary(10);
Query OK, 0 rows affected (0.01 sec)

mysql> select hex(col1) from table1;
+-----------+
| hex(col1) |
+-----------+
| 61000000  |
| 61200000  |
+-----------+
2 rows in set (0.00 sec)

mysql> insert into table1 values ('b'),('b ');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select hex(col1) from table1;
+----------------------+
| hex(col1)            |
+----------------------+
| 61000000             |
| 61200000             |
| 62000000000000000000 |
| 62200000000000000000 |
+----------------------+
4 rows in set (0.00 sec)

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

Reproducible on master branch:

commit 0d33a845766733fbb9e40dae8b7bf38fb0d3498b (HEAD -> master, origin/master, origin/HEAD)
Author: Zhuomin(Charming) Liu <lzmhhh123@gmail.com>
Date:   Wed May 20 19:54:37 2020 +0800

select tidb_version();

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: None
Edition: None
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[wjhuang2016]

Hi, @zhangysh1995 It's expected behavior. The padding handle in TiDB is not the same as MySQL. https://pingcap.com/docs-cn/stable/character-set-and-collation/ FYI

[zhangysh1995]

@wjhuang2016 Not sure why this page is related.

[zhangysh1995]

It is inconsistent, the first inserted values don't have padding zeros but the newly inserted ones have. The column uses binary type, not charset. https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html

[wjhuang2016]

Hi, @zhangysh1995 Sorry about that. It's a bug, we should forbid alter table table1 modify col1 binary(10);.

[shuke987]

/bug P2