SQLite

Check-in [c6f71115eb]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c6f71115eb933c2aee295bc31e5139112463c28e15a3b3ea242fd9bac168aed9
User & Date: drh 2018-04-19 23:52:39.113
Context
2018-04-20
00:40
Minor simplification of the cursor allocation logic for update. (check-in: fdf71be658 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: c6f71115eb user: drh tags: trunk)
21:29
Minor simplification to the upsert logic. (check-in: e657c1d60f user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/delete.c.
︙
234
235
236
237
238
239
240
241

242
243
244
245
246
247
248

234
235
236
237
238
239
240

241
242
243
244
245
246
247
248








-
+








  int iDataCur = 0;      /* VDBE cursor for the canonical data source */
  int iIdxCur = 0;       /* Cursor number of the first index */
  int nIdx;              /* Number of indices */
  sqlite3 *db;           /* Main database structure */
  AuthContext sContext;  /* Authorization context */
  NameContext sNC;       /* Name context to resolve expressions in */
  int iDb;               /* Database number */
  int memCnt = -1;       /* Memory cell used for change counting */
  int memCnt = 0;        /* Memory cell used for change counting */
  int rcauth;            /* Value returned by authorization callback */
  int eOnePass;          /* ONEPASS_OFF or _SINGLE or _MULTI */
  int aiCurOnePass[2];   /* The write cursors opened by WHERE_ONEPASS */
  u8 *aToOpen = 0;       /* Open cursor iTabCur+j if aToOpen[j] is true */
  Index *pPk;            /* The PRIMARY KEY index on the table */
  int iPk = 0;           /* First of nPk registers holding PRIMARY KEY value */
  i16 nPk = 1;           /* Number of columns in the PRIMARY KEY */
︙
367
368
369
370
371
372
373
374




375
376
377
378
379
380
381

367
368
369
370
371
372
373

374
375
376
377
378
379
380
381
382
383
384








-
+
+
+
+








  if( sqlite3ResolveExprNames(&sNC, pWhere) ){
    goto delete_from_cleanup;
  }

  /* Initialize the counter of the number of rows deleted, if
  ** we are counting rows.
  */
  if( db->flags & SQLITE_CountRows ){
  if( (db->flags & SQLITE_CountRows)!=0
   && !pParse->nested
   && !pParse->pTriggerTab
  ){
    memCnt = ++pParse->nMem;
    sqlite3VdbeAddOp2(v, OP_Integer, 0, memCnt);
  }

#ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table. Prior to version 3.6.5,
︙
395
396
397
398
399
400
401
402

403
404
405
406
407
408
409

398
399
400
401
402
403
404

405
406
407
408
409
410
411
412








-
+








#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
   && db->xPreUpdateCallback==0
#endif
  ){
    assert( !isView );
    sqlite3TableLock(pParse, iDb, pTab->tnum, 1, pTab->zName);
    if( HasRowid(pTab) ){
      sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt,
      sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt ? memCnt : -1,
                        pTab->zName, P4_STATIC);
    }
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
    }
  }else
︙
442
443
444
445
446
447
448
449

450
451
452
453
454
455
456

445
446
447
448
449
450
451

452
453
454
455
456
457
458
459








