SQLite

Check-in [62316ebaca]
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
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.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
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/expr.c.
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462





1463
1464
1465
1466
1467
1468
1469
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 - The cursor was opened on a database index.
**   IN_INDEX_EPH -   The cursor was opened on a specially created and
**                    populated epheremal table.
**   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

1585

1586
1587
1588
1589
1590
1591
1592
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;
          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
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           3
#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
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 = 1 - bRev;
      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++;