Enhance the DELETE logic so that it can make use of WHERE_ONEPASS_DESIRED

for rowid tables.

FossilOrigin-Name: 8f479a72758ab6fedb171ada612b1963143c32fa

5 files changed, 87 insertions, 61 deletions

diff --git a/src/delete.c b/src/delete.c
index 4fb394659..c36c9447f 100644
--- a/src/delete.c
+++ b/src/delete.c
@@ -234,12 +234,16 @@ void sqlite3DeleteFrom(
   int iTabCur;           /* Cursor number for the table */
   int iDataCur;          /* VDBE cursor for the canonical data source */
   int iIdxCur;           /* Cursor number of the first index */
+  int nIdx;              /* Number of indices */
   sqlite3 *db;           /* Main database structure */
   AuthContext sContext;  /* Authorization context */
   NameContext sNC;       /* Name context to resolve expressions in */
   int iDb;               /* Database number */
   int memCnt = -1;       /* Memory cell used for change counting */
   int rcauth;            /* Value returned by authorization callback */
+  int okOnePass;         /* True for one-pass algorithm without the FIFO */
+  int aiCurOnePass[2];   /* The write cursors opened by WHERE_ONEPASS */
+  u8 *aToOpen = 0;       /* Open cursor iTabCur+j if aToOpen[j] is true */
 
 #ifndef SQLITE_OMIT_TRIGGER
   int isView;                  /* True if attempting to delete from a view */
@@ -295,11 +299,11 @@ void sqlite3DeleteFrom(
   }
   assert(!isView || pTrigger);
 
-  /* Assign  cursor number to the table and all its indices.
+  /* Assign cursor numbers to the table and all its indices.
   */
   assert( pTabList->nSrc==1 );
   iTabCur = pTabList->a[0].iCursor = pParse->nTab++;
-  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
+  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){
     pParse->nTab++;
   }
 
@@ -399,8 +403,8 @@ void sqlite3DeleteFrom(
 
     /* Open cursors for all indices of the table.
     */
-    sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite,
-                               iTabCur, &iDataCur, &iIdxCur);
+    sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iTabCur, aToOpen,
+                               &iDataCur, &iIdxCur);
 
     /* Loop over the primary keys to be deleted. */
     addr = sqlite3VdbeAddOp1(v, OP_Rewind, iEph);
@@ -424,22 +428,39 @@ void sqlite3DeleteFrom(
     ** all rowids to be deleted into a RowSet.
     */
     int iRowSet = ++pParse->nMem;   /* Register for rowset of rows to delete */
-    int iRowid = ++pParse->nMem;    /* Used for storing rowid values. */
     int regRowid;                   /* Actual register containing rowids */
 
     /* Collect rowids of every row to be deleted.
     */
     sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);
     pWInfo = sqlite3WhereBegin(
-        pParse, pTabList, pWhere, 0, 0, WHERE_DUPLICATES_OK, 0
+        pParse, pTabList, pWhere, 0, 0,
+        WHERE_DUPLICATES_OK|WHERE_ONEPASS_DESIRED, iTabCur+1
     );
     if( pWInfo==0 ) goto delete_from_cleanup;
-    regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iTabCur, iRowid, 0);
-    sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid);
+    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
     if( db->flags & SQLITE_CountRows ){
       sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
     }
+    regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iTabCur,
+                                        pParse->nMem+1, 0);
+    if( regRowid>pParse->nMem ) pParse->nMem = regRowid;
+    if( okOnePass ){
+      aToOpen = sqlite3DbMallocRaw(db, nIdx+2);
+      if( aToOpen==0 ) goto delete_from_cleanup;
+      memset(aToOpen, 1, nIdx+1);
+      aToOpen[nIdx+1] = 0;
+      if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iTabCur] = 0;
+      if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iTabCur] = 0;
+      addr = sqlite3VdbeAddOp0(v, OP_Goto);
+    }else{
+      sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid);
+    }
     sqlite3WhereEnd(pWInfo);
+    if( okOnePass ){
+      sqlite3VdbeAddOp0(v, OP_Halt);
+      sqlite3VdbeJumpHere(v, addr);
+    }
 
     /* Delete every item whose key was written to the list during the
     ** database scan.  We have to delete items after the scan is complete
@@ -451,20 +472,22 @@ void sqlite3DeleteFrom(
     ** only effect this statement has is to fire the INSTEAD OF 
     ** triggers.  */
     if( !isView ){
-      sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iTabCur,
+      sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iTabCur, aToOpen,
                                  &iDataCur, &iIdxCur);
       assert( iDataCur==iTabCur );
       assert( iIdxCur==iDataCur+1 );
     }
 
-    addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, end, iRowid);
+    if( !okOnePass ){    
+      addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, end, regRowid);
+    }
 
     /* Delete the row */
 #ifndef SQLITE_OMIT_VIRTUALTABLE
     if( IsVirtual(pTab) ){
       const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
       sqlite3VtabMakeWritable(pParse, pTab);
-      sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, iRowid, pVTab, P4_VTAB);
+      sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, regRowid, pVTab, P4_VTAB);
       sqlite3VdbeChangeP5(v, OE_Abort);
       sqlite3MayAbort(pParse);
     }else
@@ -472,11 +495,13 @@ void sqlite3DeleteFrom(
     {
       int count = (pParse->nested==0);    /* True to count changes */
       sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
-                               iRowid, 1, count, OE_Default, 0);
+                               regRowid, 1, count, OE_Default, okOnePass);
     }
 
     /* End of the delete loop */
-    sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
+    if( !okOnePass ){
+      sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
+    }
     sqlite3VdbeResolveLabel(v, end);
 
     /* Close the cursors open on the table and its indexes. */
@@ -510,6 +535,7 @@ delete_from_cleanup:
   sqlite3AuthContextPop(&sContext);
   sqlite3SrcListDelete(db, pTabList);
   sqlite3ExprDelete(db, pWhere);
+  sqlite3DbFree(db, aToOpen);
   return;
 }
 /* Make sure "isView" and other macros defined above are undefined. Otherwise
diff --git a/src/insert.c b/src/insert.c
index 967abac84..b1a9104f7 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -820,7 +820,7 @@ void sqlite3Insert(
   /* If this is not a view, open the table and and all indices */
   if( !isView ){
     int nIdx;
-    nIdx = sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, -1,
+    nIdx = sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, -1, 0,
                                       &iDataCur, &iIdxCur);
     aRegIdx = sqlite3DbMallocRaw(db, sizeof(int)*(nIdx+1));
     if( aRegIdx==0 ){
@@ -1680,16 +1680,19 @@ int sqlite3OpenTableAndIndices(
   Table *pTab,     /* Table to be opened */
   int op,          /* OP_OpenRead or OP_OpenWrite */
   int iBase,       /* Use this for the table cursor, if there is one */
+  u8 *aToOpen,     /* If not NULL: boolean for each table and index */
   int *piDataCur,  /* Write the database source cursor number here */
   int *piIdxCur    /* Write the first index cursor number here */
 ){
   int i;
   int iDb;
+  int iDataCur;
   Index *pIdx;
   Vdbe *v;
 
   assert( op==OP_OpenRead || op==OP_OpenWrite );
   if( IsVirtual(pTab) ){
+    assert( aToOpen==0 );
     *piDataCur = 0;
     *piIdxCur = 1;
     return 0;
@@ -1698,20 +1701,25 @@ int sqlite3OpenTableAndIndices(
   v = sqlite3GetVdbe(pParse);
   assert( v!=0 );
   if( iBase<0 ) iBase = pParse->nTab;
-  if( HasRowid(pTab) ){
-    *piDataCur = iBase++;
-    sqlite3OpenTable(pParse, *piDataCur, iDb, pTab, op);
+  iDataCur = iBase++;
+  if( piDataCur ) *piDataCur = iDataCur;
+  if( HasRowid(pTab) && (aToOpen==0 || aToOpen[0]) ){
+    sqlite3OpenTable(pParse, iDataCur, iDb, pTab, op);
   }else{
     sqlite3TableLock(pParse, iDb, pTab->tnum, op==OP_OpenWrite, pTab->zName);
   }
-  *piIdxCur = iBase;
+  if( piIdxCur ) *piIdxCur = iBase;
   for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
     int iIdxCur = iBase++;
     assert( pIdx->pSchema==pTab->pSchema );
-    if( pIdx->autoIndex==2 && !HasRowid(pTab) ) *piDataCur = iIdxCur;
-    sqlite3VdbeAddOp3(v, op, iIdxCur, pIdx->tnum, iDb);
-    sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
-    VdbeComment((v, "%s", pIdx->zName));
+    if( pIdx->autoIndex==2 && !HasRowid(pTab) && piDataCur ){
+      *piDataCur = iIdxCur;
+    }
+    if( aToOpen==0 || aToOpen[i+1] ){
+      sqlite3VdbeAddOp3(v, op, iIdxCur, pIdx->tnum, iDb);
+      sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
+      VdbeComment((v, "%s", pIdx->zName));
+    }
   }
   if( iBase>pParse->nTab ) pParse->nTab = iBase;
   return i;
diff --git a/src/pragma.c b/src/pragma.c
index 76a452c46..bbd27b8c1 100644
--- a/src/pragma.c
+++ b/src/pragma.c
@@ -1891,7 +1891,7 @@ void sqlite3Pragma(
         sqlite3VdbeJumpHere(v, addr);
         sqlite3ExprCacheClear(pParse);
         sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenRead,
-                                   1, &iDataCur, &iIdxCur);
+                                   1, 0, &iDataCur, &iIdxCur);
         sqlite3VdbeAddOp2(v, OP_Integer, 0, 7);
         for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
           sqlite3VdbeAddOp2(v, OP_Integer, 0, 8+j); /* index entries counter */
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index e6385e27c..f52c6c1d1 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -2942,7 +2942,7 @@ int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*);
 void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
                                      u8,u8,int,int*);
 void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int);
