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: |
c6f71115eb933c2aee295bc31e513911 |
| 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
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