/ Check-in [5efb0294]
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 the latest trunk changes into the sessions branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 5efb02949dbeabccfe1d848d275529f03f3dfc84
User & Date: drh 2011-09-16 19:40:24
Context
2011-09-19
20:28
Merge in all trunk changes through the version 3.7.8 release. check-in: 98619a23 user: drh tags: sessions
2011-09-16
19:40
Merge the latest trunk changes into the sessions branch. check-in: 5efb0294 user: drh tags: sessions
19:04
Remove unreachable branches from the previous change. Add additional test cases. check-in: cf51ef8a user: drh tags: trunk
2011-09-14
19:41
Merge latest changes from the trunk into the sessions branch. check-in: c00e45ed user: dan tags: sessions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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.

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

Changes to src/vdbe.c.

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

Changes to src/vdbe.h.

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

Changes to src/vdbeaux.c.

   667    667   */
   668    668   void sqlite3VdbeLinkSubProgram(Vdbe *pVdbe, SubProgram *p){
   669    669     p->pNext = pVdbe->pProgram;
   670    670     pVdbe->pProgram = p;
   671    671   }
   672    672   
   673    673   /*
   674         -** Change N opcodes starting at addr to No-ops.
          674  +** Change the opcode at addr into OP_Noop
   675    675   */
   676         -void sqlite3VdbeChangeToNoop(Vdbe *p, int addr, int N){
          676  +void sqlite3VdbeChangeToNoop(Vdbe *p, int addr){
   677    677     if( p->aOp ){
   678    678       VdbeOp *pOp = &p->aOp[addr];
   679    679       sqlite3 *db = p->db;
   680         -    while( N-- ){
   681         -      freeP4(db, pOp->p4type, pOp->p4.p);
   682         -      memset(pOp, 0, sizeof(pOp[0]));
   683         -      pOp->opcode = OP_Noop;
   684         -      pOp++;
   685         -    }
          680  +    freeP4(db, pOp->p4type, pOp->p4.p);
          681  +    memset(pOp, 0, sizeof(pOp[0]));
          682  +    pOp->opcode = OP_Noop;
   686    683     }
   687    684   }
   688    685   
   689    686   /*
   690    687   ** Change the value of the P4 operand for a specific instruction.
   691    688   ** This routine is useful when a large program is loaded from a
   692    689   ** static array using sqlite3VdbeAddOpList but we want to make a

Changes to src/vdbeblob.c.

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

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;

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: