SQLite: Check-in [21530798]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Comment:	Enable the WHERE_ONEPASS_DESIRED optimization for UPDATE operations on WITHOUT ROWID tables.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA1:	215307985590c2f3f7aa0d5a0b7799155a506045
User & Date:	drh 2013-11-07 21:25:13

Context

2013-11-07
21:32		Fix a compiler warning introduced by the previous check-in. (check-in: 404bd98f user: drh tags: trunk)
21:25		Enable the WHERE_ONEPASS_DESIRED optimization for UPDATE operations on WITHOUT ROWID tables. (check-in: 21530798 user: drh tags: trunk)
19:43		Add the --stats and --summary options to the wordcount.c test program. (check-in: 8aa21e67 user: drh tags: trunk)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/update.c.

#ifndef SQLITE_OMIT_TRIGGER
  int isView;            /* True when updating a view (INSTEAD OF trigger) */
  Trigger *pTrigger;     /* List of triggers on pTab, if required */
  int tmask;             /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
#endif
  int newmask;           /* Mask of NEW.* columns accessed by BEFORE triggers */
  int iEph = 0;          /* Ephemeral table holding all primary key values */


  /* Register Allocations */
  int regRowCount = 0;   /* A count of rows changed */
  int regOldRowid;       /* The old rowid */
  int regNewRowid;       /* The new rowid */
  int regNew;            /* Content of the NEW.* table in triggers */
  int regOld = 0;        /* Content of OLD.* table in triggers */
................................................................................
  
    /* End the database scan loop.
    */
    sqlite3WhereEnd(pWInfo);
  }else{
    int iPk;         /* First of nPk memory cells holding PRIMARY KEY value */
    i16 nPk;         /* Number of components of the PRIMARY KEY */


    assert( pPk!=0 );
    nPk = pPk->nKeyCol;
    iPk = pParse->nMem+1;
    pParse->nMem += nPk;
    regKey = ++pParse->nMem;
    iEph = pParse->nTab++;

    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
    sqlite3VdbeSetP4KeyInfo(pParse, pPk);
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 0, 0);

    if( pWInfo==0 ) goto update_cleanup;

    for(i=0; i<nPk; i++){
      sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pPk->aiColumn[i],
                                      iPk+i);
    }





    sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey,
                      sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
    sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, regKey);


    sqlite3WhereEnd(pWInfo);
    okOnePass = 0;
  }

  /* Initialize the count of updated rows
  */
  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){
    regRowCount = ++pParse->nMem;
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
................................................................................
        VdbeComment((v, "%s", pIdx->zName));
      }
    }
  }

  /* Top of the update loop */
  labelBreak = sqlite3VdbeMakeLabel(v);



  if( pPk ){



    labelContinue = sqlite3VdbeMakeLabel(v);
    sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak);
    addrTop = sqlite3VdbeAddOp2(v, OP_RowKey, iEph, regKey);
    sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0);
  }else if( okOnePass ){
    labelContinue = labelBreak;
    sqlite3VdbeAddOp2(v, OP_IsNull, regOldRowid, labelBreak);
  }else{
    labelContinue = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, labelBreak,
                             regOldRowid);
    sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
  }

  /* If the record number will change, set register regNewRowid to
................................................................................
    /* The row-trigger may have deleted the row being updated. In this
    ** case, jump to the next row. No updates or AFTER triggers are 
    ** required. This behavior - what happens when the row being updated
    ** is deleted or renamed by a BEFORE trigger - is left undefined in the
    ** documentation.
    */
    if( pPk ){
      sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0);
    }else{
      sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
    }

    /* If it did not delete it, the row-trigger may still have modified 
    ** some of the columns of the row being updated. Load the values for 
    ** all columns not modified by the update statement into their 
................................................................................
    /* Do FK constraint checks. */
    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey);
    }

    /* Delete the index entries associated with the current record.  */
    if( pPk ){
      j1 = sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, 0, regKey, 0);
    }else{
      j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, 0, regOldRowid);
    }
    sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, aRegIdx);
  
    /* If changing the record number, delete the old record.  */
    if( hasFK || chngKey || pPk!=0 ){
................................................................................

  sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, 
      TRIGGER_AFTER, pTab, regOldRowid, onError, labelContinue);

  /* Repeat the above with the next record to be updated, until
  ** all record selected by the WHERE clause have been updated.
  */


  if( pPk ){
    sqlite3VdbeResolveLabel(v, labelContinue);
    sqlite3VdbeAddOp2(v, OP_Next, iEph, addrTop);
  }else if( !okOnePass ){

    sqlite3VdbeAddOp2(v, OP_Goto, 0, labelContinue);
  }
  sqlite3VdbeResolveLabel(v, labelBreak);

  /* Close all tables */
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    assert( aRegIdx );








