MySQL Bugs: #95192: CHECK constraint comparing column with default value is not enforced

Bug #95192	CHECK constraint comparing column with default value is not enforced
Submitted:	29 Apr 2019 19:41	Modified:	29 Oct 2019 12:25
Reporter:	Georgi Sotirov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Windows (10)
Assigned to:		CPU Architecture:	x86 (64 bits)

Description:
It's not possible to use non-deterministic functions like CURDATE() with CHECK constraints. However, since 8.0.13 it's possible to have the default value of a column as result of function or expression, so I tried the following:

a) create a DATE column with default value CURDATE();
b) implement a check constraint comparing another DATE column with that column;
c) try inserting valid and invalid rows.

Unfortunately, the INSERT queries always succeed and the CHECK constraint is thus not enforced.

How to repeat:
CREATE TABLE tst (
  id INT,
  start_date DATE,
  end_date DATE,
  created DATE DEFAULT (CURDATE()),
  PRIMARY KEY (id),
  CONSTRAINT chk_dat CHECK (start_date >= created)
);

INSERT INTO tst (id, start_date) VALUES (1, '2019-04-29');
INSERT INTO tst (id, start_date) VALUES (2, '2019-04-25');

SELECT * FROM tst;

+----+------------+----------+------------+
| id | start_date | end_date | created    |
+----+------------+----------+------------+
|  1 | 2019-04-29 | NULL     | 2019-04-29 | <-- valid
|  2 | 2019-04-25 | NULL     | 2019-04-29 | <-- invalid
+----+------------+----------+------------+
2 rows in set (0.0004 sec)

Instead, I expected the first query to succeed, while the second to fail for violated CHECK constraint.

Suggested fix:
CHECK constraints should be enforced after all column values for a row are determined (e.g. when coming from defaults).

Thank you for the bug report.

Posted by developer:
 
Fixed in 8.0.18.

CHECK constraints were not enforced when a column took its default
value from an expression.

Confirmed fixed in MySQL 8.0.18 with the provided test case as the second query now properly fails. Thanks!