Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix the ORDER BY optimization with IN constraints when the RHS of the IN constraint is a descending index. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | desc-orderby-fix-1 |
Files: | files | file ages | folders |
SHA1: |
62316ebaca933f7e5df2018e8360a2b7 |
User & Date: | drh 2013-03-12 20:38:51.980 |
Context
2013-03-12
| ||
22:13 | Fix a test case that had an ambiguous result. (check-in: 38c6bddf8c user: drh tags: desc-orderby-fix-1) | |
20:38 | Fix the ORDER BY optimization with IN constraints when the RHS of the IN constraint is a descending index. (check-in: 62316ebaca user: drh tags: desc-orderby-fix-1) | |
18:49 | Revise the patch to make it easier to understand. (check-in: 3ce7eb0265 user: drh tags: desc-orderby-fix-1) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
1452 1453 1454 1455 1456 1457 1458 | ** all members of the RHS set, skipping duplicates. ** ** A cursor is opened on the b-tree object that the RHS of the IN operator ** and pX->iTable is set to the index of that cursor. ** ** The returned value of this function indicates the b-tree type, as follows: ** | | > | | | | 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 | ** all members of the RHS set, skipping duplicates. ** ** A cursor is opened on the b-tree object that the RHS of the IN operator ** and pX->iTable is set to the index of that cursor. ** ** The returned value of this function indicates the b-tree type, as follows: ** ** IN_INDEX_ROWID - The cursor was opened on a database table. ** IN_INDEX_INDEX_ASC - The cursor was opened on an ascending index. ** IN_INDEX_INDEX_DESC - The cursor was opened on a descending index. ** IN_INDEX_EPH - The cursor was opened on a specially created and ** populated epheremal table. ** ** An existing b-tree might be used if the RHS expression pX is a simple ** subquery such as: ** ** SELECT <column> FROM <table> ** ** If the RHS of the IN operator is a list or a more complex subquery, then |
︙ | ︙ | |||
1578 1579 1580 1581 1582 1583 1584 | pKey = (char *)sqlite3IndexKeyinfo(pParse, pIdx); iAddr = sqlite3CodeOnce(pParse); sqlite3VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb, pKey,P4_KEYINFO_HANDOFF); VdbeComment((v, "%s", pIdx->zName)); | > | | 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 | pKey = (char *)sqlite3IndexKeyinfo(pParse, pIdx); iAddr = sqlite3CodeOnce(pParse); sqlite3VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb, pKey,P4_KEYINFO_HANDOFF); VdbeComment((v, "%s", pIdx->zName)); assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 ); eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0]; sqlite3VdbeJumpHere(v, iAddr); if( prNotFound && !pTab->aCol[iCol].notNull ){ *prNotFound = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound); } } |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
3259 3260 3261 3262 3263 3264 3265 | #else #define sqlite3BeginBenignMalloc() #define sqlite3EndBenignMalloc() #endif #define IN_INDEX_ROWID 1 #define IN_INDEX_EPH 2 | | > | 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 | #else #define sqlite3BeginBenignMalloc() #define sqlite3EndBenignMalloc() #endif #define IN_INDEX_ROWID 1 #define IN_INDEX_EPH 2 #define IN_INDEX_INDEX_ASC 3 #define IN_INDEX_INDEX_DESC 4 int sqlite3FindInIndex(Parse *, Expr *, int*); #ifdef SQLITE_ENABLE_ATOMIC_WRITE int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int); int sqlite3JournalSize(sqlite3_vfs *); int sqlite3JournalCreate(sqlite3_file *); int sqlite3JournalExists(sqlite3_file *p); |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
3795 3796 3797 3798 3799 3800 3801 | int iTab; struct InLoop *pIn; u8 bRev = (pLevel->plan.wsFlags & WHERE_REVERSE)!=0; if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 && pLevel->plan.u.pIdx->aSortOrder[iEq] ){ | | > | 3795 3796 3797 3798 3799 3800 3801 3802 3803 3804 3805 3806 3807 3808 3809 3810 3811 3812 3813 3814 | int iTab; struct InLoop *pIn; u8 bRev = (pLevel->plan.wsFlags & WHERE_REVERSE)!=0; if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 && pLevel->plan.u.pIdx->aSortOrder[iEq] ){ bRev = !bRev; } assert( pX->op==TK_IN ); iReg = iTarget; eType = sqlite3FindInIndex(pParse, pX, 0); if( eType==IN_INDEX_INDEX_DESC ) bRev = !bRev; iTab = pX->iTable; sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0); assert( pLevel->plan.wsFlags & WHERE_IN_ABLE ); if( pLevel->u.in.nIn==0 ){ pLevel->addrNxt = sqlite3VdbeMakeLabel(v); } pLevel->u.in.nIn++; |
︙ | ︙ |