/ Check-in [51908c8f]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Merge all the latest trunk changes into the experimental STAT3 branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | stat3-trunk
Files: files | file ages | folders
SHA1: 51908c8f2bc6c086570f7493a29b096f0a40ce34
User & Date: drh 2011-09-16 19:29:58
Context
2011-09-19
20:36
Merge in all changes through the 3.7.8 release. check-in: 9607600b user: drh tags: stat3-trunk
2011-09-16
19:29
Merge all the latest trunk changes into the experimental STAT3 branch. check-in: 51908c8f user: drh tags: stat3-trunk
19:04
Remove unreachable branches from the previous change. Add additional test cases. check-in: cf51ef8a user: drh tags: trunk
2011-09-13
19:09
Merge the latest trunk changes into the stat3-trunk branch. check-in: 11ca4ed8 user: drh tags: stat3-trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3.c.

  2062   2062     char *p2 = a2;
  2063   2063     char *p;
  2064   2064     char *aOut;
  2065   2065     int bFirstOut = 0;
  2066   2066   
  2067   2067     *paOut = 0;
  2068   2068     *pnOut = 0;
  2069         -  aOut = sqlite3_malloc(n1+n2);
         2069  +
         2070  +  /* Allocate space for the output. Both the input and output doclists
         2071  +  ** are delta encoded. If they are in ascending order (bDescDoclist==0),
         2072  +  ** then the first docid in each list is simply encoded as a varint. For
         2073  +  ** each subsequent docid, the varint stored is the difference between the
         2074  +  ** current and previous docid (a positive number - since the list is in
         2075  +  ** ascending order).
         2076  +  **
         2077  +  ** The first docid written to the output is therefore encoded using the 
         2078  +  ** same number of bytes as it is in whichever of the input lists it is
         2079  +  ** read from. And each subsequent docid read from the same input list 
         2080  +  ** consumes either the same or less bytes as it did in the input (since
         2081  +  ** the difference between it and the previous value in the output must
         2082  +  ** be a positive value less than or equal to the delta value read from 
         2083  +  ** the input list). The same argument applies to all but the first docid
         2084  +  ** read from the 'other' list. And to the contents of all position lists
         2085  +  ** that will be copied and merged from the input to the output.
         2086  +  **
         2087  +  ** However, if the first docid copied to the output is a negative number,
         2088  +  ** then the encoding of the first docid from the 'other' input list may
         2089  +  ** be larger in the output than it was in the input (since the delta value
         2090  +  ** may be a larger positive integer than the actual docid).
         2091  +  **
         2092  +  ** The space required to store the output is therefore the sum of the
         2093  +  ** sizes of the two inputs, plus enough space for exactly one of the input
         2094  +  ** docids to grow. 
         2095  +  **
         2096  +  ** A symetric argument may be made if the doclists are in descending 
         2097  +  ** order.
         2098  +  */
         2099  +  aOut = sqlite3_malloc(n1+n2+FTS3_VARINT_MAX-1);
  2070   2100     if( !aOut ) return SQLITE_NOMEM;
  2071   2101   
  2072   2102     p = aOut;
  2073   2103     fts3GetDeltaVarint3(&p1, pEnd1, 0, &i1);
  2074   2104     fts3GetDeltaVarint3(&p2, pEnd2, 0, &i2);
  2075   2105     while( p1 || p2 ){
  2076   2106       sqlite3_int64 iDiff = DOCID_CMP(i1, i2);
................................................................................
  2089   2119         fts3PoslistCopy(&p, &p2);
  2090   2120         fts3GetDeltaVarint3(&p2, pEnd2, bDescDoclist, &i2);
  2091   2121       }
  2092   2122     }
  2093   2123   
  2094   2124     *paOut = aOut;
  2095   2125     *pnOut = (p-aOut);
         2126  +  assert( *pnOut<=n1+n2+FTS3_VARINT_MAX-1 );
  2096   2127     return SQLITE_OK;
  2097   2128   }
  2098   2129   
  2099   2130   /*
  2100   2131   ** This function does a "phrase" merge of two doclists. In a phrase merge,
  2101   2132   ** the output contains a copy of each position from the right-hand input
  2102   2133   ** doclist for which there is a position in the left-hand input doclist

Changes to src/btree.c.

   660    660   
   661    661     if( pKey ){
   662    662       assert( nKey==(i64)(int)nKey );
   663    663       pIdxKey = sqlite3VdbeAllocUnpackedRecord(
   664    664           pCur->pKeyInfo, aSpace, sizeof(aSpace), &pFree
   665    665       );
   666    666       if( pIdxKey==0 ) return SQLITE_NOMEM;
   667         -    sqlite3VdbeRecordUnpack(pCur->pKeyInfo, nKey, pKey, pIdxKey);
          667  +    sqlite3VdbeRecordUnpack(pCur->pKeyInfo, (int)nKey, pKey, pIdxKey);
   668    668     }else{
   669    669       pIdxKey = 0;
   670    670     }
   671    671     rc = sqlite3BtreeMovetoUnpacked(pCur, pIdxKey, nKey, bias, pRes);
   672    672     if( pFree ){
   673    673       sqlite3DbFree(pCur->pKeyInfo->db, pFree);
   674    674     }

Changes to src/expr.c.

   897    897       struct SrcList_item *pOldItem = &p->a[i];
   898    898       Table *pTab;
   899    899       pNewItem->zDatabase = sqlite3DbStrDup(db, pOldItem->zDatabase);
   900    900       pNewItem->zName = sqlite3DbStrDup(db, pOldItem->zName);
   901    901       pNewItem->zAlias = sqlite3DbStrDup(db, pOldItem->zAlias);
   902    902       pNewItem->jointype = pOldItem->jointype;
   903    903       pNewItem->iCursor = pOldItem->iCursor;
   904         -    pNewItem->isPopulated = pOldItem->isPopulated;
          904  +    pNewItem->addrFillSub = pOldItem->addrFillSub;
          905  +    pNewItem->regReturn = pOldItem->regReturn;
   905    906       pNewItem->isCorrelated = pOldItem->isCorrelated;
   906    907       pNewItem->zIndex = sqlite3DbStrDup(db, pOldItem->zIndex);
   907    908       pNewItem->notIndexed = pOldItem->notIndexed;
   908    909       pNewItem->pIndex = pOldItem->pIndex;
   909    910       pTab = pNewItem->pTab = pOldItem->pTab;
   910    911       if( pTab ){
   911    912         pTab->nRef++;
................................................................................
  1457   1458       ** successful here.
  1458   1459       */
  1459   1460       assert(v);
  1460   1461       if( iCol<0 ){
  1461   1462         int iMem = ++pParse->nMem;
  1462   1463         int iAddr;
  1463   1464   
  1464         -      iAddr = sqlite3VdbeAddOp1(v, OP_If, iMem);
  1465         -      sqlite3VdbeAddOp2(v, OP_Integer, 1, iMem);
         1465  +      iAddr = sqlite3VdbeAddOp1(v, OP_Once, iMem);
  1466   1466   
  1467   1467         sqlite3OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
  1468   1468         eType = IN_INDEX_ROWID;
  1469   1469   
  1470   1470         sqlite3VdbeJumpHere(v, iAddr);
  1471   1471       }else{
  1472   1472         Index *pIdx;                         /* Iterator variable */
................................................................................
  1489   1489            && (!mustBeUnique || (pIdx->nColumn==1 && pIdx->onError!=OE_None))
  1490   1490           ){
  1491   1491             int iMem = ++pParse->nMem;
  1492   1492             int iAddr;
  1493   1493             char *pKey;
  1494   1494     
  1495   1495             pKey = (char *)sqlite3IndexKeyinfo(pParse, pIdx);
  1496         -          iAddr = sqlite3VdbeAddOp1(v, OP_If, iMem);
  1497         -          sqlite3VdbeAddOp2(v, OP_Integer, 1, iMem);
         1496  +          iAddr = sqlite3VdbeAddOp1(v, OP_Once, iMem);
  1498   1497     
  1499   1498             sqlite3VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb,
  1500   1499                                  pKey,P4_KEYINFO_HANDOFF);
  1501   1500             VdbeComment((v, "%s", pIdx->zName));
  1502   1501             eType = IN_INDEX_INDEX;
  1503   1502   
  1504   1503             sqlite3VdbeJumpHere(v, iAddr);
