/ Check-in [d6cd3c78]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Merge latest trunk changes into this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: d6cd3c780c6bc718d37e0f0b884e3e9a423d57be
User & Date: dan 2013-03-13 06:34:51
Context
2013-03-13
11:42
Fix a compiler warning in sqlite3VdbePreUpdateHook(). Add sqlite3session.c to the amalgamation. Fix the Makefile.in to work with sessions. check-in: e54b0225 user: drh tags: sessions
06:34
Merge latest trunk changes into this branch. check-in: d6cd3c78 user: dan tags: sessions
00:13
Fix the ORDER BY with IN constraint logic so that it works with all combinations of DESC on the ORDER BY clause, on the RHS of the IN operator, and in the index used by ORDER BY and IN. Fix for ticket [4dd95f6943fbd18]. check-in: 839aa91f user: drh tags: trunk
2013-03-12
11:38
Merge recent changes to trunk into sessions branch. check-in: 62adb0e0 user: dan tags: sessions
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/sqlite.h.in.

   279    279   ** host languages that are garbage collected, and where the order in which
   280    280   ** destructors are called is arbitrary.
   281    281   **
   282    282   ** Applications should [sqlite3_finalize | finalize] all [prepared statements],
   283    283   ** [sqlite3_blob_close | close] all [BLOB handles], and 
   284    284   ** [sqlite3_backup_finish | finish] all [sqlite3_backup] objects associated
   285    285   ** with the [sqlite3] object prior to attempting to close the object.  ^If
   286         -** sqlite3_close() is called on a [database connection] that still has
          286  +** sqlite3_close_v2() is called on a [database connection] that still has
   287    287   ** outstanding [prepared statements], [BLOB handles], and/or
   288    288   ** [sqlite3_backup] objects then it returns SQLITE_OK but the deallocation
   289    289   ** of resources is deferred until all [prepared statements], [BLOB handles],
   290    290   ** and [sqlite3_backup] objects are also destroyed.
   291    291   **
   292    292   ** ^If an [sqlite3] object is destroyed while a transaction is open,
   293    293   ** the transaction is automatically rolled back.

Changes to src/sqliteInt.h.

  3271   3271   #else
  3272   3272     #define sqlite3BeginBenignMalloc()
  3273   3273     #define sqlite3EndBenignMalloc()
  3274   3274   #endif
  3275   3275   
  3276   3276   #define IN_INDEX_ROWID           1
  3277   3277   #define IN_INDEX_EPH             2
  3278         -#define IN_INDEX_INDEX           3
         3278  +#define IN_INDEX_INDEX_ASC       3
         3279  +#define IN_INDEX_INDEX_DESC      4
  3279   3280   int sqlite3FindInIndex(Parse *, Expr *, int*);
  3280   3281   
  3281   3282   #ifdef SQLITE_ENABLE_ATOMIC_WRITE
  3282   3283     int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int);
  3283   3284     int sqlite3JournalSize(sqlite3_vfs *);
  3284   3285     int sqlite3JournalCreate(sqlite3_file *);
  3285   3286     int sqlite3JournalExists(sqlite3_file *p);

Changes to src/where.c.

  3771   3771   ** For a constraint of the form X=expr, the expression is evaluated and its
  3772   3772   ** result is left on the stack.  For constraints of the form X IN (...)
  3773   3773   ** this routine sets up a loop that will iterate over all values of X.
  3774   3774   */
  3775   3775   static int codeEqualityTerm(
  3776   3776     Parse *pParse,      /* The parsing context */
  3777   3777     WhereTerm *pTerm,   /* The term of the WHERE clause to be coded */
  3778         -  WhereLevel *pLevel, /* When level of the FROM clause we are working on */
         3778  +  WhereLevel *pLevel, /* The level of the FROM clause we are working on */
         3779  +  int iEq,            /* Index of the equality term within this level */
  3779   3780     int iTarget         /* Attempt to leave results in this register */
  3780   3781   ){
  3781   3782     Expr *pX = pTerm->pExpr;
  3782   3783     Vdbe *v = pParse->pVdbe;
  3783   3784     int iReg;                  /* Register holding results */
  3784   3785   
  3785   3786     assert( iTarget>0 );
................................................................................
  3791   3792   #ifndef SQLITE_OMIT_SUBQUERY
  3792   3793     }else{
  3793   3794       int eType;
  3794   3795       int iTab;
  3795   3796       struct InLoop *pIn;
  3796   3797       u8 bRev = (pLevel->plan.wsFlags & WHERE_REVERSE)!=0;
  3797   3798   
         3799  +    if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 
         3800  +      && pLevel->plan.u.pIdx->aSortOrder[iEq]
         3801  +    ){
         3802  +      testcase( iEq==0 );
         3803  +      testcase( iEq==pLevel->plan.u.pIdx->nColumn-1 );
         3804  +      testcase( iEq>0 && iEq+1<pLevel->plan.u.pIdx->nColumn );
         3805  +      testcase( bRev );
         3806  +      bRev = !bRev;
         3807  +    }
  3798   3808       assert( pX->op==TK_IN );
  3799   3809       iReg = iTarget;
  3800   3810       eType = sqlite3FindInIndex(pParse, pX, 0);
         3811  +    if( eType==IN_INDEX_INDEX_DESC ){
         3812  +      testcase( bRev );
         3813  +      bRev = !bRev;
         3814  +    }
  3801   3815       iTab = pX->iTable;
  3802   3816       sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
  3803   3817       assert( pLevel->plan.wsFlags & WHERE_IN_ABLE );
  3804   3818       if( pLevel->u.in.nIn==0 ){
  3805   3819         pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
  3806   3820       }
  3807   3821       pLevel->u.in.nIn++;