-int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, int*, int*);
+int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, u8*, int*, int*);
 void sqlite3BeginWriteOperation(Parse*, int, int);
 void sqlite3MultiWrite(Parse*);
 void sqlite3MayAbort(Parse*);
diff --git a/src/update.c b/src/update.c
index 34176603e..ec565660e 100644
--- a/src/update.c
+++ b/src/update.c
@@ -101,6 +101,7 @@ void sqlite3Update(
   Index *pIdx;           /* For looping over indices */
   Index *pPk;            /* The PRIMARY KEY index for WITHOUT ROWID tables */
   int nIdx;              /* Number of indices that need updating */
+  int iBaseCur;          /* Base cursor number */
   int iDataCur;          /* Cursor for the canonical data btree */
   int iIdxCur;           /* Cursor for the first index */
   sqlite3 *db;           /* The database structure */
@@ -108,6 +109,7 @@ void sqlite3Update(
   int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
                          ** an expression for the i-th column of the table.
                          ** aXRef[i]==-1 if the i-th column is not changed. */
+  u8 *aToOpen;           /* 1 for tables and indices to be opened */
   u8 chngPk;             /* PRIMARY KEY changed in a WITHOUT ROWID table */
   u8 chngRowid;          /* Rowid changed in a normal table */
   u8 chngKey;            /* Either chngPk or chngRowid */
@@ -176,16 +178,13 @@ void sqlite3Update(
   if( sqlite3IsReadOnly(pParse, pTab, tmask) ){
     goto update_cleanup;
   }
-  aXRef = sqlite3DbMallocRaw(db, sizeof(int) * pTab->nCol );
-  if( aXRef==0 ) goto update_cleanup;
-  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
 
   /* Allocate a cursors for the main database table and for all indices.
   ** The index cursors might not be used, but if they are used they
   ** need to occur right after the database cursor.  So go ahead and
   ** allocate enough space, just in case.
   */
-  pTabList->a[0].iCursor = iDataCur = pParse->nTab++;
+  pTabList->a[0].iCursor = iBaseCur = iDataCur = pParse->nTab++;
   iIdxCur = iDataCur+1;
   pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab);
   for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){
@@ -196,6 +195,17 @@ void sqlite3Update(
     pParse->nTab++;
   }
 
+  /* Allocate space for aXRef[], aRegIdx[], and aToOpen[].  
+  ** Initialize aXRef[] and aToOpen[] to their default values.
+  */
+  aXRef = sqlite3DbMallocRaw(db, sizeof(int) * (pTab->nCol+nIdx) + nIdx+2 );
+  if( aXRef==0 ) goto update_cleanup;
+  aRegIdx = aXRef+pTab->nCol;
+  aToOpen = (u8*)(aRegIdx+nIdx);
+  memset(aToOpen, 1, nIdx+1);
+  aToOpen[nIdx+1] = 0;
+  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
+
   /* Initialize the name-context */
   memset(&sNC, 0, sizeof(sNC));
   sNC.pParse = pParse;
@@ -256,15 +266,10 @@ void sqlite3Update(
 
   hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngKey);
 
-  /* Allocate memory for the array aRegIdx[].  There is one entry in the
-  ** array for each index associated with table being updated.  Fill in
-  ** the value with a register number for indices that are to be used
-  ** and with zero for unused indices.
+  /* There is one entry in the aRegIdx[] array for each index on the table
+  ** being updated.  Fill in aRegIdx[] with a register number that will hold
+  ** the key for accessing each index.  
   */
-  if( nIdx>0 ){
-    aRegIdx = sqlite3DbMallocRaw(db, sizeof(Index*) * nIdx );
-    if( aRegIdx==0 ) goto update_cleanup;
-  }
   for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
     int reg;
     if( chngKey || hasFK || pIdx->pPartIdxWhere || pIdx==pPk ){
@@ -278,6 +283,7 @@ void sqlite3Update(
         }
       }
     }
+    if( reg==0 ) aToOpen[j+1] = 0;
     aRegIdx[j] = reg;
   }
 
