Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -1926,10 +1926,14 @@ for(i=0, j=pIdx->nKeyCol; inKeyCol, pPk, i) ){ testcase( hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) ); pIdx->aiColumn[j] = pPk->aiColumn[i]; pIdx->azColl[j] = pPk->azColl[i]; + if( pPk->aSortOrder[i] ){ + /* See ticket https://www.sqlite.org/src/info/bba7b69f9849b5bf */ + pIdx->bAscKeyBug = 1; + } j++; } } assert( pIdx->nColumn>=pIdx->nKeyCol+n ); assert( pIdx->nColumn>=j ); @@ -3047,11 +3051,20 @@ sqlite3VdbeJumpHere(v, j2); }else{ addr2 = sqlite3VdbeCurrentAddr(v); } sqlite3VdbeAddOp3(v, OP_SorterData, iSorter, regRecord, iIdx); - sqlite3VdbeAddOp1(v, OP_SeekEnd, iIdx); + if( !pIndex->bAscKeyBug ){ + /* This OP_SeekEnd opcode makes index insert for a REINDEX go much + ** faster by avoiding unnecessary seeks. But the optimization does + ** not work for UNIQUE constraint indexes on WITHOUT ROWID tables + ** with DESC primary keys, since those indexes have there keys in + ** a different order from the main table. + ** See ticket: https://www.sqlite.org/src/info/bba7b69f9849b5bf + */ + sqlite3VdbeAddOp1(v, OP_SeekEnd, iIdx); + } sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdx, regRecord); sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); sqlite3ReleaseTempReg(pParse, regRecord); sqlite3VdbeAddOp2(v, OP_SorterNext, iSorter, addr2); VdbeCoverage(v); sqlite3VdbeJumpHere(v, addr1); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -2251,10 +2251,11 @@ unsigned isResized:1; /* True if resizeIndexObject() has been called */ unsigned isCovering:1; /* True if this is a covering index */ unsigned noSkipScan:1; /* Do not try to use skip-scan if true */ unsigned hasStat1:1; /* aiRowLogEst values come from sqlite_stat1 */ unsigned bNoQuery:1; /* Do not use this index to optimize queries */ + unsigned bAscKeyBug:1; /* True if the bba7b69f9849b5bf bug applies */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int nSample; /* Number of elements in aSample[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ tRowcnt *aAvgEq; /* Average nEq values for keys not in aSample */ IndexSample *aSample; /* Samples of the left-most key */ Index: test/without_rowid1.test ================================================================== --- test/without_rowid1.test +++ test/without_rowid1.test @@ -397,7 +397,17 @@ CREATE INDEX t11a ON t11(a COLLATE NOCASE); INSERT INTO t11(a,b) VALUES ('A',1),('a',2); PRAGMA integrity_check; SELECT a FROM t11 ORDER BY a COLLATE binary; } {ok A a} + +# 2019-05-13 ticket https://www.sqlite.org/src/info/bba7b69f9849b5b +do_execsql_test 12.1 { + DROP TABLE IF EXISTS t0; + 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; +} {ok} + finish_test