................................................................................
  3908   3922       int k = pIdx->aiColumn[j];
  3909   3923       pTerm = findTerm(pWC, iCur, k, notReady, pLevel->plan.wsFlags, pIdx);
  3910   3924       if( pTerm==0 ) break;
  3911   3925       /* The following true for indices with redundant columns. 
  3912   3926       ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
  3913   3927       testcase( (pTerm->wtFlags & TERM_CODED)!=0 );
  3914   3928       testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
  3915         -    r1 = codeEqualityTerm(pParse, pTerm, pLevel, regBase+j);
         3929  +    r1 = codeEqualityTerm(pParse, pTerm, pLevel, j, regBase+j);
  3916   3930       if( r1!=regBase+j ){
  3917   3931         if( nReg==1 ){
  3918   3932           sqlite3ReleaseTempReg(pParse, regBase);
  3919   3933           regBase = r1;
  3920   3934         }else{
  3921   3935           sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
  3922   3936         }
................................................................................
  4185   4199       addrNotFound = pLevel->addrBrk;
  4186   4200       for(j=1; j<=nConstraint; j++){
  4187   4201         for(k=0; k<nConstraint; k++){
  4188   4202           if( aUsage[k].argvIndex==j ){
  4189   4203             int iTarget = iReg+j+1;
  4190   4204             pTerm = &pWC->a[aConstraint[k].iTermOffset];
  4191   4205             if( pTerm->eOperator & WO_IN ){
  4192         -            codeEqualityTerm(pParse, pTerm, pLevel, iTarget);
         4206  +            codeEqualityTerm(pParse, pTerm, pLevel, k, iTarget);
  4193   4207               addrNotFound = pLevel->addrNxt;
  4194   4208             }else{
  4195   4209               sqlite3ExprCode(pParse, pTerm->pExpr->pRight, iTarget);
  4196   4210             }
  4197   4211             break;
  4198   4212           }
  4199   4213         }
................................................................................
  4226   4240       */
  4227   4241       iReleaseReg = sqlite3GetTempReg(pParse);
  4228   4242       pTerm = findTerm(pWC, iCur, -1, notReady, WO_EQ|WO_IN, 0);
  4229   4243       assert( pTerm!=0 );
  4230   4244       assert( pTerm->pExpr!=0 );
  4231   4245       assert( omitTable==0 );
  4232   4246       testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
  4233         -    iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, iReleaseReg);
         4247  +    iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, iReleaseReg);
  4234   4248       addrNxt = pLevel->addrNxt;
  4235   4249       sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt);
  4236   4250       sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg);
  4237   4251       sqlite3ExprCacheAffinityChange(pParse, iRowidReg, 1);
  4238   4252       sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  4239   4253       VdbeComment((v, "pk"));
  4240   4254       pLevel->op = OP_Noop;