................................................................................
  1575   1574     int rMayHaveNull,       /* Register that records whether NULLs exist in RHS */
  1576   1575     int isRowid             /* If true, LHS of IN operator is a rowid */
  1577   1576   ){
  1578   1577     int testAddr = 0;                       /* One-time test address */
  1579   1578     int rReg = 0;                           /* Register storing resulting */
  1580   1579     Vdbe *v = sqlite3GetVdbe(pParse);
  1581   1580     if( NEVER(v==0) ) return 0;
         1581  +  assert( sqlite3VdbeCurrentAddr(v)>0 );
  1582   1582     sqlite3ExprCachePush(pParse);
  1583   1583   
  1584   1584     /* This code must be run in its entirety every time it is encountered
  1585   1585     ** if any of the following is true:
  1586   1586     **
  1587   1587     **    *  The right-hand side is a correlated subquery
  1588   1588     **    *  The right-hand side is an expression list containing variables
................................................................................
  1589   1589     **    *  We are inside a trigger
  1590   1590     **
  1591   1591     ** If all of the above are false, then we can run this code just once
  1592   1592     ** save the results, and reuse the same result on subsequent invocations.
  1593   1593     */
  1594   1594     if( !ExprHasAnyProperty(pExpr, EP_VarSelect) && !pParse->pTriggerTab ){
  1595   1595       int mem = ++pParse->nMem;
  1596         -    sqlite3VdbeAddOp1(v, OP_If, mem);
  1597         -    testAddr = sqlite3VdbeAddOp2(v, OP_Integer, 1, mem);
         1596  +    testAddr = sqlite3VdbeAddOp1(v, OP_Once, mem);
  1598   1597       assert( testAddr>0 || pParse->db->mallocFailed );
  1599   1598     }
  1600   1599   
  1601   1600   #ifndef SQLITE_OMIT_EXPLAIN
  1602   1601     if( pParse->explain==2 ){
  1603   1602       char *zMsg = sqlite3MPrintf(
  1604   1603           pParse->db, "EXECUTE %s%s SUBQUERY %d", testAddr?"":"CORRELATED ",
................................................................................
  1690   1689   
  1691   1690             /* If the expression is not constant then we will need to
  1692   1691             ** disable the test that was generated above that makes sure
  1693   1692             ** this code only executes once.  Because for a non-constant
  1694   1693             ** expression we need to rerun this code each time.
  1695   1694             */
  1696   1695             if( testAddr && !sqlite3ExprIsConstant(pE2) ){
  1697         -            sqlite3VdbeChangeToNoop(v, testAddr-1, 2);
         1696  +            sqlite3VdbeChangeToNoop(v, testAddr);
  1698   1697               testAddr = 0;
  1699   1698             }
  1700   1699   
  1701   1700             /* Evaluate the expression and insert it into the temp table */
  1702   1701             if( isRowid && sqlite3ExprIsInteger(pE2, &iValToIns) ){
  1703   1702               sqlite3VdbeAddOp3(v, OP_InsertInt, pExpr->iTable, r2, iValToIns);
  1704   1703             }else{
................................................................................
  1761   1760         rReg = dest.iParm;
  1762   1761         ExprSetIrreducible(pExpr);
  1763   1762         break;
  1764   1763       }
  1765   1764     }
  1766   1765   
  1767   1766     if( testAddr ){
  1768         -    sqlite3VdbeJumpHere(v, testAddr-1);
         1767  +    sqlite3VdbeJumpHere(v, testAddr);
  1769   1768     }
  1770   1769     sqlite3ExprCachePop(pParse, 1);
  1771   1770   
  1772   1771     return rReg;
  1773   1772   }
  1774   1773   #endif /* SQLITE_OMIT_SUBQUERY */
  1775   1774   

Changes to src/select.c.

    85     85     pNew->addrOpenEphm[0] = -1;
    86     86     pNew->addrOpenEphm[1] = -1;
    87     87     pNew->addrOpenEphm[2] = -1;
    88     88     if( db->mallocFailed ) {
    89     89       clearSelect(db, pNew);
    90     90       if( pNew!=&standin ) sqlite3DbFree(db, pNew);
    91     91       pNew = 0;
           92  +  }else{
           93  +    assert( pNew->pSrc!=0 || pParse->nErr>0 );
    92     94     }
    93     95     return pNew;
    94     96   }
    95     97   
    96     98   /*
    97     99   ** Delete the given Select structure and all of its substructures.
    98    100   */
