TiDB always sets display width of integer fields when creating a view, MySQL does not

[smola]

Bug Report

1. Minimal reproduce step (Required)

Query:

DROP DATABASE IF EXISTS issue_display_width;
CREATE DATABASE issue_display_width;
USE issue_display_width;
CREATE TABLE t1 (
	_int8 TINYINT SIGNED,
    _uint8 TINYINT UNSIGNED,
    _int16 SMALLINT SIGNED,
    _uint16 SMALLINT UNSIGNED,
    _int24 MEDIUMINT SIGNED,
    _uint24 MEDIUMINT UNSIGNED,
	_int32 INT SIGNED,
    _uint32 INT UNSIGNED,
	_int64 BIGINT SIGNED,
    _uint64 BIGINT UNSIGNED
);
CREATE OR REPLACE VIEW v2 AS (
	SELECT
		_int8,
		(_int8+_int8) AS int8_plus_int8,
        (_uint8+_uint8) AS uint8_plus_uint8,
        (_int8+_uint8) AS int8_plus_uint8,
		(_int16+_int16) AS int16_plus_int16,
        (_uint16+_uint16) AS uint16_plus_uint16,
        (_int16+_uint16) AS int16_plus_uint16,
		(_int24+_int24) AS int24_plus_int24,
        (_uint24+_uint24) AS uint24_plus_uint24,
        (_int24+_uint24) AS int24_plus_uint24,
		(_int32+_int32) AS int32_plus_int32,
        (_uint32+_uint32) AS uint32_plus_uint32,
        (_int32+_uint32) AS int32_plus_uint32,
		(_int64+_int64) AS int64_plus_int64,
        (_uint64+_uint64) AS uint64_plus_uint64,
        (_int64+_uint64) AS int64_plus_uint64,
		(CASE WHEN TRUE THEN _int8 ELSE _int8 END) AS int8_case_int8,
        (CASE WHEN TRUE THEN _uint8 ELSE _uint8 END) AS uint8_case_uint8,
        (CASE WHEN TRUE THEN _int8 ELSE _uint8 END) AS int8_case_uint8,
		(CASE WHEN TRUE THEN _int16 ELSE _int16 END) AS int16_case_int16,
        (CASE WHEN TRUE THEN _uint16 ELSE _uint16 END) AS uint16_case_uint16,
        (CASE WHEN TRUE THEN _int16 ELSE _uint16 END) AS int16_case_uint16,
		(CASE WHEN TRUE THEN _int24 ELSE _int24 END) AS int24_case_int24,
        (CASE WHEN TRUE THEN _uint24 ELSE _uint24 END) AS uint24_case_uint24,
        (CASE WHEN TRUE THEN _int24 ELSE _uint24 END) AS int24_case_uint24,
		(CASE WHEN TRUE THEN _int32 ELSE _int32 END) AS int32_case_int32,
        (CASE WHEN TRUE THEN _uint32 ELSE _uint32 END) AS uint32_case_uint32,
        (CASE WHEN TRUE THEN _int32 ELSE _uint32 END) AS int32_case_uint32,
		(CASE WHEN TRUE THEN _int64 ELSE _int64 END) AS int64_case_int64,
        (CASE WHEN TRUE THEN _uint64 ELSE _uint64 END) AS uint64_case_uint64,
        (CASE WHEN TRUE THEN _int64 ELSE _uint64 END) AS int64_case_uint64,
		COALESCE(_int8) AS coalesce_int8,
        COALESCE(_uint8) AS coalesce_uint8,
        COALESCE(_int8, _uint8) AS coalesce_int8_uint8,
		COALESCE(_int16) AS coalesce_int16,
        COALESCE(_uint16) AS coalesce_uint16,
        COALESCE(_int16, _uint16) AS coalesce_int16_uint16,
		COALESCE(_int24) AS coalesce_int24,
        COALESCE(_uint24) AS coalesce_uint24,
        COALESCE(_int24, _uint24) AS coalesce_int24_uint24,
        COALESCE(_int32) AS coalesce_int32,
        COALESCE(_uint32) AS coalesce_uint32,
        COALESCE(_int32, _uint32) AS coalesce_int32_uint32,
        COALESCE(_int64) AS coalesce_int64,
        COALESCE(_uint64) AS coalesce_uint64,
        COALESCE(_int64, _uint64) AS coalesce_int64_uint64
	FROM t1
);
SHOW COLUMNS FROM v2;

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

