Incompatible behaviors on display width

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

I try to store the same value in a TINYINT column but with different display widths, TiDB returns different values.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TINYINT(1) NOT NULL);
INSERT INTO t1 VALUES (-127);
SELECT c1 FROM t1 WHERE CAST(IFNULL(c1, '-') AS CHAR); -- {}, wrong

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TINYINT(2) NOT NULL);
INSERT INTO t1 VALUES (-127);
SELECT c1 FROM t1 WHERE CAST(IFNULL(c1, '-') AS CHAR); -- {-127}, right

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TINYINT(3) NOT NULL);
INSERT INTO t1 VALUES (-127);
SELECT c1 FROM t1 WHERE CAST(IFNULL(c1, '-') AS CHAR); -- {-127}, right

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

These three SELECTs return the same value -127.
I try the above example in MySQL, they all return -127.

3. What did you see instead (Required)

The first returns empty, while the other two return -127.

4. What is your TiDB version? (Required)

Release Version: v7.2.0
Edition: Community
Git Commit Hash: 9fd5f4a
Git Branch: heads/refs/tags/v7.2.0
UTC Build Time: 2023-06-27 15:04:42
GoVersion: go1.20.5
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[sayJason]

I refer to MySQL Manual, which states that "The display width does not constrain the range of values that can be stored in the column." But such statement does not figure out if display width changes the evaluation of columns.

[aytrack]

like #44786

[10:29:54]TiDB root:test> explain select CAST(IFNULL(c1, '-') AS CHAR) from t2;
+---------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+
| id                  | estRows | task      | access object | operator info                                                                                                 |
+---------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+
| Projection_3        | 1.00    | root      |               | cast(cast(test.t2.c1, varchar(2) BINARY CHARACTER SET utf8 COLLATE utf8_general_ci), var_string(5))->Column#3 |
| └─TableReader_5     | 1.00    | root      |               | data:TableFullScan_4                                                                                          |
|   └─TableFullScan_4 | 1.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                |
+---------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+
3 rows in set
Time: 0.004s
[10:29:57]TiDB root:test> explain select CAST(IFNULL(c1, '-') AS CHAR) from t1;
+---------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+
| id                  | estRows | task      | access object | operator info                                                                                                 |
+---------------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------+
| Projection_3        | 1.00    | root      |               | cast(cast(test.t1.c1, varchar(1) BINARY CHARACTER SET utf8 COLLATE utf8_general_ci), var_string(5))->Column#3 |
| └─TableReader_5     | 1.00    | root      |               | data:TableFullScan_4                                                                                          |
|   └─TableFullScan_4 | 1.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                |
+---------------------+---------+-----------+---------------+-------------------------------