/ Check-in [c6f71115]
Login

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

Overview
Comment:Fix the handling of "PRAGMA count_changes=ON" with UPSERT. Also improved the implementation of count_changes in other places, without changing the behavior.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c6f71115eb933c2aee295bc31e5139112463c28e15a3b3ea242fd9bac168aed9
User & Date: drh 2018-04-19 23:52:39
Context
2018-04-20
00:40
Minor simplification of the cursor allocation logic for update. check-in: fdf71be6 user: drh tags: trunk
2018-04-19
23:52
Fix the handling of "PRAGMA count_changes=ON" with UPSERT. Also improved the implementation of count_changes in other places, without changing the behavior. check-in: c6f71115 user: drh tags: trunk
21:29
Minor simplification to the upsert logic. check-in: e657c1d6 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   234    234     int iDataCur = 0;      /* VDBE cursor for the canonical data source */
   235    235     int iIdxCur = 0;       /* Cursor number of the first index */
   236    236     int nIdx;              /* Number of indices */
   237    237     sqlite3 *db;           /* Main database structure */
   238    238     AuthContext sContext;  /* Authorization context */
   239    239     NameContext sNC;       /* Name context to resolve expressions in */
   240    240     int iDb;               /* Database number */
   241         -  int memCnt = -1;       /* Memory cell used for change counting */
          241  +  int memCnt = 0;        /* Memory cell used for change counting */
   242    242     int rcauth;            /* Value returned by authorization callback */
   243    243     int eOnePass;          /* ONEPASS_OFF or _SINGLE or _MULTI */
   244    244     int aiCurOnePass[2];   /* The write cursors opened by WHERE_ONEPASS */
   245    245     u8 *aToOpen = 0;       /* Open cursor iTabCur+j if aToOpen[j] is true */
   246    246     Index *pPk;            /* The PRIMARY KEY index on the table */
   247    247     int iPk = 0;           /* First of nPk registers holding PRIMARY KEY value */
   248    248     i16 nPk = 1;           /* Number of columns in the PRIMARY KEY */
