Description:
8.0.13 new feature, DEFAULT value with native function, breaks behavior of sql_mode = STRICT_TRANS_TABLES.
- Set DEFAULT value with NULL-unsafe function (ex. CHARACTER_LENGTH(NULL) IS NULL) and NOT NULL definition
- INSERT a value to lead returning NULL.
- Conflict between DEFAULT returning NULL and NOT NULL constraint and then MySQL fallbacks NULL into datatype implicit default value even in sql_mode = STRICT_TRANS_TABLES.
How to repeat:
mysql80 43> CREATE TABLE t1 (num serial, val varchar(32), val_len int NOT NULL DEFAULT (CHARACTER_LENGTH(val)));
Query OK, 0 rows affected (0.12 sec)
mysql80 43> INSERT INTO t1 (num, val) VALUES (1, 'one');
Query OK, 1 row affected (0.07 sec)
mysql80 43> SELECT * FROM t1;
+-----+------+---------+
| num | val | val_len |
+-----+------+---------+
| 1 | one | 3 |
+-----+------+---------+
1 row in set (0.00 sec)
mysql80 43> INSERT INTO t1 (num, val) VALUES (2, NULL); -- This should be error.
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql80 43> SHOW WARNINGS;
+-------+------+---------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------+
| Error | 1048 | Column 'val_len' cannot be null |
+-------+------+---------------------------------+
1 row in set (0.00 sec)
mysql80 43> SELECT * FROM t1;
+-----+------+---------+
| num | val | val_len |
+-----+------+---------+
| 1 | one | 3 |
| 2 | NULL | 0 | <-- This row should not be inserted.
+-----+------+---------+
2 rows in set (0.00 sec)
mysql80 43> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)