/ Check-in [6ad55319]
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:Remove rows from the ephemeral table used by window functions once they are no longer required.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: 6ad553192051eaa0c6d929baacde2de07b93c6d09de861028bbce55a2c9bfdd3
User & Date: dan 2019-03-13 15:29:14
Wiki:window-functions
Context
2019-03-13
17:20
Avoid allocating excessive registers for the PARTITION BY expressions when processing window functions. check-in: 180be266 user: dan tags: window-functions
15:29
Remove rows from the ephemeral table used by window functions once they are no longer required. check-in: 6ad55319 user: dan tags: window-functions
08:28
Minor optimization in sqlite3WindowCodeStep(). check-in: b1322ffb user: dan tags: window-functions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

8824
8825
8826
8827
8828
8829
8830
8831




8832
8833

8834
8835
8836
8837
8838
8839
8840

  assert( cursorOwnsBtShared(pCur) );
  assert( pBt->inTransaction==TRANS_WRITE );
  assert( (pBt->btsFlags & BTS_READ_ONLY)==0 );
  assert( pCur->curFlags & BTCF_WriteFlag );
  assert( hasSharedCacheTableLock(p, pCur->pgnoRoot, pCur->pKeyInfo!=0, 2) );
  assert( !hasReadConflicts(p, pCur->pgnoRoot) );
  assert( pCur->ix<pCur->pPage->nCell );




  assert( pCur->eState==CURSOR_VALID );
  assert( (flags & ~(BTREE_SAVEPOSITION | BTREE_AUXDELETE))==0 );


  iCellDepth = pCur->iPage;
  iCellIdx = pCur->ix;
  pPage = pCur->pPage;
  pCell = findCell(pPage, iCellIdx);
  if( pPage->nFree<0 && btreeComputeFreeSpace(pPage) ) return SQLITE_CORRUPT;








|
>
>
>
>

<
>







8824
8825
8826
8827
8828
8829
8830
8831
8832
8833
8834
8835
8836

8837
8838
8839
8840
8841
8842
8843
8844

  assert( cursorOwnsBtShared(pCur) );
  assert( pBt->inTransaction==TRANS_WRITE );
  assert( (pBt->btsFlags & BTS_READ_ONLY)==0 );
  assert( pCur->curFlags & BTCF_WriteFlag );
  assert( hasSharedCacheTableLock(p, pCur->pgnoRoot, pCur->pKeyInfo!=0, 2) );
  assert( !hasReadConflicts(p, pCur->pgnoRoot) );
  assert( (flags & ~(BTREE_SAVEPOSITION | BTREE_AUXDELETE))==0 );
  if( pCur->eState==CURSOR_REQUIRESEEK ){
    rc = btreeRestoreCursorPosition(pCur);
    if( rc ) return rc;
  }
  assert( pCur->eState==CURSOR_VALID );

  assert( pCur->ix<pCur->pPage->nCell );

  iCellDepth = pCur->iPage;
  iCellIdx = pCur->ix;
  pPage = pCur->pPage;
  pCell = findCell(pPage, iCellIdx);
  if( pPage->nFree<0 && btreeComputeFreeSpace(pPage) ) return SQLITE_CORRUPT;

Changes to src/window.c.

607
608
609
610
611
612
613

614
615
616
617
618
619
620
....
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
....
1528
1529
1530
1531
1532
1533
1534

1535
1536
1537
1538
1539
1540
1541

1542
1543
1544
1545
1546
1547



1548
1549
1550
1551
1552
1553
1554
....
1696
1697
1698
1699
1700
1701
1702





1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
....
2149
2150
2151
2152
2153
2154
2155































2156
2157
2158
2159
2160
2161
2162
        { row_numberName,   TK_ROWS,   TK_UNBOUNDED, TK_CURRENT }, 
        { dense_rankName,   TK_RANGE,  TK_UNBOUNDED, TK_CURRENT }, 
        { rankName,         TK_RANGE,  TK_UNBOUNDED, TK_CURRENT }, 
        { percent_rankName, TK_GROUPS, TK_CURRENT,   TK_UNBOUNDED }, 
        { cume_distName,    TK_GROUPS, TK_FOLLOWING, TK_UNBOUNDED }, 
        { ntileName,        TK_ROWS,   TK_CURRENT,   TK_UNBOUNDED }, 
        { leadName,         TK_ROWS,   TK_UNBOUNDED, TK_UNBOUNDED }, 

      };
      int i;
      for(i=0; i<ArraySize(aUp); i++){
        if( pFunc->zName==aUp[i].zFunc ){
          sqlite3ExprDelete(db, pWin->pStart);
          sqlite3ExprDelete(db, pWin->pEnd);
          pWin->pEnd = pWin->pStart = 0;
................................................................................
    }
  }
  regArg = pParse->nMem+1;
  pParse->nMem += nArg;
  return regArg;
}

