Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -1093,19 +1093,24 @@ pCol->zName = z; sqlite3ColumnPropertiesFromName(p, pCol); if( pType->n==0 ){ /* If there is no type specified, columns have the default affinity - ** 'BLOB'. */ + ** 'BLOB' with a default size of 4 bytes. */ pCol->affinity = SQLITE_AFF_BLOB; pCol->szEst = 1; +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + if( 4>=sqlite3GlobalConfig.szSorterRef ){ + pCol->colFlags |= COLFLAG_SORTERREF; + } +#endif }else{ zType = z + sqlite3Strlen30(z) + 1; memcpy(zType, pType->z, pType->n); zType[pType->n] = 0; sqlite3Dequote(zType); - pCol->affinity = sqlite3AffinityType(zType, &pCol->szEst); + pCol->affinity = sqlite3AffinityType(zType, pCol); pCol->colFlags |= COLFLAG_HASTYPE; } p->nCol++; pParse->constraintName.n = 0; } @@ -1161,11 +1166,11 @@ ** 'DOUB' | SQLITE_AFF_REAL ** ** If none of the substrings in the above table are found, ** SQLITE_AFF_NUMERIC is returned. */ -char sqlite3AffinityType(const char *zIn, u8 *pszEst){ +char sqlite3AffinityType(const char *zIn, Column *pCol){ u32 h = 0; char aff = SQLITE_AFF_NUMERIC; const char *zChar = 0; assert( zIn!=0 ); @@ -1198,31 +1203,36 @@ aff = SQLITE_AFF_INTEGER; break; } } - /* If pszEst is not NULL, store an estimate of the field size. The + /* If pCol is not NULL, store an estimate of the field size. The ** estimate is scaled so that the size of an integer is 1. */ - if( pszEst ){ - *pszEst = 1; /* default size is approx 4 bytes */ + if( pCol ){ + int v = 0; /* default size is approx 4 bytes */ if( aff r=(k/4+1) */ sqlite3GetInt32(zChar, &v); - v = v/4 + 1; - if( v>255 ) v = 255; - *pszEst = v; /* BLOB(k), VARCHAR(k), CHAR(k) -> r=(k/4+1) */ break; } zChar++; } }else{ - *pszEst = 5; /* BLOB, TEXT, CLOB -> r=5 (approx 20 bytes)*/ + v = 16; /* BLOB, TEXT, CLOB -> r=5 (approx 20 bytes)*/ } } +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + if( v>=sqlite3GlobalConfig.szSorterRef ){ + pCol->colFlags |= COLFLAG_SORTERREF; + } +#endif + v = v/4 + 1; + if( v>255 ) v = 255; + pCol->szEst = v; } return aff; } /* Index: src/ctime.c ================================================================== --- src/ctime.c +++ src/ctime.c @@ -283,10 +283,13 @@ #if SQLITE_ENABLE_SESSION "ENABLE_SESSION", #endif #if SQLITE_ENABLE_SNAPSHOT "ENABLE_SNAPSHOT", +#endif +#if SQLITE_ENABLE_SORTER_REFERENCES + "ENABLE_SORTER_REFERENCES", #endif #if SQLITE_ENABLE_SQLLOG "ENABLE_SQLLOG", #endif #if defined(SQLITE_ENABLE_STAT4) Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -1361,10 +1361,11 @@ pItem->zName = sqlite3DbStrDup(db, pOldItem->zName); pItem->zSpan = sqlite3DbStrDup(db, pOldItem->zSpan); pItem->sortOrder = pOldItem->sortOrder; pItem->done = 0; pItem->bSpanIsTab = pOldItem->bSpanIsTab; + pItem->bSorterRef = pOldItem->bSorterRef; pItem->u = pOldItem->u; } return pNew; } @@ -4369,10 +4370,16 @@ assert( pParse->pVdbe!=0 ); /* Never gets this far otherwise */ n = pList->nExpr; if( !ConstFactorOk(pParse) ) flags &= ~SQLITE_ECEL_FACTOR; for(pItem=pList->a, i=0; ipExpr; +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + if( pItem->bSorterRef ){ + i--; + n--; + }else +#endif if( (flags & SQLITE_ECEL_REF)!=0 && (j = pItem->u.x.iOrderByCol)>0 ){ if( flags & SQLITE_ECEL_OMITREF ){ i--; n--; }else{ Index: src/global.c ================================================================== --- src/global.c +++ src/global.c @@ -238,11 +238,12 @@ #endif #ifndef SQLITE_UNTESTABLE 0, /* xTestCallback */ #endif 0, /* bLocaltimeFault */ - 0x7ffffffe /* iOnceResetThreshold */ + 0x7ffffffe, /* iOnceResetThreshold */ + SQLITE_DEFAULT_SORTERREF_SIZE /* szSorterRef */ }; /* ** Hash table for global functions - functions common to all ** database connections. After initialization, this table is Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -640,10 +640,21 @@ case SQLITE_CONFIG_STMTJRNL_SPILL: { sqlite3GlobalConfig.nStmtSpill = va_arg(ap, int); break; } +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + case SQLITE_CONFIG_SORTERREF_SIZE: { + int iVal = va_arg(ap, int); + if( iVal<0 ){ + iVal = SQLITE_DEFAULT_SORTERREF_SIZE; + } + sqlite3GlobalConfig.szSorterRef = (u32)iVal; + break; + } +#endif /* SQLITE_ENABLE_SORTER_REFERENCES */ + default: { rc = SQLITE_ERROR; break; } } Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -42,10 +42,24 @@ }; /* ** An instance of the following object is used to record information about ** the ORDER BY (or GROUP BY) clause of query is being coded. +** +** The aDefer[] array is used by the sorter-references optimization. For +** example, assuming there is no index that can be used for the ORDER BY, +** for the query: +** +** SELECT a, bigblob FROM t1 ORDER BY a LIMIT 10; +** +** it may be more efficient to add just the "a" values to the sorter, and +** retrieve the associated "bigblob" values directly from table t1 as the +** 10 smallest "a" values are extracted from the sorter. +** +** When the sorter-reference optimization is used, there is one entry in the +** aDefer[] array for each database table that may be read as values are +** extracted from the sorter. */ typedef struct SortCtx SortCtx; struct SortCtx { ExprList *pOrderBy; /* The ORDER BY (or GROUP BY clause) */ int nOBSat; /* Number of ORDER BY terms satisfied by indices */ @@ -54,10 +68,18 @@ int labelBkOut; /* Start label for the block-output subroutine */ int addrSortIndex; /* Address of the OP_SorterOpen or OP_OpenEphemeral */ int labelDone; /* Jump here when done, ex: LIMIT reached */ u8 sortFlags; /* Zero or more SORTFLAG_* bits */ u8 bOrderedInnerLoop; /* ORDER BY correctly sorts the inner loop */ +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + u8 nDefer; /* Number of valid entries in aDefer[] */ + struct DeferredCsr { + Table *pTab; /* Table definition */ + int iCsr; /* Cursor number for table */ + int nKey; /* Number of PK columns for table pTab (>=1) */ + } aDefer[4]; +#endif }; #define SORTFLAG_UseSorter 0x01 /* Use SorterOpen instead of OpenEphemeral */ /* ** Delete all the content of a Select structure. Deallocate the structure @@ -676,10 +698,94 @@ sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iTab, r1, iMem, N); sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); sqlite3ReleaseTempReg(pParse, r1); } +#ifdef SQLITE_ENABLE_SORTER_REFERENCES +/* +** This function is called as part of inner-loop generation for a SELECT +** statement with an ORDER BY that is not optimized by an index. It +** determines the expressions, if any, that the sorter-reference +** optimization should be used for. The sorter-reference optimization +** is used for SELECT queries like: +** +** SELECT a, bigblob FROM t1 ORDER BY a LIMIT 10 +** +** If the optimization is used for expression "bigblob", then instead of +** storing values read from that column in the sorter records, the PK of +** the row from table t1 is stored instead. Then, as records are extracted from +** the sorter to return to the user, the required value of bigblob is +** retrieved directly from table t1. If the values are very large, this +** can be more efficient than storing them directly in the sorter records. +** +** The ExprList_item.bSorterRef flag is set for each expression in pEList +** for which the sorter-reference optimization should be enabled. +** Additionally, the pSort->aDefer[] array is populated with entries +** for all cursors required to evaluate all selected expressions. Finally. +** output variable (*ppExtra) is set to an expression list containing +** expressions for all extra PK values that should be stored in the +** sorter records. +*/ +static void selectExprDefer( + Parse *pParse, /* Leave any error here */ + SortCtx *pSort, /* Sorter context */ + ExprList *pEList, /* Expressions destined for sorter */ + ExprList **ppExtra /* Expressions to append to sorter record */ +){ + int i; + int nDefer = 0; + ExprList *pExtra = 0; + for(i=0; inExpr; i++){ + struct ExprList_item *pItem = &pEList->a[i]; + if( pItem->u.x.iOrderByCol==0 ){ + Expr *pExpr = pItem->pExpr; + Table *pTab = pExpr->pTab; + if( pExpr->op==TK_COLUMN && pTab && !IsVirtual(pTab) + && (pTab->aCol[pExpr->iColumn].colFlags & COLFLAG_SORTERREF) +#if 0 + && pTab->pSchema && pTab->pSelect==0 && !IsVirtual(pTab) +#endif + ){ + int j; + for(j=0; jaDefer[j].iCsr==pExpr->iTable ) break; + } + if( j==nDefer ){ + if( nDefer==ArraySize(pSort->aDefer) ){ + continue; + }else{ + int nKey = 1; + int k; + Index *pPk = 0; + if( !HasRowid(pTab) ){ + pPk = sqlite3PrimaryKeyIndex(pTab); + nKey = pPk->nKeyCol; + } + for(k=0; kiTable = pExpr->iTable; + pNew->pTab = pExpr->pTab; + pNew->iColumn = pPk ? pPk->aiColumn[k] : -1; + pExtra = sqlite3ExprListAppend(pParse, pExtra, pNew); + } + } + pSort->aDefer[nDefer].pTab = pExpr->pTab; + pSort->aDefer[nDefer].iCsr = pExpr->iTable; + pSort->aDefer[nDefer].nKey = nKey; + nDefer++; + } + } + pItem->bSorterRef = 1; + } + } + } + pSort->nDefer = (u8)nDefer; + *ppExtra = pExtra; +} +#endif + /* ** This routine generates the code for the inside of the inner loop ** of a SELECT. ** ** If srcTab is negative, then the p->pEList expressions @@ -748,10 +854,13 @@ for(i=0; ipEList->a[i].zName)); } }else if( eDest!=SRT_Exists ){ +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + ExprList *pExtra = 0; +#endif /* If the destination is an EXISTS(...) expression, the actual ** values returned by the SELECT are not required. */ u8 ecelFlags; if( eDest==SRT_Mem || eDest==SRT_Output || eDest==SRT_Coroutine ){ @@ -771,16 +880,38 @@ int j; if( (j = pSort->pOrderBy->a[i].u.x.iOrderByCol)>0 ){ p->pEList->a[j-1].u.x.iOrderByCol = i+1-pSort->nOBSat; } } +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + selectExprDefer(pParse, pSort, p->pEList, &pExtra); + if( pExtra && pParse->db->mallocFailed==0 ){ + /* If there are any extra PK columns to add to the sorter records, + ** allocate extra memory cells and adjust the OpenEphemeral + ** instruction to account for the larger records. This is only + ** required if there are one or more WITHOUT ROWID tables with + ** composite primary keys in the SortCtx.aDefer[] array. */ + VdbeOp *pOp = sqlite3VdbeGetOp(v, pSort->addrSortIndex); + pOp->p2 += (pExtra->nExpr - pSort->nDefer); + pOp->p4.pKeyInfo->nAllField += (pExtra->nExpr - pSort->nDefer); + pParse->nMem += pExtra->nExpr; + } +#endif regOrig = 0; assert( eDest==SRT_Set || eDest==SRT_Mem || eDest==SRT_Coroutine || eDest==SRT_Output ); } nResultCol = sqlite3ExprCodeExprList(pParse,p->pEList,regResult, 0,ecelFlags); +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + if( pExtra ){ + nResultCol += sqlite3ExprCodeExprList( + pParse, pExtra, regResult + nResultCol, 0, 0 + ); + sqlite3ExprListDelete(pParse->db, pExtra); + } +#endif } /* If the DISTINCT keyword was present on the SELECT statement ** and this row has been seen before, then do not make this row ** part of the result. @@ -1234,31 +1365,43 @@ SelectDest *pDest /* Write the sorted results here */ ){ Vdbe *v = pParse->pVdbe; /* The prepared statement */ int addrBreak = pSort->labelDone; /* Jump here to exit loop */ int addrContinue = sqlite3VdbeMakeLabel(v); /* Jump here for next cycle */ - int addr; + int addr; /* Top of output loop. Jump for Next. */ int addrOnce = 0; int iTab; ExprList *pOrderBy = pSort->pOrderBy; int eDest = pDest->eDest; int iParm = pDest->iSDParm; int regRow; int regRowid; int iCol; - int nKey; + int nKey; /* Number of key columns in sorter record */ int iSortTab; /* Sorter cursor to read from */ int i; int bSeq; /* True if sorter record includes seq. no. */ + int nRefKey = 0; struct ExprList_item *aOutEx = p->pEList->a; assert( addrBreak<0 ); if( pSort->labelBkOut ){ sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut); sqlite3VdbeGoto(v, addrBreak); sqlite3VdbeResolveLabel(v, pSort->labelBkOut); } + +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + /* Open any cursors needed for sorter-reference expressions */ + for(i=0; inDefer; i++){ + Table *pTab = pSort->aDefer[i].pTab; + int iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); + sqlite3OpenTable(pParse, pSort->aDefer[i].iCsr, iDb, pTab, OP_OpenRead); + nRefKey = MAX(nRefKey, pSort->aDefer[i].nKey); + } +#endif + iTab = pSort->iECursor; if( eDest==SRT_Output || eDest==SRT_Coroutine || eDest==SRT_Mem ){ regRowid = 0; regRow = pDest->iSdst; }else{ @@ -1270,11 +1413,12 @@ int regSortOut = ++pParse->nMem; iSortTab = pParse->nTab++; if( pSort->labelBkOut ){ addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v); } - sqlite3VdbeAddOp3(v, OP_OpenPseudo, iSortTab, regSortOut, nKey+1+nColumn); + sqlite3VdbeAddOp3(v, OP_OpenPseudo, iSortTab, regSortOut, + nKey+1+nColumn+nRefKey); if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce); addr = 1 + sqlite3VdbeAddOp2(v, OP_SorterSort, iTab, addrBreak); VdbeCoverage(v); codeOffset(v, p->iOffset, addrContinue); sqlite3VdbeAddOp3(v, OP_SorterData, iTab, regSortOut, iSortTab); @@ -1284,21 +1428,62 @@ codeOffset(v, p->iOffset, addrContinue); iSortTab = iTab; bSeq = 1; } for(i=0, iCol=nKey+bSeq-1; inDefer ){ + int iKey = iCol+1; + int regKey = sqlite3GetTempRange(pParse, nRefKey); + + for(i=0; inDefer; i++){ + int iCsr = pSort->aDefer[i].iCsr; + Table *pTab = pSort->aDefer[i].pTab; + int nKey = pSort->aDefer[i].nKey; + + sqlite3VdbeAddOp1(v, OP_NullRow, iCsr); + if( HasRowid(pTab) ){ + sqlite3VdbeAddOp3(v, OP_Column, iSortTab, iKey++, regKey); + sqlite3VdbeAddOp3(v, OP_SeekRowid, iCsr, + sqlite3VdbeCurrentAddr(v)+1, regKey); + }else{ + int k; + int iJmp; + assert( sqlite3PrimaryKeyIndex(pTab)->nKeyCol==nKey ); + for(k=0; k=0; i--){ - int iRead; - if( aOutEx[i].u.x.iOrderByCol ){ - iRead = aOutEx[i].u.x.iOrderByCol-1; - }else{ - iRead = iCol--; - } - sqlite3VdbeAddOp3(v, OP_Column, iSortTab, iRead, regRow+i); - VdbeComment((v, "%s", aOutEx[i].zName ? aOutEx[i].zName : aOutEx[i].zSpan)); +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + if( aOutEx[i].bSorterRef ){ + sqlite3ExprCode(pParse, aOutEx[i].pExpr, regRow+i); + }else +#endif + { + int iRead; + if( aOutEx[i].u.x.iOrderByCol ){ + iRead = aOutEx[i].u.x.iOrderByCol-1; + }else{ + iRead = iCol--; + } + sqlite3VdbeAddOp3(v, OP_Column, iSortTab, iRead, regRow+i); + VdbeComment((v, "%s", aOutEx[i].zName?aOutEx[i].zName : aOutEx[i].zSpan)); + } } switch( eDest ){ case SRT_Table: case SRT_EphemTab: { sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, regRowid); @@ -6070,10 +6255,11 @@ ** and send them to the callback one by one. */ if( sSort.pOrderBy ){ explainTempTable(pParse, sSort.nOBSat>0 ? "RIGHT PART OF ORDER BY":"ORDER BY"); + assert( p->pEList==pEList ); generateSortTail(pParse, p, &sSort, pEList->nExpr, pDest); } /* Jump here to skip this query */ Index: src/shell.c.in ================================================================== --- src/shell.c.in +++ src/shell.c.in @@ -8074,10 +8074,13 @@ " -nullvalue TEXT set text string for NULL values. Default ''\n" " -pagecache SIZE N use N slots of SZ bytes each for page cache memory\n" " -quote set output mode to 'quote'\n" " -readonly open the database read-only\n" " -separator SEP set output column separator. Default: '|'\n" +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + " -sorterref SIZE sorter references threshold size\n" +#endif " -stats print memory stats before each finalize\n" " -version show SQLite version\n" " -vfs NAME use NAME as the default VFS\n" #ifdef SQLITE_ENABLE_VFSTRACE " -vfstrace enable tracing of all VFS calls\n" @@ -8329,10 +8332,15 @@ sqlite3_multiplex_initialize(0, 1); #endif }else if( strcmp(z,"-mmap")==0 ){ sqlite3_int64 sz = integerValue(cmdline_option_value(argc,argv,++i)); sqlite3_config(SQLITE_CONFIG_MMAP_SIZE, sz, sz); +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + }else if( strcmp(z,"-sorterref")==0 ){ + sqlite3_int64 sz = integerValue(cmdline_option_value(argc,argv,++i)); + sqlite3_config(SQLITE_CONFIG_SORTERREF_SIZE, (int)sz); +#endif }else if( strcmp(z,"-vfs")==0 ){ sqlite3_vfs *pVfs = sqlite3_vfs_find(cmdline_option_value(argc,argv,++i)); if( pVfs ){ sqlite3_vfs_register(pVfs, 1); }else{ @@ -8465,10 +8473,14 @@ i+=2; }else if( strcmp(z,"-lookaside")==0 ){ i+=2; }else if( strcmp(z,"-mmap")==0 ){ i++; +#ifdef SQLITE_ENABLE_SORTER_REFERENCES + }else if( strcmp(z,"-sorterref")==0 ){ + i++; +#endif }else if( strcmp(z,"-vfs")==0 ){ i++; #ifdef SQLITE_ENABLE_VFSTRACE }else if( strcmp(z,"-vfstrace")==0 ){ i++; Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -1928,10 +1928,26 @@ ** Since many statement journals never become large, setting the spill ** threshold to a value such as 64KiB can greatly reduce the amount of ** I/O required to support statement rollback. ** The default value for this setting is controlled by the ** [SQLITE_STMTJRNL_SPILL] compile-time option. +** +** [[SQLITE_CONFIG_SORTERREF_SIZE]] +**
SQLITE_CONFIG_SORTERREF_SIZE +**
The SQLITE_CONFIG_SORTERREF_SIZE option accepts a single parameter +** of type (int) - the new value of the sorter-reference size threshold. +** Usually, when SQLite uses an external sort to order records according +** to an ORDER BY clause, all fields required by the caller are present in the +** sorted records. However, if SQLite determines based on the declared type +** of a table column that its values are likely to be very large - larger +** than the configured sorter-reference size threshold - then a reference +** is stored in each sorted record and the required column values loaded +** from the database as records are returned in sorted order. The default +** value for this option is to never use this optimization. Specifying a +** negative value for this option restores the default behaviour. +** This option is only available if SQLite is compiled with the +** [SQLITE_ENABLE_SORTER_REFERENCES] compile-time option. ** */ #define SQLITE_CONFIG_SINGLETHREAD 1 /* nil */ #define SQLITE_CONFIG_MULTITHREAD 2 /* nil */ #define SQLITE_CONFIG_SERIALIZED 3 /* nil */ @@ -1957,10 +1973,11 @@ #define SQLITE_CONFIG_WIN32_HEAPSIZE 23 /* int nByte */ #define SQLITE_CONFIG_PCACHE_HDRSZ 24 /* int *psz */ #define SQLITE_CONFIG_PMASZ 25 /* unsigned int szPma */ #define SQLITE_CONFIG_STMTJRNL_SPILL 26 /* int nByte */ #define SQLITE_CONFIG_SMALL_MALLOC 27 /* boolean */ +#define SQLITE_CONFIG_SORTERREF_SIZE 28 /* int nByte */ /* ** CAPI3REF: Database Connection Configuration Options ** ** These constants are the available integer configuration options that Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -635,10 +635,17 @@ */ #ifndef SQLITE_DEFAULT_PCACHE_INITSZ # define SQLITE_DEFAULT_PCACHE_INITSZ 20 #endif +/* +** Default value for the SQLITE_CONFIG_SORTERREF_SIZE option. +*/ +#ifndef SQLITE_DEFAULT_SORTERREF_SIZE +# define SQLITE_DEFAULT_SORTERREF_SIZE 0x7fffffff +#endif + /* ** The compile-time options SQLITE_MMAP_READWRITE and ** SQLITE_ENABLE_BATCH_ATOMIC_WRITE are not compatible with one another. ** You must choose one or the other (or neither) but not both. */ @@ -1757,10 +1764,11 @@ */ #define COLFLAG_PRIMKEY 0x0001 /* Column is part of the primary key */ #define COLFLAG_HIDDEN 0x0002 /* A hidden column in a virtual table */ #define COLFLAG_HASTYPE 0x0004 /* Type name follows column name */ #define COLFLAG_UNIQUE 0x0008 /* Column def contains "UNIQUE" or "PK" */ +#define COLFLAG_SORTERREF 0x0010 /* Use sorter-refs with this column */ /* ** A "Collating Sequence" is defined by an instance of the following ** structure. Conceptually, a collating sequence consists of a name and ** a comparison routine that defines the order of that sequence. @@ -2497,10 +2505,11 @@ char *zSpan; /* Original text of the expression */ u8 sortOrder; /* 1 for DESC or 0 for ASC */ unsigned done :1; /* A flag to indicate when processing is finished */ unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */ unsigned reusable :1; /* Constant expression is reusable */ + unsigned bSorterRef :1; /* Defer evaluation until after sorting */ union { struct { u16 iOrderByCol; /* For ORDER BY, column number in result set */ u16 iAlias; /* Index into Parse.aAlias[] for zName */ } x; @@ -3318,10 +3327,11 @@ #ifndef SQLITE_UNTESTABLE int (*xTestCallback)(int); /* Invoked by sqlite3FaultSim() */ #endif int bLocaltimeFault; /* True to fail localtime() calls */ int iOnceResetThreshold; /* When to reset OP_Once counters */ + u32 szSorterRef; /* Min size in bytes to use sorter-refs */ }; /* ** This macro is used inside of assert() statements to indicate that ** the assert is only valid on a well-formed database. Instead of: @@ -4100,11 +4110,11 @@ int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*); void sqlite3ColumnDefault(Vdbe *, Table *, int, int); void sqlite3AlterFinishAddColumn(Parse *, Token *); void sqlite3AlterBeginAddColumn(Parse *, SrcList *); CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*); -char sqlite3AffinityType(const char*, u8*); +char sqlite3AffinityType(const char*, Column*); void sqlite3Analyze(Parse*, Token*, Token*); int sqlite3InvokeBusyHandler(BusyHandler*, sqlite3_file*); int sqlite3FindDb(sqlite3*, Token*); int sqlite3FindDbName(sqlite3 *, const char *); int sqlite3AnalysisLoad(sqlite3*,int iDB); Index: src/test1.c ================================================================== --- src/test1.c +++ src/test1.c @@ -2253,10 +2253,31 @@ } sqlite3_config(SQLITE_CONFIG_SQLLOG, 0, 0); return TCL_OK; } #endif + +/* +** Usage: sqlite3_config_sorterref +** +** Set the SQLITE_CONFIG_SORTERREF_SIZE configuration option +*/ +static int SQLITE_TCLAPI test_config_sorterref( + void * clientData, + Tcl_Interp *interp, + int objc, + Tcl_Obj *CONST objv[] +){ + int iVal; + if( objc!=2 ){ + Tcl_WrongNumArgs(interp, 1, objv, "NBYTE"); + return TCL_ERROR; + } + if( Tcl_GetIntFromObj(interp, objv[1], &iVal) ) return TCL_ERROR; + sqlite3_config(SQLITE_CONFIG_SORTERREF_SIZE, iVal); + return TCL_OK; +} /* ** Usage: vfs_current_time_int64 ** ** Return the value returned by the default VFS's xCurrentTimeInt64 method. @@ -7749,10 +7770,11 @@ { "sqlite3_snapshot_cmp_blob", test_snapshot_cmp_blob, 0 }, #endif { "sqlite3_delete_database", test_delete_database, 0 }, { "atomic_batch_write", test_atomic_batch_write, 0 }, { "sqlite3_mmap_warm", test_mmap_warm, 0 }, + { "sqlite3_config_sorterref", test_config_sorterref, 0 }, }; static int bitmask_size = sizeof(Bitmask)*8; static int longdouble_size = sizeof(LONGDOUBLE_TYPE); int i; extern int sqlite3_sync_count, sqlite3_fullsync_count; Index: test/permutations.test ================================================================== --- test/permutations.test +++ test/permutations.test @@ -1070,10 +1070,30 @@ #$::dbhandle cache size 0 } -files [ test_set $allquicktests -exclude *malloc* *ioerr* *fault* \ stmtvtab1.test index9.test ] + +test_suite "sorterref" -prefix "" -description { + Run the "veryquick" test suite with SQLITE_CONFIG_SORTERREF_SIZE set + to 0 so that sorter-references are used whenever possible. +} -files [ + test_set $allquicktests -exclude *malloc* *ioerr* *fault* *bigfile* *_err* \ + *fts5corrupt* *fts5big* *fts5aj* +] -initialize { + catch {db close} + sqlite3_shutdown + sqlite3_config_sorterref 0 + sqlite3_initialize + autoinstall_test_functions +} -shutdown { + catch {db close} + sqlite3_shutdown + sqlite3_config_sorterref -1 + sqlite3_initialize + autoinstall_test_functions +} # End of tests ############################################################################# # run_tests NAME OPTIONS ADDED test/sorterref.test Index: test/sorterref.test ================================================================== --- /dev/null +++ test/sorterref.test @@ -0,0 +1,50 @@ +# 2018 April 14. +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix sorterref + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b, c); + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(4, 5, 6); + ALTER TABLE t1 ADD COLUMN d DEFAULT 'string'; + INSERT INTO t1 VALUES(7, 8, 9, 'text'); +} + +do_execsql_test 1.1 { + SELECT * FROM t1 ORDER BY b; +} { + 1 2 3 string 4 5 6 string 7 8 9 text +} + +do_execsql_test 2.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a, b); + CREATE TABLE t2(c, d, PRIMARY KEY(c)) WITHOUT ROWID; + + INSERT INTO t1 VALUES(1, 2); + INSERT INTO t1 VALUES(2, 3); + INSERT INTO t1 VALUES(3, 4); + + INSERT INTO t2 VALUES(1, 'one'); + INSERT INTO t2 VALUES(3, 'three'); +} + +do_execsql_test 2.1 { + SELECT * FROM t1 LEFT JOIN t2 ON (a=c) ORDER BY b; +} {1 2 1 one 2 3 {} {} 3 4 3 three} + + + +finish_test