>







 







>







>
|

|
>

>




>
>
>
>
>
|
|
|
>
>

<







 







>
>
>
|
>
>
>




<
<
<







 







|







 







|







 







>
>
|


<
>

#ifndef SQLITE_OMIT_TRIGGER
  int isView;            /* True when updating a view (INSTEAD OF trigger) */
  Trigger *pTrigger;     /* List of triggers on pTab, if required */
  int tmask;             /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
#endif
  int newmask;           /* Mask of NEW.* columns accessed by BEFORE triggers */
  int iEph = 0;          /* Ephemeral table holding all primary key values */
  int nKey;              /* Number of elements in regKey */

  /* Register Allocations */
  int regRowCount = 0;   /* A count of rows changed */
  int regOldRowid;       /* The old rowid */
  int regNewRowid;       /* The new rowid */
  int regNew;            /* Content of the NEW.* table in triggers */
  int regOld = 0;        /* Content of OLD.* table in triggers */
................................................................................
  
    /* End the database scan loop.
    */
    sqlite3WhereEnd(pWInfo);
  }else{
    int iPk;         /* First of nPk memory cells holding PRIMARY KEY value */
    i16 nPk;         /* Number of components of the PRIMARY KEY */
    int addrOpen;    /* Address of the OpenEphemeral instruction */

    assert( pPk!=0 );
    nPk = pPk->nKeyCol;
    iPk = pParse->nMem+1;
    pParse->nMem += nPk;
    regKey = ++pParse->nMem;
    iEph = pParse->nTab++;
    sqlite3VdbeAddOp2(v, OP_Null, 0, iPk);
    addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
    sqlite3VdbeSetP4KeyInfo(pParse, pPk);
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 
                               WHERE_ONEPASS_DESIRED, 0);
    if( pWInfo==0 ) goto update_cleanup;
    okOnePass = sqlite3WhereOkOnePass(pWInfo);
    for(i=0; i<nPk; i++){
      sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pPk->aiColumn[i],
                                      iPk+i);
    }
    if( okOnePass ){
      sqlite3VdbeChangeToNoop(v, addrOpen);
      nKey = nPk;
      regKey = iPk;
    }else{
      sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey,
                        sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
      sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, regKey);
      nKey = 0;
    }
    sqlite3WhereEnd(pWInfo);

  }

  /* Initialize the count of updated rows
  */
  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){
    regRowCount = ++pParse->nMem;
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
................................................................................
        VdbeComment((v, "%s", pIdx->zName));
      }
    }
  }

  /* Top of the update loop */
  labelBreak = sqlite3VdbeMakeLabel(v);
  if( okOnePass ){
    labelContinue = labelBreak;
    sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak);
    if( pPk ){
      sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey, nKey);
    }
  }else if( pPk ){
    labelContinue = sqlite3VdbeMakeLabel(v);
    sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak);
    addrTop = sqlite3VdbeAddOp2(v, OP_RowKey, iEph, regKey);
    sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0);



  }else{
    labelContinue = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, labelBreak,
                             regOldRowid);
    sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
  }

  /* If the record number will change, set register regNewRowid to
................................................................................
    /* The row-trigger may have deleted the row being updated. In this
    ** case, jump to the next row. No updates or AFTER triggers are 
    ** required. This behavior - what happens when the row being updated
    ** is deleted or renamed by a BEFORE trigger - is left undefined in the
    ** documentation.
    */
    if( pPk ){
      sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue,regKey,nKey);
    }else{
      sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid);
    }

    /* If it did not delete it, the row-trigger may still have modified 
    ** some of the columns of the row being updated. Load the values for 
    ** all columns not modified by the update statement into their 
................................................................................
    /* Do FK constraint checks. */
    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey);
    }

    /* Delete the index entries associated with the current record.  */
    if( pPk ){
      j1 = sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, 0, regKey, nKey);
    }else{
      j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, 0, regOldRowid);
    }
    sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, aRegIdx);
  
    /* If changing the record number, delete the old record.  */
    if( hasFK || chngKey || pPk!=0 ){
................................................................................

  sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, 
      TRIGGER_AFTER, pTab, regOldRowid, onError, labelContinue);

  /* Repeat the above with the next record to be updated, until
  ** all record selected by the WHERE clause have been updated.
  */
  if( okOnePass ){
    /* Nothing to do at end-of-loop for a single-pass */
  }else if( pPk ){
    sqlite3VdbeResolveLabel(v, labelContinue);
    sqlite3VdbeAddOp2(v, OP_Next, iEph, addrTop);

  }else{
    sqlite3VdbeAddOp2(v, OP_Goto, 0, labelContinue);
  }
  sqlite3VdbeResolveLabel(v, labelBreak);

  /* Close all tables */
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    assert( aRegIdx );

Changes to src/where.c.

      }else{
        m = pSrc->colUsed & ~columnsInIndex(pProbe);
        pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
      }

      /* Full scan via index */
      if( b

       || ( m==0
         && pProbe->bUnordered==0
         && (!HasRowid(pTab) || pProbe->szIdxRow<pTab->szTabRow)
         && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
         && sqlite3GlobalConfig.bUseCis
         && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
          )
      ){
        pNew->iSortIdx = b ? iSortIdx : 0;
        if( m==0 ){
................................................................................
          **  +  The extra factor K of between 1.1 and 3.0 that depends
          **     on the relative sizes of the table and the index.  K
          **     is smaller for smaller indices, thus favoring them.
          */
          pNew->rRun = sqlite3LogEstAdd(rSize,rLogSize) + 1 +
                        (15*pProbe->szIdxRow)/pTab->szTabRow;
        }else{
          assert( b!=0 ); 
          /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N)
          ** which we will simplify to just N*log2(N) */
          pNew->rRun = rSize + rLogSize;
        }
        whereLoopOutputAdjust(pWC, pNew);
        rc = whereLoopInsert(pBuilder, pNew);
        pNew->nOut = rSize;
................................................................................
  ** The one-pass algorithm only works if the WHERE clause constrains
  ** the statement to update a single row.
  */
  assert( (wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || pWInfo->nLevel==1 );
  if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 
   && (pWInfo->a[0].pWLoop->wsFlags & WHERE_ONEROW)!=0 ){
    pWInfo->okOnePass = 1;

    pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY;

  }

  /* Open all tables in the pTabList and any indices selected for
  ** searching those tables.
  */
  notReady = ~(Bitmask)0;
  for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){

      }else{
        m = pSrc->colUsed & ~columnsInIndex(pProbe);
        pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
      }

      /* Full scan via index */
      if( b
       || !HasRowid(pTab)
       || ( m==0
         && pProbe->bUnordered==0
         && (pProbe->szIdxRow<pTab->szTabRow)
         && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
         && sqlite3GlobalConfig.bUseCis
         && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
          )
      ){
        pNew->iSortIdx = b ? iSortIdx : 0;
        if( m==0 ){
................................................................................
          **  +  The extra factor K of between 1.1 and 3.0 that depends
          **     on the relative sizes of the table and the index.  K
          **     is smaller for smaller indices, thus favoring them.
          */
          pNew->rRun = sqlite3LogEstAdd(rSize,rLogSize) + 1 +
                        (15*pProbe->szIdxRow)/pTab->szTabRow;
        }else{

          /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N)
          ** which we will simplify to just N*log2(N) */
          pNew->rRun = rSize + rLogSize;
        }
        whereLoopOutputAdjust(pWC, pNew);
        rc = whereLoopInsert(pBuilder, pNew);
        pNew->nOut = rSize;
................................................................................
  ** The one-pass algorithm only works if the WHERE clause constrains
  ** the statement to update a single row.
  */
  assert( (wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || pWInfo->nLevel==1 );
  if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 
   && (pWInfo->a[0].pWLoop->wsFlags & WHERE_ONEROW)!=0 ){
    pWInfo->okOnePass = 1;
    if( HasRowid(pTabList->a[0].pTab) ){
      pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY;
    }
  }

  /* Open all tables in the pTabList and any indices selected for
  ** searching those tables.
  */
  notReady = ~(Bitmask)0;
  for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){