TiDB does not trim space from ENUM DEFAULT definition

[Alkaagr81]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1;
CREATE TABLE `t1` (   `a` enum('','a','b') NOT NULL DEFAULT 'b' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
show create table t1;
drop table if exists t1;
CREATE TABLE `t1` (   `a` enum('','a','b ') NOT NULL DEFAULT 'b ' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
show create table t1;

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

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `t1` (   `a` enum('','a','b') NOT NULL DEFAULT 'b' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` enum('','a','b') NOT NULL DEFAULT 'b'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t1` (   `a` enum('','a','b ') NOT NULL DEFAULT 'b ' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` enum('','a','b') NOT NULL DEFAULT 'b'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `t1` (   `a` enum('','a','b') NOT NULL DEFAULT 'b' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.13 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` enum('','a','b') COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'b'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.23 sec)

mysql> CREATE TABLE `t1` (   `a` enum('','a','b ') NOT NULL DEFAULT 'b ' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ERROR 1067 (42000): Invalid default value for 'a'
mysql> show create table t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

4. What is your TiDB version? (Required)

| Release Version: v5.2.2
Edition: Community
Git Commit Hash: da1c21fd45a4ea5900ac16d2f4a248143f378d18
Git Branch: heads/refs/tags/v5.2.2
UTC Build Time: 2021-10-20 06:03:45
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[morgo]

Verified against master. The specific issue is the default value is not trimmed for comparison to the list of possible values:

tidb> create table t1 (a enum (' ','a','b ') not null default 'b ');
ERROR 1067 (42000): Invalid default value for 'a'
tidb> create table t1 (a enum (' ','a','b ') not null default 'b');
Query OK, 0 rows affected (0.12 sec)

[xiongjiwei]

similar with #18949

[morgo]

similar with #18949

It looks like it was an incomplete fix when accounting for DEFAULT.

[c4pt0r]

Is there anyone working on this issue, if not, I will fix this :)

[xiongjiwei]

when the default value has the tailing space, MySQL will trim the space. See the create table SQL below, the default value 'a ' has a tailing space, and in the definition of the table, the space is gone, but in tidb, it will not be trimmed.

We can fix it in the parser, just trimming the tailing space for DefaultValueExpr is enough.

mysql> create table t(a enum('a') not null default 'a ');
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` enum('a') NOT NULL DEFAULT 'a'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

tidb will report error because 'a' != 'a ' (the new collation is not enabled)

[bb7133]

@xiongjiwei Are you sure that the default value should be trimmed, no matter whether new collation is enabled?

[xiongjiwei]

Are you sure that the default value should be trimmed, no matter whether new collation is enabled?

actually, I am wrong, it is related to the collation. we should also revert pingcap/parser#1006, that is a wrong fix 😥

[bb7133]

I think it may be fine to keep trimming the space even when new collation is not enabled, as long as it doesn't lead to any bug.

[bb7133]

I don't see any reason that users DO NOT want trimming spaces for SET/ENUM values.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.