@@ -401,42 +407,29 @@ void sqlite3Update(
     ** action, then we need to open all indices because we might need
     ** to be deleting some records.
     */
-    if( !okOnePass && HasRowid(pTab) ){
-      sqlite3OpenTable(pParse, iDataCur, iDb, pTab, OP_OpenWrite); 
-    }
-    sqlite3TableLock(pParse, iDb, pTab->tnum, 1, pTab->zName);
     if( onError==OE_Replace ){
-      openAll = 1;
+      memset(aToOpen, 1, nIdx+1);
     }else{
-      openAll = 0;
       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
         if( pIdx->onError==OE_Replace ){
-          openAll = 1;
+          memset(aToOpen, 1, nIdx+1);
           break;
         }
       }
     }
-    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
-      int iThisCur = iIdxCur+i;
-      assert( aRegIdx );
-      if( (openAll || aRegIdx[i]>0)
-       && iThisCur!=aiCurOnePass[1]
-      ){
-        assert( iThisCur!=aiCurOnePass[0] );
-        sqlite3VdbeAddOp3(v, OP_OpenWrite, iThisCur, pIdx->tnum, iDb);
-        sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
-        assert( pParse->nTab>iThisCur );
-        VdbeComment((v, "%s", pIdx->zName));
-        if( okOnePass && pPk && iThisCur==iDataCur ){
-          sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak,
-                               regKey, nKey);
-        }
-      }
+    if( okOnePass ){
+      if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0;
+      if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0;
     }
+    sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iBaseCur, aToOpen,
+                               0, 0);
   }
 
   /* Top of the update loop */
   if( okOnePass ){
+    if( pPk ){
+      sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey, nKey);
+    }
     labelContinue = labelBreak;
     sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak);
   }else if( pPk ){
@@ -656,8 +649,7 @@ void sqlite3Update(
 
 update_cleanup:
   sqlite3AuthContextPop(&sContext);
-  sqlite3DbFree(db, aRegIdx);
-  sqlite3DbFree(db, aXRef);
+  sqlite3DbFree(db, aXRef); /* Also frees aRegIdx[] and aToOpen[] */
   sqlite3SrcListDelete(db, pTabList);
   sqlite3ExprListDelete(db, pChanges);
   sqlite3ExprDelete(db, pWhere);