|13:37||• Fixed ticket [79cad5e4]: UPSERT results in a corrupt database plus 4 other changes (artifact: 2f1cb386 user: drh)|
|13:34||On an UPSERT when the order of constraint checks is rearranged, make sure that the affinity transformations on the inserted content occur before any of the constraint checks. Fix for ticket [79cad5e4b2e219dd197242e9e5f4e]. (check-in: d120c45f user: drh tags: trunk)|
|12:50||• Ticket [79cad5e4] UPSERT results in a corrupt database status still Open with 3 other changes (artifact: 131ac5b2 user: drh)|
|12:12||• Ticket [79cad5e4]: 6 changes (artifact: bea01e21 user: drh)|
|12:11||• New ticket [79cad5e4]. (artifact: 151ec237 user: drh)|
|Title:||UPSERT results in a corrupt database|
|Last Modified:||2018-07-11 13:37:14|
|Version Found In:||3.24.0|
drh added on 2018-07-11 12:11:13:
An UPSERT discovered by OSSFuzz results in a corrupt database file:
CREATE TABLE t1( a INT, b INT, c INT UNIQUE, PRIMARY KEY(a,b) ) WITHOUT ROWID; INSERT INTO t1(a,b) VALUES(json(3),4),(1,2) ON CONFLICT(a,b) DO NOTHING; PRAGMA integrity_check;
The corruption is a missing entry from the unique index and is easily fixed using REINDEX. Nevertheless, this should not be happening.
drh added on 2018-07-11 12:12:20:
Correction: The error case was not found by OSSFuzz but rather by the AFL fuzzer running locally.
drh added on 2018-07-11 12:50:58:
Here is a simplified script to demonstrate the problem:
CREATE TABLE t1(b UNIQUE, a INT PRIMARY KEY) WITHOUT ROWID; INSERT INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING; PRAGMA integrity_check;
drh added on 2018-07-11 13:37:14:
The problem was that the affinity change from '1' to 1 on the PRIMARY KEY column was happening on the first constraint check in bytecode order, which is the UNIQUE constraint. But the PRIMARY KEY constraint was checked first, and hence was checked before the affinity change. The fix was to move the affinity change to before the branch that reorders the constraint checks for the UPSERT.