SQLite

View Ticket
Login
Ticket Hash: bba7b69f9849b5bf11b4459b6548befa5eb24188
Title: REINDEX on WITHOUT ROWID table with DESC PRIMARY KEY corrupts automatic UNIQUE index
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-05-14 00:43:27
Version Found In:
User Comments:
dan added on 2019-05-13 20:44:24: (text/x-fossil-wiki)
For example:

<verbatim>
  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;
</verbatim>

drh added on 2019-05-14 00:22:42: (text/x-fossil-wiki)
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.