Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Reenable covering index support in NGQP. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
9e999145199ee9a4d9043a2d98398bc3 |
User & Date: | dan 2013-07-24 18:30:19.876 |
Context
2013-07-24
| ||
18:47 | Remove the (non-working) xfer-optimization code. check-in: edd188471c user: drh tags: trunk | |
18:30 | Reenable covering index support in NGQP. check-in: 9e99914519 user: dan tags: trunk | |
2013-07-23
| ||
16:50 | Change where.c to read values from covering indexes instead of tables whenever possible. Modify OP_SeekPk to defer seeks (as OP_Seek does in sqlite3). check-in: 7f6b4708c2 user: dan tags: trunk | |
Changes
Changes to src/resolve.c.
︙ | ︙ | |||
362 363 364 365 366 367 368 | /* If a column from a table in pSrcList is referenced, then record ** this fact in the pSrcList.a[].colUsed bitmask. Column 0 causes ** bit 0 to be set. Column 1 sets bit 1. And so forth. If the ** column number is greater than the number of bits in the bitmask ** then set the high-order bit of the bitmask. */ | | | | 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 | /* If a column from a table in pSrcList is referenced, then record ** this fact in the pSrcList.a[].colUsed bitmask. Column 0 causes ** bit 0 to be set. Column 1 sets bit 1. And so forth. If the ** column number is greater than the number of bits in the bitmask ** then set the high-order bit of the bitmask. */ if( pMatch!=0 ){ int n = pExpr->iColumn; testcase( n==BMS-1 ); if( n>=BMS || n<0 ){ n = BMS-1; } assert( pMatch->iCursor==pExpr->iTable ); pMatch->colUsed |= ((Bitmask)1)<<n; } /* Clean up and return |
︙ | ︙ | |||
410 411 412 413 414 415 416 | if( p ){ SrcListItem *pItem = &pSrc->a[iSrc]; p->pTab = pItem->pTab; p->iTable = pItem->iCursor; p->iColumn = (ynVar)iCol; testcase( iCol==BMS ); testcase( iCol==BMS-1 ); | | | 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 | if( p ){ SrcListItem *pItem = &pSrc->a[iSrc]; p->pTab = pItem->pTab; p->iTable = pItem->iCursor; p->iColumn = (ynVar)iCol; testcase( iCol==BMS ); testcase( iCol==BMS-1 ); pItem->colUsed |= ((Bitmask)1)<<((iCol>=BMS || iCol<0) ? BMS-1 : iCol); ExprSetProperty(p, EP_Resolved); } return p; } static void resolveMatchArg(Parse *pParse, NameContext *pNC, Expr *pExpr){ SrcList *pSrc = pNC->pSrcList; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1789 1790 1791 1792 1793 1794 1795 | ** ** The jointype starts out showing the join type between the current table ** and the next table on the list. The parser builds the list this way. ** But sqlite4SrcListShiftJoinType() later shifts the jointypes so that each ** jointype expresses the join between the table and the previous table. ** ** In the colUsed field, the high-order bit (bit 63) is set if the table | | > | 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 | ** ** The jointype starts out showing the join type between the current table ** and the next table on the list. The parser builds the list this way. ** But sqlite4SrcListShiftJoinType() later shifts the jointypes so that each ** jointype expresses the join between the table and the previous table. ** ** In the colUsed field, the high-order bit (bit 63) is set if the table ** contains more than 63 columns and the 64-th or later column is used, or ** if the table uses an implicit primary key and it is accessed. */ struct SrcList { i16 nSrc; /* Number of tables or subqueries in the FROM clause */ i16 nAlloc; /* Number of entries allocated in a[] below */ SrcListItem a[1]; /* One entry for each identifier on the list */ }; |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
214 215 216 217 218 219 220 221 222 223 224 225 226 227 | p->apCsr[iCur] = 0; } if( SQLITE4_OK==sqlite4VdbeMemGrow(pMem, nByte, 0) ){ p->apCsr[iCur] = pCx = (VdbeCursor*)pMem->z; memset(pCx, 0, sizeof(VdbeCursor)); pCx->iDb = iDb; pCx->nField = nField; } return pCx; } /* ** Try to convert a value into a numeric representation if we can ** do so without loss of information. In other words, if the string | > | 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 | p->apCsr[iCur] = 0; } if( SQLITE4_OK==sqlite4VdbeMemGrow(pMem, nByte, 0) ){ p->apCsr[iCur] = pCx = (VdbeCursor*)pMem->z; memset(pCx, 0, sizeof(VdbeCursor)); pCx->iDb = iDb; pCx->nField = nField; sqlite4_buffer_init(&pCx->key, p->db->pEnv->pMM); } return pCx; } /* ** Try to convert a value into a numeric representation if we can ** do so without loss of information. In other words, if the string |
︙ | ︙ | |||
2892 2893 2894 2895 2896 2897 2898 2899 2900 | ** ** This opcode seeks cursor P1 so that it points to the PK index entry ** that corresponds to the same table row as the current entry that ** cursor P3 points to. The entry must exist. If it does not, this opcode ** throws an SQLITE4_CORRUPT exception. */ case OP_SeekPk: { VdbeCursor *pPk; /* Cursor P1 */ VdbeCursor *pIdx; /* Cursor P3 */ | > > < < < | | | > > > > > > > > > > > | > > > | 2893 2894 2895 2896 2897 2898 2899 2900 2901 2902 2903 2904 2905 2906 2907 2908 2909 2910 2911 2912 2913 2914 2915 2916 2917 2918 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 | ** ** This opcode seeks cursor P1 so that it points to the PK index entry ** that corresponds to the same table row as the current entry that ** cursor P3 points to. The entry must exist. If it does not, this opcode ** throws an SQLITE4_CORRUPT exception. */ case OP_SeekPk: { KVByteArray *aKey; /* Key data from cursor pIdx */ KVSize nKey; /* Size of aKey[] in bytes */ VdbeCursor *pPk; /* Cursor P1 */ VdbeCursor *pIdx; /* Cursor P3 */ int nShort; /* Size of aKey[] without PK fields */ int nVarint; /* Size of varint pPk->iRoot */ pPk = p->apCsr[pOp->p1]; pIdx = p->apCsr[pOp->p3]; if( pIdx->pFts ){ rc = sqlite4Fts5Pk(pIdx->pFts, pPk->iRoot, &aKey, &nKey); if( rc==SQLITE4_OK ){ rc = sqlite4KVCursorSeek(pPk->pKVCur, aKey, nKey, 0); if( rc==SQLITE4_NOTFOUND ) rc = SQLITE4_CORRUPT_BKPT; pPk->nullRow = 0; } }else{ rc = sqlite4KVCursorKey(pIdx->pKVCur, (const KVByteArray **)&aKey, &nKey); if( rc!=SQLITE4_OK ) break; nShort = sqlite4VdbeShortKey(aKey, nKey, pIdx->pKeyInfo->nField - pIdx->pKeyInfo->nPK, 0 ); nVarint = sqlite4VarintLen(pPk->iRoot); rc = sqlite4_buffer_resize(&pPk->key, nVarint + nKey - nShort); if( rc!=SQLITE4_OK ) break; putVarint32((u8 *)(pPk->key.p), pPk->iRoot); memcpy(((u8*)pPk->key.p) + nVarint, &aKey[nShort], nKey-nShort); assert( pPk->key.n>0 ); #if 0 rc = sqlite4VdbeCursorMoveto(db, pPk); #endif } break; } /* Opcode: SeekGe P1 P2 P3 P4 * ** |
︙ | ︙ | |||
2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 | KVSize nProbe; /* Size of aProbe[] in bytes */ int dir; /* KV search dir (+ve or -ve) */ const KVByteArray *aKey; /* Pointer to final cursor key */ KVSize nKey; /* Size of aKey[] in bytes */ pC = p->apCsr[pOp->p1]; pC->nullRow = 0; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); assert( pOp->p2!=0 ); assert( pC!=0 ); assert( pC->pseudoTableReg==0 ); assert( OP_SeekLe == OP_SeekLt+1 ); assert( OP_SeekGe == OP_SeekLt+2 ); | > | 2997 2998 2999 3000 3001 3002 3003 3004 3005 3006 3007 3008 3009 3010 3011 | KVSize nProbe; /* Size of aProbe[] in bytes */ int dir; /* KV search dir (+ve or -ve) */ const KVByteArray *aKey; /* Pointer to final cursor key */ KVSize nKey; /* Size of aKey[] in bytes */ pC = p->apCsr[pOp->p1]; pC->nullRow = 0; pC->key.n = 0; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); assert( pOp->p2!=0 ); assert( pC!=0 ); assert( pC->pseudoTableReg==0 ); assert( OP_SeekLe == OP_SeekLt+1 ); assert( OP_SeekGe == OP_SeekLt+2 ); |
︙ | ︙ | |||
3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 | sqlite4_found_count++; #endif alreadyExists = 0; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); assert( pOp->p4type==P4_INT32 ); pC = p->apCsr[pOp->p1]; assert( pC!=0 ); pIn3 = &aMem[pOp->p3]; assert( pC->pKVCur!=0 ); assert( pC->isTable==0 || pOp->opcode==OP_NotExists ); if( pOp->p4.i>0 ){ rc = sqlite4VdbeEncodeKey( db, pIn3, pOp->p4.i, pOp->p4.i + (pOp->p5 & OPFLAG_PARTIALKEY), | > | 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 | sqlite4_found_count++; #endif alreadyExists = 0; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); assert( pOp->p4type==P4_INT32 ); pC = p->apCsr[pOp->p1]; pC->key.n = 0; assert( pC!=0 ); pIn3 = &aMem[pOp->p3]; assert( pC->pKVCur!=0 ); assert( pC->isTable==0 || pOp->opcode==OP_NotExists ); if( pOp->p4.i>0 ){ rc = sqlite4VdbeEncodeKey( db, pIn3, pOp->p4.i, pOp->p4.i + (pOp->p5 & OPFLAG_PARTIALKEY), |
︙ | ︙ | |||
3483 3484 3485 3486 3487 3488 3489 | ** P1 must not be pseudo-table. It has to be a real table. */ case OP_Delete: { VdbeCursor *pC; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; assert( pC!=0 ); | | | 3499 3500 3501 3502 3503 3504 3505 3506 3507 3508 3509 3510 3511 3512 3513 | ** P1 must not be pseudo-table. It has to be a real table. */ case OP_Delete: { VdbeCursor *pC; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; assert( pC!=0 ); assert( pC->key.n==0 ); rc = sqlite4KVCursorDelete(pC->pKVCur); if( pOp->p2 & OPFLAG_NCHANGE ) p->nChange++; break; } /* Opcode: ResetCount * * * * * ** |
︙ | ︙ | |||
3701 3702 3703 3704 3705 3706 3707 | KVSize nKey; int n; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; rc = sqlite4VdbeCursorMoveto(db, pC); if( rc!=SQLITE4_OK ) break; | | | 3717 3718 3719 3720 3721 3722 3723 3724 3725 3726 3727 3728 3729 3730 3731 | KVSize nKey; int n; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; rc = sqlite4VdbeCursorMoveto(db, pC); if( rc!=SQLITE4_OK ) break; assert( pC->key.n==0 ); assert( pC->pseudoTableReg==0 ); if( pC->nullRow ){ pOut->flags = MEM_Null; break; #ifndef SQLITE4_OMIT_VIRTUALTABLE }else if( pC->pVtabCursor ){ pVtab = pC->pVtabCursor->pVtab; |
︙ | ︙ | |||
3946 3947 3948 3949 3950 3951 3952 | }else if( rc==SQLITE4_NOTFOUND ){ rc = SQLITE4_OK; } break; } | | | | | > | | > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3962 3963 3964 3965 3966 3967 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 3979 3980 3981 3982 3983 3984 3985 3986 3987 3988 3989 3990 3991 3992 3993 3994 3995 3996 3997 3998 3999 4000 4001 4002 4003 4004 4005 4006 4007 4008 4009 4010 4011 4012 4013 4014 4015 4016 4017 4018 4019 4020 4021 4022 | }else if( rc==SQLITE4_NOTFOUND ){ rc = SQLITE4_OK; } break; } /* Opcode: IdxRowkey P1 P2 P3 * * ** ** Cursor P1 points to an index entry. Extract the encoded primary key ** fields from the entry. Then set output register P2 to a blob value ** containing the value P3 as a varint followed by the encoded PK ** fields. ** ** See also: Rowkey */ case OP_IdxRowkey: { /* out2-prerelease */ KVByteArray const *aKey; /* Key data from cursor pIdx */ KVSize nKey; /* Size of aKey[] in bytes */ int nShort; /* Size of aKey[] without PK fields */ KVByteArray *aPkKey; /* Pointer to PK buffer */ KVSize nPkKey; /* Size of aPkKey in bytes */ int iRoot; VdbeCursor *pC; iRoot = pOp->p3; pOut = &aMem[pOp->p2]; memAboutToChange(p, pOut); pC = p->apCsr[pOp->p1]; rc = sqlite4KVCursorKey(pC->pKVCur, &aKey, &nKey); if( rc!=SQLITE4_OK ) break; nShort = sqlite4VdbeShortKey(aKey, nKey, pC->pKeyInfo->nField - pC->pKeyInfo->nPK, 0 ); nPkKey = sqlite4VarintLen(iRoot) + nKey - nShort; if( nPkKey>db->aLimit[SQLITE4_LIMIT_LENGTH] ){ goto too_big; } rc = sqlite4VdbeMemGrow(pOut, nPkKey, 0); if( rc!=SQLITE4_OK ) break; aPkKey = pOut->z; putVarint32(aPkKey, iRoot); memcpy(&aPkKey[nPkKey - (nKey-nShort)], &aKey[nShort], nKey-nShort); pOut->type = SQLITE4_BLOB; pOut->n = nPkKey; MemSetTypeFlag(pOut, MEM_Blob); pOut->enc = SQLITE4_UTF8; /* In case the blob is ever cast to text */ UPDATE_MAX_BLOBSIZE(pOut); break; } /* Opcode: IdxGE P1 P2 P3 ** ** P1 is an open cursor. P3 contains a database key formatted by MakeKey. ** This opcode compares the current key that index P1 points to with |
︙ | ︙ |
Changes to src/vdbeInt.h.
︙ | ︙ | |||
65 66 67 68 69 70 71 | Bool isOrdered; /* True if the underlying table is BTREE_UNORDERED */ sqlite4_vtab_cursor *pVtabCursor; /* The cursor for a virtual table */ const sqlite4_module *pModule; /* Module for cursor pVtabCursor */ i64 seqCount; /* Sequence counter */ i64 movetoTarget; /* Argument to the deferred move-to */ VdbeSorter *pSorter; /* Sorter object for OP_SorterOpen cursors */ Fts5Cursor *pFts; /* Fts5 cursor object (or NULL) */ | | > | 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | Bool isOrdered; /* True if the underlying table is BTREE_UNORDERED */ sqlite4_vtab_cursor *pVtabCursor; /* The cursor for a virtual table */ const sqlite4_module *pModule; /* Module for cursor pVtabCursor */ i64 seqCount; /* Sequence counter */ i64 movetoTarget; /* Argument to the deferred move-to */ VdbeSorter *pSorter; /* Sorter object for OP_SorterOpen cursors */ Fts5Cursor *pFts; /* Fts5 cursor object (or NULL) */ sqlite4_buffer key; /* Result of last sqlite4-Moveto() done by an OP_NotExists or ** OP_IsUnique opcode on this cursor. */ int seekResult; }; /* |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 | sqlite4Fts5Close(pCx->pFts); if( pCx->pKVCur ){ sqlite4KVCursorClose(pCx->pKVCur); } if( pCx->pTmpKV ){ sqlite4KVStoreClose(pCx->pTmpKV); } #ifndef SQLITE4_OMIT_VIRTUALTABLE if( pCx->pVtabCursor ){ sqlite4_vtab_cursor *pVtabCursor = pCx->pVtabCursor; const sqlite4_module *pModule = pCx->pModule; p->inVtabMethod = 1; pModule->xClose(pVtabCursor); p->inVtabMethod = 0; | > | 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 | sqlite4Fts5Close(pCx->pFts); if( pCx->pKVCur ){ sqlite4KVCursorClose(pCx->pKVCur); } if( pCx->pTmpKV ){ sqlite4KVStoreClose(pCx->pTmpKV); } sqlite4_buffer_clear(&pCx->key); #ifndef SQLITE4_OMIT_VIRTUALTABLE if( pCx->pVtabCursor ){ sqlite4_vtab_cursor *pVtabCursor = pCx->pVtabCursor; const sqlite4_module *pModule = pCx->pModule; p->inVtabMethod = 1; pModule->xClose(pVtabCursor); p->inVtabMethod = 0; |
︙ | ︙ | |||
2282 2283 2284 2285 2286 2287 2288 | ** ** If the operation is a success, SQLITE4_OK is returned. Or, if the ** required entry is not found in the PK index, SQLITE4_CORRUPT. Or if ** some other error occurs, an error code is returned. */ int sqlite4VdbeCursorMoveto(sqlite4 *db, VdbeCursor *pPk){ int rc = SQLITE4_OK; /* Return code */ | < < < < | < < < < < < < < < < < < < < < < < | | | | | < < | | < < | 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 | ** ** If the operation is a success, SQLITE4_OK is returned. Or, if the ** required entry is not found in the PK index, SQLITE4_CORRUPT. Or if ** some other error occurs, an error code is returned. */ int sqlite4VdbeCursorMoveto(sqlite4 *db, VdbeCursor *pPk){ int rc = SQLITE4_OK; /* Return code */ if( pPk->key.n!=0 ){ assert( pPk->pKeyInfo->nPK==0 ); rc = sqlite4KVCursorSeek(pPk->pKVCur, pPk->key.p, pPk->key.n, 0); if( rc==SQLITE4_NOTFOUND ){ rc = SQLITE4_CORRUPT_BKPT; } pPk->nullRow = 0; pPk->key.n = 0; } return rc; } |
Changes to src/where.c.
︙ | ︙ | |||
3694 3695 3696 3697 3698 3699 3700 3701 3702 3703 3704 3705 3706 3707 | } /* Seek the PK cursor, if required */ disableTerm(pLevel, pRangeStart); disableTerm(pLevel, pRangeEnd); if( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY && pIdx->eIndexType!=SQLITE4_INDEX_TEMP ){ sqlite4VdbeAddOp3(v, OP_SeekPk, iCur, 0, iIdxCur); } /* If there are inequality constraints, check that the value ** of the table column that the inequality contrains is not NULL. ** If it is, jump to the next iteration of the loop. | > | 3694 3695 3696 3697 3698 3699 3700 3701 3702 3703 3704 3705 3706 3707 3708 | } /* Seek the PK cursor, if required */ disableTerm(pLevel, pRangeStart); disableTerm(pLevel, pRangeEnd); if( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY && pIdx->eIndexType!=SQLITE4_INDEX_TEMP && 0==(pLoop->wsFlags & WHERE_IDX_ONLY) ){ sqlite4VdbeAddOp3(v, OP_SeekPk, iCur, 0, iIdxCur); } /* If there are inequality constraints, check that the value ** of the table column that the inequality contrains is not NULL. ** If it is, jump to the next iteration of the loop. |
︙ | ︙ | |||
4544 4545 4546 4547 4548 4549 4550 | /* ** Return a bitmask where 1s indicate that the corresponding column of ** the table is used by an index. Only the first 63 columns are considered. */ static Bitmask columnsInIndex(Index *pIdx){ Bitmask m = 0; | > | | | | | | > | 4545 4546 4547 4548 4549 4550 4551 4552 4553 4554 4555 4556 4557 4558 4559 4560 4561 4562 4563 4564 4565 4566 | /* ** Return a bitmask where 1s indicate that the corresponding column of ** the table is used by an index. Only the first 63 columns are considered. */ static Bitmask columnsInIndex(Index *pIdx){ Bitmask m = 0; if( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY ){ int j; for(j=pIdx->nCover-1; j>=0; j--){ int x = pIdx->aiCover[j]; testcase( x==BMS-1 ); testcase( x==BMS-2 ); if( x<BMS-1 ) m |= MASKBIT(x); } } return m; } /* ** Add all WhereLoop objects for a single table of the join where the table |
︙ | ︙ | |||
4656 4657 4658 4659 4660 4661 4662 | pNew->wsFlags = 0; pNew->rRun = whereCostAdd(rSize,rLogSize) + 16; rc = whereLoopInsert(pBuilder, pNew); } /* Loop through the set of indices being considered. */ for(; rc==SQLITE4_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){ | | | | | | > > > > > > | | | | | < | > > > > > > > > > > > > > > > > | 4659 4660 4661 4662 4663 4664 4665 4666 4667 4668 4669 4670 4671 4672 4673 4674 4675 4676 4677 4678 4679 4680 4681 4682 4683 4684 4685 4686 4687 4688 4689 4690 4691 4692 4693 4694 4695 4696 4697 4698 4699 4700 4701 4702 4703 4704 4705 4706 4707 4708 4709 4710 4711 4712 4713 4714 4715 4716 4717 4718 4719 4720 | pNew->wsFlags = 0; pNew->rRun = whereCostAdd(rSize,rLogSize) + 16; rc = whereLoopInsert(pBuilder, pNew); } /* Loop through the set of indices being considered. */ for(; rc==SQLITE4_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){ int bCover = (pProbe!=pPk && 0==(pSrc->colUsed & ~columnsInIndex(pProbe))); if( pProbe->eIndexType==SQLITE4_INDEX_FTS5 ) continue; assert( pProbe->tnum>0 ); pNew->u.btree.nEq = 0; pNew->nLTerm = 0; pNew->rSetup = 0; pNew->prereq = mExtra; pNew->nOut = rSize; pNew->u.btree.pIndex = pProbe; pNew->wsFlags = bCover ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED; b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor); /* The ONEPASS_DESIRED flags never occurs together with ORDER BY */ assert( (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || b==0 ); pNew->iSortIdx = b ? iSortIdx : 0; if( pProbe==pPk || b || (bCover && pProbe->bUnordered==0 && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 #if 0 && sqlite3GlobalConfig.bUseCis && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan) #endif )){ if( pProbe==pPk ){ /* TUNING: Cost of full table scan is 3*(N + log2(N)). ** + The extra 3 factor is to encourage the use of indexed lookups ** over full scans. A smaller constant 2 is used for covering ** index scans so that a covering index scan will be favored over ** a table scan. */ pNew->rRun = whereCostAdd(rSize,rLogSize) + 16; }else if( bCover ){ /* TUNING: Cost of a covering index scan is 2*(N + log2(N)). ** + The extra 2 factor is to encourage the use of indexed lookups ** over index scans. A table scan uses a factor of 3 so that ** index scans are favored over table scans. ** + If this covering index might also help satisfy the ORDER BY ** clause, then the cost is fudged down slightly so that this ** index is favored above other indices that have no hope of ** helping with the ORDER BY. */ pNew->rRun = 10 + whereCostAdd(rSize,rLogSize) - b; }else{ assert( b!=0 ); /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N) ** which we will simplify to just N*log2(N) */ pNew->rRun = rSize + rLogSize; } rc = whereLoopInsert(pBuilder, pNew); if( rc ) break; } rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0); /* If there was an INDEXED BY or NOT INDEXED clause, then only one |
︙ | ︙ | |||
6169 6170 6171 6172 6173 6174 6175 | ** reference the index. */ if( pLoop->wsFlags & (WHERE_INDEXED|WHERE_IDX_ONLY) ){ pIdx = pLoop->u.btree.pIndex; }else if( pLoop->wsFlags & WHERE_MULTI_OR ){ pIdx = pLevel->u.pCovidx; } | > | > > > > > > | 6193 6194 6195 6196 6197 6198 6199 6200 6201 6202 6203 6204 6205 6206 6207 6208 6209 6210 6211 6212 6213 6214 6215 6216 6217 6218 6219 6220 6221 6222 6223 6224 6225 6226 6227 6228 6229 6230 6231 6232 6233 6234 6235 6236 | ** reference the index. */ if( pLoop->wsFlags & (WHERE_INDEXED|WHERE_IDX_ONLY) ){ pIdx = pLoop->u.btree.pIndex; }else if( pLoop->wsFlags & WHERE_MULTI_OR ){ pIdx = pLevel->u.pCovidx; } if( pIdx && pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY && !db->mallocFailed ){ int *aiCover = pIdx->aiCover; int nCover = pIdx->nCover; int k, j, last; VdbeOp *pOp; pOp = sqlite4VdbeGetOp(v, pWInfo->iTop); last = sqlite4VdbeCurrentAddr(v); for(k=pWInfo->iTop; k<last; k++, pOp++){ if( pOp->p1!=pLevel->iTabCur ) continue; if( pOp->opcode==OP_Column ){ for(j=0; j<nCover; j++){ if( pOp->p2==aiCover[j] ){ pOp->p2 = j; pOp->p1 = pLevel->iIdxCur; break; } } assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 || j<nCover ); }else if( pOp->opcode==OP_RowKey ){ Index *pPk = sqlite4FindPrimaryKey(pTab, 0); pOp->p3 = pPk->tnum; pOp->p1 = pLevel->iIdxCur; pOp->opcode = OP_IdxRowkey; } } } } /* Final cleanup */ pParse->nQueryLoop = pWInfo->savedNQueryLoop; whereInfoFree(db, pWInfo); return; } |
Changes to test/collate4.test.
︙ | ︙ | |||
85 86 87 88 89 90 91 | cksort {SELECT b FROM collate4t1 ORDER BY b} } {{} A B a b nosort} do_test collate4-1.1.5 { cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT} } {{} A B a b nosort} do_test collate4-1.1.6 { cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE} | | | 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | cksort {SELECT b FROM collate4t1 ORDER BY b} } {{} A B a b nosort} do_test collate4-1.1.5 { cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT} } {{} A B a b nosort} do_test collate4-1.1.6 { cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE} } {{} a A b B sort} do_test collate4-1.1.7 { execsql { CREATE TABLE collate4t2( a PRIMARY KEY COLLATE NOCASE, b UNIQUE COLLATE TEXT ); |
︙ | ︙ | |||
162 163 164 165 166 167 168 | INSERT INTO collate4t4 VALUES( 'A', 'A' ); CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT); CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE); } } {} do_test collate4-1.1.22 { cksort {SELECT a FROM collate4t4 ORDER BY a} | | | | 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 | INSERT INTO collate4t4 VALUES( 'A', 'A' ); CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT); CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE); } } {} do_test collate4-1.1.22 { cksort {SELECT a FROM collate4t4 ORDER BY a} } {{} a A b B sort} do_test collate4-1.1.23 { cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE} } {{} a A b B sort} do_test collate4-1.1.24 { cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT} } {{} A B a b nosort} do_test collate4-1.1.25 { cksort {SELECT b FROM collate4t4 ORDER BY b} } {{} A B a b sort} do_test collate4-1.1.26 { |
︙ | ︙ | |||
213 214 215 216 217 218 219 | cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text} } {{} A B a b sort} do_test collate4-1.2.4 { cksort {SELECT a FROM collate4t1 ORDER BY a, b} } {{} A a B b nosort} do_test collate4-1.2.5 { cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase} | | | 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 | cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text} } {{} A B a b sort} do_test collate4-1.2.4 { cksort {SELECT a FROM collate4t1 ORDER BY a, b} } {{} A a B b nosort} do_test collate4-1.2.5 { cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase} } {{} a A b B sort} do_test collate4-1.2.6 { cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text} } {{} A a B b nosort} do_test collate4-1.2.7 { execsql { CREATE TABLE collate4t2( |
︙ | ︙ | |||
262 263 264 265 266 267 268 | INSERT INTO collate4t3 VALUES( 'B', 'B' ); INSERT INTO collate4t3 VALUES( 'A', 'A' ); CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE); } } {} do_test collate4-1.2.15 { cksort {SELECT a FROM collate4t3 ORDER BY a} | | | | 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 | INSERT INTO collate4t3 VALUES( 'B', 'B' ); INSERT INTO collate4t3 VALUES( 'A', 'A' ); CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE); } } {} do_test collate4-1.2.15 { cksort {SELECT a FROM collate4t3 ORDER BY a} } {{} a A b B sort} do_test collate4-1.2.16 { cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase} } {{} a A b B sort} do_test collate4-1.2.17 { cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text} } {{} A B a b nosort} do_test collate4-1.2.18 { cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b} } {{} A B a b sort} do_test collate4-1.2.19 { |
︙ | ︙ | |||
605 606 607 608 609 610 611 | execsql { DROP INDEX collate4i1; CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC); } count { SELECT min(a) FROM collate4t1; } | | | | 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 | execsql { DROP INDEX collate4i1; CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC); } count { SELECT min(a) FROM collate4t1; } } {10 5} do_test collate4-4.6 { count { SELECT max(a) FROM collate4t1; } } {20 5} do_test collate4-4.7 { execsql { DROP TABLE collate4t1; } } {} # Also test the scalar min() and max() functions. |
︙ | ︙ |
Changes to test/where.test.
︙ | ︙ | |||
476 477 478 479 480 481 482 | # using an index rather than by sorting. # do_test where-6.1 { execsql { CREATE TABLE t3(a,b,c); CREATE INDEX t3a ON t3(a); CREATE INDEX t3bc ON t3(b,c); | | | 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 | # using an index rather than by sorting. # do_test where-6.1 { execsql { CREATE TABLE t3(a,b,c); CREATE INDEX t3a ON t3(a); CREATE INDEX t3bc ON t3(b,c); CREATE INDEX t3acb ON t3(a,c,b) COVERING(a,b,c); INSERT INTO t3 SELECT w, 101-w, y FROM t1; SELECT count(*), sum(a), sum(b), sum(c) FROM t3; } } {100 5050 5050 348550} do_test where-6.2 { cksort { SELECT * FROM t3 ORDER BY a LIMIT 3 |
︙ | ︙ |