-
+








    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, wcf, iTabCur+1);
    if( pWInfo==0 ) goto delete_from_cleanup;
    eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
    assert( IsVirtual(pTab)==0 || eOnePass!=ONEPASS_MULTI );
    assert( IsVirtual(pTab) || bComplex || eOnePass!=ONEPASS_OFF );
  
    /* Keep track of the number of rows to be deleted */
    if( db->flags & SQLITE_CountRows ){
    if( memCnt ){
      sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
    }
  
    /* Extract the rowid or primary key for the current row */
    if( pPk ){
      for(i=0; i<nPk; i++){
        assert( pPk->aiColumn[i]>=0 );
︙
585
586
587
588
589
590
591
592

593
594
595
596
597
598
599

588
589
590
591
592
593
594

595
596
597
598
599
600
601
602








-
+








    sqlite3AutoincrementEnd(pParse);
  }

  /* Return the number of rows that were deleted. If this routine is 
  ** generating code because of a call to sqlite3NestedParse(), do not
  ** invoke the callback function.
  */
  if( (db->flags&SQLITE_CountRows) && !pParse->nested && !pParse->pTriggerTab ){
  if( memCnt ){
    sqlite3VdbeAddOp2(v, OP_ResultRow, memCnt, 1);
    sqlite3VdbeSetNumCols(v, 1);
    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows deleted", SQLITE_STATIC);
  }

delete_from_cleanup:
  sqlite3AuthContextPop(&sContext);
︙
Changes to src/insert.c.
︙
780
781
782
783
784
785
786
787




788
789
790
791
792
793
794

780
781
782
783
784
785
786

787
788
789
790
791
792
793
794
795
796
797








-
+
+
+
+








  if( pColumn!=0 && nColumn!=pColumn->nId ){
    sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
    goto insert_cleanup;
  }
    
  /* Initialize the count of rows to be inserted
  */
  if( db->flags & SQLITE_CountRows ){
  if( (db->flags & SQLITE_CountRows)!=0
   && !pParse->nested
   && !pParse->pTriggerTab
  ){
    regRowCount = ++pParse->nMem;
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
  }

  /* If this is not a view, open the table and and all indices */
  if( !isView ){
    int nIdx;
︙
1036
1037
1038
1039
1040
1041
1042
1043

1044
1045
1046
1047
1048
1049
1050

1039
1040
1041
1042
1043
1044
1045

1046
1047
1048
1049
1050
1051
1052
1053








-
+








          regIns, aRegIdx, 0, appendFlag, bUseSeek
      );
    }
  }

  /* Update the count of rows that are inserted
  */
  if( (db->flags & SQLITE_CountRows)!=0 ){
  if( regRowCount ){
    sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
  }

  if( pTrigger ){
    /* Code AFTER triggers */
    sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_AFTER, 
        pTab, regData-2-pTab->nCol, onError, endOfLoop);
︙
1073
1074
1075
1076
1077
1078
1079
1080

1081
1082
1083
1084
1085
1086
1087

1076
1077
1078
1079
1080
1081
1082

1083
1084
1085
1086
1087
1088
1089
1090








-
+








  }

  /*
  ** Return the number of rows inserted. If this routine is 
  ** generating code because of a call to sqlite3NestedParse(), do not
  ** invoke the callback function.
  */
  if( (db->flags&SQLITE_CountRows) && !pParse->nested && !pParse->pTriggerTab ){
  if( regRowCount ){
    sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
    sqlite3VdbeSetNumCols(v, 1);
    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC);
  }

insert_cleanup:
  sqlite3SrcListDelete(db, pTabList);
︙
Changes to src/update.c.
︙
380
381
382
383
384
385
386

387




388
389
390
391
392
393
394

380
381
382
383
384
385
386
387

388
389
390
391
392
393
394
395
396
397
398








+
-
+
+
+
+








    updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef,
                       pWhere, onError);
    goto update_cleanup;
  }
#endif

  /* Initialize the count of updated rows */
  if( (db->flags&SQLITE_CountRows)!=0
  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){
   && !pParse->pTriggerTab
   && !pParse->nested
   && !pUpsert
  ){
    regRowCount = ++pParse->nMem;
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
  }

  if( HasRowid(pTab) ){
    sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
  }else{
︙
695
696
697
698
699
700
701
702

703
704
705
706
707
708
709

699
700
701
702
703
704
705

706
707
708
709
710
711
712
713








-
+








    if( hasFK ){
      sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey);
    }
  }

  /* Increment the row counter 
  */
  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){
  if( regRowCount ){
    sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
  }

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

  /* Repeat the above with the next record to be updated, until
︙
727
728
729
730
731
732
733
734

735
736

737
738

739
740
741
742
743
744
745

731
732
733
734
735
736
737

738


739
740

741
742
743
744
745
746
747
748








-
+
-
-
+

-
+








  ** autoincrement tables.
  */
  if( pParse->nested==0 && pParse->pTriggerTab==0 ){
    sqlite3AutoincrementEnd(pParse);
  }

  /*
  ** Return the number of rows that were changed. If this routine is 
  ** Return the number of rows that were changed, if we are tracking
  ** generating code because of a call to sqlite3NestedParse(), do not
  ** invoke the callback function.
  ** that information.
  */
  if( (db->flags&SQLITE_CountRows) && !pParse->pTriggerTab && !pParse->nested ){
  if( regRowCount ){
    sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
    sqlite3VdbeSetNumCols(v, 1);
    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC);
  }

update_cleanup:
  sqlite3AuthContextPop(&sContext);
︙
Changes to test/upsert1.test.
︙
84
85
86
87
88
89
90















91
92

84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107








+
+
+
+
+
+
+
+
+
+
+
+
+
+
+



} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
do_execsql_test upsert1-320 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20)
         ON CONFLICT(b) WHERE b>10 DO NOTHING;
  SELECT *, 'x' FROM t1 ORDER BY b, a;
} {1 2 0 x 3 2 0 x 4 20 0 x}

# Upsert works with count_changes=on;
do_execsql_test upsert1-400 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(a TEXT UNIQUE, b INT DEFAULT 1);
  INSERT INTO t2(a) VALUES('one'),('two'),('three');
  PRAGMA count_changes=ON;
  INSERT INTO t2(a) VALUES('one'),('one'),('three'),('four')
      ON CONFLICT(a) DO UPDATE SET b=b+1;
} {1}
do_execsql_test upsert1-410 {
  PRAGMA count_changes=OFF;
  SELECT a, b FROM t2 ORDER BY a;
} {four 1 one 3 three 2 two 1}


finish_test