SQLite

Check-in [f14ce948]
Login

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

Overview
Comment:If an UPSERT can cause an Abort due to a constraint failure, make sure the query planner knows this. Ticket [7c13db5c3bf74001].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f14ce948662f3445a06d84d4f32d9b81143e0cffaf773d8aa4d3a609bfb9682b
User & Date: drh 2019-12-26 23:40:33
Context
2019-12-27
00:19
Fix buffer underflows in the zipfile extension associated with zero-length or NULL filename in the ZIP archive. But report on the mailing list by Yongheng and Rui. (check-in: 465a15c5 user: drh tags: trunk)
2019-12-26
23:40
If an UPSERT can cause an Abort due to a constraint failure, make sure the query planner knows this. Ticket [7c13db5c3bf74001]. (check-in: f14ce948 user: drh tags: trunk)
23:16
An UPDATE of a table that is indexed by a constant virtual column that uses the one-pass optimization might cause the table seek to be omitted before reaching row DELETE/INSERT. Fix this by coding an extra OP_Column in that circumstance. Ticket [ec8abb025e78f40c] (check-in: e5456049 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/upsert.c.

232
233
234
235
236
237
238

239
240
241
242
243
244
245
                    pTab->aCol[pPk->aiColumn[i]].zName));
      }
      sqlite3VdbeVerifyAbortable(v, OE_Abort);
      i = sqlite3VdbeAddOp4Int(v, OP_Found, iDataCur, 0, iPk, nPk);
      VdbeCoverage(v);
      sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CORRUPT, OE_Abort, 0, 
            "corrupt database", P4_STATIC);

      sqlite3VdbeJumpHere(v, i);
    }
  }
  /* pUpsert does not own pUpsertSrc - the outer INSERT statement does.  So
  ** we have to make a copy before passing it down into sqlite3Update() */
  pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0);
  /* excluded.* columns of type REAL need to be converted to a hard real */







>







232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
                    pTab->aCol[pPk->aiColumn[i]].zName));
      }
      sqlite3VdbeVerifyAbortable(v, OE_Abort);
      i = sqlite3VdbeAddOp4Int(v, OP_Found, iDataCur, 0, iPk, nPk);
      VdbeCoverage(v);
      sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CORRUPT, OE_Abort, 0, 
            "corrupt database", P4_STATIC);
      sqlite3MayAbort(pParse);
      sqlite3VdbeJumpHere(v, i);
    }
  }
  /* pUpsert does not own pUpsertSrc - the outer INSERT statement does.  So
  ** we have to make a copy before passing it down into sqlite3Update() */
  pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0);
  /* excluded.* columns of type REAL need to be converted to a hard real */

Changes to test/upsert1.test.

229
230
231
232
233
234
235








236
     SELECT 2;
  END;
}
do_catchsql_test upsert1-910 {
  INSERT INTO t1 VALUES(3) ON CONFLICT(x) DO NOTHING;
} {1 {cannot UPSERT a view}}









finish_test







>
>
>
>
>
>
>
>

229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
     SELECT 2;
  END;
}
do_catchsql_test upsert1-910 {
  INSERT INTO t1 VALUES(3) ON CONFLICT(x) DO NOTHING;
} {1 {cannot UPSERT a view}}

# 2019-12-26 ticket 7c13db5c3bf74001
reset_db
do_catchsql_test upsert1-1000 {
  CREATE TABLE t0(c0 PRIMARY KEY, c1, c2 UNIQUE) WITHOUT ROWID;
  INSERT OR FAIL INTO t0(c2) VALUES (0), (NULL)
    ON CONFLICT(c2) DO UPDATE SET c1 = c0;
} {1 {NOT NULL constraint failed: t0.c0}}

finish_test