Tidb: functions format_bytes and format_nano_time incorrectly use varbinary (no collation)

[wwar]

Bug Report

The helper functions introduced in https://github.com/pingcap/tidb/pull/14975 have a bug, where I can't easily use them in the MySQL CLI because they print as hex.

1. What did you do?

mysql -P4000 -uroot --column-type-info=1 -e 'SELECT FORMAT_BYTES(1024), CAST(FORMAT_BYTES(1024) as CHAR)'
Field   1:  `FORMAT_BYTES(1024)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     0
Max_length: 8
Decimals:   31
Flags:      UNSIGNED 

Field   2:  `CAST(FORMAT_BYTES(1024) as CHAR)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_bin (46)
Length:     0
Max_length: 8
Decimals:   31
Flags:      


+----------------------------------------+----------------------------------+
| FORMAT_BYTES(1024)                     | CAST(FORMAT_BYTES(1024) as CHAR) |
+----------------------------------------+----------------------------------+
| 0x312E3030204B6942                     | 1.00 KiB                         |
+----------------------------------------+----------------------------------+

1. What did you expect to see?

Here is the behavior of MySQL:

./use --column-type-info=1 -e 'SELECT FORMAT_BYTES(1024), CAST(FORMAT_BYTES(1024) as CHAR)'
Field   1:  `FORMAT_BYTES(1024)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     44
Max_length: 8
Decimals:   31
Flags:      

Field   2:  `CAST(FORMAT_BYTES(1024) as CHAR)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     44
Max_length: 8
Decimals:   31
Flags:      


+--------------------+----------------------------------+
| FORMAT_BYTES(1024) | CAST(FORMAT_BYTES(1024) as CHAR) |
+--------------------+----------------------------------+
| 1.00 KiB           | 1.00 KiB                         |
+--------------------+----------------------------------+

1. What did you see instead?

This applies to both format_bytes and format_nano_time. They need to be wrapped in a cast function to return printable characters for the mysql cli.

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

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta-385-g107b071e0
Git Commit Hash: 107b071e0c1d8c5678911ccb2808e42db0abf63c
Git Branch: master
UTC Build Time: 2020-03-11 03:54:18
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)

[wwar]

PTAL @gauss1314

[gauss1314]

Let me fix it.

[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: https://github.com/pingcap/tidb/issues/20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions