SQLite

The functions found in CHECK constraints should be deterministic - meaning that they should aways give the same answer for the same inputs.

Date and time functions can sometimes be deterministic and sometimes now. They are deterministic if they avoid arguments like 'now' and 'localtime'. The functions are coded in such a way that if those arguments are used in a context where a deterministic function is required, then the function throws an error at run-time.

But this mechanism sometimes does not work. Example:

CREATE TABLE t1(a REAL CHECK( a<julianday('now') ));
INSERT INTO t1(a) VALUES(julianday('now')-0.01); -- works
INSERT INTO t1(a) VALUES(julianday('now')+0.01); -- error

Because of the use of "julianday('now')" inside of a CHECK constraint, *every* insert into table t1 ought to fail. But sometimes the inserts run to completion without error.

This same problem can lead to index corruption:

CREATE TABLE t1(a,b);
CREATE INDEX t1x1 ON t1(julianday('now')+b);
INSERT INTO t1(a,b) VALUES(12345, 15);
.shell sleep 1
SELECT a FROM t1 WHERE julianday('now')+b=julianday('now')+15;
-- previous query returns zero row, but should return one row of 12345
PRAGMA integrity_check; -- reports missing index entry

It turns out that PostgreSQL, MySQL, and SQLServer all allow non-deterministic functions like CURRENT_TIMESTAMP and RANDOM() inside of CHECK constraints. So, SQLite will too.