Bug #88049 ENUM, NOT NULL; DEFAULT is not correctly handled

• Submitted: 11 Oct 2017 3:09
• Modified: 12 Oct 2017 1:46
• Reporter: J Scavok
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.7.19-log MySQL Community Server (GPL)
• OS: Windows (Win 7 Pro 64-bit)
• CPU Architecture: Any
• Tags: DEFAULT, enum, NOT NULL, pos

[11 Oct 2017 3:09] J Scavok

Description:
From https://dev.mysql.com/doc/refman/5.7/en/enum.html#enum-nulls:

"If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values."

In actuality, the default value is ''

How to repeat:
set @old_sql_mode=@@sql_mode;
set sql_mode='';

create table `e_table` (
  `e` enum('unknown', 'A', 'B', 'C') NOT NULL
);

show create table `e_table`;          # Note absence of DEFAULT 'unknown'

insert into `e_table` values('A'),(NULL),(''),('X');

select * from `e_table`;              # Incorrect results--
select * from `e_table` where `e`=''; # Should be 'unknown', not ''

set sql_mode=@old_sql_mode;

[11 Oct 2017 3:39] J Scavok

P.S.

Even when the CREATE TABLE is changed to include DEFAULT 'unknown', incorrect results are still produced.

[11 Oct 2017 7:25] MySQL Verification Team

Hello J Scavok,

Thank you for the report.
Imho, this is an expected behavior. Quoting from the same referenced page "If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value..".

From the provided test case - You are trying to insert invalid values i.e (NULL),(''),('X') which are not in the enum list and hence it is inserting empty string which is expected. 

-- sql_mode='';

root@localhost [test]> truncate e_table;
Query OK, 0 rows affected (0.00 sec)

-- Use the keyword DEFAULT to set a column explicitly to its default value

root@localhost [test]> insert into `e_table` values('A'),(NULL),(''),('X'),(DEFAULT);
Query OK, 5 rows affected, 3 warnings (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 3

root@localhost [test]> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1048 | Column 'e' cannot be null              |
| Warning | 1265 | Data truncated for column 'e' at row 3 |
| Warning | 1265 | Data truncated for column 'e' at row 4 |
+---------+------+----------------------------------------+
3 rows in set (0.00 sec)

root@localhost [test]> select * from e_table;
+---------+
| e       |
+---------+
| A       |
|         |
|         |
|         |
| unknown |
+---------+
5 rows in set (0.00 sec)

^^ If you see, it does as expected here.

root@localhost [test]> truncate e_table;
Query OK, 0 rows affected (0.00 sec)

-- Below issue already tracked in Bug #84055 (If strict mode is not enabled, MySQL uses the implicit default value for any column that has no explicitly defined default. If strict mode is enabled, an error occurs if any column has no default value.)

root@localhost [test]> insert into `e_table` values(default(e));
ERROR 1364 (HY000): Field 'e' doesn't have a default value
root@localhost [test]>
root@localhost [test]> select * from `e_table`;
Empty set (0.00 sec)

-- If both the column list and the VALUES list are empty, INSERT creates a row with each column set to its default value and since ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values.

root@localhost [test]>  insert into `e_table` values();
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> select * from `e_table`;
+---------+
| e       |
+---------+
| unknown |
+---------+
1 row in set (0.00 sec)

Thanks,
Umesh

[12 Oct 2017 1:46] J Scavok

Umesh: Thank you for your thorough explanation.