................................................................................
  3797   3799   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  3798   3800     for(i=0; !p->pPrior && i<pTabList->nSrc; i++){
  3799   3801       struct SrcList_item *pItem = &pTabList->a[i];
  3800   3802       SelectDest dest;
  3801   3803       Select *pSub = pItem->pSelect;
  3802   3804       int isAggSub;
  3803   3805   
  3804         -    if( pSub==0 || pItem->isPopulated ) continue;
         3806  +    if( pSub==0 ) continue;
         3807  +    if( pItem->addrFillSub ){
         3808  +      sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
         3809  +      continue;
         3810  +    }
  3805   3811   
  3806   3812       /* Increment Parse.nHeight by the height of the largest expression
  3807   3813       ** tree refered to by this, the parent select. The child select
  3808   3814       ** may contain expression trees of at most
  3809   3815       ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
  3810   3816       ** more conservative than necessary, but much easier than enforcing
  3811   3817       ** an exact limit.
  3812   3818       */
  3813   3819       pParse->nHeight += sqlite3SelectExprHeight(p);
  3814   3820   
  3815         -    /* Check to see if the subquery can be absorbed into the parent. */
  3816   3821       isAggSub = (pSub->selFlags & SF_Aggregate)!=0;
  3817   3822       if( flattenSubquery(pParse, p, i, isAgg, isAggSub) ){
         3823  +      /* This subquery can be absorbed into its parent. */
  3818   3824         if( isAggSub ){
  3819   3825           isAgg = 1;
  3820   3826           p->selFlags |= SF_Aggregate;
  3821   3827         }
  3822   3828         i = -1;
  3823   3829       }else{
         3830  +      /* Generate a subroutine that will fill an ephemeral table with
         3831  +      ** the content of this subquery.  pItem->addrFillSub will point
         3832  +      ** to the address of the generated subroutine.  pItem->regReturn
         3833  +      ** is a register allocated to hold the subroutine return address
         3834  +      */
         3835  +      int topAddr;
         3836  +      int onceAddr = 0;
         3837  +      int retAddr;
         3838  +      assert( pItem->addrFillSub==0 );
         3839  +      pItem->regReturn = ++pParse->nMem;
         3840  +      topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
         3841  +      pItem->addrFillSub = topAddr+1;
         3842  +      VdbeNoopComment((v, "materialize %s", pItem->pTab->zName));
         3843  +      if( pItem->isCorrelated==0 && pParse->pTriggerTab==0 ){
         3844  +        /* If the subquery is no correlated and if we are not inside of
         3845  +        ** a trigger, then we only need to compute the value of the subquery
         3846  +        ** once. */
         3847  +        int regOnce = ++pParse->nMem;
         3848  +        onceAddr = sqlite3VdbeAddOp1(v, OP_Once, regOnce);
         3849  +      }
  3824   3850         sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
  3825         -      assert( pItem->isPopulated==0 );
  3826   3851         explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
  3827   3852         sqlite3Select(pParse, pSub, &dest);
  3828         -      pItem->isPopulated = 1;
  3829   3853         pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow;
         3854  +      if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
         3855  +      retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
         3856  +      VdbeComment((v, "end %s", pItem->pTab->zName));
         3857  +      sqlite3VdbeChangeP1(v, topAddr, retAddr);
         3858  +
  3830   3859       }
  3831   3860       if( /*pParse->nErr ||*/ db->mallocFailed ){
  3832   3861         goto select_end;
  3833   3862       }
  3834   3863       pParse->nHeight -= sqlite3SelectExprHeight(p);
  3835   3864       pTabList = p->pSrc;
  3836   3865       if( !IgnorableOrderby(pDest) ){
................................................................................
  3963   3992       if( pWInfo->nRowOut < p->nSelectRow ) p->nSelectRow = pWInfo->nRowOut;
  3964   3993   
  3965   3994       /* If sorting index that was created by a prior OP_OpenEphemeral 
  3966   3995       ** instruction ended up not being needed, then change the OP_OpenEphemeral
  3967   3996       ** into an OP_Noop.
  3968   3997       */
  3969   3998       if( addrSortIndex>=0 && pOrderBy==0 ){
  3970         -      sqlite3VdbeChangeToNoop(v, addrSortIndex, 1);
         3999  +      sqlite3VdbeChangeToNoop(v, addrSortIndex);
  3971   4000         p->addrOpenEphm[2] = -1;
  3972   4001       }
  3973   4002   
  3974   4003       if( pWInfo->eDistinct ){
  3975   4004         VdbeOp *pOp;                /* No longer required OpenEphemeral instr. */
  3976   4005        
  3977   4006         assert( addrDistinctIndex>=0 );
................................................................................
  4246   4275   
  4247   4276         /* End of the loop
  4248   4277         */
  4249   4278         if( groupBySort ){
  4250   4279           sqlite3VdbeAddOp2(v, OP_SorterNext, sAggInfo.sortingIdx, addrTopOfLoop);
  4251   4280         }else{
  4252   4281           sqlite3WhereEnd(pWInfo);
  4253         -        sqlite3VdbeChangeToNoop(v, addrSortingIdx, 1);
         4282  +        sqlite3VdbeChangeToNoop(v, addrSortingIdx);
  4254   4283         }
  4255   4284   
  4256   4285         /* Output the final row of result
  4257   4286         */
  4258   4287         sqlite3VdbeAddOp2(v, OP_Gosub, regOutputRow, addrOutputRow);
  4259   4288         VdbeComment((v, "output final row"));
  4260   4289   

Changes to src/sqliteInt.h.

  1868   1868     i16 nAlloc;      /* Number of entries allocated in a[] below */
  1869   1869     struct SrcList_item {
  1870   1870       char *zDatabase;  /* Name of database holding this table */
  1871   1871       char *zName;      /* Name of the table */
  1872   1872       char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
  1873   1873       Table *pTab;      /* An SQL table corresponding to zName */
  1874   1874       Select *pSelect;  /* A SELECT statement used in place of a table name */
  1875         -    u8 isPopulated;   /* Temporary table associated with SELECT is populated */
         1875  +    int addrFillSub;  /* Address of subroutine to manifest a subquery */
         1876  +    int regReturn;    /* Register holding return address of addrFillSub */
  1876   1877       u8 jointype;      /* Type of join between this able and the previous */
  1877   1878       u8 notIndexed;    /* True if there is a NOT INDEXED clause */
  1878   1879       u8 isCorrelated;  /* True if sub-query is correlated */
  1879   1880   #ifndef SQLITE_OMIT_EXPLAIN
  1880   1881       u8 iSelectId;     /* If pSelect!=0, the id of the sub-select in EQP */
  1881   1882   #endif
  1882   1883       int iCursor;      /* The VDBE cursor number used to access this table */

Changes to src/vdbe.c.

  2017   2017       sqlite3VdbeMemSetNull(pOut);
  2018   2018     }else{
  2019   2019       sqlite3VdbeMemSetInt64(pOut, ~sqlite3VdbeIntValue(pIn1));
  2020   2020     }
  2021   2021     break;
  2022   2022   }
  2023   2023   
         2024  +/* Opcode: Once P1 P2 * * *
         2025  +**
         2026  +** Jump to P2 if the value in register P1 is a not null or zero.  If
         2027  +** the value is NULL or zero, fall through and change the P1 register
         2028  +** to an integer 1.
         2029  +**
         2030  +** When P1 is not used otherwise in a program, this opcode falls through
         2031  +** once and jumps on all subsequent invocations.  It is the equivalent
         2032  +** of "OP_If P1 P2", followed by "OP_Integer 1 P1".
         2033  +*/
  2024   2034   /* Opcode: If P1 P2 P3 * *
  2025   2035   **
  2026   2036   ** Jump to P2 if the value in register P1 is true.  The value
  2027   2037   ** is considered true if it is numeric and non-zero.  If the value
  2028   2038   ** in P1 is NULL then take the jump if P3 is true.
  2029   2039   */
  2030   2040   /* Opcode: IfNot P1 P2 P3 * *
  2031   2041   **
  2032   2042   ** Jump to P2 if the value in register P1 is False.  The value
  2033   2043   ** is considered true if it has a numeric value of zero.  If the value
  2034   2044   ** in P1 is NULL then take the jump if P3 is true.
  2035   2045   */
         2046  +case OP_Once:               /* jump, in1 */
  2036   2047   case OP_If:                 /* jump, in1 */
  2037   2048   case OP_IfNot: {            /* jump, in1 */
  2038   2049     int c;
  2039   2050     pIn1 = &aMem[pOp->p1];
  2040   2051     if( pIn1->flags & MEM_Null ){
  2041   2052       c = pOp->p3;
  2042   2053     }else{
................................................................................
  2045   2056   #else
  2046   2057       c = sqlite3VdbeRealValue(pIn1)!=0.0;
  2047   2058   #endif
  2048   2059       if( pOp->opcode==OP_IfNot ) c = !c;
  2049   2060     }
  2050   2061     if( c ){
  2051   2062       pc = pOp->p2-1;
         2063  +  }else if( pOp->opcode==OP_Once ){
         2064  +    assert( (pIn1->flags & (MEM_Agg|MEM_Dyn|MEM_RowSet|MEM_Frame))==0 );
         2065  +    memAboutToChange(p, pIn1);
         2066  +    pIn1->flags = MEM_Int;
         2067  +    pIn1->u.i = 1;
         2068  +    REGISTER_TRACE(pOp->p1, pIn1);
  2052   2069     }
  2053   2070     break;
  2054   2071   }
  2055   2072   
  2056   2073   /* Opcode: IsNull P1 P2 * * *
  2057   2074   **
  2058   2075   ** Jump to P2 if the value in register P1 is NULL.

Changes to src/vdbe.h.

   176    176   int sqlite3VdbeAddOpList(Vdbe*, int nOp, VdbeOpList const *aOp);
   177    177   void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*);
   178    178   void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1);
   179    179   void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2);
   180    180   void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3);
   181    181   void sqlite3VdbeChangeP5(Vdbe*, u8 P5);
   182    182   void sqlite3VdbeJumpHere(Vdbe*, int addr);
   183         -void sqlite3VdbeChangeToNoop(Vdbe*, int addr, int N);
          183  +void sqlite3VdbeChangeToNoop(Vdbe*, int addr);
   184    184   void sqlite3VdbeChangeP4(Vdbe*, int addr, const char *zP4, int N);
   185    185   void sqlite3VdbeUsesBtree(Vdbe*, int);
   186    186   VdbeOp *sqlite3VdbeGetOp(Vdbe*, int);
   187    187   int sqlite3VdbeMakeLabel(Vdbe*);
   188    188   void sqlite3VdbeRunOnlyOnce(Vdbe*);
   189    189   void sqlite3VdbeDelete(Vdbe*);
   190    190   void sqlite3VdbeDeleteObject(sqlite3*,Vdbe*);

Changes to src/vdbeaux.c.

   666    666   */
   667    667   void sqlite3VdbeLinkSubProgram(Vdbe *pVdbe, SubProgram *p){
   668    668     p->pNext = pVdbe->pProgram;
   669    669     pVdbe->pProgram = p;
   670    670   }
   671    671   
   672    672   /*
   673         -** Change N opcodes starting at addr to No-ops.
          673  +** Change the opcode at addr into OP_Noop
   674    674   */
   675         -void sqlite3VdbeChangeToNoop(Vdbe *p, int addr, int N){
          675  +void sqlite3VdbeChangeToNoop(Vdbe *p, int addr){
   676    676     if( p->aOp ){
   677    677       VdbeOp *pOp = &p->aOp[addr];
   678    678       sqlite3 *db = p->db;
   679         -    while( N-- ){
   680         -      freeP4(db, pOp->p4type, pOp->p4.p);
   681         -      memset(pOp, 0, sizeof(pOp[0]));
   682         -      pOp->opcode = OP_Noop;
   683         -      pOp++;
   684         -    }
          679  +    freeP4(db, pOp->p4type, pOp->p4.p);
          680  +    memset(pOp, 0, sizeof(pOp[0]));
          681  +    pOp->opcode = OP_Noop;
   685    682     }
   686    683   }
   687    684   
   688    685   /*
   689    686   ** Change the value of the P4 operand for a specific instruction.
   690    687   ** This routine is useful when a large program is loaded from a
   691    688   ** static array using sqlite3VdbeAddOpList but we want to make a
................................................................................
   833    830   ** having to double-check to make sure that the result is non-negative. But
   834    831   ** if SQLITE_OMIT_TRACE is defined, the OP_Trace is omitted and we do need to
   835    832   ** check the value of p->nOp-1 before continuing.
   836    833   */
   837    834   VdbeOp *sqlite3VdbeGetOp(Vdbe *p, int addr){
   838    835     /* C89 specifies that the constant "dummy" will be initialized to all
   839    836     ** zeros, which is correct.  MSVC generates a warning, nevertheless. */
   840         -  static const VdbeOp dummy;  /* Ignore the MSVC warning about no initializer */
          837  +  static VdbeOp dummy;  /* Ignore the MSVC warning about no initializer */
   841    838     assert( p->magic==VDBE_MAGIC_INIT );
   842    839     if( addr<0 ){
   843    840   #ifdef SQLITE_OMIT_TRACE
   844    841       if( p->nOp==0 ) return (VdbeOp*)&dummy;
   845    842   #endif
   846    843       addr = p->nOp - 1;
   847    844     }

Changes to src/vdbeblob.c.

   269    269         sqlite3VdbeChangeP3(v, 1, pTab->pSchema->iGeneration);
   270    270   
   271    271         /* Make sure a mutex is held on the table to be accessed */
   272    272         sqlite3VdbeUsesBtree(v, iDb); 
   273    273   
   274    274         /* Configure the OP_TableLock instruction */
   275    275   #ifdef SQLITE_OMIT_SHARED_CACHE
   276         -      sqlite3VdbeChangeToNoop(v, 2, 1);
          276  +      sqlite3VdbeChangeToNoop(v, 2);
   277    277   #else
   278    278         sqlite3VdbeChangeP1(v, 2, iDb);
   279    279         sqlite3VdbeChangeP2(v, 2, pTab->tnum);
   280    280         sqlite3VdbeChangeP3(v, 2, flags);
   281    281         sqlite3VdbeChangeP4(v, 2, pTab->zName, P4_TRANSIENT);
   282    282   #endif
   283    283   
   284    284         /* Remove either the OP_OpenWrite or OpenRead. Set the P2 
   285    285         ** parameter of the other to pTab->tnum.  */
   286         -      sqlite3VdbeChangeToNoop(v, 4 - flags, 1);
          286  +      sqlite3VdbeChangeToNoop(v, 4 - flags);
   287    287         sqlite3VdbeChangeP2(v, 3 + flags, pTab->tnum);
   288    288         sqlite3VdbeChangeP3(v, 3 + flags, iDb);
   289    289   
   290    290         /* Configure the number of columns. Configure the cursor to
   291    291         ** think that the table has one more column than it really
   292    292         ** does. An OP_Column to retrieve this imaginary column will
   293    293         ** always return an SQL NULL. This is useful because it means

Changes to src/vdbesort.c.

   156    156     VdbeSorterIter *pIter           /* Iterator to advance */
   157    157   ){
   158    158     int rc;                         /* Return Code */
   159    159     int nRead;                      /* Number of bytes read */
   160    160     int nRec = 0;                   /* Size of record in bytes */
   161    161     int iOff = 0;                   /* Size of serialized size varint in bytes */
   162    162   
   163         -  nRead = pIter->iEof - pIter->iReadOff;
   164         -  if( nRead>5 ) nRead = 5;
          163  +  assert( pIter->iEof>=pIter->iReadOff );
          164  +  if( pIter->iEof-pIter->iReadOff>5 ){
          165  +    nRead = 5;
          166  +  }else{
          167  +    nRead = (int)(pIter->iEof - pIter->iReadOff);
          168  +  }
   165    169     if( nRead<=0 ){
   166    170       /* This is an EOF condition */
   167    171       vdbeSorterIterZero(db, pIter);
   168    172       return SQLITE_OK;
   169    173     }
   170    174   
   171    175     rc = sqlite3OsRead(pIter->pFile, pIter->aAlloc, nRead, pIter->iReadOff);
................................................................................
   294    298   ** field. For the purposes of the comparison, ignore it. Also, if bOmitRowid
   295    299   ** is true and key1 contains even a single NULL value, it is considered to
   296    300   ** be less than key2. Even if key2 also contains NULL values.
   297    301   **
   298    302   ** If pKey2 is passed a NULL pointer, then it is assumed that the pCsr->aSpace
   299    303   ** has been allocated and contains an unpacked record that is used as key2.
   300    304   */
   301         -static int vdbeSorterCompare(
          305  +static void vdbeSorterCompare(
   302    306     VdbeCursor *pCsr,               /* Cursor object (for pKeyInfo) */
   303    307     int bOmitRowid,                 /* Ignore rowid field at end of keys */
   304    308     void *pKey1, int nKey1,         /* Left side of comparison */
   305    309     void *pKey2, int nKey2,         /* Right side of comparison */
   306    310     int *pRes                       /* OUT: Result of comparison */
   307    311   ){
   308    312     KeyInfo *pKeyInfo = pCsr->pKeyInfo;
................................................................................
   316    320   
   317    321     if( bOmitRowid ){
   318    322       r2->nField = pKeyInfo->nField;
   319    323       assert( r2->nField>0 );
   320    324       for(i=0; i<r2->nField; i++){
   321    325         if( r2->aMem[i].flags & MEM_Null ){
   322    326           *pRes = -1;
   323         -        return SQLITE_OK;
          327  +        return;
   324    328         }
   325    329       }
   326    330       r2->flags |= UNPACKED_PREFIX_MATCH;
   327    331     }
   328    332   
   329    333     *pRes = sqlite3VdbeRecordCompare(nKey1, pKey1, r2);
   330         -  return SQLITE_OK;
   331    334   }
   332    335   
   333    336   /*
   334    337   ** This function is called to compare two iterator keys when merging 
   335    338   ** multiple b-tree segments. Parameter iOut is the index of the aTree[] 
   336    339   ** value to recalculate.
   337    340   */
................................................................................
   358    361   
   359    362     if( p1->pFile==0 ){
   360    363       iRes = i2;
   361    364     }else if( p2->pFile==0 ){
   362    365       iRes = i1;
   363    366     }else{
   364    367       int res;
   365         -    int rc;
   366    368       assert( pCsr->pSorter->pUnpacked!=0 );  /* allocated in vdbeSorterMerge() */
   367         -    rc = vdbeSorterCompare(
          369  +    vdbeSorterCompare(
   368    370           pCsr, 0, p1->aKey, p1->nKey, p2->aKey, p2->nKey, &res
   369    371       );
   370         -    /* The vdbeSorterCompare() call cannot fail since pCsr->pSorter->pUnpacked
   371         -    ** has already been allocated. */
   372         -    assert( rc==SQLITE_OK );
   373         -
   374    372       if( res<=0 ){
   375    373         iRes = i1;
   376    374       }else{
   377    375         iRes = i2;
   378    376       }
   379    377     }
   380    378   
................................................................................
   458    456         SQLITE_OPEN_TEMP_JOURNAL |
   459    457         SQLITE_OPEN_READWRITE    | SQLITE_OPEN_CREATE |
   460    458         SQLITE_OPEN_EXCLUSIVE    | SQLITE_OPEN_DELETEONCLOSE, &dummy
   461    459     );
   462    460   }
   463    461   
   464    462   /*
   465         -** Attemp to merge the two sorted lists p1 and p2 into a single list. If no
   466         -** error occurs set *ppOut to the head of the new list and return SQLITE_OK.
          463  +** Merge the two sorted lists p1 and p2 into a single list.
          464  +** Set *ppOut to the head of the new list.
   467    465   */
   468         -static int vdbeSorterMerge(
   469         -  sqlite3 *db,                    /* Database handle */
          466  +static void vdbeSorterMerge(
   470    467     VdbeCursor *pCsr,               /* For pKeyInfo */
   471    468     SorterRecord *p1,               /* First list to merge */
   472    469     SorterRecord *p2,               /* Second list to merge */
   473    470     SorterRecord **ppOut            /* OUT: Head of merged list */
   474    471   ){
   475         -  int rc = SQLITE_OK;
   476    472     SorterRecord *pFinal = 0;
   477    473     SorterRecord **pp = &pFinal;
   478    474     void *pVal2 = p2 ? p2->pVal : 0;
   479    475   
   480    476     while( p1 && p2 ){
   481    477       int res;
   482         -    rc = vdbeSorterCompare(pCsr, 0, p1->pVal, p1->nVal, pVal2, p2->nVal, &res);
   483         -    if( rc!=SQLITE_OK ){
   484         -      *pp = 0;
   485         -      vdbeSorterRecordFree(db, p1);
   486         -      vdbeSorterRecordFree(db, p2);
   487         -      vdbeSorterRecordFree(db, pFinal);
   488         -      *ppOut = 0;
   489         -      return rc;
   490         -    }
          478  +    vdbeSorterCompare(pCsr, 0, p1->pVal, p1->nVal, pVal2, p2->nVal, &res);
   491    479       if( res<=0 ){
   492    480         *pp = p1;
   493    481         pp = &p1->pNext;
   494    482         p1 = p1->pNext;
   495    483         pVal2 = 0;
   496    484       }else{
   497    485         *pp = p2;
................................................................................
   498    486          pp = &p2->pNext;
   499    487         p2 = p2->pNext;
   500    488         if( p2==0 ) break;
   501    489         pVal2 = p2->pVal;
   502    490       }
   503    491     }
   504    492     *pp = p1 ? p1 : p2;
   505         -
   506    493     *ppOut = pFinal;
   507         -  return SQLITE_OK;
   508    494   }
   509    495   
   510    496   /*
   511    497   ** Sort the linked list of records headed at pCsr->pRecord. Return SQLITE_OK
   512    498   ** if successful, or an SQLite error code (i.e. SQLITE_NOMEM) if an error
   513    499   ** occurs.
   514    500   */
   515         -static int vdbeSorterSort(sqlite3 *db, VdbeCursor *pCsr){
   516         -  int rc = SQLITE_OK;
          501  +static int vdbeSorterSort(VdbeCursor *pCsr){
   517    502     int i;
   518    503     SorterRecord **aSlot;
   519    504     SorterRecord *p;
   520    505     VdbeSorter *pSorter = pCsr->pSorter;
   521    506   
   522    507     aSlot = (SorterRecord **)sqlite3MallocZero(64 * sizeof(SorterRecord *));
   523    508     if( !aSlot ){
................................................................................
   524    509       return SQLITE_NOMEM;
   525    510     }
   526    511   
   527    512     p = pSorter->pRecord;
   528    513     while( p ){
   529    514       SorterRecord *pNext = p->pNext;
   530    515       p->pNext = 0;
   531         -    for(i=0; rc==SQLITE_OK && aSlot[i]; i++){
   532         -      rc = vdbeSorterMerge(db, pCsr, p, aSlot[i], &p);
          516  +    for(i=0; aSlot[i]; i++){
          517  +      vdbeSorterMerge(pCsr, p, aSlot[i], &p);
   533    518         aSlot[i] = 0;
   534    519       }
   535         -    if( rc!=SQLITE_OK ){
   536         -      vdbeSorterRecordFree(db, pNext);
   537         -      break;
   538         -    }
   539    520       aSlot[i] = p;
   540    521       p = pNext;
   541    522     }
   542    523   
   543    524     p = 0;
   544    525     for(i=0; i<64; i++){
   545         -    if( rc==SQLITE_OK ){
   546         -      rc = vdbeSorterMerge(db, pCsr, p, aSlot[i], &p);
   547         -    }else{
   548         -      vdbeSorterRecordFree(db, aSlot[i]);
   549         -    }
          526  +    vdbeSorterMerge(pCsr, p, aSlot[i], &p);
   550    527     }
   551    528     pSorter->pRecord = p;
   552    529   
   553    530     sqlite3_free(aSlot);
   554         -  return rc;
          531  +  return SQLITE_OK;
   555    532   }
   556    533   
   557    534   
   558    535   /*
   559    536   ** Write the current contents of the in-memory linked-list to a PMA. Return
   560    537   ** SQLITE_OK if successful, or an SQLite error code otherwise.
   561    538   **
................................................................................
   573    550     VdbeSorter *pSorter = pCsr->pSorter;
   574    551   
   575    552     if( pSorter->nInMemory==0 ){
   576    553       assert( pSorter->pRecord==0 );
   577    554       return rc;
   578    555     }
   579    556   
   580         -  rc = vdbeSorterSort(db, pCsr);
          557  +  rc = vdbeSorterSort(pCsr);
   581    558   
   582    559     /* If the first temporary PMA file has not been opened, open it now. */
   583    560     if( rc==SQLITE_OK && pSorter->pTemp1==0 ){
   584    561       rc = vdbeSorterOpenTempFile(db, &pSorter->pTemp1);
   585    562       assert( rc!=SQLITE_OK || pSorter->pTemp1 );
   586    563       assert( pSorter->iWriteOff==0 );
   587    564       assert( pSorter->nPMA==0 );
................................................................................
   720    697   
   721    698     /* If no data has been written to disk, then do not do so now. Instead,
   722    699     ** sort the VdbeSorter.pRecord list. The vdbe layer will read data directly
   723    700     ** from the in-memory list.  */
   724    701     if( pSorter->nPMA==0 ){
   725    702       *pbEof = !pSorter->pRecord;
   726    703       assert( pSorter->aTree==0 );
   727         -    return vdbeSorterSort(db, pCsr);
          704  +    return vdbeSorterSort(pCsr);
   728    705     }
   729    706   
   730    707     /* Write the current b-tree to a PMA. Close the b-tree cursor. */
   731    708     rc = vdbeSorterListToPMA(db, pCsr);
   732    709     if( rc!=SQLITE_OK ) return rc;
   733    710   
   734    711     /* Allocate space for aIter[] and aTree[]. */
................................................................................
   890    867   ** key.
   891    868   */
   892    869   int sqlite3VdbeSorterCompare(
   893    870     VdbeCursor *pCsr,               /* Sorter cursor */
   894    871     Mem *pVal,                      /* Value to compare to current sorter key */
   895    872     int *pRes                       /* OUT: Result of comparison */
   896    873   ){
   897         -  int rc;
   898    874     VdbeSorter *pSorter = pCsr->pSorter;
   899    875     void *pKey; int nKey;           /* Sorter key to compare pVal with */
   900    876   
   901    877     pKey = vdbeSorterRowkey(pSorter, &nKey);
   902         -  rc = vdbeSorterCompare(pCsr, 1, pVal->z, pVal->n, pKey, nKey, pRes);
   903         -  assert( rc!=SQLITE_OK || pVal->db->mallocFailed || (*pRes)<=0 );
   904         -  return rc;
          878  +  vdbeSorterCompare(pCsr, 1, pVal->z, pVal->n, pKey, nKey, pRes);
          879  +  return SQLITE_OK;
   905    880   }
   906    881   
   907    882   #endif /* #ifndef SQLITE_OMIT_MERGE_SORT */

Changes to src/where.c.

   463    463       }
   464    464     }
   465    465     return mask;
   466    466   }
   467    467   static Bitmask exprSelectTableUsage(WhereMaskSet *pMaskSet, Select *pS){
   468    468     Bitmask mask = 0;
   469    469     while( pS ){
          470  +    SrcList *pSrc = pS->pSrc;
   470    471       mask |= exprListTableUsage(pMaskSet, pS->pEList);
   471    472       mask |= exprListTableUsage(pMaskSet, pS->pGroupBy);
   472    473       mask |= exprListTableUsage(pMaskSet, pS->pOrderBy);
   473    474       mask |= exprTableUsage(pMaskSet, pS->pWhere);
   474    475       mask |= exprTableUsage(pMaskSet, pS->pHaving);
          476  +    if( ALWAYS(pSrc!=0) ){
          477  +      int i;
          478  +      for(i=0; i<pSrc->nSrc; i++){
          479  +        mask |= exprSelectTableUsage(pMaskSet, pSrc->a[i].pSelect);
          480  +        mask |= exprTableUsage(pMaskSet, pSrc->a[i].pOn);
          481  +      }
          482  +    }
   475    483       pS = pS->pPrior;
   476    484     }
   477    485     return mask;
   478    486   }
   479    487   
   480    488   /*
   481    489   ** Return TRUE if the given operator is one of the operators that is
................................................................................
  1990   1998     Bitmask extraCols;          /* Bitmap of additional columns */
  1991   1999   
  1992   2000     /* Generate code to skip over the creation and initialization of the
  1993   2001     ** transient index on 2nd and subsequent iterations of the loop. */
  1994   2002     v = pParse->pVdbe;
  1995   2003     assert( v!=0 );
  1996   2004     regIsInit = ++pParse->nMem;
  1997         -  addrInit = sqlite3VdbeAddOp1(v, OP_If, regIsInit);
  1998         -  sqlite3VdbeAddOp2(v, OP_Integer, 1, regIsInit);
         2005  +  addrInit = sqlite3VdbeAddOp1(v, OP_Once, regIsInit);
  1999   2006   
  2000   2007     /* Count the number of columns that will be added to the index
  2001   2008     ** and used to match WHERE clause constraints */
  2002   2009     nColumn = 0;
  2003   2010     pTable = pSrc->pTab;
  2004   2011     pWCEnd = &pWC->a[pWC->nTerm];
  2005   2012     idxCols = 0;

Changes to test/fts3sort.test.

   153    153       INSERT INTO t2 VALUES('cc aa');
   154    154       SELECT docid FROM t2 WHERE t2 MATCH 'aa';
   155    155     END;
   156    156   } {3 1}
   157    157   do_execsql_test 2.3 {
   158    158     SELECT docid FROM t2 WHERE t2 MATCH 'aa';
   159    159   } {3 1}
          160  +
          161  +#-------------------------------------------------------------------------
          162  +# Test that ticket [56be976859] has been fixed.
          163  +#
          164  +do_execsql_test 3.1 {
          165  +  CREATE VIRTUAL TABLE t3 USING fts4(x, order=DESC);
          166  +  INSERT INTO t3(docid, x) VALUES(113382409004785664, 'aa');
          167  +  INSERT INTO t3(docid, x) VALUES(1, 'ab');
          168  +  SELECT rowid FROM t3 WHERE x MATCH 'a*' ORDER BY docid DESC;
          169  +} {113382409004785664 1}
          170  +do_execsql_test 3.2 {
          171  +  CREATE VIRTUAL TABLE t4 USING fts4(x);
          172  +  INSERT INTO t4(docid, x) VALUES(-113382409004785664, 'aa');
          173  +  INSERT INTO t4(docid, x) VALUES(1, 'ab');
          174  +  SELECT rowid FROM t4 WHERE x MATCH 'a*';
          175  +} {-113382409004785664 1}
          176  +
          177  +
   160    178   
   161    179   finish_test
   162    180   

Added test/subquery2.test.

            1  +# 2011 September 16
            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.  The
           12  +# focus of this script is testing correlated subqueries
           13  +#
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +ifcapable !subquery {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +do_test subquery2-1.1 {
           25  +  execsql {
           26  +    BEGIN;
           27  +    CREATE TABLE t1(a,b);
           28  +    INSERT INTO t1 VALUES(1,2);
           29  +    INSERT INTO t1 VALUES(3,4);
           30  +    INSERT INTO t1 VALUES(5,6);
           31  +    INSERT INTO t1 VALUES(7,8);
           32  +    CREATE TABLE t2(c,d);
           33  +    INSERT INTO t2 VALUES(1,1);
           34  +    INSERT INTO t2 VALUES(3,9);
           35  +    INSERT INTO t2 VALUES(5,25);
           36  +    INSERT INTO t2 VALUES(7,49);
           37  +    CREATE TABLE t3(e,f);
           38  +    INSERT INTO t3 VALUES(1,1);
           39  +    INSERT INTO t3 VALUES(3,27);
           40  +    INSERT INTO t3 VALUES(5,125);
           41  +    INSERT INTO t3 VALUES(7,343);
           42  +    COMMIT;
           43  +  }
           44  +  execsql {
           45  +    SELECT a FROM t1
           46  +     WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
           47  +  }
           48  +} {1 3 5 7}
           49  +do_test subquery2-1.2 {
           50  +  execsql {
           51  +    CREATE INDEX t1b ON t1(b);
           52  +    SELECT a FROM t1
           53  +     WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
           54  +  }
           55  +} {1 3 5 7}
           56  +
           57  +do_test subquery2-1.11 {
           58  +  execsql {
           59  +    SELECT a FROM t1
           60  +     WHERE +b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
           61  +  }
           62  +} {1}
           63  +do_test subquery2-1.12 {
           64  +  execsql {
           65  +    SELECT a FROM t1
           66  +     WHERE b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
           67  +  }
           68  +} {1}
           69  +
           70  +do_test subquery2-1.21 {
           71  +  execsql {
           72  +    SELECT a FROM t1
           73  +     WHERE +b=(SELECT x+1 FROM 
           74  +                 (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f))
           75  +  }
           76  +} {1 3 5 7}
           77  +do_test subquery2-1.22 {
           78  +  execsql {
           79  +    SELECT a FROM t1
           80  +     WHERE b=(SELECT x+1 FROM 
           81  +                 (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f))
           82  +  }
           83  +} {1 3 5 7}
           84  +
           85  +
           86  +finish_test

Changes to test/tkt-31338dca7e.test.

    69     69      CREATE INDEX t4x ON t4(x);
    70     70       SELECT * FROM t3, t4, t5
    71     71        WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444)
    72     72        ORDER BY v, w, x, y, z;
    73     73     }
    74     74   } {111 222 222 333 888 333 444 444 555 888 333 444 444 555 999}
    75     75   
           76  +
           77  +# Ticket [2c2de252666662f5459904fc33a9f2956cbff23c]
           78  +#
           79  +do_test tkt-31338-3.1 {
           80  +  foreach x [db eval {SELECT name FROM sqlite_master WHERE type='table'}] {
           81  +     db eval "DROP TABLE $x"
           82  +  }
           83  +  db eval {
           84  +    CREATE TABLE t1(a,b,c,d);
           85  +    CREATE TABLE t2(e,f);
           86  +    INSERT INTO t1 VALUES(1,2,3,4);
           87  +    INSERT INTO t2 VALUES(10,-8);
           88  +    CREATE INDEX t1a ON t1(a);
           89  +    CREATE INDEX t1b ON t1(b);
           90  +    CREATE TABLE t3(g);
           91  +    INSERT INTO t3 VALUES(4);
           92  +    CREATE TABLE t4(h);
           93  +    INSERT INTO t4 VALUES(5);
           94  +    
           95  +    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
           96  +     WHERE (a=1 AND h=4)
           97  +         OR (b IN (
           98  +               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
           99  +               GROUP BY e
          100  +            ));
          101  +  }    
          102  +} {4 1 2 3 4 {}}
          103  +do_test tkt-31338-3.2 {
          104  +  db eval {    
          105  +    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
          106  +     WHERE (a=1 AND h=4)
          107  +         OR (b=2 AND b NOT IN (
          108  +               SELECT x+1 FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
          109  +               GROUP BY e
          110  +            ));
          111  +  }    
          112  +} {4 1 2 3 4 {}}
          113  +do_test tkt-31338-3.3 {
          114  +  db eval {    
          115  +    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
          116  +     WHERE (+a=1 AND h=4)
          117  +         OR (b IN (
          118  +               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
          119  +               GROUP BY e
          120  +            ));
          121  +  }    
          122  +} {4 1 2 3 4 {}}
          123  +do_test tkt-31338-3.4 {
          124  +  db eval {    
          125  +    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
          126  +     WHERE (a=1 AND h=4)
          127  +         OR (+b IN (
          128  +               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
          129  +               GROUP BY e
          130  +            ));
          131  +  }    
          132  +} {4 1 2 3 4 {}}
          133  +
          134  +do_test tkt-31338-3.5 {
          135  +  db eval {
          136  +    CREATE TABLE t5(a,b,c,d,e,f);
          137  +    CREATE TABLE t6(g,h);
          138  +    CREATE TRIGGER t6r AFTER INSERT ON t6 BEGIN
          139  +      INSERT INTO t5    
          140  +        SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
          141  +         WHERE (a=1 AND h=4)
          142  +            OR (b IN (
          143  +               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
          144  +               GROUP BY e
          145  +            ));
          146  +    END;
          147  +    INSERT INTO t6 VALUES(88,99);
          148  +    SELECT * FROM t5;
          149  +  }    
          150  +} {4 1 2 3 4 {}}
          151  +
          152  +do_test tkt-31338-3.6 {
          153  +  db eval {    
          154  +    INSERT INTO t1 VALUES(2,4,3,4);
          155  +    INSERT INTO t1 VALUES(99,101,3,4);
          156  +    INSERT INTO t1 VALUES(98,97,3,4);
          157  +    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
          158  +     WHERE (a=1 AND h=4)
          159  +         OR (b IN (
          160  +               SELECT x+a FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
          161  +               GROUP BY e
          162  +            ));
          163  +  }    
          164  +} {4 2 4 3 4 {} 4 99 101 3 4 {}}
          165  +
          166  +do_test tkt-31338-3.7 {
          167  +  db eval {
          168  +    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
          169  +     WHERE (a=1 AND h=4)
          170  +         OR (b IN (
          171  +               SELECT x FROM (SELECT e+f+a AS x, e FROM t2 ORDER BY 1 LIMIT 2)
          172  +               GROUP BY e
          173  +            ));
          174  +  }    
          175  +} {4 2 4 3 4 {} 4 99 101 3 4 {}}
          176  +
    76    177   
    77    178   finish_test

Changes to tool/spaceanal.tcl.

     1      1   # Run this TCL script using "testfixture" in order get a report that shows
     2      2   # how much disk space is used by a particular data to actually store data
     3      3   # versus how much space is unused.
     4      4   #
     5      5   
     6      6   if {[catch {
            7  +if {![info exists argv0]} {
            8  +  set argv0 [file rootname [file tail [info nameofexecutable]]]
            9  +}
     7     10   
     8     11   # Get the name of the database to analyze
     9     12   #
    10     13   #set argv $argv0
    11         -if {[llength $argv]!=1} {
           14  +if {![info exists argv] || [llength $argv]!=1} {
    12     15     puts stderr "Usage: $argv0 database-name"
    13     16     exit 1
    14     17   }
    15     18   set file_to_analyze [lindex $argv 0]
    16     19   if {![file exists $file_to_analyze]} {
    17     20     puts stderr "No such file: $file_to_analyze"
    18     21     exit 1
................................................................................
    24     27   if {[file size $file_to_analyze]<512} {
    25     28     puts stderr "Empty or malformed database: $file_to_analyze"
    26     29     exit 1
    27     30   }
    28     31   
    29     32   # Open the database
    30     33   #
    31         -sqlite3 db [lindex $argv 0]
           34  +sqlite3 db $file_to_analyze
    32     35   register_dbstat_vtab db
    33     36   
    34     37   set pageSize [db one {PRAGMA page_size}]
    35     38   
    36         -#set DB [btree_open [lindex $argv 0] 1000 0]
           39  +#set DB [btree_open $file_to_analyze 1000 0]
    37     40   
    38     41   # In-memory database for collecting statistics. This script loops through
    39     42   # the tables and indices in the database being analyzed, adding a row for each
    40     43   # to an in-memory database (for which the schema is shown below). It then
    41     44   # queries the in-memory db to produce the space-analysis report.
    42     45   #
    43     46   sqlite3 mem :memory: