/ Check-in [3e02474c]
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:Split the OP_Last opcode into OP_Last and OP_SeekEnd. Use OP_SeekEnd to position a cursor prior to appending. Ticket [cb91bf4290c211d].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 3e02474c7bbe16891a7cfc8771cf72f64cd2c0692779037982d7d307512a4f23
User & Date: drh 2017-08-01 19:53:43
Context
2017-08-01
20:59
Slightly smaller and faster by allocating Parser objects on the stack. check-in: 436a89b9 user: drh tags: trunk
19:53
Split the OP_Last opcode into OP_Last and OP_SeekEnd. Use OP_SeekEnd to position a cursor prior to appending. Ticket [cb91bf4290c211d]. check-in: 3e02474c user: drh tags: trunk
14:16
Take advantage of atomic-write capabilities in the F2FS filesystem when the database is stored on such a filesystem. This is a compile-time option activated using SQLITE_ENABLE_BATCH_ATOMIC_WRITE. check-in: 24190b22 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
    sqlite3VdbeAddOp4Int(v, OP_SorterCompare, iSorter, j2, regRecord,
                         pIndex->nKeyCol); VdbeCoverage(v);
    sqlite3UniqueConstraint(pParse, OE_Abort, pIndex);
  }else{
    addr2 = sqlite3VdbeCurrentAddr(v);
  }
  sqlite3VdbeAddOp3(v, OP_SorterData, iSorter, regRecord, iIdx);
  sqlite3VdbeAddOp3(v, OP_Last, iIdx, 0, -1);
  sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdx, regRecord);
  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  sqlite3ReleaseTempReg(pParse, regRecord);
  sqlite3VdbeAddOp2(v, OP_SorterNext, iSorter, addr2); VdbeCoverage(v);
  sqlite3VdbeJumpHere(v, addr1);

  sqlite3VdbeAddOp1(v, OP_Close, iTab);







|







2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
    sqlite3VdbeAddOp4Int(v, OP_SorterCompare, iSorter, j2, regRecord,
                         pIndex->nKeyCol); VdbeCoverage(v);
    sqlite3UniqueConstraint(pParse, OE_Abort, pIndex);
  }else{
    addr2 = sqlite3VdbeCurrentAddr(v);
  }
  sqlite3VdbeAddOp3(v, OP_SorterData, iSorter, regRecord, iIdx);
  sqlite3VdbeAddOp1(v, OP_SeekEnd, iIdx);
  sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdx, regRecord);
  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  sqlite3ReleaseTempReg(pParse, regRecord);
  sqlite3VdbeAddOp2(v, OP_SorterNext, iSorter, addr2); VdbeCoverage(v);
  sqlite3VdbeJumpHere(v, addr1);

  sqlite3VdbeAddOp1(v, OP_Close, iTab);

Changes to src/insert.c.

2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
....
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
....
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
      addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
    }else{
      addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
      assert( (pDest->tabFlags & TF_Autoincrement)==0 );
    }
    sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
    if( db->mDbFlags & DBFLAG_Vacuum ){
      sqlite3VdbeAddOp3(v, OP_Last, iDest, 0, -1);
      insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|
                           OPFLAG_APPEND|OPFLAG_USESEEKRESULT;
    }else{
      insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND;
    }
    sqlite3VdbeAddOp4(v, OP_Insert, iDest, regData, regRowid,
                      (char*)pDest, P4_TABLE);
................................................................................
    sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
    if( db->mDbFlags & DBFLAG_Vacuum ){
      /* This INSERT command is part of a VACUUM operation, which guarantees
      ** that the destination table is empty. If all indexed columns use
      ** collation sequence BINARY, then it can also be assumed that the
      ** index will be populated by inserting keys in strictly sorted 
      ** order. In this case, instead of seeking within the b-tree as part
      ** of every OP_IdxInsert opcode, an OP_Last is added before the
      ** OP_IdxInsert to seek to the point within the b-tree where each key 
      ** should be inserted. This is faster.
      **
      ** If any of the indexed columns use a collation sequence other than
      ** BINARY, this optimization is disabled. This is because the user 
      ** might change the definition of a collation sequence and then run
      ** a VACUUM command. In that case keys may not be written in strictly
................................................................................
      ** sorted order.  */
      for(i=0; i<pSrcIdx->nColumn; i++){
        const char *zColl = pSrcIdx->azColl[i];
        if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break;
      }
      if( i==pSrcIdx->nColumn ){
        idxInsFlags = OPFLAG_USESEEKRESULT;
        sqlite3VdbeAddOp3(v, OP_Last, iDest, 0, -1);
      }
    }
    if( !HasRowid(pSrc) && pDestIdx->idxType==2 ){
      idxInsFlags |= OPFLAG_NCHANGE;
    }
    sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);
    sqlite3VdbeChangeP5(v, idxInsFlags|OPFLAG_APPEND);







