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 | SQL 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 |
Context
|
2013-03-12
| ||
| 22:13 | Fix a test case that had an ambiguous result. check-in: 38c6bddf 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: 62316eba user: drh tags: desc-orderby-fix-1 | |
| 18:49 | Revise the patch to make it easier to understand. check-in: 3ce7eb02 user: drh tags: desc-orderby-fix-1 | |
Changes
Changes to src/expr.c.
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
....
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
|
** 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 - The cursor was opened on a database 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 ................................................................................ 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)); eType = IN_INDEX_INDEX; sqlite3VdbeJumpHere(v, iAddr); if( prNotFound && !pTab->aCol[iCol].notNull ){ *prNotFound = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound); } } |
|
>
|
|
|
>
|
|
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
....
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
|
** 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 ................................................................................ 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 3266 3267 3268 3269 3270 3271 3272 3273 |
#else
#define sqlite3BeginBenignMalloc()
#define sqlite3EndBenignMalloc()
#endif
#define IN_INDEX_ROWID 1
#define IN_INDEX_EPH 2
#define IN_INDEX_INDEX 3
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);
|
| > |
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 3802 3803 3804 3805 3806 3807 3808 3809 3810 3811 3812 3813 |
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 = 1 - bRev;
}
assert( pX->op==TK_IN );
iReg = iTarget;
eType = sqlite3FindInIndex(pParse, pX, 0);
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++;
|
| > |
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++;
|