SQLite

For example:

  CREATE TABLE t0 (c0 INTEGER PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID;
  INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
  REINDEX;
  PRAGMA integrity_check;

This problem arises from an oversight in the implementation of WITHOUT ROWID. The original error dates from 2013, and cannot be fixed without breaking backwards compatibility.

A UNIQUE constraint is just a UNIQUE index on the columns named.

Every index is a btree that is keyed by the columns being indexed followed by the primary key for the main table. (Special rules apply if the primary key of the table is one of the columns being indexed.) Normally, the sort order of the primary key column(s) in the index match the sort order of the primary key column(s) in the main table. However, if

1. The table is a WITHOUT ROWID table, and
2. The PRIMARY KEY of the table uses DESC sort order, and
3. The index results from a UNIQUE constraint in the original CREATE TABLE statement, not from a separate CREATE INDEX or CREATE UNIQUE INDEX statement,

Then the primary key columns in the index will use ASC sort order and will thus have a different sort order from the main table. Call this situation the "WITHOUT ROWID DESC PRIMARY KEY UNIQUE constraint index anomaly".

I suspect that this anomaly does not occur in the wild, since I do not recall ever seeing a real-world schema that included a PRIMARY KEY with a DESC sort order.

Normally, the anomaly is without consequence. All queries still work correctly.

However, the REINDEX command was trying to take advantage of the fact that the keys in the main table are in the same order as the keys in the index. Normally this is true and the REINDEX runs much faster because it could avoid some unnecessary seek operations. But this condition is not true for the obscure indexes described above, and will cause the REINDEX to compute an incorrect index.

The fix here is to omit the REINDEX optimization in the corner case where the WITHOUT ROWID DESC PRIMARY KEY UNIQUE constraint index anomaly occurs.