Field type compatibility for blob types in MySQL protocol

[dveeden]

Enhancement

This returns different types for MySQL 9.2.0 and TiDB v8.5.1:

CREATE TABLE t1(id int primary key, f1 longblob, f2 blob, f3 mediumblob, f4 tinyblob);
INSERT INTO t1 VALUES (1, 0x1, 0x2, 0x3, 0x4);
TABLE t1;

MySQL:

mysql-9.2.0> CREATE TABLE t1(id int primary key, f1 longblob, f2 blob, f3 mediumblob, f4 tinyblob);
Query OK, 0 rows affected (0.03 sec)
mysql-9.2.0> INSERT INTO t1 VALUES (1, 0x1, 0x2, 0x3, 0x4);
Query OK, 1 row affected (0.00 sec)
mysql-9.2.0> TABLE t1;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY NO_DEFAULT_VALUE NUM PART_KEY 
Field   2:  `f1`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       BLOB
Collation:  binary (63)
Length:     4294967295
Max_length: 1
Decimals:   0
Flags:      BLOB BINARY 
Field   3:  `f2`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       BLOB
Collation:  binary (63)
Length:     65535
Max_length: 1
Decimals:   0
Flags:      BLOB BINARY 
Field   4:  `f3`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       BLOB
Collation:  binary (63)
Length:     16777215
Max_length: 1
Decimals:   0
Flags:      BLOB BINARY 
Field   5:  `f4`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       BLOB
Collation:  binary (63)
Length:     255
Max_length: 1
Decimals:   0
Flags:      BLOB BINARY 
+----+------------+------------+------------+------------+
| id | f1         | f2         | f3         | f4         |
+----+------------+------------+------------+------------+
|  1 | 0x01       | 0x02       | 0x03       | 0x04       |
+----+------------+------------+------------+------------+
1 row in set (0.00 sec)
mysql-9.2.0> 

TiDB

mysql-8.0.11-TiDB-v8.5.1> CREATE TABLE t1(id int primary key, f1 longblob, f2 blob, f3 mediumblob, f4 tinyblob);
Query OK, 0 rows affected (0.05 sec)
mysql-8.0.11-TiDB-v8.5.1> INSERT INTO t1 VALUES (1, 0x1, 0x2, 0x3, 0x4);
Query OK, 1 row affected (0.02 sec)
mysql-8.0.11-TiDB-v8.5.1> TABLE t1;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY NO_DEFAULT_VALUE NUM 
Field   2:  `f1`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       LONG_BLOB
Collation:  binary (63)
Length:     4294967295
Max_length: 1
Decimals:   0
Flags:      BINARY 
Field   3:  `f2`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       BLOB
Collation:  binary (63)
Length:     65535
Max_length: 1
Decimals:   0
Flags:      BINARY 
Field   4:  `f3`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       MEDIUM_BLOB
Collation:  binary (63)
Length:     16777215
Max_length: 1
Decimals:   0
Flags:      BINARY 
Field   5:  `f4`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       TINY_BLOB
Collation:  binary (63)
Length:     255
Max_length: 1
Decimals:   0
Flags:      BINARY 
+----+------------+------------+------------+------------+
| id | f1         | f2         | f3         | f4         |
+----+------------+------------+------------+------------+
|  1 | 0x01       | 0x02       | 0x03       | 0x04       |
+----+------------+------------+------------+------------+
1 row in set (0.00 sec)
mysql-8.0.11-TiDB-v8.5.1>