/ Check-in [1ddbb0ff]
Login

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

Overview
Comment:Fixes to the logic for constraint check reordering during upsert. Improved comments on constraint check bytecode. Add an assert that prevents the same label from being resolved more than once.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256:1ddbb0ff5586ef5ca987e4309979f24f95eea883ed68937094a2db2d61e75657
User & Date: drh 2018-04-17 20:06:24
Context
2018-04-17
20:09
Added a comment on the assert() added to the previous check-in. check-in: 542547c1 user: drh tags: upsert
20:06
Fixes to the logic for constraint check reordering during upsert. Improved comments on constraint check bytecode. Add an assert that prevents the same label from being resolved more than once. check-in: 1ddbb0ff user: drh tags: upsert
19:29
During PRAGMA vdbe_addoptrace=ON, show calls to sqlite3VdbeResolveLabel() in the debugging output. check-in: 9ff07a06 user: drh tags: upsert
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1503   1503        && pTab->pIndex
  1504   1504       ){
  1505   1505         sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
  1506   1506       }
  1507   1507   
  1508   1508       /* Check to see if the new rowid already exists in the table.  Skip
  1509   1509       ** the following conflict logic if it does not. */
  1510         -    VdbeNoopComment((v, "constraint checks for ROWID"));
         1510  +    VdbeNoopComment((v, "uniqueness check for ROWID"));
  1511   1511       sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
  1512   1512       VdbeCoverage(v);
  1513   1513   
  1514   1514       switch( onError ){
  1515   1515         default: {
  1516   1516           onError = OE_Abort;
  1517   1517           /* Fall thru into the next case */
................................................................................
  1599   1599     for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
  1600   1600       int regIdx;          /* Range of registers hold conent for pIdx */
  1601   1601       int regR;            /* Range of registers holding conflicting PK */
  1602   1602       int iThisCur;        /* Cursor for this UNIQUE index */
  1603   1603       int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */
  1604   1604   
  1605   1605       if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */
  1606         -    VdbeNoopComment((v, "constraint checks for %s", pIdx->zName));
         1606  +    if( pUpIdx==pIdx ){
         1607  +      addrUniqueOk = sAddr.upsertBtm;
         1608  +      upsertBypass = sqlite3VdbeGoto(v, 0);
         1609  +      VdbeComment((v, "Skip upsert subroutine"));
         1610  +      sqlite3VdbeResolveLabel(v, sAddr.upsertTop);
         1611  +    }else{
         1612  +      addrUniqueOk = sqlite3VdbeMakeLabel(v);
         1613  +    }
         1614  +    VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
  1607   1615       if( bAffinityDone==0 ){
  1608   1616         sqlite3TableAffinity(v, pTab, regNewData+1);
  1609   1617         bAffinityDone = 1;
  1610   1618       }
  1611   1619       iThisCur = iIdxCur+ix;
  1612         -    if( pUpIdx==pIdx ){
  1613         -      addrUniqueOk = sAddr.upsertBtm;
  1614         -    }else{
  1615         -      addrUniqueOk = sqlite3VdbeMakeLabel(v);
  1616         -    }
         1620  +
  1617   1621   
  1618   1622       /* Skip partial indices for which the WHERE clause is not true */
  1619   1623       if( pIdx->pPartIdxWhere ){
  1620   1624         sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]);
  1621   1625         pParse->iSelfTab = -(regNewData+1);
  1622   1626         sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, addrUniqueOk,
  1623   1627                               SQLITE_JUMPIFNULL);
................................................................................
  1679   1683       /* Figure out if the upsert clause applies to this index */
  1680   1684       if( pUpIdx==pIdx ){
  1681   1685         if( pUpsert->pUpsertSet==0 ){
  1682   1686           onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
  1683   1687         }else{
  1684   1688           onError = OE_Update;  /* DO UPDATE */
  1685   1689         }
  1686         -      upsertBypass = sqlite3VdbeGoto(v, 0);
  1687         -      VdbeComment((v, "Upsert bypass"));
  1688         -      sqlite3VdbeResolveLabel(v, sAddr.upsertTop);
  1689   1690       }
  1690   1691   
  1691   1692       /* Collision detection may be omitted if all of the following are true:
  1692   1693       **   (1) The conflict resolution algorithm is REPLACE
  1693   1694       **   (2) The table is a WITHOUT ROWID table
  1694   1695       **   (3) There are no secondary indexes on the table
  1695   1696       **   (4) No delete triggers need to be fired if there is a conflict
................................................................................
  1798   1799           sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
  1799   1800               regR, nPkField, 0, OE_Replace,
  1800   1801               (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
  1801   1802           seenReplace = 1;
  1802   1803           break;
  1803   1804         }
  1804   1805       }
  1805         -    sqlite3VdbeResolveLabel(v, addrUniqueOk);
         1806  +    if( pUpIdx==pIdx ){
         1807  +      sqlite3VdbeJumpHere(v, upsertBypass);
         1808  +    }else{
         1809  +      sqlite3VdbeResolveLabel(v, addrUniqueOk);
         1810  +    }
  1806   1811       sqlite3ExprCachePop(pParse);
  1807   1812       if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
  1808         -    if( pUpIdx==pIdx ) sqlite3VdbeJumpHere(v, upsertBypass);
  1809   1813   
  1810   1814     }
  1811   1815     reorderConstraintChecks(v, &sAddr);
  1812   1816     
  1813   1817     *pbMayReplace = seenReplace;
  1814   1818     VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
  1815   1819   }

Changes to src/vdbeaux.c.

   393    393     assert( j>=0 );
   394    394     if( p->aLabel ){
   395    395   #ifdef SQLITE_DEBUG
   396    396       if( p->db->flags & SQLITE_VdbeAddopTrace ){
   397    397         printf("RESOLVE LABEL %d to %d\n", x, v->nOp);
   398    398       }
   399    399   #endif
          400  +    assert( p->aLabel[j]==(-1) );
   400    401       p->aLabel[j] = v->nOp;
   401    402     }
   402    403   }
   403    404   
   404    405   /*
   405    406   ** Mark the VDBE as one that can only be run one time.
   406    407   */