Format of bit default value on `INFORMATION_SCHEMA.COLUMNS` is wrong/inconsistent

[hedwigz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

test=> create table "bug" ("bit" bit(10) DEFAULT b'100');
CREATE TABLE
test=> show create table bug;
 Table |                        Create Table                         
-------+-------------------------------------------------------------
 bug   | CREATE TABLE "bug" (                                       +
       |   "bit" bit(10) DEFAULT b'100'                             +
       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 
(1 row)
test=> SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'bug';
 COLUMN_DEFAULT 
----------------
 \x04 
(1 row)

2. What did you expect to see? (Required)

The following is MySQL output:

test=> CREATE TABLE "bug" ("tBit" bit(10) DEFAULT b'100');
CREATE TABLE
test=> SELECT COLUMN_DEFAULT FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_NAME = 'bug';
 COLUMN_DEFAULT 
----------------
 b'100' 
(1 row)

3. What did you see instead (Required)

test=> SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'bug';
 COLUMN_DEFAULT 
----------------
 \x04 
(1 row)

4. What is your TiDB version? (Required)

test=> SELECT tidb_version();
tidb_version()                          
-------------------------------------------------------------------
 Release Version: v5.4.0                                          
 Edition: Community                                               
 Git Commit Hash: 55f3b24c1c9f506bd652ef1d162283541e428872        
 Git Branch: heads/refs/tags/v5.4.0                               
 UTC Build Time: 2022-01-25 08:39:26                              
 GoVersion: go1.16.4                                             
 Race Enabled: false                                              
 TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
 Check Table Before Drop: false 
(1 row)

[hedwigz]

Also worth noting, is that the output of show create table is correct (ran on TiDB):

test=> show create table bug;
 Table |                        Create Table                         
-------+-------------------------------------------------------------
 bug   | CREATE TABLE "bug" (                                       +
       |   "bit" bit(10) DEFAULT b'100'                             +
       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 
(1 row)

[aytrack]

compare to mysql 8.0.28, seems only the record in information_schema.columns is different, others are same.
mysql 8.0.28

mysql>  create table `bug` (`bit` bit(10) DEFAULT b'100');
Query OK, 0 rows affected (0.08 sec)

mysql> insert into bug values (DEFAULT);
Query OK, 1 row affected (0.05 sec)

mysql> select * from bug;
+------------+
| bit        |
+------------+
| 0x0004     |
+------------+
1 row in set (0.04 sec)

mysql> show create table bug;
+-------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------+
| bug   | CREATE TABLE `bug` (
  `bit` bit(10) DEFAULT b'100'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

mysql> SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'bug';
+----------------+
| COLUMN_DEFAULT |
+----------------+
| b'100'         |
+----------------+
1 row in set (0.06 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.05 sec)

tidb

mysql> create table `bug` (`bit` bit(10) DEFAULT b'100');
Query OK, 0 rows affected (0.10 sec)

mysql> insert into bug values (DEFAULT);
Query OK, 1 row affected (0.06 sec)

mysql>   select * from bug;
+------------+
| bit        |
+------------+
| 0x0004     |
+------------+
1 row in set (0.01 sec)

mysql> show create table bug;
+-------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------+
| bug   | CREATE TABLE `bug` (
  `bit` bit(10) DEFAULT b'100'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'bug';
+----------------+
| COLUMN_DEFAULT |
+----------------+
|               |
+----------------+
1 row in set (0.06 sec)

[Defined2014]

/severity moderate