#if 0
/* 
** Return true if the current frame should be cached in the ephemeral table,
** even if there are no xInverse() calls required.
*/
static int windowCacheFrame(Window *pMWin){
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    if( (pFunc->zName==nth_valueName)
     || (pFunc->zName==first_valueName)
     || (pFunc->zName==leadName) */
     || (pFunc->zName==lagName)
    ){
      return 1;
    }
  }
  return 0;
}
#endif

/*
** regOld and regNew are each the first register in an array of size
** pOrderBy->nExpr. This function generates code to compare the two
** arrays of registers using the collation sequences and other comparison
** parameters specified by pOrderBy. 
**
................................................................................

typedef struct WindowCodeArg WindowCodeArg;
typedef struct WindowCsrAndReg WindowCsrAndReg;
struct WindowCsrAndReg {
  int csr;
  int reg;
};

struct WindowCodeArg {
  Parse *pParse;
  Window *pMWin;
  Vdbe *pVdbe;
  int regGosub;
  int addrGosub;
  int regArg;


  WindowCsrAndReg start;
  WindowCsrAndReg current;
  WindowCsrAndReg end;
};




#define WINDOW_RETURN_ROW 1
#define WINDOW_AGGINVERSE 2
#define WINDOW_AGGSTEP    3

/*
** Generate VM code to read the window frames peer values from cursor csr into
** an array of registers starting at reg.
................................................................................

    case WINDOW_AGGSTEP:
      csr = p->end.csr;
      reg = p->end.reg;
      windowAggStep(pParse, pMWin, csr, 0, p->regArg, 0);
      break;
  }






  if( jumpOnEof ){
    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+2);
    ret = sqlite3VdbeAddOp0(v, OP_Goto);
  }else{
    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+1+bPeer);
    if( bPeer ){
      addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
    }
  }

  if( bPeer ){
    int addr;
    int nReg = (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0);
    int regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0);
    windowReadPeerValues(p, csr, regTmp);
    windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg, addrContinue);
    sqlite3ReleaseTempRange(pParse, regTmp, nReg);
  }

................................................................................
  s.pVdbe = v;
  s.regGosub = regGosub;
  s.addrGosub = addrGosub;
  s.current.csr = pMWin->iEphCsr;
  csrWrite = s.current.csr+1;
  s.start.csr = s.current.csr+2;
  s.end.csr = s.current.csr+3;
































  /* Allocate registers for the array of values from the sub-query, the
  ** samve values in record form, and the rowid used to insert said record
  ** into the ephemeral table.  */
  regNew = pParse->nMem+1;
  pParse->nMem += nInput;
  regRecord = ++pParse->nMem;







>







 







<










|







<







 







>







>






>
>
>







 







>
>
>
>
>












<







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
....
1469
1470
1471
1472
1473
1474
1475

1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493

1494
1495
1496
1497
1498
1499
1500
....
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
....
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723

1724
1725
1726
1727
1728
1729
1730
....
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
        { row_numberName,   TK_ROWS,   TK_UNBOUNDED, TK_CURRENT }, 
        { dense_rankName,   TK_RANGE,  TK_UNBOUNDED, TK_CURRENT }, 
        { rankName,         TK_RANGE,  TK_UNBOUNDED, TK_CURRENT }, 
        { percent_rankName, TK_GROUPS, TK_CURRENT,   TK_UNBOUNDED }, 
        { cume_distName,    TK_GROUPS, TK_FOLLOWING, TK_UNBOUNDED }, 
        { ntileName,        TK_ROWS,   TK_CURRENT,   TK_UNBOUNDED }, 
        { leadName,         TK_ROWS,   TK_UNBOUNDED, TK_UNBOUNDED }, 
        { lagName,          TK_ROWS,   TK_UNBOUNDED, TK_CURRENT }, 
      };
      int i;
      for(i=0; i<ArraySize(aUp); i++){
        if( pFunc->zName==aUp[i].zFunc ){
          sqlite3ExprDelete(db, pWin->pStart);
          sqlite3ExprDelete(db, pWin->pEnd);
          pWin->pEnd = pWin->pStart = 0;
................................................................................
    }
  }
  regArg = pParse->nMem+1;
  pParse->nMem += nArg;
  return regArg;
}


/* 
** Return true if the current frame should be cached in the ephemeral table,
** even if there are no xInverse() calls required.
*/
static int windowCacheFrame(Window *pMWin){
  Window *pWin;
  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
    FuncDef *pFunc = pWin->pFunc;
    if( (pFunc->zName==nth_valueName)
     || (pFunc->zName==first_valueName)
     || (pFunc->zName==leadName)
     || (pFunc->zName==lagName)
    ){
      return 1;
    }
  }
  return 0;
}


/*
** regOld and regNew are each the first register in an array of size
** pOrderBy->nExpr. This function generates code to compare the two
** arrays of registers using the collation sequences and other comparison
** parameters specified by pOrderBy. 
**
................................................................................

typedef struct WindowCodeArg WindowCodeArg;
typedef struct WindowCsrAndReg WindowCsrAndReg;
struct WindowCsrAndReg {
  int csr;
  int reg;
};

struct WindowCodeArg {
  Parse *pParse;
  Window *pMWin;
  Vdbe *pVdbe;
  int regGosub;
  int addrGosub;
  int regArg;
  int eDelete;

  WindowCsrAndReg start;
  WindowCsrAndReg current;
  WindowCsrAndReg end;
};

/*
** Values that may be passed as the second argument to windowCodeOp().
*/
#define WINDOW_RETURN_ROW 1
#define WINDOW_AGGINVERSE 2
#define WINDOW_AGGSTEP    3

/*
** Generate VM code to read the window frames peer values from cursor csr into
** an array of registers starting at reg.
................................................................................

    case WINDOW_AGGSTEP:
      csr = p->end.csr;
      reg = p->end.reg;
      windowAggStep(pParse, pMWin, csr, 0, p->regArg, 0);
      break;
  }

  if( op==p->eDelete ){
    sqlite3VdbeAddOp1(v, OP_Delete, csr);
    sqlite3VdbeChangeP5(v, OPFLAG_SAVEPOSITION);
  }

  if( jumpOnEof ){
    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+2);
    ret = sqlite3VdbeAddOp0(v, OP_Goto);
  }else{
    sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+1+bPeer);
    if( bPeer ){
      addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
    }
  }

  if( bPeer ){

    int nReg = (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0);
    int regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0);
    windowReadPeerValues(p, csr, regTmp);
    windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg, addrContinue);
    sqlite3ReleaseTempRange(pParse, regTmp, nReg);
  }

................................................................................
  s.pVdbe = v;
  s.regGosub = regGosub;
  s.addrGosub = addrGosub;
  s.current.csr = pMWin->iEphCsr;
  csrWrite = s.current.csr+1;
  s.start.csr = s.current.csr+2;
  s.end.csr = s.current.csr+3;

  /* Figure out when rows may be deleted from the ephemeral table. There
  ** are four options - they may never be deleted (eDelete==0), they may 
  ** be deleted as soon as they are no longer part of the window frame
  ** (eDelete==WINDOW_AGGINVERSE), they may be deleted as after the row 
  ** has been returned to the caller (WINDOW_RETURN_ROW), or they may
  ** be deleted after they enter the frame (WINDOW_AGGSTEP). */
  switch( pMWin->eStart ){
    case TK_FOLLOWING: {
      sqlite3 *db = pParse->db;
      sqlite3_value *pVal = 0;
      sqlite3ValueFromExpr(db, pMWin->pStart, db->enc,SQLITE_AFF_NUMERIC,&pVal);
      if( pVal && sqlite3_value_int(pVal)>0 ){
        s.eDelete = WINDOW_RETURN_ROW;
      }
      sqlite3ValueFree(pVal);
      break;
    }
    case TK_UNBOUNDED:
      if( windowCacheFrame(pMWin)==0 ){
        if( pMWin->eEnd==TK_PRECEDING ){
          s.eDelete = WINDOW_AGGSTEP;
        }else{
          s.eDelete = WINDOW_RETURN_ROW;
        }
      }
      break;
    default:
      s.eDelete = WINDOW_AGGINVERSE;
      break;
  }

  /* Allocate registers for the array of values from the sub-query, the
  ** samve values in record form, and the rowid used to insert said record
  ** into the ephemeral table.  */
  regNew = pParse->nMem+1;
  pParse->nMem += nInput;
  regRecord = ++pParse->nMem;