Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 11.4.12
-
Can result in unexpected behaviour
Description
A column with compression enabled automatically adds DEFAULT ''
CREATE TABLE `t` ( |
`c1` longtext COMPRESSED NOT NULL , |
`nc1` longtext NOT NULL, |
`c2` longtext COMPRESSED NOT NULL DEFAULT '', |
`nc2` longtext NOT NULL DEFAULT '' |
) ENGINE=InnoDB
|
SHOW CREATE TABLE t; |
shows:
CREATE TABLE `t` ( |
`c1` longtext /*M!100301 COMPRESSED*/ NOT NULL DEFAULT '', |
`nc1` longtext NOT NULL, |
`c2` longtext /*M!100301 COMPRESSED*/ NOT NULL DEFAULT '', |
`nc2` longtext NOT NULL DEFAULT '' |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
SHOW FULL COLUMNS FROM `t` |
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
|---|---|---|---|---|---|---|---|---|
| c1 | longtext /*M!100301 COMPRESSED*/ | utf8mb4_general_ci | NO | select,insert,update,references | ||||
| nc1 | longtext | utf8mb4_general_ci | NO | NULL | select,insert,update,references | |||
| c2 | longtext /*M!100301 COMPRESSED*/ | utf8mb4_general_ci | NO | '' | select,insert,update,references | |||
| nc2 | longtext | utf8mb4_general_ci | NO | '' | select,insert,update,references |
Note the different default values - empty string for c1 and two single quotes for c2
This insert statement succeeds, so c1 effectively has a default value:
INSERT INTO `t` (`nc1`) VALUES ('x') |