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
Unified 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

  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.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);








>
>
>
>









<
<
<
<








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));




    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

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}

















finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



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