bit, char type column metadata is not compatible with mysql.

[jingshanglu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `PK_MULTI_COL_527_tmp` (
  `COL1` char(10) COLLATE utf8_general_ci NOT NULL,
  `COL3` bit(1) NOT NULL,
  PRIMARY KEY (`COL1`(5),`COL3`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

select table_catalog table_catalog,table_name table_name,column_name column_name,ordinal_position ordinal_position,column_default column_default,is_nullable is_nullable,data_type data_type,character_maximum_length character_maximum_length,character_octet_length character_octet_length,numeric_precision numeric_precision,numeric_scale numeric_scale,datetime_precision datetime_precision,character_set_name character_set_name,collation_name collation_name,column_type column_type,column_key column_key,extra extra,privileges privileges,column_comment column_comment,generation_expression generation_expression from information_schema.columns where table_schema=(select database()) and table_name="PK_MULTI_COL_527_tmp" order by ordinal_position;

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

+---------------+----------------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------+------------+-------+---------------------------------+----------------+-----------------------+
| table_catalog | table_name           | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_scale | datetime_precision | character_set_name | collation_name  | column_type     | column_key | extra | privileges                      | column_comment | generation_expression |
+---------------+----------------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------+------------+-------+---------------------------------+----------------+-----------------------+
| def           | PK_MULTI_COL_527_tmp | COL1        |                1 | NULL           | NO          | char      |                       10 |                     30 |              NULL |          NULL |               NULL | utf8               | utf8_general_ci | char(10)        | PRI        |       | select,insert,update,references |                |                       |
| def           | PK_MULTI_COL_527_tmp | COL3        |                2 | NULL           | NO          | bit       |                     NULL |                   NULL |                 1 |             NULL |               NULL | NULL               | NULL            | bit(1) | PRI        |       | select,insert,update,references |                |                       |
+---------------+----------------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------+------------+-------+---------------------------------+----------------+-----------------------+

3. What did you see instead (Required)

+---------------+----------------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------+------------+-------+---------------------------------+----------------+-----------------------+
| table_catalog | table_name           | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_scale | datetime_precision | character_set_name | collation_name  | column_type     | column_key | extra | privileges                      | column_comment | generation_expression |
+---------------+----------------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------+------------+-------+---------------------------------+----------------+-----------------------+
| def           | PK_MULTI_COL_527_tmp | COL1        |                1 | NULL           | NO          | char      |                       10 |                     10 |              NULL |          NULL |               NULL | utf8               | utf8_general_ci | char(10)        | PRI        |       | select,insert,update,references |                |                       |
| def           | PK_MULTI_COL_527_tmp | COL3        |                2 | NULL           | NO          | bit       |                     NULL |                   NULL |                 1 |             0 |               NULL | NULL               | NULL            | bit(1) unsigned | PRI        |       | select,insert,update,references |                |                       |
+---------------+----------------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-----------------+------------+-------+---------------------------------+----------------+-----------------------+

4. What is your TiDB version? (Required)

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.1.0-20210611
Edition: Community
Git Commit Hash: 29bfbb28437609d0a524b7b84e6920dc765978c7
Git Branch: heads/refs/tags/v5.1.0-20210611
UTC Build Time: 2021-06-11 07:52:09
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[cz3pearl]

come from https://asktug.com/t/topic/183254

We encountered same problem,
information_schema.columns is not fully compatible while tidb version: 5.0.2 and mysql: 5.7, 5.8

table schema:
CREATE TABLE test_bit (
b bit(1) NOT NULL
);

check column info in information_schema
we got

tidb
NUMERIC_SCALE: 0
COLUMN_TYPE: bit(1) unsigned

mysql:
NUMERIC_SCALE: NULL
COLUMN_TYPE: bit(1)

[Mini256]

This problem is still reproduce in the nightly version (Release Version: v6.3.0-alpha). This will cause some ORM test cases to fail.

cc: @hawkingrei, @zimulala, @tangenta

mysql> CREATE TABLE T3(a INT PRIMARY KEY, b bit(1));
Query OK, 0 rows affected (2.52 sec)

mysql> SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.columns WHERE table_name = 'T3';
+------------+-------------+-----------------+
| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE     |
+------------+-------------+-----------------+
| T3         | a           | int(11)         |
| T3         | b           | bit(1) unsigned |
+------------+-------------+-----------------+
2 rows in set (0.35 sec)

[Mini256]

/type compatibility

[Mini256]

year type also has the same problem, whose metadata is year(4) unsigned. It's different from MySQL.

(
            "year",
            "Int",
            "Year",
            if api.is_mysql_8() {
                "year"
            } else if api.is_tidb() {
                "year(4) unsigned"
            } else {
                "year(4)"
            },
),

https://github.com/prisma/prisma-engines/blob/49ab8b879e4778a76fe9bfa2c85c8e10afd27b10/migration-engine/migration-engine-tests/tests/migrations/mysql.rs#L253