SQLite

Affinity transformations on an INSERT occur after the CHECK constraint checks, not before. This can cause problems. For example:

CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text'));
INSERT INTO t1 VALUES('123');
PRAGMA integrity_check;

The initial insert succeeds because the input is TEXT before affinity is applied. But afterwards, the CHECK constraint will always fail.

This problem was reported on the mailing list by Keith Medcalf. The problem has existed in the code since before version 3.1.0 (2015-01-21).

Check-in [1d4f86201dab9a22] causes affinity to be applied prior to CHECK constraint processing, and thereby clears the integrity-check problem. It turns out that this was already being done for tables that contained one or more generated columns. Now it is done for all tables.

There is the potential to break legacy databases, however. After this fix, the INSERT in the example shown in the initial problem statement above now fails. Formerly, the INSERT worked, but a subsequent integrity-check failed. If an application uses a schema like the one shown in the example but never calls integrity-check, that application would work fine in version 3.31.1 and earlier but would begin to report constraint errors when upgrading to 3.32.0. On the other hand, why would an application ever add a CHECK constraint that requires the input type to an INSERT to be different from the column type? That's just weird. One can argue that a programmer who does something like that deserves whatever happens.

A more common construct is to try to provide "strict" type checking using constructs like this:

CREATE TABLE t2(x INT CHECK(typeof(x)=='integer'));
INSERT INTO t2(x) VALUES('123');

The INSERT into t2 used to fail. But after this bug is fixed, the INSERT will succeed, as the '123' string is converted into an integer 123 prior to the CHECK constraint running. If an application is doing this, then the fix should not cause any new constraint failures. However, it will fail to prevent the use of a string value to specify an integer constant. The CHECK constraint will continue to enforce the rule that no non-integer values are allowed in the column, which seems to be the intent. But it will allow integer values to be added using string-literal or floating-point literal notation, rather than just integer literal notation. That seems harmless and unlikely to break anything.