/ Check-in [62316eba]
Login

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: 62316ebaca933f7e5df2018e8360a2b74234f30a
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1452   1452   ** all members of the RHS set, skipping duplicates.
  1453   1453   **
  1454   1454   ** A cursor is opened on the b-tree object that the RHS of the IN operator
  1455   1455   ** and pX->iTable is set to the index of that cursor.
  1456   1456   **
  1457   1457   ** The returned value of this function indicates the b-tree type, as follows:
  1458   1458   **
  1459         -**   IN_INDEX_ROWID - The cursor was opened on a database table.
  1460         -**   IN_INDEX_INDEX - The cursor was opened on a database index.
  1461         -**   IN_INDEX_EPH -   The cursor was opened on a specially created and
  1462         -**                    populated epheremal table.
         1459  +**   IN_INDEX_ROWID      - The cursor was opened on a database table.
         1460  +**   IN_INDEX_INDEX_ASC  - The cursor was opened on an ascending index.
         1461  +**   IN_INDEX_INDEX_DESC - The cursor was opened on a descending index.
         1462  +**   IN_INDEX_EPH        - The cursor was opened on a specially created and
         1463  +**                         populated epheremal table.
  1463   1464   **
  1464   1465   ** An existing b-tree might be used if the RHS expression pX is a simple
  1465   1466   ** subquery such as:
  1466   1467   **
  1467   1468   **     SELECT <column> FROM <table>
  1468   1469   **
  1469   1470   ** If the RHS of the IN operator is a list or a more complex subquery, then
................................................................................
  1578   1579     
  1579   1580             pKey = (char *)sqlite3IndexKeyinfo(pParse, pIdx);
  1580   1581             iAddr = sqlite3CodeOnce(pParse);
  1581   1582     
  1582   1583             sqlite3VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb,
  1583   1584                                  pKey,P4_KEYINFO_HANDOFF);
  1584   1585             VdbeComment((v, "%s", pIdx->zName));
  1585         -          eType = IN_INDEX_INDEX;
         1586  +          assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 );
         1587  +          eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0];
  1586   1588   
  1587   1589             sqlite3VdbeJumpHere(v, iAddr);
  1588   1590             if( prNotFound && !pTab->aCol[iCol].notNull ){
  1589   1591               *prNotFound = ++pParse->nMem;
  1590   1592               sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound);
  1591   1593             }
  1592   1594           }

Changes to src/sqliteInt.h.

  3259   3259   #else
  3260   3260     #define sqlite3BeginBenignMalloc()
  3261   3261     #define sqlite3EndBenignMalloc()
  3262   3262   #endif
  3263   3263   
  3264   3264   #define IN_INDEX_ROWID           1
  3265   3265   #define IN_INDEX_EPH             2
  3266         -#define IN_INDEX_INDEX           3
         3266  +#define IN_INDEX_INDEX_ASC       3
         3267  +#define IN_INDEX_INDEX_DESC      4
  3267   3268   int sqlite3FindInIndex(Parse *, Expr *, int*);
  3268   3269   
  3269   3270   #ifdef SQLITE_ENABLE_ATOMIC_WRITE
  3270   3271     int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int);
  3271   3272     int sqlite3JournalSize(sqlite3_vfs *);
  3272   3273     int sqlite3JournalCreate(sqlite3_file *);
  3273   3274     int sqlite3JournalExists(sqlite3_file *p);

Changes to src/where.c.

  3795   3795       int iTab;
  3796   3796       struct InLoop *pIn;
  3797   3797       u8 bRev = (pLevel->plan.wsFlags & WHERE_REVERSE)!=0;
  3798   3798   
  3799   3799       if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 
  3800   3800         && pLevel->plan.u.pIdx->aSortOrder[iEq]
  3801   3801       ){
  3802         -      bRev = 1 - bRev;
         3802  +      bRev = !bRev;
  3803   3803       }
  3804   3804       assert( pX->op==TK_IN );
  3805   3805       iReg = iTarget;
  3806   3806       eType = sqlite3FindInIndex(pParse, pX, 0);
         3807  +    if( eType==IN_INDEX_INDEX_DESC ) bRev = !bRev;
  3807   3808       iTab = pX->iTable;
  3808   3809       sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
  3809   3810       assert( pLevel->plan.wsFlags & WHERE_IN_ABLE );
  3810   3811       if( pLevel->u.in.nIn==0 ){
  3811   3812         pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
  3812   3813       }
  3813   3814       pLevel->u.in.nIn++;