Result in MySQL 8.0.20:

+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
| Field      | Type      | Null      | Key      | Default      | Extra      |
+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
| _int8      | tinyint   | YES       |          |              |            |
| int8_plus_int8 | int       | YES       |          |              |            |
| uint8_plus_uint8 | int unsigned | YES       |          |              |            |
| int8_plus_uint8 | int unsigned | YES       |          |              |            |
| int16_plus_int16 | int       | YES       |          |              |            |
| uint16_plus_uint16 | int unsigned | YES       |          |              |            |
| int16_plus_uint16 | int unsigned | YES       |          |              |            |
| int24_plus_int24 | bigint    | YES       |          |              |            |
| uint24_plus_uint24 | int unsigned | YES       |          |              |            |
| int24_plus_uint24 | int unsigned | YES       |          |              |            |
| int32_plus_int32 | bigint    | YES       |          |              |            |
| uint32_plus_uint32 | bigint unsigned | YES       |          |              |            |
| int32_plus_uint32 | bigint unsigned | YES       |          |              |            |
| int64_plus_int64 | bigint    | YES       |          |              |            |
| uint64_plus_uint64 | bigint unsigned | YES       |          |              |            |
| int64_plus_uint64 | bigint unsigned | YES       |          |              |            |
| int8_case_int8 | int       | YES       |          |              |            |
| uint8_case_uint8 | int unsigned | YES       |          |              |            |
| int8_case_uint8 | int       | YES       |          |              |            |
| int16_case_int16 | int       | YES       |          |              |            |
| uint16_case_uint16 | int unsigned | YES       |          |              |            |
| int16_case_uint16 | int       | YES       |          |              |            |
| int24_case_int24 | int       | YES       |          |              |            |
| uint24_case_uint24 | int unsigned | YES       |          |              |            |
| int24_case_uint24 | int       | YES       |          |              |            |
| int32_case_int32 | bigint    | YES       |          |              |            |
| uint32_case_uint32 | bigint unsigned | YES       |          |              |            |
| int32_case_uint32 | bigint    | YES       |          |              |            |
| int64_case_int64 | bigint    | YES       |          |              |            |
| uint64_case_uint64 | bigint unsigned | YES       |          |              |            |
| int64_case_uint64 | decimal(20,0) | YES       |          |              |            |
| coalesce_int8 | int       | YES       |          |              |            |
| coalesce_uint8 | int unsigned | YES       |          |              |            |
| coalesce_int8_uint8 | int       | YES       |          |              |            |
| coalesce_int16 | int       | YES       |          |              |            |
| coalesce_uint16 | int unsigned | YES       |          |              |            |
| coalesce_int16_uint16 | int       | YES       |          |              |            |
| coalesce_int24 | int       | YES       |          |              |            |
| coalesce_uint24 | int unsigned | YES       |          |              |            |
| coalesce_int24_uint24 | int       | YES       |          |              |            |
| coalesce_int32 | bigint    | YES       |          |              |            |
| coalesce_uint32 | bigint unsigned | YES       |          |              |            |
| coalesce_int32_uint32 | bigint    | YES       |          |              |            |
| coalesce_int64 | bigint    | YES       |          |              |            |
| coalesce_uint64 | bigint unsigned | YES       |          |              |            |
| coalesce_int64_uint64 | decimal(20,0) | YES       |          |              |            |
+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
46 rows

3. What did you see instead (Required)

Output in TiDB 4.0.0:

