View Ticket
Ticket Hash: 86ba67afafded936963d2339d13a4aba8277ac30
Title: Incorrect affinity for CHECK constraints
Status: Fixed Type: Code_Defect
Severity: Important Priority: Low
Subsystem: Code_Generator Resolution: Fixed
Last Modified: 2020-02-04 01:50:11
Version Found In: 3.31.1
User Comments:
drh added on 2020-02-01 14:30:11: (text/x-fossil-wiki)
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'));
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).

drh added on 2020-02-03 16:07:19: (text/x-fossil-wiki)
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.