Changes to test/descidx3.test.

   128    128   } {9 7 6 8 3 4 2 5}
   129    129   
   130    130   ifcapable subquery {
   131    131     # If the subquery capability is not compiled in to the binary, then
   132    132     # the IN(...) operator is not available. Hence these tests cannot be 
   133    133     # run.
   134    134     do_test descidx3-4.1 {
   135         -    execsql {
          135  +    lsort [execsql {
   136    136         UPDATE t1 SET a=2 WHERE i<6;
   137    137         SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
   138         -    }
   139         -  } {8 6 2 4 3}
          138  +    }]
          139  +  } {2 3 4 6 8}
   140    140     do_test descidx3-4.2 {
   141    141       execsql {
   142    142         UPDATE t1 SET a=1;
   143    143         SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
   144    144       }
   145    145     } {2 4 3 8 6}
   146    146     do_test descidx3-4.3 {

Added test/tkt-4dd95f6943.test.

            1  +# 2013 March 13
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library. 
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set ::testprefix tkt-4dd95f6943
           17  +
           18  +do_execsql_test 1.0 {
           19  +  CREATE TABLE t1(x);
           20  +  INSERT INTO t1 VALUES (3), (4), (2), (1), (5), (6);
           21  +}
           22  +
           23  +foreach {tn1 idx} {
           24  +  1 { CREATE INDEX i1 ON t1(x ASC) }
           25  +  2 { CREATE INDEX i1 ON t1(x DESC) }
           26  +} {
           27  +  do_execsql_test 1.$tn1.1 { DROP INDEX IF EXISTS i1; }
           28  +  do_execsql_test 1.$tn1.2 $idx
           29  +
           30  +  do_execsql_test 1.$tn1.3 {
           31  +    SELECT x FROM t1 WHERE x IN(2, 4, 5) ORDER BY x ASC;
           32  +  } {2 4 5}
           33  +
           34  +  do_execsql_test 1.$tn1.4 {
           35  +    SELECT x FROM t1 WHERE x IN(2, 4, 5) ORDER BY x DESC;
           36  +  } {5 4 2}
           37  +}
           38  +
           39  +
           40  +do_execsql_test 2.0 {
           41  +  CREATE TABLE t2(x, y);
           42  +  INSERT INTO t2 VALUES (5, 3), (5, 4), (5, 2), (5, 1), (5, 5), (5, 6);
           43  +  INSERT INTO t2 VALUES (1, 3), (1, 4), (1, 2), (1, 1), (1, 5), (1, 6);
           44  +  INSERT INTO t2 VALUES (3, 3), (3, 4), (3, 2), (3, 1), (3, 5), (3, 6);
           45  +  INSERT INTO t2 VALUES (2, 3), (2, 4), (2, 2), (2, 1), (2, 5), (2, 6);
           46  +  INSERT INTO t2 VALUES (4, 3), (4, 4), (4, 2), (4, 1), (4, 5), (4, 6);
           47  +  INSERT INTO t2 VALUES (6, 3), (6, 4), (6, 2), (6, 1), (6, 5), (6, 6);
           48  +
           49  +  CREATE TABLE t3(a, b);
           50  +  INSERT INTO t3 VALUES (2, 2), (4, 4), (5, 5);
           51  +  CREATE INDEX t3i1 ON t3(a ASC);
           52  +  CREATE INDEX t3i2 ON t3(b DESC);
           53  +}
           54  +
           55  +foreach {tn1 idx} {
           56  +  1 { CREATE INDEX i1 ON t2(x ASC,  y ASC) }
           57  +  2 { CREATE INDEX i1 ON t2(x ASC,  y DESC) }
           58  +  3 { CREATE INDEX i1 ON t2(x DESC, y ASC) }
           59  +  4 { CREATE INDEX i1 ON t2(x DESC, y DESC) }
           60  +
           61  +  5 { CREATE INDEX i1 ON t2(y ASC,  x ASC) }
           62  +  6 { CREATE INDEX i1 ON t2(y ASC,  x DESC) }
           63  +  7 { CREATE INDEX i1 ON t2(y DESC, x ASC) }
           64  +  8 { CREATE INDEX i1 ON t2(y DESC, x DESC) }
           65  +} {
           66  +  do_execsql_test 2.$tn1.1 { DROP INDEX IF EXISTS i1; }
           67  +  do_execsql_test 2.$tn1.2 $idx
           68  +
           69  +  foreach {tn2 inexpr} {
           70  +    1  "(2, 4, 5)"
           71  +    2  "(SELECT a FROM t3)"
           72  +    3  "(SELECT b FROM t3)"
           73  +  } {
           74  +    do_execsql_test 2.$tn1.3 "
           75  +      SELECT x, y FROM t2 WHERE x = 1 AND y IN $inexpr ORDER BY x ASC, y ASC;
           76  +    " {1 2  1 4  1 5}
           77  +    do_execsql_test 2.$tn1.4 "
           78  +      SELECT x, y FROM t2 WHERE x = 2 AND y IN $inexpr ORDER BY x ASC, y DESC;
           79  +    " {2 5  2 4  2 2}
           80  +    do_execsql_test 2.$tn1.5 "
           81  +      SELECT x, y FROM t2 WHERE x = 3 AND y IN $inexpr ORDER BY x DESC, y ASC;
           82  +    " {3 2  3 4  3 5}
           83  +    do_execsql_test 2.$tn1.6 "
           84  +      SELECT x, y FROM t2 WHERE x = 4 AND y IN $inexpr ORDER BY x DESC, y DESC;
           85  +    " {4 5  4 4  4 2}
           86  +  }
           87  +}
           88  +
           89  +do_execsql_test 3.0 {
           90  +  CREATE TABLE t7(x);
           91  +  INSERT INTO t7 VALUES (1), (2), (3);
           92  +  CREATE INDEX i7 ON t7(x);
           93  +
           94  +  CREATE TABLE t8(y);
           95  +  INSERT INTO t8 VALUES (1), (2), (3);
           96  +
           97  +  CREATE UNIQUE INDEX i8 ON t8(y DESC);
           98  +  SELECT x FROM t7 WHERE x IN (SELECT y FROM t8) ORDER BY x ASC;
           99  +} {1 2 3}
          100  +
          101  +finish_test