SET type has inconsistent behavior compared with MySQL

[zhexuany]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create table myset2(i int, s set('4','5', '6') default 4);
insert into myset2(i) values(1);

2. What did you expect to see?

mysql>  select * from myset2;
+------+------+
| i    | s    |
+------+------+
|    1 | 6    |
+------+------+
1 row in set (0.01 sec)

3. What did you see instead?

mysql>  select * from myset2;
+------+------+
| i    | s    |
+------+------+
|    1 | 4    |
+------+------+
1 row in set (0.01 sec)

MySQL's show create table is the following

mysql> show create table myset2;
+--------+-----------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                |
+--------+-----------------------------------------------------------------------------------------------------------------------------+
| myset2 | CREATE TABLE `myset2` (
  `i` int(11) DEFAULT NULL,
  `s` set('4','5','6') DEFAULT '6'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

while tidb's is different

mysql> show create table myset2;
+--------+-----------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                |
+--------+-----------------------------------------------------------------------------------------------------------------------------+
| myset2 | CREATE TABLE `myset2` (
  `i` int(11) DEFAULT NULL,
  `s` set('4','5','6') DEFAULT '4'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[zhexuany]

TiDB's behavior:

mysql> create table myset3(i int, s set('4','5', '6') default 7);
Query OK, 0 rows affected (0.18 sec)

mysql> show create table myset3;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                       |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| myset3 | CREATE TABLE `myset3` (
  `i` int(11) DEFAULT NULL,
  `s` set('4','5','6') DEFAULT '7'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL's behavior

mysql> create table myset3(i int, s set('4','5', '6') default 7);
ERROR 1067 (42000): Invalid default value for 's'

[zhexuany]

With the following ddl query,

mysql> create table myset3(i int, s set('4','5', '6') default 6);

TiDB's behavior:

Query OK, 0 rows affected (0.18 sec)

mysql> show create table myset3;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                       |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| myset3 | CREATE TABLE `myset3` (
  `i` int(11) DEFAULT NULL,
  `s` set('4','5','6') DEFAULT '6'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL's behavior:

mysql> show create table myset4;
+--------+-------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                  |
+--------+-------------------------------------------------------------------------------------------------------------------------------+
| myset4 | CREATE TABLE `myset4` (
  `i` int(11) DEFAULT NULL,
  `s` set('4','5','6') DEFAULT '5,6'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[zhexuany]

I suspect something is wrong against the filling the default value.

[zhexuany]

With the the following schema

create table myset4(i int, s set('104','105','106') default 4);

TiDB's behavior:

mysql> create table myset4(i int, s set('104','105','106') default 4);
Query OK, 0 rows affected (0.14 sec)
mysql> insert into myset4(i) values(1);
Query OK, 1 row affected (0.04 sec)

mysql> select * from myset4;
+------+------+
| i    | s    |
+------+------+
|    1 | 106  |
+------+------+
1 row in set (0.01 sec)

MySQL's behavior:

mysql> create table myset4(i int, s set('104','105','106') default 4);
ERROR 1067 (42000): Invalid default value for 's'

[zimulala]

In MySQL

mysql> create table t_set (a int, b set('1', '5') default 1);
Query OK, 0 rows affected (0.02 sec)
mysql> create table t (a int, b set('1', '5') default 2);
ERROR 1067 (42000): Invalid default value for 'b'
mysql> create table t (a int, b set('4', '5') default 1);

mysql> create table t (a int, b set('4', '5') default 2);
ERROR 1067 (42000): Invalid default value for 'b'
mysql> create table t (a int, b set('4', '5') default 4);
ERROR 1067 (42000): Invalid default value for 'b'
mysql> create table t (a int, b set('4', '5') default 1);
ERROR 1067 (42000): Invalid default value for 'b'
mysql> create table t (a int, b set('4', '5') default 3);
ERROR 1067 (42000): Invalid default value for 'b'

The above behavior is inconsistent with the description of MySQL set type