|







 







|







 







|







2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
....
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
....
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
      addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
    }else{
      addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
      assert( (pDest->tabFlags & TF_Autoincrement)==0 );
    }
    sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
    if( db->mDbFlags & DBFLAG_Vacuum ){
      sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
      insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|
                           OPFLAG_APPEND|OPFLAG_USESEEKRESULT;
    }else{
      insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND;
    }
    sqlite3VdbeAddOp4(v, OP_Insert, iDest, regData, regRowid,
                      (char*)pDest, P4_TABLE);
................................................................................
    sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
    if( db->mDbFlags & DBFLAG_Vacuum ){
      /* This INSERT command is part of a VACUUM operation, which guarantees
      ** that the destination table is empty. If all indexed columns use
      ** collation sequence BINARY, then it can also be assumed that the
      ** index will be populated by inserting keys in strictly sorted 
      ** order. In this case, instead of seeking within the b-tree as part
      ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the
      ** OP_IdxInsert to seek to the point within the b-tree where each key 
      ** should be inserted. This is faster.
      **
      ** If any of the indexed columns use a collation sequence other than
      ** BINARY, this optimization is disabled. This is because the user 
      ** might change the definition of a collation sequence and then run
      ** a VACUUM command. In that case keys may not be written in strictly
................................................................................
      ** sorted order.  */
      for(i=0; i<pSrcIdx->nColumn; i++){
        const char *zColl = pSrcIdx->azColl[i];
        if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break;
      }
      if( i==pSrcIdx->nColumn ){
        idxInsFlags = OPFLAG_USESEEKRESULT;
        sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
      }
    }
    if( !HasRowid(pSrc) && pDestIdx->idxType==2 ){
      idxInsFlags |= OPFLAG_NCHANGE;
    }
    sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);
    sqlite3VdbeChangeP5(v, idxInsFlags|OPFLAG_APPEND);

Changes to src/vdbe.c.

4772
4773
4774
4775
4776
4777
4778










4779
4780
4781
4782
4783
4784
4785
4786
4787
4788
4789
4790
4791
4792
4793
4794
4795
4796
4797

4798
4799
4800
4801
4802
4803
4804
4805
4806
4807
4808
4809
4810
4811
4812
4813



4814



4815
4816
4817
4818
4819
4820
4821
4822
4823
4824
4825
4826
4827
4828
4829
4830
4831
4832
  if( pC->eCurType==CURTYPE_BTREE ){
    assert( pC->uc.pCursor!=0 );
    sqlite3BtreeClearCursor(pC->uc.pCursor);
  }
  break;
}











/* Opcode: Last P1 P2 P3 * *
**
** The next use of the Rowid or Column or Prev instruction for P1 
** will refer to the last entry in the database table or index.
** If the table or index is empty and P2>0, then jump immediately to P2.
** If P2 is 0 or if the table or index is not empty, fall through
** to the following instruction.
**
** This opcode leaves the cursor configured to move in reverse order,
** from the end toward the beginning.  In other words, the cursor is
** configured to use Prev, not Next.
**
** If P3 is -1, then the cursor is positioned at the end of the btree
** for the purpose of appending a new entry onto the btree.  In that
** case P2 must be 0.  It is assumed that the cursor is used only for
** appending and so if the cursor is valid, then the cursor must already
** be pointing at the end of the btree and so no changes are made to
** the cursor.
*/

case OP_Last: {        /* jump */
  VdbeCursor *pC;
  BtCursor *pCrsr;
  int res;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->eCurType==CURTYPE_BTREE );
  pCrsr = pC->uc.pCursor;
  res = 0;
  assert( pCrsr!=0 );
  pC->seekResult = pOp->p3;
#ifdef SQLITE_DEBUG
  pC->seekOp = OP_Last;
