/ Check-in [93a2bace]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:For DELETE operations, make sure that seeks on the main table are not deferred, since if they are and none of the indexes reference columns of the table, the seek might never occur until the OP_Delete opcode, which is too late. Fix for ticket [16c9801ceba49].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 93a2bace5704f7ecad56541a6b3e06a88f7f393f
User & Date: drh 2016-05-06 16:49:54
Context
2016-05-06
18:47
Corrections to comments on the whereScanInit() interface. check-in: ab7ab415 user: drh tags: trunk
16:49
For DELETE operations, make sure that seeks on the main table are not deferred, since if they are and none of the indexes reference columns of the table, the seek might never occur until the OP_Delete opcode, which is too late. Fix for ticket [16c9801ceba49]. check-in: 93a2bace user: drh tags: trunk
16:30
Fix an error in main.mk causing testfixture to use individual fts5 source code files instead of fts5.c. check-in: 933a1c81 user: dan tags: trunk
16:06
In the WHERE generator, when building code for a DELETE operation, make sure that seeks to the main table are not deferred. This is a better fix for the [16c9801ceba49] bug than the previous. Closed-Leaf check-in: 150dd09e user: drh tags: ticket-16c9801ce
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
    }
  }else
#endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
  {
    u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK;
    if( sNC.ncFlags & NC_VarSelect ) bComplex = 1;
    wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW);
    if( HasRowid(pTab) ){
      /* For a rowid table, initialize the RowSet to an empty set */
      pPk = 0;
      nPk = 1;
      iRowSet = ++pParse->nMem;







|







369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
    }
  }else
#endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
  {
    u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK|WHERE_SEEK_TABLE;
    if( sNC.ncFlags & NC_VarSelect ) bComplex = 1;
    wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW);
    if( HasRowid(pTab) ){
      /* For a rowid table, initialize the RowSet to an empty set */
      pPk = 0;
      nPk = 1;
      iRowSet = ++pParse->nMem;

Changes to src/sqliteInt.h.

2539
2540
2541
2542
2543
2544
2545

2546
2547
2548
2549
2550
2551
2552
#define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
#define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
#define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */
#define WHERE_SORTBYGROUP      0x0800 /* Support sqlite3WhereIsSorted() */
#define WHERE_REOPEN_IDX       0x1000 /* Try to use OP_ReopenIdx */
#define WHERE_ONEPASS_MULTIROW 0x2000 /* ONEPASS is ok with multiple rows */
#define WHERE_USE_LIMIT        0x4000 /* There is a constant LIMIT clause */


/* Allowed return values from sqlite3WhereIsDistinct()
*/
#define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
#define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
#define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
#define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */







>







2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
#define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
#define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
#define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */
#define WHERE_SORTBYGROUP      0x0800 /* Support sqlite3WhereIsSorted() */
#define WHERE_REOPEN_IDX       0x1000 /* Try to use OP_ReopenIdx */
#define WHERE_ONEPASS_MULTIROW 0x2000 /* ONEPASS is ok with multiple rows */
#define WHERE_USE_LIMIT        0x4000 /* There is a constant LIMIT clause */
#define WHERE_SEEK_TABLE       0x8000 /* Do not defer seeks on main table */

/* Allowed return values from sqlite3WhereIsDistinct()
*/
#define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
#define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
#define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
#define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */

Changes to src/wherecode.c.

1320
1321
1322
1323
1324
1325
1326
1327


1328
1329
1330
1331
1332
1333
1334
....
1516
1517
1518
1519
1520
1521
1522
1523

1524
1525
1526
1527
1528
1529
1530

    /* Seek the table cursor, if required */
    disableTerm(pLevel, pRangeStart);
    disableTerm(pLevel, pRangeEnd);
    if( omitTable ){
      /* pIdx is a covering index.  No need to access the main table. */
    }else if( HasRowid(pIdx->pTable) ){
      if( pWInfo->eOnePass!=ONEPASS_OFF ){


        iRowidReg = ++pParse->nMem;
        sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
        sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
        sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iRowidReg);
        VdbeCoverage(v);
      }else{
        codeDeferredSeek(pWInfo, pIdx, iCur, iIdxCur);
................................................................................
    /* Run a separate WHERE clause for each term of the OR clause.  After
    ** eliminating duplicates from other WHERE clauses, the action for each
    ** sub-WHERE clause is to to invoke the main loop body as a subroutine.
    */
    wctrlFlags =  WHERE_OMIT_OPEN_CLOSE
                | WHERE_FORCE_TABLE
                | WHERE_ONETABLE_ONLY
                | WHERE_NO_AUTOINDEX;

    for(ii=0; ii<pOrWc->nTerm; ii++){
      WhereTerm *pOrTerm = &pOrWc->a[ii];
      if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){
        WhereInfo *pSubWInfo;           /* Info for single OR-term scan */
        Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */
        int jmp1 = 0;                   /* Address of jump operation */
        if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){







|
>
>







 







|
>







1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
....
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533

    /* Seek the table cursor, if required */
    disableTerm(pLevel, pRangeStart);
    disableTerm(pLevel, pRangeEnd);
    if( omitTable ){
      /* pIdx is a covering index.  No need to access the main table. */
    }else if( HasRowid(pIdx->pTable) ){
      if( pWInfo->eOnePass!=ONEPASS_OFF
       || (pWInfo->wctrlFlags & WHERE_SEEK_TABLE)!=0
      ){
        iRowidReg = ++pParse->nMem;
        sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
        sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
        sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iRowidReg);
        VdbeCoverage(v);
      }else{
        codeDeferredSeek(pWInfo, pIdx, iCur, iIdxCur);
................................................................................
    /* Run a separate WHERE clause for each term of the OR clause.  After
    ** eliminating duplicates from other WHERE clauses, the action for each
    ** sub-WHERE clause is to to invoke the main loop body as a subroutine.
    */
    wctrlFlags =  WHERE_OMIT_OPEN_CLOSE
                | WHERE_FORCE_TABLE
                | WHERE_ONETABLE_ONLY
                | WHERE_NO_AUTOINDEX
                | (pWInfo->wctrlFlags & WHERE_SEEK_TABLE);
    for(ii=0; ii<pOrWc->nTerm; ii++){
      WhereTerm *pOrTerm = &pOrWc->a[ii];
      if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){
        WhereInfo *pSubWInfo;           /* Info for single OR-term scan */
        Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */
        int jmp1 = 0;                   /* Address of jump operation */
        if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){

Changes to test/intpkey.test.

610
611
612
613
614
615
616





617















618
619
do_execsql_test intpkey-16.0 {
  CREATE TABLE t16a(id "INTEGER" PRIMARY KEY AUTOINCREMENT, b [TEXT], c `INT`);
} {}
do_execsql_test intpkey-16.1 {
  PRAGMA table_info=t16a;
} {0 id INTEGER 0 {} 1 1 b TEXT 0 {} 0 2 c INT 0 {} 0}























finish_test







>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
do_execsql_test intpkey-16.0 {
  CREATE TABLE t16a(id "INTEGER" PRIMARY KEY AUTOINCREMENT, b [TEXT], c `INT`);
} {}
do_execsql_test intpkey-16.1 {
  PRAGMA table_info=t16a;
} {0 id INTEGER 0 {} 1 1 b TEXT 0 {} 0 2 c INT 0 {} 0}

# 2016-05-06 ticket https://www.sqlite.org/src/tktview/16c9801ceba4923939085
# When the schema contains an index on the IPK and no other index
# and a WHERE clause on a delete uses an OR where both sides referencing
# the IPK, then it is possible that the OP_Delete will fail because there
# deferred seek of the OP_Seek is not resolved prior to reaching the OP_Delete.
#
do_execsql_test intpkey-17.0 {
  CREATE TABLE t17(x INTEGER PRIMARY KEY, y TEXT);
  INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe');
  CREATE INDEX t17x ON t17(x);
  DELETE FROM t17 WHERE x=99 OR x<130;
  SELECT * FROM t17;
} {248 giraffe}
do_execsql_test intpkey-17.1 {
  DROP INDEX t17x;
  DELETE FROM t17;
  INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe');
  CREATE INDEX t17x ON t17(abs(x));
  DELETE FROM t17 WHERE abs(x) IS NULL OR abs(x)<130;
  SELECT * FROM t17;
} {248 giraffe}

finish_test