+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
| Field      | Type      | Null      | Key      | Default      | Extra      |
+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
| _int8      | tinyint(4) | YES       |          |              |            |
| int8_plus_int8 | bigint(20) | YES       |          |              |            |
| uint8_plus_uint8 | bigint(20) unsigned | YES       |          |              |            |
| int8_plus_uint8 | bigint(20) unsigned | YES       |          |              |            |
| int16_plus_int16 | bigint(20) | YES       |          |              |            |
| uint16_plus_uint16 | bigint(20) unsigned | YES       |          |              |            |
| int16_plus_uint16 | bigint(20) unsigned | YES       |          |              |            |
| int24_plus_int24 | bigint(20) | YES       |          |              |            |
| uint24_plus_uint24 | bigint(20) unsigned | YES       |          |              |            |
| int24_plus_uint24 | bigint(20) unsigned | YES       |          |              |            |
| int32_plus_int32 | bigint(20) | YES       |          |              |            |
| uint32_plus_uint32 | bigint(20) unsigned | YES       |          |              |            |
| int32_plus_uint32 | bigint(20) unsigned | YES       |          |              |            |
| int64_plus_int64 | bigint(20) | YES       |          |              |            |
| uint64_plus_uint64 | bigint(20) unsigned | YES       |          |              |            |
| int64_plus_uint64 | bigint(20) unsigned | YES       |          |              |            |
| int8_case_int8 | tinyint(4) | YES       |          |              |            |
| uint8_case_uint8 | tinyint(3) unsigned | YES       |          |              |            |
| int8_case_uint8 | tinyint(4) | YES       |          |              |            |
| int16_case_int16 | smallint(6) | YES       |          |              |            |
| uint16_case_uint16 | smallint(5) unsigned | YES       |          |              |            |
| int16_case_uint16 | smallint(6) | YES       |          |              |            |
| int24_case_int24 | mediumint(9) | YES       |          |              |            |
| uint24_case_uint24 | mediumint(8) unsigned | YES       |          |              |            |
| int24_case_uint24 | mediumint(9) | YES       |          |              |            |
| int32_case_int32 | int(11)   | YES       |          |              |            |
| uint32_case_uint32 | int(10) unsigned | YES       |          |              |            |
| int32_case_uint32 | int(11)   | YES       |          |              |            |
| int64_case_int64 | bigint(20) | YES       |          |              |            |
| uint64_case_uint64 | bigint(20) unsigned | YES       |          |              |            |
| int64_case_uint64 | bigint(20) | YES       |          |              |            |
| coalesce_int8 | tinyint(4) | YES       |          |              |            |
| coalesce_uint8 | tinyint(3) unsigned | YES       |          |              |            |
| coalesce_int8_uint8 | tinyint(4) | YES       |          |              |            |
| coalesce_int16 | smallint(6) | YES       |          |              |            |
| coalesce_uint16 | smallint(5) unsigned | YES       |          |              |            |
| coalesce_int16_uint16 | smallint(6) | YES       |          |              |            |
| coalesce_int24 | mediumint(9) | YES       |          |              |            |
| coalesce_uint24 | mediumint(8) unsigned | YES       |          |              |            |
| coalesce_int24_uint24 | mediumint(9) | YES       |          |              |            |
| coalesce_int32 | int(11)   | YES       |          |              |            |
| coalesce_uint32 | int(10) unsigned | YES       |          |              |            |
| coalesce_int32_uint32 | int(11)   | YES       |          |              |            |
| coalesce_int64 | bigint(20) | YES       |          |              |            |
| coalesce_uint64 | bigint(20) unsigned | YES       |          |              |            |
| coalesce_int64_uint64 | bigint(21) | YES       |          |              |            |
+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
46 rows

The created view has display width set for all fields in TiDB, while that is not the case for MySQL. Note that display width for each field on the query used to create the view are the same in TiDB and MySQL, so the difference seems to be only on view creation and not on querying in general. Also, disregard other differences in types (e.g. INT vs BIGINT), as these are part of different issues.

I found this while investigating a different issue. I'm not aware of any real world compatibility problem caused by this difference. However, if this is intended, it may be worth to document it at https://github.com/pingcap/docs/blob/master/mysql-compatibility.md

4. Affected version (Required)

v4.0.0

5. Root Cause Analysis

[wwar]

The behavior of removing display width is a very recent addition to MySQL - it shipped in 8.0.19 earlier this year:

Display width specification for integer data types was deprecated in MySQL 8.0.17, and now statements that include data type definitions in their output no longer show the display width for integer types, with these exceptions:

The type is TINYINT(1). MySQL Connectors make the assumption that TINYINT(1) columns originated as BOOLEAN columns; this exception enables them to continue to make that assumption.

The type includes the ZEROFILL attribute.

This change applies to tables, views, and stored routines, and affects the output from SHOW CREATE and DESCRIBE statements, and from INFORMATION_SCHEMA tables.

It is documented in mysql-compatibility.md, insofar as TiDB currently advertises/claims MySQL 5.7 compatibility. MySQL 8.0 is a bit of a moving target, but you can see what is missing in #7968

[dveeden]

I think the deprecate-integer-display-length config option might be helpful here?