#endif



  if( pOp->p3==0 || !sqlite3BtreeCursorIsValidNN(pCrsr) ){



    rc = sqlite3BtreeLast(pCrsr, &res);
    pC->nullRow = (u8)res;
    pC->deferredMoveto = 0;
    pC->cacheStatus = CACHE_STALE;
    if( rc ) goto abort_due_to_error;
    if( pOp->p2>0 ){
      VdbeBranchTaken(res!=0,2);
      if( res ) goto jump_to_p2;
    }
  }else{
    assert( pOp->p2==0 );
  }
  break;
}

/* Opcode: IfSmaller P1 P2 P3 * *
**
** Estimate the number of rows in the table P1.  Jump to P2 if that







>
>
>
>
>
>
>
>
>
>
|










<
<
<
<
<
<
<

>












<

|

>
>
>
|
>
>
>
|
|
|
|
|
|
|
|
<
<
<







4772
4773
4774
4775
4776
4777
4778
4779
4780
4781
4782
4783
4784
4785
4786
4787
4788
4789
4790
4791
4792
4793
4794
4795
4796
4797
4798
4799







4800
4801
4802
4803
4804
4805
4806
4807
4808
4809
4810
4811
4812
4813

4814
4815
4816
4817
4818
4819
4820
4821
4822
4823
4824
4825
4826
4827
4828
4829
4830
4831



4832
4833
4834
4835
4836
4837
4838
  if( pC->eCurType==CURTYPE_BTREE ){
    assert( pC->uc.pCursor!=0 );
    sqlite3BtreeClearCursor(pC->uc.pCursor);
  }
  break;
}

/* Opcode: SeekEnd P1 * * * *
**
** Position cursor P1 at the end of the btree for the purpose of
** appending a new entry onto the btree.
**
** It is assumed that the cursor is used only for appending and so
** if the cursor is valid, then the cursor must already be pointing
** at the end of the btree and so no changes are made to
** the cursor.
*/
/* Opcode: Last P1 P2 * * *
**
** The next use of the Rowid or Column or Prev instruction for P1 
** will refer to the last entry in the database table or index.
** If the table or index is empty and P2>0, then jump immediately to P2.
** If P2 is 0 or if the table or index is not empty, fall through
** to the following instruction.
**
** This opcode leaves the cursor configured to move in reverse order,
** from the end toward the beginning.  In other words, the cursor is
** configured to use Prev, not Next.







*/
case OP_SeekEnd:
case OP_Last: {        /* jump */
  VdbeCursor *pC;
  BtCursor *pCrsr;
  int res;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->eCurType==CURTYPE_BTREE );
  pCrsr = pC->uc.pCursor;
  res = 0;
  assert( pCrsr!=0 );

#ifdef SQLITE_DEBUG
  pC->seekOp = pOp->opcode;
#endif
  if( pOp->opcode==OP_SeekEnd ){
    assert( pOp->p2==0 );
    pC->seekResult = -1;
    if( sqlite3BtreeCursorIsValidNN(pCrsr) ){
      break;
    }
  }
  rc = sqlite3BtreeLast(pCrsr, &res);
  pC->nullRow = (u8)res;
  pC->deferredMoveto = 0;
  pC->cacheStatus = CACHE_STALE;
  if( rc ) goto abort_due_to_error;
  if( pOp->p2>0 ){
    VdbeBranchTaken(res!=0,2);
    if( res ) goto jump_to_p2;



  }
  break;
}

/* Opcode: IfSmaller P1 P2 P3 * *
**
** Estimate the number of rows in the table P1.  Jump to P2 if that

Changes to test/whereA.test.

153
154
155
156
157
158
159












160
161
162
  }
} {1 2 1}
do_test whereA-4.6 {
  count {
    SELECT x FROM t2 ORDER BY x DESC;
  }
} {2 1 1}














finish_test







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



153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
  }
} {1 2 1}
do_test whereA-4.6 {
  count {
    SELECT x FROM t2 ORDER BY x DESC;
  }
} {2 1 1}

# Ticket https://sqlite.org/src/tktview/cb91bf4290c211  2017-08-01
# Assertion fault following PRAGMA reverse_unordered_selects=ON.
# 
do_execsql_test whereA-5.1 {
  PRAGMA reverse_unordered_selects=on;
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a,b);
  INSERT INTO t1 VALUES(1,2);
  CREATE INDEX t1b ON t1(b);
  SELECT a FROM t1 WHERE b=-99 OR b>1;
} {1}


finish_test