/ Check-in [4b8357ee]
Login

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

Overview
Comment:Materialize subqueries using a subroutine and invoke that subroutine prior to each use of the materialization. Fix for ticket [002caede898aee4]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-002caede898
Files: files | file ages | folders
SHA1: 4b8357ee3c4ccdbd34e0cd077efd84cca677f496
User & Date: drh 2011-09-15 23:58:14
Original Comment: Materialize subqueries using a subroutine and invoke that subroutine prior to each use of the subqueries manifestation. Fix for ticket [002caede898aee4]
Context
2011-09-16
01:34
Add the new OP_Once opcode. Use it to clean up and simplify various one-time initialization sections in the code, including the fix for ticket [002caede898ae]. Closed-Leaf check-in: 7f00552b user: drh tags: tkt-002caede898
2011-09-15
23:58
Materialize subqueries using a subroutine and invoke that subroutine prior to each use of the materialization. Fix for ticket [002caede898aee4] check-in: 4b8357ee user: drh tags: tkt-002caede898
19:39
Adding test case for ticket [002caede898] check-in: 62dfc51a user: drh tags: tkt-002caede898
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++;

Changes to src/select.c.

  3797   3797   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  3798   3798     for(i=0; !p->pPrior && i<pTabList->nSrc; i++){
  3799   3799       struct SrcList_item *pItem = &pTabList->a[i];
  3800   3800       SelectDest dest;
  3801   3801       Select *pSub = pItem->pSelect;
  3802   3802       int isAggSub;
  3803   3803   
  3804         -    if( pSub==0 || pItem->isPopulated ) continue;
         3804  +    if( pSub==0 ) continue;
         3805  +    if( pItem->addrFillSub ){
         3806  +      sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
         3807  +      continue;
         3808  +    }
  3805   3809   
  3806   3810       /* Increment Parse.nHeight by the height of the largest expression
  3807   3811       ** tree refered to by this, the parent select. The child select
  3808   3812       ** may contain expression trees of at most
  3809   3813       ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
  3810   3814       ** more conservative than necessary, but much easier than enforcing
  3811   3815       ** an exact limit.
  3812   3816       */
  3813   3817       pParse->nHeight += sqlite3SelectExprHeight(p);
  3814   3818   
  3815         -    /* Check to see if the subquery can be absorbed into the parent. */
  3816   3819       isAggSub = (pSub->selFlags & SF_Aggregate)!=0;
  3817   3820       if( flattenSubquery(pParse, p, i, isAgg, isAggSub) ){
         3821  +      /* This subquery can be absorbed into its parent. */
  3818   3822         if( isAggSub ){
  3819   3823           isAgg = 1;
  3820   3824           p->selFlags |= SF_Aggregate;
  3821   3825         }
  3822   3826         i = -1;
  3823   3827       }else{
         3828  +      /* Generate a subroutine that will fill an ephemeral table with
         3829  +      ** the content of this subquery.  pItem->addrFillSub will point
         3830  +      ** to the address of the generated subroutine.  pItem->regReturn
         3831  +      ** is a register allocated to hold the subroutine return address
         3832  +      */
         3833  +      int topAddr = sqlite3VdbeAddOp0(v, OP_Goto);
         3834  +      int regOnce = 0;
         3835  +      assert( pItem->addrFillSub==0 );
         3836  +      pItem->addrFillSub = topAddr+1;
         3837  +      pItem->regReturn = ++pParse->nMem;
         3838  +      if( pItem->isCorrelated==0 && pParse->pTriggerTab==0 ){
         3839  +        /* If the subquery is no correlated and if we are not inside of
         3840  +        ** a trigger, then we only need to compute the value of the subquery
         3841  +        ** once. */
         3842  +        regOnce = ++pParse->nMem;
         3843  +        sqlite3VdbeAddOp1(v, OP_If, regOnce);
         3844  +        sqlite3VdbeAddOp2(v, OP_Integer, 1, regOnce);
         3845  +      }
  3824   3846         sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
  3825         -      assert( pItem->isPopulated==0 );
  3826   3847         explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
  3827   3848         sqlite3Select(pParse, pSub, &dest);
  3828         -      pItem->isPopulated = 1;
  3829   3849         pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow;
         3850  +      if( regOnce ) sqlite3VdbeJumpHere(v, topAddr+1);
         3851  +      sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
         3852  +      sqlite3VdbeJumpHere(v, topAddr);
         3853  +      sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, topAddr+1);
  3830   3854       }
  3831   3855       if( /*pParse->nErr ||*/ db->mallocFailed ){
  3832   3856         goto select_end;
  3833   3857       }
  3834   3858       pParse->nHeight -= sqlite3SelectExprHeight(p);
  3835   3859       pTabList = p->pSrc;
  3836   3860       if( !IgnorableOrderby(pDest) ){

Changes to src/sqliteInt.h.

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