/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
....
1599
1600
1601
1602
1603
1604
1605








1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
....
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
....
1798
1799
1800
1801
1802
1803
1804



1805

1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
     && pTab->pIndex
    ){
      sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
    }

    /* Check to see if the new rowid already exists in the table.  Skip
    ** the following conflict logic if it does not. */
    VdbeNoopComment((v, "constraint checks for ROWID"));
    sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
    VdbeCoverage(v);

    switch( onError ){
      default: {
        onError = OE_Abort;
        /* Fall thru into the next case */
................................................................................
  for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
    int regIdx;          /* Range of registers hold conent for pIdx */
    int regR;            /* Range of registers holding conflicting PK */
    int iThisCur;        /* Cursor for this UNIQUE index */
    int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */

    if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */








    VdbeNoopComment((v, "constraint checks for %s", pIdx->zName));
    if( bAffinityDone==0 ){
      sqlite3TableAffinity(v, pTab, regNewData+1);
      bAffinityDone = 1;
    }
    iThisCur = iIdxCur+ix;
    if( pUpIdx==pIdx ){
      addrUniqueOk = sAddr.upsertBtm;
    }else{
      addrUniqueOk = sqlite3VdbeMakeLabel(v);
    }

    /* Skip partial indices for which the WHERE clause is not true */
    if( pIdx->pPartIdxWhere ){
      sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]);
      pParse->iSelfTab = -(regNewData+1);
      sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, addrUniqueOk,
                            SQLITE_JUMPIFNULL);
................................................................................
    /* Figure out if the upsert clause applies to this index */
    if( pUpIdx==pIdx ){
      if( pUpsert->pUpsertSet==0 ){
        onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
      }else{
        onError = OE_Update;  /* DO UPDATE */
      }
      upsertBypass = sqlite3VdbeGoto(v, 0);
      VdbeComment((v, "Upsert bypass"));
      sqlite3VdbeResolveLabel(v, sAddr.upsertTop);
    }

    /* Collision detection may be omitted if all of the following are true:
    **   (1) The conflict resolution algorithm is REPLACE
    **   (2) The table is a WITHOUT ROWID table
    **   (3) There are no secondary indexes on the table
    **   (4) No delete triggers need to be fired if there is a conflict
................................................................................
        sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
            regR, nPkField, 0, OE_Replace,
            (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
        seenReplace = 1;
        break;
      }
    }



    sqlite3VdbeResolveLabel(v, addrUniqueOk);

    sqlite3ExprCachePop(pParse);
    if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
    if( pUpIdx==pIdx ) sqlite3VdbeJumpHere(v, upsertBypass);

  }
  reorderConstraintChecks(v, &sAddr);
  
  *pbMayReplace = seenReplace;
  VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
}







|







 







>
>
>
>
>
>
>
>
|





<
<
<
<
|







 







<
<
<







 







>
>
>
|
>


<







1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
....
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619




1620
1621
1622
1623
1624
1625
1626
1627
....
1683
1684
1685
1686
1687
1688
1689



1690
1691
1692
1693
1694
1695
1696
....
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812

1813
1814
1815
1816
1817
1818
1819
     && pTab->pIndex
    ){
      sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
    }

    /* Check to see if the new rowid already exists in the table.  Skip
    ** the following conflict logic if it does not. */
    VdbeNoopComment((v, "uniqueness check for ROWID"));
    sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
    VdbeCoverage(v);

    switch( onError ){
      default: {
        onError = OE_Abort;
        /* Fall thru into the next case */
................................................................................
  for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
    int regIdx;          /* Range of registers hold conent for pIdx */
    int regR;            /* Range of registers holding conflicting PK */
    int iThisCur;        /* Cursor for this UNIQUE index */
    int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */

    if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */
    if( pUpIdx==pIdx ){
      addrUniqueOk = sAddr.upsertBtm;
      upsertBypass = sqlite3VdbeGoto(v, 0);
      VdbeComment((v, "Skip upsert subroutine"));
      sqlite3VdbeResolveLabel(v, sAddr.upsertTop);
    }else{
      addrUniqueOk = sqlite3VdbeMakeLabel(v);
    }
    VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
    if( bAffinityDone==0 ){
      sqlite3TableAffinity(v, pTab, regNewData+1);
      bAffinityDone = 1;
    }
    iThisCur = iIdxCur+ix;






    /* Skip partial indices for which the WHERE clause is not true */
    if( pIdx->pPartIdxWhere ){
      sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]);
      pParse->iSelfTab = -(regNewData+1);
      sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, addrUniqueOk,
                            SQLITE_JUMPIFNULL);
................................................................................
    /* Figure out if the upsert clause applies to this index */
    if( pUpIdx==pIdx ){
      if( pUpsert->pUpsertSet==0 ){
        onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
      }else{
        onError = OE_Update;  /* DO UPDATE */
      }



    }

    /* Collision detection may be omitted if all of the following are true:
    **   (1) The conflict resolution algorithm is REPLACE
    **   (2) The table is a WITHOUT ROWID table
    **   (3) There are no secondary indexes on the table
    **   (4) No delete triggers need to be fired if there is a conflict
................................................................................
        sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
            regR, nPkField, 0, OE_Replace,
            (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
        seenReplace = 1;
        break;
      }
    }
    if( pUpIdx==pIdx ){
      sqlite3VdbeJumpHere(v, upsertBypass);
    }else{
      sqlite3VdbeResolveLabel(v, addrUniqueOk);
    }
    sqlite3ExprCachePop(pParse);
    if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);


  }
  reorderConstraintChecks(v, &sAddr);
  
  *pbMayReplace = seenReplace;
  VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
}

Changes to src/vdbeaux.c.

393
394
395
396
397
398
399

400
401
402
403
404
405
406
  assert( j>=0 );
  if( p->aLabel ){
#ifdef SQLITE_DEBUG
    if( p->db->flags & SQLITE_VdbeAddopTrace ){
      printf("RESOLVE LABEL %d to %d\n", x, v->nOp);
    }
#endif

    p->aLabel[j] = v->nOp;
  }
}

/*
** Mark the VDBE as one that can only be run one time.
*/







>







393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
  assert( j>=0 );
  if( p->aLabel ){
#ifdef SQLITE_DEBUG
    if( p->db->flags & SQLITE_VdbeAddopTrace ){
      printf("RESOLVE LABEL %d to %d\n", x, v->nOp);
    }
#endif
    assert( p->aLabel[j]==(-1) );
    p->aLabel[j] = v->nOp;
  }
}

/*
** Mark the VDBE as one that can only be run one time.
*/