................................................................................
   367    367     if( sqlite3ResolveExprNames(&sNC, pWhere) ){
   368    368       goto delete_from_cleanup;
   369    369     }
   370    370   
   371    371     /* Initialize the counter of the number of rows deleted, if
   372    372     ** we are counting rows.
   373    373     */
   374         -  if( db->flags & SQLITE_CountRows ){
          374  +  if( (db->flags & SQLITE_CountRows)!=0
          375  +   && !pParse->nested
          376  +   && !pParse->pTriggerTab
          377  +  ){
   375    378       memCnt = ++pParse->nMem;
   376    379       sqlite3VdbeAddOp2(v, OP_Integer, 0, memCnt);
   377    380     }
   378    381   
   379    382   #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
   380    383     /* Special case: A DELETE without a WHERE clause deletes everything.
   381    384     ** It is easier just to erase the whole table. Prior to version 3.6.5,
................................................................................
   395    398   #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
   396    399      && db->xPreUpdateCallback==0
   397    400   #endif
   398    401     ){
   399    402       assert( !isView );
   400    403       sqlite3TableLock(pParse, iDb, pTab->tnum, 1, pTab->zName);
   401    404       if( HasRowid(pTab) ){
   402         -      sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt,
          405  +      sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt ? memCnt : -1,
   403    406                           pTab->zName, P4_STATIC);
   404    407       }
   405    408       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   406    409         assert( pIdx->pSchema==pTab->pSchema );
   407    410         sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
   408    411       }
   409    412     }else
................................................................................
   442    445       pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, wcf, iTabCur+1);
   443    446       if( pWInfo==0 ) goto delete_from_cleanup;
   444    447       eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
   445    448       assert( IsVirtual(pTab)==0 || eOnePass!=ONEPASS_MULTI );
   446    449       assert( IsVirtual(pTab) || bComplex || eOnePass!=ONEPASS_OFF );
   447    450     
   448    451       /* Keep track of the number of rows to be deleted */
   449         -    if( db->flags & SQLITE_CountRows ){
          452  +    if( memCnt ){
   450    453         sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
   451    454       }
   452    455     
   453    456       /* Extract the rowid or primary key for the current row */
   454    457       if( pPk ){
   455    458         for(i=0; i<nPk; i++){
   456    459           assert( pPk->aiColumn[i]>=0 );
................................................................................
   585    588       sqlite3AutoincrementEnd(pParse);
   586    589     }
   587    590   
   588    591     /* Return the number of rows that were deleted. If this routine is 
   589    592     ** generating code because of a call to sqlite3NestedParse(), do not
   590    593     ** invoke the callback function.
   591    594     */
   592         -  if( (db->flags&SQLITE_CountRows) && !pParse->nested && !pParse->pTriggerTab ){
          595  +  if( memCnt ){
   593    596       sqlite3VdbeAddOp2(v, OP_ResultRow, memCnt, 1);
   594    597       sqlite3VdbeSetNumCols(v, 1);
   595    598       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows deleted", SQLITE_STATIC);
   596    599     }
   597    600   
   598    601   delete_from_cleanup:
   599    602     sqlite3AuthContextPop(&sContext);

Changes to src/insert.c.

   780    780     if( pColumn!=0 && nColumn!=pColumn->nId ){
   781    781       sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
   782    782       goto insert_cleanup;
   783    783     }
   784    784       
   785    785     /* Initialize the count of rows to be inserted
   786    786     */
   787         -  if( db->flags & SQLITE_CountRows ){
          787  +  if( (db->flags & SQLITE_CountRows)!=0
          788  +   && !pParse->nested
          789  +   && !pParse->pTriggerTab
          790  +  ){
   788    791       regRowCount = ++pParse->nMem;
   789    792       sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
   790    793     }
   791    794   
   792    795     /* If this is not a view, open the table and and all indices */
   793    796     if( !isView ){
   794    797       int nIdx;
................................................................................
  1036   1039             regIns, aRegIdx, 0, appendFlag, bUseSeek
  1037   1040         );
  1038   1041       }
  1039   1042     }
  1040   1043   
  1041   1044     /* Update the count of rows that are inserted
  1042   1045     */
  1043         -  if( (db->flags & SQLITE_CountRows)!=0 ){
         1046  +  if( regRowCount ){
  1044   1047       sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
  1045   1048     }
  1046   1049   
  1047   1050     if( pTrigger ){
  1048   1051       /* Code AFTER triggers */
  1049   1052       sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_AFTER, 
  1050   1053           pTab, regData-2-pTab->nCol, onError, endOfLoop);
................................................................................
  1073   1076     }
  1074   1077   
  1075   1078     /*
  1076   1079     ** Return the number of rows inserted. If this routine is 
  1077   1080     ** generating code because of a call to sqlite3NestedParse(), do not
  1078   1081     ** invoke the callback function.
  1079   1082     */
  1080         -  if( (db->flags&SQLITE_CountRows) && !pParse->nested && !pParse->pTriggerTab ){
         1083  +  if( regRowCount ){
  1081   1084       sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
  1082   1085       sqlite3VdbeSetNumCols(v, 1);
  1083   1086       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC);
  1084   1087     }
  1085   1088   
  1086   1089   insert_cleanup:
  1087   1090     sqlite3SrcListDelete(db, pTabList);

Changes to src/update.c.

   380    380       updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef,
   381    381                          pWhere, onError);
   382    382       goto update_cleanup;
   383    383     }
   384    384   #endif
   385    385   
   386    386     /* Initialize the count of updated rows */
   387         -  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){
          387  +  if( (db->flags&SQLITE_CountRows)!=0
          388  +   && !pParse->pTriggerTab
          389  +   && !pParse->nested
          390  +   && !pUpsert
          391  +  ){
   388    392       regRowCount = ++pParse->nMem;
   389    393       sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
   390    394     }
   391    395   
   392    396     if( HasRowid(pTab) ){
   393    397       sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
   394    398     }else{
................................................................................
   695    699       if( hasFK ){
   696    700         sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey);
   697    701       }
   698    702     }
   699    703   
   700    704     /* Increment the row counter 
   701    705     */
   702         -  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){
          706  +  if( regRowCount ){
   703    707       sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
   704    708     }
   705    709   
   706    710     sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, 
   707    711         TRIGGER_AFTER, pTab, regOldRowid, onError, labelContinue);
   708    712   
   709    713     /* Repeat the above with the next record to be updated, until
................................................................................
   727    731     ** autoincrement tables.
   728    732     */
   729    733     if( pParse->nested==0 && pParse->pTriggerTab==0 ){
   730    734       sqlite3AutoincrementEnd(pParse);
   731    735     }
   732    736   
   733    737     /*
   734         -  ** Return the number of rows that were changed. If this routine is 
   735         -  ** generating code because of a call to sqlite3NestedParse(), do not
   736         -  ** invoke the callback function.
          738  +  ** Return the number of rows that were changed, if we are tracking
          739  +  ** that information.
   737    740     */
   738         -  if( (db->flags&SQLITE_CountRows) && !pParse->pTriggerTab && !pParse->nested ){
          741  +  if( regRowCount ){
   739    742       sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
   740    743       sqlite3VdbeSetNumCols(v, 1);
   741    744       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC);
   742    745     }
   743    746   
   744    747   update_cleanup:
   745    748     sqlite3AuthContextPop(&sContext);

Changes to test/upsert1.test.

    84     84   } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
    85     85   do_execsql_test upsert1-320 {
    86     86     DELETE FROM t1;
    87     87     INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20)
    88     88            ON CONFLICT(b) WHERE b>10 DO NOTHING;
    89     89     SELECT *, 'x' FROM t1 ORDER BY b, a;
    90     90   } {1 2 0 x 3 2 0 x 4 20 0 x}
           91  +
           92  +# Upsert works with count_changes=on;
           93  +do_execsql_test upsert1-400 {
           94  +  DROP TABLE IF EXISTS t2;
           95  +  CREATE TABLE t2(a TEXT UNIQUE, b INT DEFAULT 1);
           96  +  INSERT INTO t2(a) VALUES('one'),('two'),('three');
           97  +  PRAGMA count_changes=ON;
           98  +  INSERT INTO t2(a) VALUES('one'),('one'),('three'),('four')
           99  +      ON CONFLICT(a) DO UPDATE SET b=b+1;
          100  +} {1}
          101  +do_execsql_test upsert1-410 {
          102  +  PRAGMA count_changes=OFF;
          103  +  SELECT a, b FROM t2 ORDER BY a;
          104  +} {four 1 one 3 three 2 two 1}
          105  +
    91    106   
    92    107   finish_test