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: |
6ad553192051eaa0c6d929baacde2de0 |
| 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
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;
|