Bug #92946 NOT NULL column with functions as default values unexpected work
Submitted: 25 Oct 2018 10:14 Modified: 3 Jan 11:35
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.13 OS:CentOS (7.5)
Assigned to: CPU Architecture:x86

[25 Oct 2018 10:14] tsubasa tanaka
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)
[25 Oct 2018 10:34] Umesh Shastry
Hello Tanaka-San,

Thank you for the report and test case.

regards,
Umesh
[9 Nov 2018 11:34] Catalin Besleaga
Was fixed in a follow-up patch but did not make it to 8.0.13
[3 Jan 11:35] Erlend Dahl
No longer reproducible on 8.0.14.