SQLite

Check-in [d120c45f3d]
Login

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

Overview
Comment:On an UPSERT when the order of constraint checks is rearranged, make sure that the affinity transformations on the inserted content occur before any of the constraint checks. Fix for ticket [79cad5e4b2e219dd197242e9e5f4e].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: d120c45f3dc79f67afed0e44e5133569f784bc6792b15f5d79529deac2d13072
User & Date: drh 2018-07-11 13:34:24.107
Context
2018-07-12
11:28
Add a test case to check that SQLITE_DBCONFIG_RESET_DATABASE can be used with wal mode databases even if there are active readers. (check-in: 6145f5b3ba user: dan tags: trunk)
2018-07-11
13:34
On an UPSERT when the order of constraint checks is rearranged, make sure that the affinity transformations on the inserted content occur before any of the constraint checks. Fix for ticket [79cad5e4b2e219dd197242e9e5f4e]. (check-in: d120c45f3d user: drh tags: trunk)
03:27
Adjustments to VdbeCoverage macros to deal with byte-code branches that can never be taken in some directions. (check-in: b170c0092b user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/insert.c.
︙
1628
1629
1630
1631
1632
1633
1634




1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654

1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647




1648
1649
1650
1651
1652
1653
1654








+
+
+
+









-
-
-
-








  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( bAffinityDone==0 ){
      sqlite3TableAffinity(v, pTab, regNewData+1);
      bAffinityDone = 1;
    }
    if( pUpIdx==pIdx ){
      addrUniqueOk = sAddr.upsertBtm;
      upsertBypass = sqlite3VdbeGoto(v, 0);
      VdbeComment((v, "Skip upsert subroutine"));
      sqlite3VdbeResolveLabel(v, sAddr.upsertTop2);
    }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);
︙
Changes to test/upsert1.test.
︙
107
108
109
110
111
112
113
















114
115

107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131








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



do_execsql_test upsert1-500 {
  DROP TABLE t1;
  CREATE TABLE t1(x INTEGER PRIMARY KEY, y INT UNIQUE);
  INSERT INTO t1(x,y) SELECT 1,2 WHERE true
    ON CONFLICT(x) DO UPDATE SET y=max(t1.y,excluded.y) AND true;
  SELECT * FROM t1;
} {1 2}

# 2018-07-11
# Ticket https://sqlite.org/src/tktview/79cad5e4b2e219dd197242e9e5f4
# UPSERT leads to a corrupt index.
#
do_execsql_test upsert1-600 {
  DROP TABLE t1;
  CREATE TABLE t1(b UNIQUE, a INT PRIMARY KEY) WITHOUT ROWID;
  INSERT OR IGNORE INTO t1(a) VALUES('1') ON CONFLICT(a) DO NOTHING;
  PRAGMA integrity_check;
} {ok}
do_execsql_test upsert1-610 {
  DELETE FROM t1;
  INSERT OR IGNORE INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING;
  PRAGMA integrity_check;
} {ok}

finish_test