Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Merge latest trunk changes with this branch. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | exp-window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
251022034219819a1dc356542770ff46 |
| User & Date: | dan 2018-06-07 20:35:28 |
Context
|
2018-06-08
| ||
| 11:45 | Fixes to allow group_concat() to be used as a window function. (check-in: 89bbc9ba user: dan tags: exp-window-functions) | |
|
2018-06-07
| ||
| 20:35 | Merge latest trunk changes with this branch. (check-in: 25102203 user: dan tags: exp-window-functions) | |
| 20:08 | Add window functions lag() and lead(). (check-in: ef342070 user: dan tags: exp-window-functions) | |
| 18:13 | The IN-early-out optimization: When doing a look-up on a multi-column index and an IN operator is used on a column other than the left-most column, then if no rows match against the first IN value, check to make sure there exist rows that match the columns to the right before continuing with the next IN value. (check-in: 09fffbdf user: drh tags: trunk) | |
Changes
Changes to VERSION.
1 -3.24.0 1 +3.25.0
Changes to configure.
1 1 #! /bin/sh 2 2 # Guess values for system-dependent variables and create Makefiles. 3 -# Generated by GNU Autoconf 2.69 for sqlite 3.24.0. 3 +# Generated by GNU Autoconf 2.69 for sqlite 3.25.0. 4 4 # 5 5 # 6 6 # Copyright (C) 1992-1996, 1998-2012 Free Software Foundation, Inc. 7 7 # 8 8 # 9 9 # This configure script is free software; the Free Software Foundation 10 10 # gives unlimited permission to copy, distribute and modify it. ................................................................................ 722 722 subdirs= 723 723 MFLAGS= 724 724 MAKEFLAGS= 725 725 726 726 # Identity of this package. 727 727 PACKAGE_NAME='sqlite' 728 728 PACKAGE_TARNAME='sqlite' 729 -PACKAGE_VERSION='3.24.0' 730 -PACKAGE_STRING='sqlite 3.24.0' 729 +PACKAGE_VERSION='3.25.0' 730 +PACKAGE_STRING='sqlite 3.25.0' 731 731 PACKAGE_BUGREPORT='' 732 732 PACKAGE_URL='' 733 733 734 734 # Factoring default headers for most tests. 735 735 ac_includes_default="\ 736 736 #include <stdio.h> 737 737 #ifdef HAVE_SYS_TYPES_H ................................................................................ 1461 1461 # 1462 1462 # Report the --help message. 1463 1463 # 1464 1464 if test "$ac_init_help" = "long"; then 1465 1465 # Omit some internal or obsolete options to make the list less imposing. 1466 1466 # This message is too long to be a string in the A/UX 3.1 sh. 1467 1467 cat <<_ACEOF 1468 -\`configure' configures sqlite 3.24.0 to adapt to many kinds of systems. 1468 +\`configure' configures sqlite 3.25.0 to adapt to many kinds of systems. 1469 1469 1470 1470 Usage: $0 [OPTION]... [VAR=VALUE]... 1471 1471 1472 1472 To assign environment variables (e.g., CC, CFLAGS...), specify them as 1473 1473 VAR=VALUE. See below for descriptions of some of the useful variables. 1474 1474 1475 1475 Defaults for the options are specified in brackets. ................................................................................ 1526 1526 --build=BUILD configure for building on BUILD [guessed] 1527 1527 --host=HOST cross-compile to build programs to run on HOST [BUILD] 1528 1528 _ACEOF 1529 1529 fi 1530 1530 1531 1531 if test -n "$ac_init_help"; then 1532 1532 case $ac_init_help in 1533 - short | recursive ) echo "Configuration of sqlite 3.24.0:";; 1533 + short | recursive ) echo "Configuration of sqlite 3.25.0:";; 1534 1534 esac 1535 1535 cat <<\_ACEOF 1536 1536 1537 1537 Optional Features: 1538 1538 --disable-option-checking ignore unrecognized --enable/--with options 1539 1539 --disable-FEATURE do not include FEATURE (same as --enable-FEATURE=no) 1540 1540 --enable-FEATURE[=ARG] include FEATURE [ARG=yes] ................................................................................ 1651 1651 cd "$ac_pwd" || { ac_status=$?; break; } 1652 1652 done 1653 1653 fi 1654 1654 1655 1655 test -n "$ac_init_help" && exit $ac_status 1656 1656 if $ac_init_version; then 1657 1657 cat <<\_ACEOF 1658 -sqlite configure 3.24.0 1658 +sqlite configure 3.25.0 1659 1659 generated by GNU Autoconf 2.69 1660 1660 1661 1661 Copyright (C) 2012 Free Software Foundation, Inc. 1662 1662 This configure script is free software; the Free Software Foundation 1663 1663 gives unlimited permission to copy, distribute and modify it. 1664 1664 _ACEOF 1665 1665 exit ................................................................................ 2070 2070 eval $as_lineno_stack; ${as_lineno_stack:+:} unset as_lineno 2071 2071 2072 2072 } # ac_fn_c_check_header_mongrel 2073 2073 cat >config.log <<_ACEOF 2074 2074 This file contains any messages produced by compilers while 2075 2075 running configure, to aid debugging if configure makes a mistake. 2076 2076 2077 -It was created by sqlite $as_me 3.24.0, which was 2077 +It was created by sqlite $as_me 3.25.0, which was 2078 2078 generated by GNU Autoconf 2.69. Invocation command line was 2079 2079 2080 2080 $ $0 $@ 2081 2081 2082 2082 _ACEOF 2083 2083 exec 5>>config.log 2084 2084 { ................................................................................ 12238 12238 test $as_write_fail = 0 && chmod +x $CONFIG_STATUS || ac_write_fail=1 12239 12239 12240 12240 cat >>$CONFIG_STATUS <<\_ACEOF || ac_write_fail=1 12241 12241 # Save the log message, to keep $0 and so on meaningful, and to 12242 12242 # report actual input values of CONFIG_FILES etc. instead of their 12243 12243 # values after options handling. 12244 12244 ac_log=" 12245 -This file was extended by sqlite $as_me 3.24.0, which was 12245 +This file was extended by sqlite $as_me 3.25.0, which was 12246 12246 generated by GNU Autoconf 2.69. Invocation command line was 12247 12247 12248 12248 CONFIG_FILES = $CONFIG_FILES 12249 12249 CONFIG_HEADERS = $CONFIG_HEADERS 12250 12250 CONFIG_LINKS = $CONFIG_LINKS 12251 12251 CONFIG_COMMANDS = $CONFIG_COMMANDS 12252 12252 $ $0 $@ ................................................................................ 12304 12304 12305 12305 Report bugs to the package provider." 12306 12306 12307 12307 _ACEOF 12308 12308 cat >>$CONFIG_STATUS <<_ACEOF || ac_write_fail=1 12309 12309 ac_cs_config="`$as_echo "$ac_configure_args" | sed 's/^ //; s/[\\""\`\$]/\\\\&/g'`" 12310 12310 ac_cs_version="\\ 12311 -sqlite config.status 3.24.0 12311 +sqlite config.status 3.25.0 12312 12312 configured by $0, generated by GNU Autoconf 2.69, 12313 12313 with options \\"\$ac_cs_config\\" 12314 12314 12315 12315 Copyright (C) 2012 Free Software Foundation, Inc. 12316 12316 This config.status script is free software; the Free Software Foundation 12317 12317 gives unlimited permission to copy, distribute and modify it." 12318 12318
Changes to src/alter.c.
138 138 zOutput = zOut; 139 139 zInput = &z[n]; 140 140 } 141 141 sqlite3DbFree(db, zParent); 142 142 } 143 143 } 144 144 145 - zResult = sqlite3MPrintf(db, "%s%s", (zOutput?zOutput:""), zInput), 145 + zResult = sqlite3MPrintf(db, "%s%s", (zOutput?zOutput:""), zInput); 146 146 sqlite3_result_text(context, zResult, -1, SQLITE_DYNAMIC); 147 147 sqlite3DbFree(db, zOutput); 148 148 } 149 149 #endif 150 150 151 151 #ifndef SQLITE_OMIT_TRIGGER 152 152 /* This function is used by SQL generated to implement the
Changes to src/backup.c.
378 378 } 379 379 380 380 /* If there is no open read-transaction on the source database, open 381 381 ** one now. If a transaction is opened here, then it will be closed 382 382 ** before this function exits. 383 383 */ 384 384 if( rc==SQLITE_OK && 0==sqlite3BtreeIsInReadTrans(p->pSrc) ){ 385 - rc = sqlite3BtreeBeginTrans(p->pSrc, 0); 385 + rc = sqlite3BtreeBeginTrans(p->pSrc, 0, 0); 386 386 bCloseTrans = 1; 387 387 } 388 388 389 389 /* If the destination database has not yet been locked (i.e. if this 390 390 ** is the first call to backup_step() for the current backup operation), 391 391 ** try to set its page size to the same as the source database. This 392 392 ** is especially important on ZipVFS systems, as in that case it is ................................................................................ 394 394 ** writing to it with another. */ 395 395 if( p->bDestLocked==0 && rc==SQLITE_OK && setDestPgsz(p)==SQLITE_NOMEM ){ 396 396 rc = SQLITE_NOMEM; 397 397 } 398 398 399 399 /* Lock the destination database, if it is not locked already. */ 400 400 if( SQLITE_OK==rc && p->bDestLocked==0 401 - && SQLITE_OK==(rc = sqlite3BtreeBeginTrans(p->pDest, 2)) 401 + && SQLITE_OK==(rc = sqlite3BtreeBeginTrans(p->pDest, 2, 402 + (int*)&p->iDestSchema)) 402 403 ){ 403 404 p->bDestLocked = 1; 404 - sqlite3BtreeGetMeta(p->pDest, BTREE_SCHEMA_VERSION, &p->iDestSchema); 405 405 } 406 406 407 407 /* Do not allow backup if the destination database is in WAL mode 408 408 ** and the page sizes are different between source and destination */ 409 409 pgszSrc = sqlite3BtreeGetPageSize(p->pSrc); 410 410 pgszDest = sqlite3BtreeGetPageSize(p->pDest); 411 411 destMode = sqlite3PagerGetJournalMode(sqlite3BtreePager(p->pDest));
Changes to src/btree.c.
3296 3296 ** a reserved lock. B tries to promote to exclusive but is blocked because 3297 3297 ** of A's read lock. A tries to promote to reserved but is blocked by B. 3298 3298 ** One or the other of the two processes must give way or there can be 3299 3299 ** no progress. By returning SQLITE_BUSY and not invoking the busy callback 3300 3300 ** when A already has a read lock, we encourage A to give up and let B 3301 3301 ** proceed. 3302 3302 */ 3303 -int sqlite3BtreeBeginTrans(Btree *p, int wrflag){ 3303 +int sqlite3BtreeBeginTrans(Btree *p, int wrflag, int *pSchemaVersion){ 3304 3304 BtShared *pBt = p->pBt; 3305 3305 int rc = SQLITE_OK; 3306 3306 3307 3307 sqlite3BtreeEnter(p); 3308 3308 btreeIntegrity(p); 3309 3309 3310 3310 /* If the btree is already in a write-transaction, or it ................................................................................ 3424 3424 } 3425 3425 } 3426 3426 } 3427 3427 } 3428 3428 3429 3429 3430 3430 trans_begun: 3431 - if( rc==SQLITE_OK && wrflag ){ 3431 + if( rc==SQLITE_OK ){ 3432 + if( pSchemaVersion ){ 3433 + *pSchemaVersion = get4byte(&pBt->pPage1->aData[40]); 3434 + } 3435 + if( wrflag ){ 3432 3436 /* This call makes sure that the pager has the correct number of 3433 3437 ** open savepoints. If the second parameter is greater than 0 and 3434 3438 ** the sub-journal is not already open, then it will be opened here. 3435 3439 */ 3436 3440 rc = sqlite3PagerOpenSavepoint(pBt->pPager, p->db->nSavepoint); 3437 3441 } 3442 + } 3438 3443 3439 3444 btreeIntegrity(p); 3440 3445 sqlite3BtreeLeave(p); 3441 3446 return rc; 3442 3447 } 3443 3448 3444 3449 #ifndef SQLITE_OMIT_AUTOVACUUM ................................................................................ 10087 10092 10088 10093 /* If setting the version fields to 1, do not automatically open the 10089 10094 ** WAL connection, even if the version fields are currently set to 2. 10090 10095 */ 10091 10096 pBt->btsFlags &= ~BTS_NO_WAL; 10092 10097 if( iVersion==1 ) pBt->btsFlags |= BTS_NO_WAL; 10093 10098 10094 - rc = sqlite3BtreeBeginTrans(pBtree, 0); 10099 + rc = sqlite3BtreeBeginTrans(pBtree, 0, 0); 10095 10100 if( rc==SQLITE_OK ){ 10096 10101 u8 *aData = pBt->pPage1->aData; 10097 10102 if( aData[18]!=(u8)iVersion || aData[19]!=(u8)iVersion ){ 10098 - rc = sqlite3BtreeBeginTrans(pBtree, 2); 10103 + rc = sqlite3BtreeBeginTrans(pBtree, 2, 0); 10099 10104 if( rc==SQLITE_OK ){ 10100 10105 rc = sqlite3PagerWrite(pBt->pPage1->pDbPage); 10101 10106 if( rc==SQLITE_OK ){ 10102 10107 aData[18] = (u8)iVersion; 10103 10108 aData[19] = (u8)iVersion; 10104 10109 } 10105 10110 }
Changes to src/btree.h.
74 74 int sqlite3BtreeMaxPageCount(Btree*,int); 75 75 u32 sqlite3BtreeLastPage(Btree*); 76 76 int sqlite3BtreeSecureDelete(Btree*,int); 77 77 int sqlite3BtreeGetOptimalReserve(Btree*); 78 78 int sqlite3BtreeGetReserveNoMutex(Btree *p); 79 79 int sqlite3BtreeSetAutoVacuum(Btree *, int); 80 80 int sqlite3BtreeGetAutoVacuum(Btree *); 81 -int sqlite3BtreeBeginTrans(Btree*,int); 81 +int sqlite3BtreeBeginTrans(Btree*,int,int*); 82 82 int sqlite3BtreeCommitPhaseOne(Btree*, const char *zMaster); 83 83 int sqlite3BtreeCommitPhaseTwo(Btree*, int); 84 84 int sqlite3BtreeCommit(Btree*); 85 85 int sqlite3BtreeRollback(Btree*,int,int); 86 86 int sqlite3BtreeBeginStmt(Btree*,int); 87 87 int sqlite3BtreeCreateTable(Btree*, int*, int flags); 88 88 int sqlite3BtreeIsInTrans(Btree*);
Changes to src/dbpage.c.
365 365 */ 366 366 static int dbpageBegin(sqlite3_vtab *pVtab){ 367 367 DbpageTable *pTab = (DbpageTable *)pVtab; 368 368 sqlite3 *db = pTab->db; 369 369 int i; 370 370 for(i=0; i<db->nDb; i++){ 371 371 Btree *pBt = db->aDb[i].pBt; 372 - if( pBt ) sqlite3BtreeBeginTrans(pBt, 1); 372 + if( pBt ) sqlite3BtreeBeginTrans(pBt, 1, 0); 373 373 } 374 374 return SQLITE_OK; 375 375 } 376 376 377 377 378 378 /* 379 379 ** Invoke this routine to register the "dbpage" virtual table module
Changes to src/main.c.
4111 4111 sqlite3_mutex_enter(db->mutex); 4112 4112 4113 4113 if( db->autoCommit==0 ){ 4114 4114 int iDb = sqlite3FindDbName(db, zDb); 4115 4115 if( iDb==0 || iDb>1 ){ 4116 4116 Btree *pBt = db->aDb[iDb].pBt; 4117 4117 if( 0==sqlite3BtreeIsInTrans(pBt) ){ 4118 - rc = sqlite3BtreeBeginTrans(pBt, 0); 4118 + rc = sqlite3BtreeBeginTrans(pBt, 0, 0); 4119 4119 if( rc==SQLITE_OK ){ 4120 4120 rc = sqlite3PagerSnapshotGet(sqlite3BtreePager(pBt), ppSnapshot); 4121 4121 } 4122 4122 } 4123 4123 } 4124 4124 } 4125 4125 ................................................................................ 4149 4149 int iDb; 4150 4150 iDb = sqlite3FindDbName(db, zDb); 4151 4151 if( iDb==0 || iDb>1 ){ 4152 4152 Btree *pBt = db->aDb[iDb].pBt; 4153 4153 if( 0==sqlite3BtreeIsInReadTrans(pBt) ){ 4154 4154 rc = sqlite3PagerSnapshotOpen(sqlite3BtreePager(pBt), pSnapshot); 4155 4155 if( rc==SQLITE_OK ){ 4156 - rc = sqlite3BtreeBeginTrans(pBt, 0); 4156 + rc = sqlite3BtreeBeginTrans(pBt, 0, 0); 4157 4157 sqlite3PagerSnapshotOpen(sqlite3BtreePager(pBt), 0); 4158 4158 } 4159 4159 } 4160 4160 } 4161 4161 } 4162 4162 4163 4163 sqlite3_mutex_leave(db->mutex); ................................................................................ 4181 4181 #endif 4182 4182 4183 4183 sqlite3_mutex_enter(db->mutex); 4184 4184 iDb = sqlite3FindDbName(db, zDb); 4185 4185 if( iDb==0 || iDb>1 ){ 4186 4186 Btree *pBt = db->aDb[iDb].pBt; 4187 4187 if( 0==sqlite3BtreeIsInReadTrans(pBt) ){ 4188 - rc = sqlite3BtreeBeginTrans(pBt, 0); 4188 + rc = sqlite3BtreeBeginTrans(pBt, 0, 0); 4189 4189 if( rc==SQLITE_OK ){ 4190 4190 rc = sqlite3PagerSnapshotRecover(sqlite3BtreePager(pBt)); 4191 4191 sqlite3BtreeCommit(pBt); 4192 4192 } 4193 4193 } 4194 4194 } 4195 4195 sqlite3_mutex_leave(db->mutex);
Changes to src/pragma.c.
1560 1560 for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ 1561 1561 sqlite3VdbeAddOp2(v, OP_Integer, 0, 8+j); /* index entries counter */ 1562 1562 } 1563 1563 assert( pParse->nMem>=8+j ); 1564 1564 assert( sqlite3NoTempsInRange(pParse,1,7+j) ); 1565 1565 sqlite3VdbeAddOp2(v, OP_Rewind, iDataCur, 0); VdbeCoverage(v); 1566 1566 loopTop = sqlite3VdbeAddOp2(v, OP_AddImm, 7, 1); 1567 + if( !isQuick ){ 1568 + /* Sanity check on record header decoding */ 1569 + sqlite3VdbeAddOp3(v, OP_Column, iDataCur, pTab->nCol-1, 3); 1570 + sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG); 1571 + } 1567 1572 /* Verify that all NOT NULL columns really are NOT NULL */ 1568 1573 for(j=0; j<pTab->nCol; j++){ 1569 1574 char *zErr; 1570 1575 int jmp2; 1571 1576 if( j==pTab->iPKey ) continue; 1572 1577 if( pTab->aCol[j].notNull==0 ) continue; 1573 1578 sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, j, 3); ................................................................................ 1602 1607 integrityCheckResultRow(v); 1603 1608 sqlite3VdbeResolveLabel(v, addrCkOk); 1604 1609 sqlite3ExprCachePop(pParse); 1605 1610 } 1606 1611 sqlite3ExprListDelete(db, pCheck); 1607 1612 } 1608 1613 if( !isQuick ){ /* Omit the remaining tests for quick_check */ 1609 - /* Sanity check on record header decoding */ 1610 - sqlite3VdbeAddOp3(v, OP_Column, iDataCur, pTab->nCol-1, 3); 1611 - sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG); 1612 1614 /* Validate index entries for the current row */ 1613 1615 for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ 1614 1616 int jmp2, jmp3, jmp4, jmp5; 1615 1617 int ckUniq = sqlite3VdbeMakeLabel(v); 1616 1618 if( pPk==pIdx ) continue; 1617 1619 r1 = sqlite3GenerateIndexKey(pParse, pIdx, iDataCur, 0, 0, &jmp3, 1618 1620 pPrior, r1);
Changes to src/prepare.c.
184 184 } 185 185 186 186 /* If there is not already a read-only (or read-write) transaction opened 187 187 ** on the b-tree database, open one now. If a transaction is opened, it 188 188 ** will be closed before this function returns. */ 189 189 sqlite3BtreeEnter(pDb->pBt); 190 190 if( !sqlite3BtreeIsInReadTrans(pDb->pBt) ){ 191 - rc = sqlite3BtreeBeginTrans(pDb->pBt, 0); 191 + rc = sqlite3BtreeBeginTrans(pDb->pBt, 0, 0); 192 192 if( rc!=SQLITE_OK ){ 193 193 sqlite3SetString(pzErrMsg, db, sqlite3ErrStr(rc)); 194 194 goto initone_error_out; 195 195 } 196 196 openedTransaction = 1; 197 197 } 198 198 ................................................................................ 429 429 Btree *pBt = db->aDb[iDb].pBt; /* Btree database to read cookie from */ 430 430 if( pBt==0 ) continue; 431 431 432 432 /* If there is not already a read-only (or read-write) transaction opened 433 433 ** on the b-tree database, open one now. If a transaction is opened, it 434 434 ** will be closed immediately after reading the meta-value. */ 435 435 if( !sqlite3BtreeIsInReadTrans(pBt) ){ 436 - rc = sqlite3BtreeBeginTrans(pBt, 0); 436 + rc = sqlite3BtreeBeginTrans(pBt, 0, 0); 437 437 if( rc==SQLITE_NOMEM || rc==SQLITE_IOERR_NOMEM ){ 438 438 sqlite3OomFault(db); 439 439 } 440 440 if( rc!=SQLITE_OK ) return; 441 441 openedTransaction = 1; 442 442 } 443 443
Changes to src/select.c.
5099 5099 for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){ 5100 5100 ExprList *pList = pF->pExpr->x.pList; 5101 5101 assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) ); 5102 5102 sqlite3VdbeAddOp2(v, OP_AggFinal, pF->iMem, pList ? pList->nExpr : 0); 5103 5103 sqlite3VdbeAppendP4(v, pF->pFunc, P4_FUNCDEF); 5104 5104 } 5105 5105 } 5106 + 5106 5107 5107 5108 /* 5108 5109 ** Update the accumulator memory cells for an aggregate based on 5109 5110 ** the current cursor position. 5111 +** 5112 +** If regAcc is non-zero and there are no min() or max() aggregates 5113 +** in pAggInfo, then only populate the pAggInfo->nAccumulator accumulator 5114 +** registers i register regAcc contains 0. The caller will take care 5115 +** of setting and clearing regAcc. 5110 5116 */ 5111 -static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){ 5117 +static void updateAccumulator(Parse *pParse, int regAcc, AggInfo *pAggInfo){ 5112 5118 Vdbe *v = pParse->pVdbe; 5113 5119 int i; 5114 5120 int regHit = 0; 5115 5121 int addrHitTest = 0; 5116 5122 struct AggInfo_func *pF; 5117 5123 struct AggInfo_col *pC; 5118 5124 ................................................................................ 5168 5174 ** to pC->iMem. But by the time the value is used, the original register 5169 5175 ** may have been used, invalidating the underlying buffer holding the 5170 5176 ** text or blob value. See ticket [883034dcb5]. 5171 5177 ** 5172 5178 ** Another solution would be to change the OP_SCopy used to copy cached 5173 5179 ** values to an OP_Copy. 5174 5180 */ 5181 + if( regHit==0 && pAggInfo->nAccumulator ){ 5182 + regHit = regAcc; 5183 + } 5175 5184 if( regHit ){ 5176 5185 addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); VdbeCoverage(v); 5177 5186 } 5178 5187 sqlite3ExprCacheClear(pParse); 5179 5188 for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){ 5180 5189 sqlite3ExprCode(pParse, pC->pExpr, pC->iMem); 5181 5190 } ................................................................................ 6063 6072 addrReset = sqlite3VdbeMakeLabel(v); 6064 6073 iAMem = pParse->nMem + 1; 6065 6074 pParse->nMem += pGroupBy->nExpr; 6066 6075 iBMem = pParse->nMem + 1; 6067 6076 pParse->nMem += pGroupBy->nExpr; 6068 6077 sqlite3VdbeAddOp2(v, OP_Integer, 0, iAbortFlag); 6069 6078 VdbeComment((v, "clear abort flag")); 6070 - sqlite3VdbeAddOp2(v, OP_Integer, 0, iUseFlag); 6071 - VdbeComment((v, "indicate accumulator empty")); 6072 6079 sqlite3VdbeAddOp3(v, OP_Null, 0, iAMem, iAMem+pGroupBy->nExpr-1); 6073 6080 6074 6081 /* Begin a loop that will extract all source rows in GROUP BY order. 6075 6082 ** This might involve two separate loops with an OP_Sort in between, or 6076 6083 ** it might be a single loop that uses an index to extract information 6077 6084 ** in the right order to begin with. 6078 6085 */ ................................................................................ 6197 6204 sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); 6198 6205 VdbeComment((v, "reset accumulator")); 6199 6206 6200 6207 /* Update the aggregate accumulators based on the content of 6201 6208 ** the current row 6202 6209 */ 6203 6210 sqlite3VdbeJumpHere(v, addr1); 6204 - updateAccumulator(pParse, &sAggInfo); 6211 + updateAccumulator(pParse, iUseFlag, &sAggInfo); 6205 6212 sqlite3VdbeAddOp2(v, OP_Integer, 1, iUseFlag); 6206 6213 VdbeComment((v, "indicate data in accumulator")); 6207 6214 6208 6215 /* End of the loop 6209 6216 */ 6210 6217 if( groupBySort ){ 6211 6218 sqlite3VdbeAddOp2(v, OP_SorterNext, sAggInfo.sortingIdx, addrTopOfLoop); ................................................................................ 6249 6256 sqlite3VdbeAddOp1(v, OP_Return, regOutputRow); 6250 6257 VdbeComment((v, "end groupby result generator")); 6251 6258 6252 6259 /* Generate a subroutine that will reset the group-by accumulator 6253 6260 */ 6254 6261 sqlite3VdbeResolveLabel(v, addrReset); 6255 6262 resetAccumulator(pParse, &sAggInfo); 6263 + sqlite3VdbeAddOp2(v, OP_Integer, 0, iUseFlag); 6264 + VdbeComment((v, "indicate accumulator empty")); 6256 6265 sqlite3VdbeAddOp1(v, OP_Return, regReset); 6257 6266 6258 6267 } /* endif pGroupBy. Begin aggregate queries without GROUP BY: */ 6259 6268 else { 6260 6269 #ifndef SQLITE_OMIT_BTREECOUNT 6261 6270 Table *pTab; 6262 6271 if( (pTab = isSimpleCount(p, &sAggInfo))!=0 ){ ................................................................................ 6314 6323 } 6315 6324 sqlite3VdbeAddOp2(v, OP_Count, iCsr, sAggInfo.aFunc[0].iMem); 6316 6325 sqlite3VdbeAddOp1(v, OP_Close, iCsr); 6317 6326 explainSimpleCount(pParse, pTab, pBest); 6318 6327 }else 6319 6328 #endif /* SQLITE_OMIT_BTREECOUNT */ 6320 6329 { 6330 + int regAcc = 0; /* "populate accumulators" flag */ 6331 + 6332 + /* If there are accumulator registers but no min() or max() functions, 6333 + ** allocate register regAcc. Register regAcc will contain 0 the first 6334 + ** time the inner loop runs, and 1 thereafter. The code generated 6335 + ** by updateAccumulator() only updates the accumulator registers if 6336 + ** regAcc contains 0. */ 6337 + if( sAggInfo.nAccumulator ){ 6338 + for(i=0; i<sAggInfo.nFunc; i++){ 6339 + if( sAggInfo.aFunc[i].pFunc->funcFlags&SQLITE_FUNC_NEEDCOLL ) break; 6340 + } 6341 + if( i==sAggInfo.nFunc ){ 6342 + regAcc = ++pParse->nMem; 6343 + sqlite3VdbeAddOp2(v, OP_Integer, 0, regAcc); 6344 + } 6345 + } 6346 + 6321 6347 /* This case runs if the aggregate has no GROUP BY clause. The 6322 6348 ** processing is much simpler since there is only a single row 6323 6349 ** of output. 6324 6350 */ 6325 6351 assert( p->pGroupBy==0 ); 6326 6352 resetAccumulator(pParse, &sAggInfo); 6327 6353 ................................................................................ 6335 6361 6336 6362 SELECTTRACE(1,pParse,p,("WhereBegin\n")); 6337 6363 pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMaxOrderBy, 6338 6364 0, minMaxFlag, 0); 6339 6365 if( pWInfo==0 ){ 6340 6366 goto select_end; 6341 6367 } 6342 - updateAccumulator(pParse, &sAggInfo); 6368 + updateAccumulator(pParse, regAcc, &sAggInfo); 6369 + if( regAcc ) sqlite3VdbeAddOp2(v, OP_Integer, 1, regAcc); 6343 6370 if( sqlite3WhereIsOrdered(pWInfo)>0 ){ 6344 6371 sqlite3VdbeGoto(v, sqlite3WhereBreakLabel(pWInfo)); 6345 6372 VdbeComment((v, "%s() by index", 6346 6373 (minMaxFlag==WHERE_ORDERBY_MIN?"min":"max"))); 6347 6374 } 6348 6375 sqlite3WhereEnd(pWInfo); 6349 6376 finalizeAggFunctions(pParse, &sAggInfo);
Changes to src/shell.c.in.
2563 2563 static void explain_data_prepare(ShellState *p, sqlite3_stmt *pSql){ 2564 2564 const char *zSql; /* The text of the SQL statement */ 2565 2565 const char *z; /* Used to check if this is an EXPLAIN */ 2566 2566 int *abYield = 0; /* True if op is an OP_Yield */ 2567 2567 int nAlloc = 0; /* Allocated size of p->aiIndent[], abYield */ 2568 2568 int iOp; /* Index of operation in p->aiIndent[] */ 2569 2569 2570 - const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext", 2571 - "NextIfOpen", "PrevIfOpen", 0 }; 2570 + const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext", 0 }; 2572 2571 const char *azYield[] = { "Yield", "SeekLT", "SeekGT", "RowSetRead", 2573 2572 "Rewind", 0 }; 2574 2573 const char *azGoto[] = { "Goto", 0 }; 2575 2574 2576 2575 /* Try to figure out if this is really an EXPLAIN statement. If this 2577 2576 ** cannot be verified, return early. */ 2578 2577 if( sqlite3_column_count(pSql)!=8 ){ ................................................................................ 2968 2967 sqlite3_free(zEQP); 2969 2968 } 2970 2969 if( pArg->autoEQP>=AUTOEQP_trigger && triggerEQP==0 ){ 2971 2970 sqlite3_db_config(db, SQLITE_DBCONFIG_TRIGGER_EQP, 0, 0); 2972 2971 /* Reprepare pStmt before reactiving trace modes */ 2973 2972 sqlite3_finalize(pStmt); 2974 2973 sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0); 2974 + if( pArg ) pArg->pStmt = pStmt; 2975 2975 } 2976 2976 restore_debug_trace_modes(); 2977 2977 } 2978 2978 2979 2979 if( pArg ){ 2980 2980 pArg->cMode = pArg->mode; 2981 2981 if( pArg->autoExplain ){ ................................................................................ 5280 5280 ** Implementation of .ar "eXtract" command. 5281 5281 */ 5282 5282 static int arExtractCommand(ArCommand *pAr){ 5283 5283 const char *zSql1 = 5284 5284 "SELECT " 5285 5285 " ($dir || name)," 5286 5286 " writefile(($dir || name), %s, mode, mtime) " 5287 - "FROM %s WHERE (%s) AND (data IS NULL OR $dirOnly = 0)"; 5287 + "FROM %s WHERE (%s) AND (data IS NULL OR $dirOnly = 0)" 5288 + " AND name NOT GLOB '*..[/\\]*'"; 5288 5289 5289 5290 const char *azExtraArg[] = { 5290 5291 "sqlar_uncompress(data, sz)", 5291 5292 "data" 5292 5293 }; 5293 5294 5294 5295 sqlite3_stmt *pSql = 0;
Changes to src/test3.c.
129 129 if( argc!=2 ){ 130 130 Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0], 131 131 " ID\"", 0); 132 132 return TCL_ERROR; 133 133 } 134 134 pBt = sqlite3TestTextToPtr(argv[1]); 135 135 sqlite3BtreeEnter(pBt); 136 - rc = sqlite3BtreeBeginTrans(pBt, 1); 136 + rc = sqlite3BtreeBeginTrans(pBt, 1, 0); 137 137 sqlite3BtreeLeave(pBt); 138 138 if( rc!=SQLITE_OK ){ 139 139 Tcl_AppendResult(interp, sqlite3ErrName(rc), 0); 140 140 return TCL_ERROR; 141 141 } 142 142 return TCL_OK; 143 143 }
Changes to src/vacuum.c.
220 220 221 221 /* Begin a transaction and take an exclusive lock on the main database 222 222 ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below, 223 223 ** to ensure that we do not try to change the page-size on a WAL database. 224 224 */ 225 225 rc = execSql(db, pzErrMsg, "BEGIN"); 226 226 if( rc!=SQLITE_OK ) goto end_of_vacuum; 227 - rc = sqlite3BtreeBeginTrans(pMain, 2); 227 + rc = sqlite3BtreeBeginTrans(pMain, 2, 0); 228 228 if( rc!=SQLITE_OK ) goto end_of_vacuum; 229 229 230 230 /* Do not attempt to change the page size for a WAL database */ 231 231 if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain)) 232 232 ==PAGER_JOURNALMODE_WAL ){ 233 233 db->nextPagesize = 0; 234 234 }
Changes to src/vdbe.c.
3198 3198 ** if the schema generation counter in P4 differs from the current 3199 3199 ** generation counter, then an SQLITE_SCHEMA error is raised and execution 3200 3200 ** halts. The sqlite3_step() wrapper function might then reprepare the 3201 3201 ** statement and rerun it from the beginning. 3202 3202 */ 3203 3203 case OP_Transaction: { 3204 3204 Btree *pBt; 3205 - int iMeta; 3206 - int iGen; 3205 + int iMeta = 0; 3207 3206 3208 3207 assert( p->bIsReader ); 3209 3208 assert( p->readOnly==0 || pOp->p2==0 ); 3210 3209 assert( pOp->p1>=0 && pOp->p1<db->nDb ); 3211 3210 assert( DbMaskTest(p->btreeMask, pOp->p1) ); 3212 3211 if( pOp->p2 && (db->flags & SQLITE_QueryOnly)!=0 ){ 3213 3212 rc = SQLITE_READONLY; 3214 3213 goto abort_due_to_error; 3215 3214 } 3216 3215 pBt = db->aDb[pOp->p1].pBt; 3217 3216 3218 3217 if( pBt ){ 3219 - rc = sqlite3BtreeBeginTrans(pBt, pOp->p2); 3218 + rc = sqlite3BtreeBeginTrans(pBt, pOp->p2, &iMeta); 3220 3219 testcase( rc==SQLITE_BUSY_SNAPSHOT ); 3221 3220 testcase( rc==SQLITE_BUSY_RECOVERY ); 3222 3221 if( rc!=SQLITE_OK ){ 3223 3222 if( (rc&0xff)==SQLITE_BUSY ){ 3224 3223 p->pc = (int)(pOp - aOp); 3225 3224 p->rc = rc; 3226 3225 goto vdbe_return; ................................................................................ 3245 3244 3246 3245 /* Store the current value of the database handles deferred constraint 3247 3246 ** counter. If the statement transaction needs to be rolled back, 3248 3247 ** the value of this counter needs to be restored too. */ 3249 3248 p->nStmtDefCons = db->nDeferredCons; 3250 3249 p->nStmtDefImmCons = db->nDeferredImmCons; 3251 3250 } 3252 - 3253 - /* Gather the schema version number for checking: 3251 + } 3252 + assert( pOp->p5==0 || pOp->p4type==P4_INT32 ); 3253 + if( pOp->p5 3254 + && (iMeta!=pOp->p3 3255 + || db->aDb[pOp->p1].pSchema->iGeneration!=pOp->p4.i) 3256 + ){ 3257 + /* 3254 3258 ** IMPLEMENTATION-OF: R-03189-51135 As each SQL statement runs, the schema 3255 3259 ** version is checked to ensure that the schema has not changed since the 3256 3260 ** SQL statement was prepared. 3257 3261 */ 3258 - sqlite3BtreeGetMeta(pBt, BTREE_SCHEMA_VERSION, (u32 *)&iMeta); 3259 - iGen = db->aDb[pOp->p1].pSchema->iGeneration; 3260 - }else{ 3261 - iGen = iMeta = 0; 3262 - } 3263 - assert( pOp->p5==0 || pOp->p4type==P4_INT32 ); 3264 - if( pOp->p5 && (iMeta!=pOp->p3 || iGen!=pOp->p4.i) ){ 3265 3262 sqlite3DbFree(db, p->zErrMsg); 3266 3263 p->zErrMsg = sqlite3DbStrDup(db, "database schema has changed"); 3267 3264 /* If the schema-cookie from the database file matches the cookie 3268 3265 ** stored with the in-memory representation of the schema, do 3269 3266 ** not reload the schema from the database file. 3270 3267 ** 3271 3268 ** If virtual-tables are in use, this is not just an optimization. ................................................................................ 3366 3363 ** P2 in a database file. The database file is determined by P3. 3367 3364 ** P3==0 means the main database, P3==1 means the database used for 3368 3365 ** temporary tables, and P3>1 means used the corresponding attached 3369 3366 ** database. Give the new cursor an identifier of P1. The P1 3370 3367 ** values need not be contiguous but all P1 values should be small integers. 3371 3368 ** It is an error for P1 to be negative. 3372 3369 ** 3373 -** If P5!=0 then use the content of register P2 as the root page, not 3374 -** the value of P2 itself. 3375 -** 3376 -** There will be a read lock on the database whenever there is an 3377 -** open cursor. If the database was unlocked prior to this instruction 3378 -** then a read lock is acquired as part of this instruction. A read 3379 -** lock allows other processes to read the database but prohibits 3380 -** any other process from modifying the database. The read lock is 3381 -** released when all cursors are closed. If this instruction attempts 3382 -** to get a read lock but fails, the script terminates with an 3383 -** SQLITE_BUSY error code. 3370 +** Allowed P5 bits: 3371 +** <ul> 3372 +** <li> <b>0x02 OPFLAG_SEEKEQ</b>: This cursor will only be used for 3373 +** equality lookups (implemented as a pair of opcodes OP_SeekGE/OP_IdxGT 3374 +** of OP_SeekLE/OP_IdxGT) 3375 +** </ul> 3384 3376 ** 3385 3377 ** The P4 value may be either an integer (P4_INT32) or a pointer to 3386 3378 ** a KeyInfo structure (P4_KEYINFO). If it is a pointer to a KeyInfo 3387 -** structure, then said structure defines the content and collating 3388 -** sequence of the index being opened. Otherwise, if P4 is an integer 3389 -** value, it is set to the number of columns in the table. 3379 +** object, then table being opened must be an [index b-tree] where the 3380 +** KeyInfo object defines the content and collating 3381 +** sequence of that index b-tree. Otherwise, if P4 is an integer 3382 +** value, then the table being opened must be a [table b-tree] with a 3383 +** number of columns no less than the value of P4. 3390 3384 ** 3391 3385 ** See also: OpenWrite, ReopenIdx 3392 3386 */ 3393 3387 /* Opcode: ReopenIdx P1 P2 P3 P4 P5 3394 3388 ** Synopsis: root=P2 iDb=P3 3395 3389 ** 3396 -** The ReopenIdx opcode works exactly like ReadOpen except that it first 3397 -** checks to see if the cursor on P1 is already open with a root page 3398 -** number of P2 and if it is this opcode becomes a no-op. In other words, 3390 +** The ReopenIdx opcode works like OP_OpenRead except that it first 3391 +** checks to see if the cursor on P1 is already open on the same 3392 +** b-tree and if it is this opcode becomes a no-op. In other words, 3399 3393 ** if the cursor is already open, do not reopen it. 3400 3394 ** 3401 -** The ReopenIdx opcode may only be used with P5==0 and with P4 being 3402 -** a P4_KEYINFO object. Furthermore, the P3 value must be the same as 3403 -** every other ReopenIdx or OpenRead for the same cursor number. 3395 +** The ReopenIdx opcode may only be used with P5==0 or P5==OPFLAG_SEEKEQ 3396 +** and with P4 being a P4_KEYINFO object. Furthermore, the P3 value must 3397 +** be the same as every other ReopenIdx or OpenRead for the same cursor 3398 +** number. 3404 3399 ** 3405 -** See the OpenRead opcode documentation for additional information. 3400 +** Allowed P5 bits: 3401 +** <ul> 3402 +** <li> <b>0x02 OPFLAG_SEEKEQ</b>: This cursor will only be used for 3403 +** equality lookups (implemented as a pair of opcodes OP_SeekGE/OP_IdxGT 3404 +** of OP_SeekLE/OP_IdxGT) 3405 +** </ul> 3406 +** 3407 +** See also: OP_OpenRead, OP_OpenWrite 3406 3408 */ 3407 3409 /* Opcode: OpenWrite P1 P2 P3 P4 P5 3408 3410 ** Synopsis: root=P2 iDb=P3 3409 3411 ** 3410 3412 ** Open a read/write cursor named P1 on the table or index whose root 3411 -** page is P2. Or if P5!=0 use the content of register P2 to find the 3412 -** root page. 3413 +** page is P2 (or whose root page is held in register P2 if the 3414 +** OPFLAG_P2ISREG bit is set in P5 - see below). 3413 3415 ** 3414 3416 ** The P4 value may be either an integer (P4_INT32) or a pointer to 3415 3417 ** a KeyInfo structure (P4_KEYINFO). If it is a pointer to a KeyInfo 3416 -** structure, then said structure defines the content and collating 3417 -** sequence of the index being opened. Otherwise, if P4 is an integer 3418 -** value, it is set to the number of columns in the table, or to the 3419 -** largest index of any column of the table that is actually used. 3418 +** object, then table being opened must be an [index b-tree] where the 3419 +** KeyInfo object defines the content and collating 3420 +** sequence of that index b-tree. Otherwise, if P4 is an integer 3421 +** value, then the table being opened must be a [table b-tree] with a 3422 +** number of columns no less than the value of P4. 3420 3423 ** 3421 -** This instruction works just like OpenRead except that it opens the cursor 3422 -** in read/write mode. For a given table, there can be one or more read-only 3423 -** cursors or a single read/write cursor but not both. 3424 +** Allowed P5 bits: 3425 +** <ul> 3426 +** <li> <b>0x02 OPFLAG_SEEKEQ</b>: This cursor will only be used for 3427 +** equality lookups (implemented as a pair of opcodes OP_SeekGE/OP_IdxGT 3428 +** of OP_SeekLE/OP_IdxGT) 3429 +** <li> <b>0x08 OPFLAG_FORDELETE</b>: This cursor is used only to seek 3430 +** and subsequently delete entries in an index btree. This is a 3431 +** hint to the storage engine that the storage engine is allowed to 3432 +** ignore. The hint is not used by the official SQLite b*tree storage 3433 +** engine, but is used by COMDB2. 3434 +** <li> <b>0x10 OPFLAG_P2ISREG</b>: Use the content of register P2 3435 +** as the root page, not the value of P2 itself. 3436 +** </ul> 3424 3437 ** 3425 -** See also OpenRead. 3438 +** This instruction works like OpenRead except that it opens the cursor 3439 +** in read/write mode. 3440 +** 3441 +** See also: OP_OpenRead, OP_ReopenIdx 3426 3442 */ 3427 3443 case OP_ReopenIdx: { 3428 3444 int nField; 3429 3445 KeyInfo *pKeyInfo; 3430 3446 int p2; 3431 3447 int iDb; 3432 3448 int wrFlag; ................................................................................ 3474 3490 } 3475 3491 }else{ 3476 3492 wrFlag = 0; 3477 3493 } 3478 3494 if( pOp->p5 & OPFLAG_P2ISREG ){ 3479 3495 assert( p2>0 ); 3480 3496 assert( p2<=(p->nMem+1 - p->nCursor) ); 3497 + assert( pOp->opcode==OP_OpenWrite ); 3481 3498 pIn2 = &aMem[p2]; 3482 3499 assert( memIsValid(pIn2) ); 3483 3500 assert( (pIn2->flags & MEM_Int)!=0 ); 3484 3501 sqlite3VdbeMemIntegerify(pIn2); 3485 3502 p2 = (int)pIn2->u.i; 3486 3503 /* The p2 value always comes from a prior OP_CreateBtree opcode and 3487 3504 ** that opcode will always set the p2 value to 2 or more or else fail. ................................................................................ 3602 3619 pCx = allocateCursor(p, pOp->p1, pOp->p2, -1, CURTYPE_BTREE); 3603 3620 if( pCx==0 ) goto no_mem; 3604 3621 pCx->nullRow = 1; 3605 3622 pCx->isEphemeral = 1; 3606 3623 rc = sqlite3BtreeOpen(db->pVfs, 0, db, &pCx->pBtx, 3607 3624 BTREE_OMIT_JOURNAL | BTREE_SINGLE | pOp->p5, vfsFlags); 3608 3625 if( rc==SQLITE_OK ){ 3609 - rc = sqlite3BtreeBeginTrans(pCx->pBtx, 1); 3626 + rc = sqlite3BtreeBeginTrans(pCx->pBtx, 1, 0); 3610 3627 } 3611 3628 if( rc==SQLITE_OK ){ 3612 3629 /* If a transient index is required, create it by calling 3613 3630 ** sqlite3BtreeCreateTable() with the BTREE_BLOBKEY flag before 3614 3631 ** opening it. If a transient table is required, just use the 3615 3632 ** automatically created table with root-page 1 (an BLOB_INTKEY table). 3616 3633 */ ................................................................................ 4009 4026 goto jump_to_p2; 4010 4027 }else if( eqOnly ){ 4011 4028 assert( pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT ); 4012 4029 pOp++; /* Skip the OP_IdxLt or OP_IdxGT that follows */ 4013 4030 } 4014 4031 break; 4015 4032 } 4033 + 4034 +/* Opcode: SeekHit P1 P2 * * * 4035 +** Synopsis: seekHit=P2 4036 +** 4037 +** Set the seekHit flag on cursor P1 to the value in P2. 4038 +** The seekHit flag is used by the IfNoHope opcode. 4039 +** 4040 +** P1 must be a valid b-tree cursor. P2 must be a boolean value, 4041 +** either 0 or 1. 4042 +*/ 4043 +case OP_SeekHit: { 4044 + VdbeCursor *pC; 4045 + assert( pOp->p1>=0 && pOp->p1<p->nCursor ); 4046 + pC = p->apCsr[pOp->p1]; 4047 + assert( pC!=0 ); 4048 + assert( pOp->p2==0 || pOp->p2==1 ); 4049 + pC->seekHit = pOp->p2 & 1; 4050 + break; 4051 +} 4016 4052 4017 4053 /* Opcode: Found P1 P2 P3 P4 * 4018 4054 ** Synopsis: key=r[P3@P4] 4019 4055 ** 4020 4056 ** If P4==0 then register P3 holds a blob constructed by MakeRecord. If 4021 4057 ** P4>0 then register P3 is the first of P4 registers that form an unpacked 4022 4058 ** record. ................................................................................ 4044 4080 ** falls through to the next instruction and P1 is left pointing at the 4045 4081 ** matching entry. 4046 4082 ** 4047 4083 ** This operation leaves the cursor in a state where it cannot be 4048 4084 ** advanced in either direction. In other words, the Next and Prev 4049 4085 ** opcodes do not work after this operation. 4050 4086 ** 4051 -** See also: Found, NotExists, NoConflict 4087 +** See also: Found, NotExists, NoConflict, IfNoHope 4088 +*/ 4089 +/* Opcode: IfNoHope P1 P2 P3 P4 * 4090 +** Synopsis: key=r[P3@P4] 4091 +** 4092 +** Register P3 is the first of P4 registers that form an unpacked 4093 +** record. 4094 +** 4095 +** Cursor P1 is on an index btree. If the seekHit flag is set on P1, then 4096 +** this opcode is a no-op. But if the seekHit flag of P1 is clear, then 4097 +** check to see if there is any entry in P1 that matches the 4098 +** prefix identified by P3 and P4. If no entry matches the prefix, 4099 +** jump to P2. Otherwise fall through. 4100 +** 4101 +** This opcode behaves like OP_NotFound if the seekHit 4102 +** flag is clear and it behaves like OP_Noop if the seekHit flag is set. 4103 +** 4104 +** This opcode is used in IN clause processing for a multi-column key. 4105 +** If an IN clause is attached to an element of the key other than the 4106 +** left-most element, and if there are no matches on the most recent 4107 +** seek over the whole key, then it might be that one of the key element 4108 +** to the left is prohibiting a match, and hence there is "no hope" of 4109 +** any match regardless of how many IN clause elements are checked. 4110 +** In such a case, we abandon the IN clause search early, using this 4111 +** opcode. The opcode name comes from the fact that the 4112 +** jump is taken if there is "no hope" of achieving a match. 4113 +** 4114 +** See also: NotFound, SeekHit 4052 4115 */ 4053 4116 /* Opcode: NoConflict P1 P2 P3 P4 * 4054 4117 ** Synopsis: key=r[P3@P4] 4055 4118 ** 4056 4119 ** If P4==0 then register P3 holds a blob constructed by MakeRecord. If 4057 4120 ** P4>0 then register P3 is the first of P4 registers that form an unpacked 4058 4121 ** record. ................................................................................ 4069 4132 ** 4070 4133 ** This operation leaves the cursor in a state where it cannot be 4071 4134 ** advanced in either direction. In other words, the Next and Prev 4072 4135 ** opcodes do not work after this operation. 4073 4136 ** 4074 4137 ** See also: NotFound, Found, NotExists 4075 4138 */ 4139 +case OP_IfNoHope: { /* jump, in3 */ 4140 + VdbeCursor *pC; 4141 + assert( pOp->p1>=0 && pOp->p1<p->nCursor ); 4142 + pC = p->apCsr[pOp->p1]; 4143 + assert( pC!=0 ); 4144 + if( pC->seekHit ) break; 4145 + /* Fall through into OP_NotFound */ 4146 +} 4076 4147 case OP_NoConflict: /* jump, in3 */ 4077 4148 case OP_NotFound: /* jump, in3 */ 4078 4149 case OP_Found: { /* jump, in3 */ 4079 4150 int alreadyExists; 4080 4151 int takeJump; 4081 4152 int ii; 4082 4153 VdbeCursor *pC; ................................................................................ 4217 4288 case OP_NotExists: /* jump, in3 */ 4218 4289 pIn3 = &aMem[pOp->p3]; 4219 4290 assert( pIn3->flags & MEM_Int ); 4220 4291 assert( pOp->p1>=0 && pOp->p1<p->nCursor ); 4221 4292 pC = p->apCsr[pOp->p1]; 4222 4293 assert( pC!=0 ); 4223 4294 #ifdef SQLITE_DEBUG 4224 - pC->seekOp = 0; 4295 + pC->seekOp = OP_SeekRowid; 4225 4296 #endif 4226 4297 assert( pC->isTable ); 4227 4298 assert( pC->eCurType==CURTYPE_BTREE ); 4228 4299 pCrsr = pC->uc.pCursor; 4229 4300 assert( pCrsr!=0 ); 4230 4301 res = 0; 4231 4302 iKey = pIn3->u.i; ................................................................................ 4871 4942 assert( pC!=0 ); 4872 4943 pC->nullRow = 1; 4873 4944 pC->cacheStatus = CACHE_STALE; 4874 4945 if( pC->eCurType==CURTYPE_BTREE ){ 4875 4946 assert( pC->uc.pCursor!=0 ); 4876 4947 sqlite3BtreeClearCursor(pC->uc.pCursor); 4877 4948 } 4949 +#ifdef SQLITE_DEBUG 4950 + if( pC->seekOp==0 ) pC->seekOp = OP_NullRow; 4951 +#endif 4878 4952 break; 4879 4953 } 4880 4954 4881 4955 /* Opcode: SeekEnd P1 * * * * 4882 4956 ** 4883 4957 ** Position cursor P1 at the end of the btree for the purpose of 4884 4958 ** appending a new entry onto the btree. ................................................................................ 5058 5132 ** 5059 5133 ** P4 is always of type P4_ADVANCE. The function pointer points to 5060 5134 ** sqlite3BtreeNext(). 5061 5135 ** 5062 5136 ** If P5 is positive and the jump is taken, then event counter 5063 5137 ** number P5-1 in the prepared statement is incremented. 5064 5138 ** 5065 -** See also: Prev, NextIfOpen 5066 -*/ 5067 -/* Opcode: NextIfOpen P1 P2 P3 P4 P5 5068 -** 5069 -** This opcode works just like Next except that if cursor P1 is not 5070 -** open it behaves a no-op. 5139 +** See also: Prev 5071 5140 */ 5072 5141 /* Opcode: Prev P1 P2 P3 P4 P5 5073 5142 ** 5074 5143 ** Back up cursor P1 so that it points to the previous key/data pair in its 5075 5144 ** table or index. If there is no previous key/value pairs then fall through 5076 5145 ** to the following instruction. But if the cursor backup was successful, 5077 5146 ** jump immediately to P2. ................................................................................ 5091 5160 ** 5092 5161 ** P4 is always of type P4_ADVANCE. The function pointer points to 5093 5162 ** sqlite3BtreePrevious(). 5094 5163 ** 5095 5164 ** If P5 is positive and the jump is taken, then event counter 5096 5165 ** number P5-1 in the prepared statement is incremented. 5097 5166 */ 5098 -/* Opcode: PrevIfOpen P1 P2 P3 P4 P5 5099 -** 5100 -** This opcode works just like Prev except that if cursor P1 is not 5101 -** open it behaves a no-op. 5102 -*/ 5103 5167 /* Opcode: SorterNext P1 P2 * * P5 5104 5168 ** 5105 5169 ** This opcode works just like OP_Next except that P1 must be a 5106 5170 ** sorter object for which the OP_SorterSort opcode has been 5107 5171 ** invoked. This opcode advances the cursor to the next sorted 5108 5172 ** record, or jumps to P2 if there are no more sorted records. 5109 5173 */ ................................................................................ 5110 5174 case OP_SorterNext: { /* jump */ 5111 5175 VdbeCursor *pC; 5112 5176 5113 5177 pC = p->apCsr[pOp->p1]; 5114 5178 assert( isSorter(pC) ); 5115 5179 rc = sqlite3VdbeSorterNext(db, pC); 5116 5180 goto next_tail; 5117 -case OP_PrevIfOpen: /* jump */ 5118 -case OP_NextIfOpen: /* jump */ 5119 - if( p->apCsr[pOp->p1]==0 ) break; 5120 - /* Fall through */ 5121 5181 case OP_Prev: /* jump */ 5122 5182 case OP_Next: /* jump */ 5123 5183 assert( pOp->p1>=0 && pOp->p1<p->nCursor ); 5124 5184 assert( pOp->p5<ArraySize(p->aCounter) ); 5125 5185 pC = p->apCsr[pOp->p1]; 5126 5186 assert( pC!=0 ); 5127 5187 assert( pC->deferredMoveto==0 ); 5128 5188 assert( pC->eCurType==CURTYPE_BTREE ); 5129 5189 assert( pOp->opcode!=OP_Next || pOp->p4.xAdvance==sqlite3BtreeNext ); 5130 5190 assert( pOp->opcode!=OP_Prev || pOp->p4.xAdvance==sqlite3BtreePrevious ); 5131 - assert( pOp->opcode!=OP_NextIfOpen || pOp->p4.xAdvance==sqlite3BtreeNext ); 5132 - assert( pOp->opcode!=OP_PrevIfOpen || pOp->p4.xAdvance==sqlite3BtreePrevious); 5133 5191 5134 - /* The Next opcode is only used after SeekGT, SeekGE, and Rewind. 5192 + /* The Next opcode is only used after SeekGT, SeekGE, Rewind, and Found. 5135 5193 ** The Prev opcode is only used after SeekLT, SeekLE, and Last. */ 5136 - assert( pOp->opcode!=OP_Next || pOp->opcode!=OP_NextIfOpen 5194 + assert( pOp->opcode!=OP_Next 5137 5195 || pC->seekOp==OP_SeekGT || pC->seekOp==OP_SeekGE 5138 - || pC->seekOp==OP_Rewind || pC->seekOp==OP_Found); 5139 - assert( pOp->opcode!=OP_Prev || pOp->opcode!=OP_PrevIfOpen 5196 + || pC->seekOp==OP_Rewind || pC->seekOp==OP_Found 5197 + || pC->seekOp==OP_NullRow); 5198 + assert( pOp->opcode!=OP_Prev 5140 5199 || pC->seekOp==OP_SeekLT || pC->seekOp==OP_SeekLE 5141 - || pC->seekOp==OP_Last ); 5200 + || pC->seekOp==OP_Last 5201 + || pC->seekOp==OP_NullRow); 5142 5202 5143 5203 rc = pOp->p4.xAdvance(pC->uc.pCursor, pOp->p3); 5144 5204 next_tail: 5145 5205 pC->cacheStatus = CACHE_STALE; 5146 5206 VdbeBranchTaken(rc==SQLITE_OK,2); 5147 5207 if( rc==SQLITE_OK ){ 5148 5208 pC->nullRow = 0;
Changes to src/vdbeInt.h.
81 81 #ifdef SQLITE_DEBUG 82 82 u8 seekOp; /* Most recent seek operation on this cursor */ 83 83 u8 wrFlag; /* The wrFlag argument to sqlite3BtreeCursor() */ 84 84 #endif 85 85 Bool isEphemeral:1; /* True for an ephemeral table */ 86 86 Bool useRandomRowid:1; /* Generate new record numbers semi-randomly */ 87 87 Bool isOrdered:1; /* True if the table is not BTREE_UNORDERED */ 88 + Bool seekHit:1; /* See the OP_SeekHit and OP_IfNoHope opcodes */ 88 89 Btree *pBtx; /* Separate file holding temporary table */ 89 90 i64 seqCount; /* Sequence counter */ 90 91 int *aAltMap; /* Mapping from table to index column numbers */ 91 92 92 93 /* Cached OP_Column parse information is only valid if cacheStatus matches 93 94 ** Vdbe.cacheCtr. Vdbe.cacheCtr will never take on the value of 94 95 ** CACHE_STALE (0) and so setting cacheStatus=CACHE_STALE guarantees that
Changes to src/vdbeaux.c.
685 685 case OP_Vacuum: 686 686 case OP_JournalMode: { 687 687 p->readOnly = 0; 688 688 p->bIsReader = 1; 689 689 break; 690 690 } 691 691 case OP_Next: 692 - case OP_NextIfOpen: 693 692 case OP_SorterNext: { 694 693 pOp->p4.xAdvance = sqlite3BtreeNext; 695 694 pOp->p4type = P4_ADVANCE; 696 695 /* The code generator never codes any of these opcodes as a jump 697 696 ** to a label. They are always coded as a jump backwards to a 698 697 ** known address */ 699 698 assert( pOp->p2>=0 ); 700 699 break; 701 700 } 702 - case OP_Prev: 703 - case OP_PrevIfOpen: { 701 + case OP_Prev: { 704 702 pOp->p4.xAdvance = sqlite3BtreePrevious; 705 703 pOp->p4type = P4_ADVANCE; 706 704 /* The code generator never codes any of these opcodes as a jump 707 705 ** to a label. They are always coded as a jump backwards to a 708 706 ** known address */ 709 707 assert( pOp->p2>=0 ); 710 708 break; ................................................................................ 4125 4123 ){ 4126 4124 u32 d1; /* Offset into aKey[] of next data element */ 4127 4125 int i; /* Index of next field to compare */ 4128 4126 u32 szHdr1; /* Size of record header in bytes */ 4129 4127 u32 idx1; /* Offset of first type in header */ 4130 4128 int rc = 0; /* Return value */ 4131 4129 Mem *pRhs = pPKey2->aMem; /* Next field of pPKey2 to compare */ 4132 - KeyInfo *pKeyInfo = pPKey2->pKeyInfo; 4130 + KeyInfo *pKeyInfo; 4133 4131 const unsigned char *aKey1 = (const unsigned char *)pKey1; 4134 4132 Mem mem1; 4135 4133 4136 4134 /* If bSkip is true, then the caller has already determined that the first 4137 4135 ** two elements in the keys are equal. Fix the various stack variables so 4138 4136 ** that this routine begins comparing at the second field. */ 4139 4137 if( bSkip ){ ................................................................................ 4220 4218 }else{ 4221 4219 mem1.n = (serial_type - 12) / 2; 4222 4220 testcase( (d1+mem1.n)==(unsigned)nKey1 ); 4223 4221 testcase( (d1+mem1.n+1)==(unsigned)nKey1 ); 4224 4222 if( (d1+mem1.n) > (unsigned)nKey1 ){ 4225 4223 pPKey2->errCode = (u8)SQLITE_CORRUPT_BKPT; 4226 4224 return 0; /* Corruption */ 4227 - }else if( pKeyInfo->aColl[i] ){ 4225 + }else if( (pKeyInfo = pPKey2->pKeyInfo)->aColl[i] ){ 4228 4226 mem1.enc = pKeyInfo->enc; 4229 4227 mem1.db = pKeyInfo->db; 4230 4228 mem1.flags = MEM_Str; 4231 4229 mem1.z = (char*)&aKey1[d1]; 4232 4230 rc = vdbeCompareMemString( 4233 4231 &mem1, pRhs, pKeyInfo->aColl[i], &pPKey2->errCode 4234 4232 ); ................................................................................ 4271 4269 /* RHS is null */ 4272 4270 else{ 4273 4271 serial_type = aKey1[idx1]; 4274 4272 rc = (serial_type!=0); 4275 4273 } 4276 4274 4277 4275 if( rc!=0 ){ 4278 - if( pKeyInfo->aSortOrder[i] ){ 4276 + if( pPKey2->pKeyInfo->aSortOrder[i] ){ 4279 4277 rc = -rc; 4280 4278 } 4281 4279 assert( vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, rc) ); 4282 4280 assert( mem1.szMalloc==0 ); /* See comment below */ 4283 4281 return rc; 4284 4282 } 4285 4283 4286 4284 i++; 4285 + if( i==pPKey2->nField ) break; 4287 4286 pRhs++; 4288 4287 d1 += sqlite3VdbeSerialTypeLen(serial_type); 4289 4288 idx1 += sqlite3VarintLen(serial_type); 4290 - }while( idx1<(unsigned)szHdr1 && i<pPKey2->nField && d1<=(unsigned)nKey1 ); 4289 + }while( idx1<(unsigned)szHdr1 && d1<=(unsigned)nKey1 ); 4291 4290 4292 4291 /* No memory allocation is ever used on mem1. Prove this using 4293 4292 ** the following assert(). If the assert() fails, it indicates a 4294 4293 ** memory leak and a need to call sqlite3VdbeMemRelease(&mem1). */ 4295 4294 assert( mem1.szMalloc==0 ); 4296 4295 4297 4296 /* rc==0 here means that one or both of the keys ran out of fields and 4298 4297 ** all the fields up to that point were equal. Return the default_rc 4299 4298 ** value. */ 4300 4299 assert( CORRUPT_DB 4301 4300 || vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, pPKey2->default_rc) 4302 - || pKeyInfo->db->mallocFailed 4301 + || pPKey2->pKeyInfo->db->mallocFailed 4303 4302 ); 4304 4303 pPKey2->eqSeen = 1; 4305 4304 return pPKey2->default_rc; 4306 4305 } 4307 4306 int sqlite3VdbeRecordCompare( 4308 4307 int nKey1, const void *pKey1, /* Left key */ 4309 4308 UnpackedRecord *pPKey2 /* Right key */ ................................................................................ 4621 4620 return SQLITE_CORRUPT_BKPT; 4622 4621 } 4623 4622 sqlite3VdbeMemInit(&m, db, 0); 4624 4623 rc = sqlite3VdbeMemFromBtree(pCur, 0, (u32)nCellKey, &m); 4625 4624 if( rc ){ 4626 4625 return rc; 4627 4626 } 4628 - *res = sqlite3VdbeRecordCompare(m.n, m.z, pUnpacked); 4627 + *res = sqlite3VdbeRecordCompareWithSkip(m.n, m.z, pUnpacked, 0); 4629 4628 sqlite3VdbeMemRelease(&m); 4630 4629 return SQLITE_OK; 4631 4630 } 4632 4631 4633 4632 /* 4634 4633 ** This routine sets the value to be returned by subsequent calls to 4635 4634 ** sqlite3_changes() on the database handle 'db'.
Changes to src/where.c.
5079 5079 if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){ 5080 5080 struct InLoop *pIn; 5081 5081 int j; 5082 5082 sqlite3VdbeResolveLabel(v, pLevel->addrNxt); 5083 5083 for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){ 5084 5084 sqlite3VdbeJumpHere(v, pIn->addrInTop+1); 5085 5085 if( pIn->eEndLoopOp!=OP_Noop ){ 5086 + if( pIn->nPrefix ){ 5087 + assert( pLoop->wsFlags & WHERE_IN_EARLYOUT ); 5088 + sqlite3VdbeAddOp4Int(v, OP_IfNoHope, pLevel->iIdxCur, 5089 + sqlite3VdbeCurrentAddr(v)+2, 5090 + pIn->iBase, pIn->nPrefix); 5091 + VdbeCoverage(v); 5092 + } 5086 5093 sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop); 5087 5094 VdbeCoverage(v); 5088 - VdbeCoverageIf(v, pIn->eEndLoopOp==OP_PrevIfOpen); 5089 - VdbeCoverageIf(v, pIn->eEndLoopOp==OP_NextIfOpen); 5095 + VdbeCoverageIf(v, pIn->eEndLoopOp==OP_Prev); 5096 + VdbeCoverageIf(v, pIn->eEndLoopOp==OP_Next); 5090 5097 } 5091 5098 sqlite3VdbeJumpHere(v, pIn->addrInTop-1); 5092 5099 } 5093 5100 } 5094 5101 sqlite3VdbeResolveLabel(v, pLevel->addrBrk); 5095 5102 if( pLevel->addrSkip ){ 5096 5103 sqlite3VdbeGoto(v, pLevel->addrSkip);
Changes to src/whereInt.h.
78 78 int p1, p2; /* Operands of the opcode used to ends the loop */ 79 79 union { /* Information that depends on pWLoop->wsFlags */ 80 80 struct { 81 81 int nIn; /* Number of entries in aInLoop[] */ 82 82 struct InLoop { 83 83 int iCur; /* The VDBE cursor used by this IN operator */ 84 84 int addrInTop; /* Top of the IN loop */ 85 + int iBase; /* Base register of multi-key index record */ 86 + int nPrefix; /* Number of prior entires in the key */ 85 87 u8 eEndLoopOp; /* IN Loop terminator. OP_Next or OP_Prev */ 86 88 } *aInLoop; /* Information about each nested IN operator */ 87 89 } in; /* Used when pWLoop->wsFlags&WHERE_IN_ABLE */ 88 90 Index *pCovidx; /* Possible covering index for WHERE_MULTI_OR */ 89 91 } u; 90 92 struct WhereLoop *pWLoop; /* The selected WhereLoop object */ 91 93 Bitmask notReady; /* FROM entries not usable at this level */ ................................................................................ 551 553 #define WHERE_IN_ABLE 0x00000800 /* Able to support an IN operator */ 552 554 #define WHERE_ONEROW 0x00001000 /* Selects no more than one row */ 553 555 #define WHERE_MULTI_OR 0x00002000 /* OR using multiple indices */ 554 556 #define WHERE_AUTO_INDEX 0x00004000 /* Uses an ephemeral index */ 555 557 #define WHERE_SKIPSCAN 0x00008000 /* Uses the skip-scan algorithm */ 556 558 #define WHERE_UNQ_WANTED 0x00010000 /* WHERE_ONEROW would have been helpful*/ 557 559 #define WHERE_PARTIALIDX 0x00020000 /* The automatic index is partial */ 560 +#define WHERE_IN_EARLYOUT 0x00040000 /* Perhaps quit IN loops early */
Changes to src/wherecode.c.
587 587 }else{ 588 588 int iCol = aiMap ? aiMap[iMap++] : 0; 589 589 pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut); 590 590 } 591 591 sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v); 592 592 if( i==iEq ){ 593 593 pIn->iCur = iTab; 594 - pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen; 594 + pIn->eEndLoopOp = bRev ? OP_Prev : OP_Next; 595 + if( iEq>0 && (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 ){ 596 + pIn->iBase = iReg - i; 597 + pIn->nPrefix = i; 598 + pLoop->wsFlags |= WHERE_IN_EARLYOUT; 599 + }else{ 600 + pIn->nPrefix = 0; 601 + } 595 602 }else{ 596 603 pIn->eEndLoopOp = OP_Noop; 597 604 } 598 605 pIn++; 599 606 } 600 607 } 601 608 }else{ ................................................................................ 1654 1661 } 1655 1662 codeApplyAffinity(pParse, regBase, nConstraint - bSeekPastNull, zStartAff); 1656 1663 if( pLoop->nSkip>0 && nConstraint==pLoop->nSkip ){ 1657 1664 /* The skip-scan logic inside the call to codeAllEqualityConstraints() 1658 1665 ** above has already left the cursor sitting on the correct row, 1659 1666 ** so no further seeking is needed */ 1660 1667 }else{ 1668 + if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){ 1669 + sqlite3VdbeAddOp1(v, OP_SeekHit, iIdxCur); 1670 + } 1661 1671 op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev]; 1662 1672 assert( op!=0 ); 1663 1673 sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint); 1664 1674 VdbeCoverage(v); 1665 1675 VdbeCoverageIf(v, op==OP_Rewind); testcase( op==OP_Rewind ); 1666 1676 VdbeCoverageIf(v, op==OP_Last); testcase( op==OP_Last ); 1667 1677 VdbeCoverageIf(v, op==OP_SeekGT); testcase( op==OP_SeekGT ); ................................................................................ 1716 1726 op = aEndOp[bRev*2 + endEq]; 1717 1727 sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint); 1718 1728 testcase( op==OP_IdxGT ); VdbeCoverageIf(v, op==OP_IdxGT ); 1719 1729 testcase( op==OP_IdxGE ); VdbeCoverageIf(v, op==OP_IdxGE ); 1720 1730 testcase( op==OP_IdxLT ); VdbeCoverageIf(v, op==OP_IdxLT ); 1721 1731 testcase( op==OP_IdxLE ); VdbeCoverageIf(v, op==OP_IdxLE ); 1722 1732 } 1733 + 1734 + if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){ 1735 + sqlite3VdbeAddOp2(v, OP_SeekHit, iIdxCur, 1); 1736 + } 1723 1737 1724 1738 /* Seek the table cursor, if required */ 1725 1739 if( omitTable ){ 1726 1740 /* pIdx is a covering index. No need to access the main table. */ 1727 1741 }else if( HasRowid(pIdx->pTable) ){ 1728 1742 if( (pWInfo->wctrlFlags & WHERE_SEEK_TABLE) || ( 1729 1743 (pWInfo->wctrlFlags & WHERE_SEEK_UNIQ_TABLE)
Changes to test/aggnested.test.
61 61 NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1)); 62 62 REPLACE INTO t2 VALUES(1,88,888,8888); 63 63 REPLACE INTO t2 VALUES(2,99,999,9999); 64 64 SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2), 65 65 t1.* 66 66 FROM t1; 67 67 } 68 -} {A,B,B 3 33 333 3333} 68 +} {A,B,B 1 11 111 1111} 69 69 db2 close 70 70 71 71 ##################### Test cases for ticket [bfbf38e5e9956ac69f] ############ 72 72 # 73 73 # This first test case is the original problem report: 74 74 do_test aggnested-3.0 { 75 75 db eval {
Changes to test/e_select.test.
797 797 do_select_tests e_select-4.1 { 798 798 1 "SELECT * FROM z1 LIMIT 1" {51.65 -59.58 belfries} 799 799 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} 800 800 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} 801 801 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} 802 802 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} 803 803 804 - 6 "SELECT count(*), * FROM z1" {6 63 born -26} 804 + 6 "SELECT count(*), * FROM z1" {6 51.65 -59.58 belfries} 805 805 7 "SELECT max(a), * FROM z1" {63 63 born -26} 806 806 8 "SELECT *, min(a) FROM z1" {-5 {} 75 -5} 807 807 808 808 9 "SELECT *,* FROM z1,z2 LIMIT 1" { 809 809 51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21 810 810 } 811 811 10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" { ................................................................................ 935 935 CREATE TABLE a2(one PRIMARY KEY, three); 936 936 INSERT INTO a2 VALUES(1, 1); 937 937 INSERT INTO a2 VALUES(3, 2); 938 938 INSERT INTO a2 VALUES(6, 3); 939 939 INSERT INTO a2 VALUES(10, 4); 940 940 } {} 941 941 do_select_tests e_select-4.6 { 942 - 1 "SELECT one, two, count(*) FROM a1" {4 10 4} 943 - 2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {2 3 2} 942 + 1 "SELECT one, two, count(*) FROM a1" {1 1 4} 943 + 2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {1 1 2} 944 944 3 "SELECT one, two, count(*) FROM a1 WHERE one>3" {4 10 1} 945 - 4 "SELECT *, count(*) FROM a1 JOIN a2" {4 10 10 4 16} 946 - 5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} 947 - 6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} 948 - 7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6} 945 + 4 "SELECT *, count(*) FROM a1 JOIN a2" {1 1 1 1 16} 946 + 5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {1 1 1 3} 947 + 6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {1 1 1 3} 948 + 7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 1 1} 949 949 } 950 950 951 951 # EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then 952 952 # each non-aggregate expression is evaluated against a row consisting 953 953 # entirely of NULL values. 954 954 # 955 955 do_select_tests e_select-4.7 { ................................................................................ 1124 1124 1.2 "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y} 1125 1125 1.3 "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x} 1126 1126 1.4 "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4} 1127 1127 1128 1128 2.1 "SELECT up FROM c1 GROUP BY up HAVING down>10" {y} 1129 1129 2.2 "SELECT up FROM c1 GROUP BY up HAVING up='y'" {y} 1130 1130 1131 - 2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6" {9 36} 1131 + 2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING j>6" {5 10} 1132 1132 } 1133 1133 1134 1134 # EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then 1135 1135 # evaluated once for each group of rows. 1136 1136 # 1137 1137 # EVIDENCE-OF: R-53735-47017 If the expression is an aggregate 1138 1138 # expression, it is evaluated across all rows in the group. ................................................................................ 1150 1150 # arbitrarily chosen row from within the group. 1151 1151 # 1152 1152 # EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate 1153 1153 # expression in the result-set, then all such expressions are evaluated 1154 1154 # for the same row. 1155 1155 # 1156 1156 do_select_tests e_select-4.15 { 1157 - 1 "SELECT i, j FROM c2 GROUP BY i%2" {8 28 9 36} 1158 - 2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28} 1159 - 3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} 1160 - 4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} 1157 + 1 "SELECT i, j FROM c2 GROUP BY i%2" {2 1 1 0} 1158 + 2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {2 1 1 0} 1159 + 3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {} 1160 + 4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {} 1161 1161 5 "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)" 1162 - {2 5 boron 2 2 helium 1 3 lithium} 1162 + {2 4 beryllium 2 1 hydrogen 1 3 lithium} 1163 1163 } 1164 1164 1165 1165 # EVIDENCE-OF: R-19334-12811 Each group of input dataset rows 1166 1166 # contributes a single row to the set of result rows. 1167 1167 # 1168 1168 # EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the 1169 1169 # DISTINCT keyword, the number of rows returned by an aggregate query
Added test/in6.test.
1 +# 2018-06-07 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# 12 +# A multi-key index that uses an IN operator on one of the keys other 13 +# than the left-most key is able to abort the IN-operator loop early 14 +# if key terms further to the left do not match. 15 +# 16 +# Call this the "multikey-IN-operator early-out optimization" or 17 +# just "IN-early-out" optimization for short. 18 +# 19 + 20 +set testdir [file dirname $argv0] 21 +source $testdir/tester.tcl 22 +set testprefix in6 23 + 24 +do_test in6-1.1 { 25 + db eval { 26 + CREATE TABLE t1(a,b,c,d); 27 + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) 28 + INSERT INTO t1(a,b,c,d) 29 + SELECT 100, 200+x/2, 300+x/5, x FROM c; 30 + CREATE INDEX t1abc ON t1(a,b,c); 31 + } 32 + set ::sqlite_search_count 0 33 + db eval { 34 + SELECT d FROM t1 35 + WHERE a=99 36 + AND b IN (200,205,201,204) 37 + AND c IN (304,302,309,308); 38 + } 39 +} {} 40 +do_test in6-1.2 { 41 + set ::sqlite_search_count 42 +} {0} ;# Without the IN-early-out optimization, this value would be 15 43 + 44 +# The multikey-IN-operator early-out optimization does not apply 45 +# when the IN operator is on the left-most column of the index. 46 +# 47 +do_test in6-1.3 { 48 + db eval { 49 + EXPLAIN 50 + SELECT d FROM t1 51 + WHERE a IN (98,99,100,101) 52 + AND b=200 AND c=300; 53 + } 54 +} {~/(IfNoHope|SeekHit)/} 55 + 56 +set sqlite_search_count 0 57 +do_execsql_test in6-1.4 { 58 + SELECT d FROM t1 59 + WHERE a=100 60 + AND b IN (200,201,202,204) 61 + AND c IN (300,302,301,305) 62 + ORDER BY +d; 63 +} {1 2 3 4 5 8 9} 64 +do_test in6-1.5 { 65 + set ::sqlite_search_count 66 +} {39} 67 + 68 +do_execsql_test in6-2.1 { 69 + CREATE TABLE t2(e INT UNIQUE, f TEXT); 70 + SELECT d, f FROM t1 LEFT JOIN t2 ON (e=d) 71 + WHERE a=100 72 + AND b IN (200,201,202,204) 73 + AND c IN (300,302,301,305) 74 + ORDER BY +d; 75 +} {1 {} 2 {} 3 {} 4 {} 5 {} 8 {} 9 {}} 76 + 77 +finish_test
Changes to test/select5.test.
150 150 SELECT a, b FROM t2 GROUP BY a, b; 151 151 } 152 152 } {1 2 1 4 6 4} 153 153 do_test select5-5.5 { 154 154 execsql { 155 155 SELECT a, b FROM t2 GROUP BY a; 156 156 } 157 -} {1 4 6 4} 157 +} {1 2 6 4} 158 158 159 159 # Test rendering of columns for the GROUP BY clause. 160 160 # 161 161 do_test select5-5.11 { 162 162 execsql { 163 163 SELECT max(c), b*a, b, a FROM t2 GROUP BY b*a, b, a 164 164 }
Changes to test/shell1.test.
632 632 do_test shell1-3.23b.3 { 633 633 catchcmd "test.db" ".stats OFF" 634 634 } {0 {}} 635 635 do_test shell1-3.23b.4 { 636 636 # too many arguments 637 637 catchcmd "test.db" ".stats OFF BAD" 638 638 } {1 {Usage: .stats ?on|off?}} 639 + 640 +# Ticket 7be932dfa60a8a6b3b26bcf7623ec46e0a403ddb 2018-06-07 641 +# Adverse interaction between .stats and .eqp 642 +# 643 +do_test shell1-3.23b.5 { 644 + catchcmd "test.db" [string map {"\n " "\n"} { 645 + CREATE TEMP TABLE t1(x); 646 + INSERT INTO t1 VALUES(1),(2); 647 + .stats on 648 + .eqp full 649 + SELECT * FROM t1; 650 + }] 651 +} {/1\n2\n/} 639 652 640 653 # .tables ?TABLE? List names of tables 641 654 # If TABLE specified, only list tables matching 642 655 # LIKE pattern TABLE. 643 656 do_test shell1-3.24.1 { 644 657 catchcmd "test.db" ".tables" 645 658 } {0 {}}
Changes to test/where.test.
486 486 SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; 487 487 } 488 488 } {2 1 9 3 1 16 6} 489 489 do_test where-5.14 { 490 490 count { 491 491 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; 492 492 } 493 - } {2 1 9 5} 493 + } {2 1 9 4} 494 494 do_test where-5.15 { 495 495 count { 496 496 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1; 497 497 } 498 - } {2 1 9 3 1 16 9} 498 + } {2 1 9 3 1 16 8} 499 499 do_test where-5.100 { 500 500 db eval { 501 501 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) 502 502 ORDER BY x, y 503 503 } 504 504 } {2 1 9 54 5 3025 62 5 3969} 505 505 do_test where-5.101 {