Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | In order to identify the first row of each partition, check if the rowid in the ephemeral table is 1 instead of using a dedicated flag register. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
f2d5f7a24c7aa483c579706c5bd7268a |
| User & Date: | dan 2019-03-16 10:15:24 |
Context
|
2019-03-16
| ||
| 20:29 | Fix problems with RANGE windows and string, blob and NULL values. (check-in: cebe09e11c user: dan tags: window-functions) | |
| 10:15 | In order to identify the first row of each partition, check if the rowid in the ephemeral table is 1 instead of using a dedicated flag register. (check-in: f2d5f7a24c user: dan tags: window-functions) | |
|
2019-03-15
| ||
| 20:46 | Implement the EXCLUDE clause for window frames. (check-in: 9b43c3ee2e user: dan tags: window-functions) | |
Changes
Changes to src/sqliteInt.h.
3572 3573 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 3587 |
int regResult;
int csrApp; /* Function cursor (used by min/max) */
int regApp; /* Function register (also used by min/max) */
int regPart; /* Array of registers for PARTITION BY values */
Expr *pOwner; /* Expression object this window is attached to */
int nBufferCol; /* Number of columns in buffer table */
int iArgCol; /* Offset of first argument for this function */
int regFirst;
int regStartRowid;
int regEndRowid;
};
#ifndef SQLITE_OMIT_WINDOWFUNC
void sqlite3WindowDelete(sqlite3*, Window*);
void sqlite3WindowListDelete(sqlite3 *db, Window *p);
|
| < |
3572 3573 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 |
int regResult;
int csrApp; /* Function cursor (used by min/max) */
int regApp; /* Function register (also used by min/max) */
int regPart; /* Array of registers for PARTITION BY values */
Expr *pOwner; /* Expression object this window is attached to */
int nBufferCol; /* Number of columns in buffer table */
int iArgCol; /* Offset of first argument for this function */
int regOne; /* Register containing constant value 1 */
int regStartRowid;
int regEndRowid;
};
#ifndef SQLITE_OMIT_WINDOWFUNC
void sqlite3WindowDelete(sqlite3*, Window*);
void sqlite3WindowListDelete(sqlite3 *db, Window *p);
|
Changes to src/window.c.
1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 .... 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 .... 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 .... 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 .... 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 |
if( pMWin->pPartition ){
int nExpr = pMWin->pPartition->nExpr;
pMWin->regPart = pParse->nMem+1;
pParse->nMem += nExpr;
sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nExpr-1);
}
pMWin->regFirst = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
if( pMWin->eExclude ){
pMWin->regStartRowid = ++pParse->nMem;
pMWin->regEndRowid = ++pParse->nMem;
pMWin->csrApp = pParse->nTab++;
sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid);
sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid);
................................................................................
Window *pMWin = p->pWin;
ExprList *pOrderBy = pMWin->pOrderBy;
Vdbe *v = sqlite3GetVdbe(pParse);
int csrWrite; /* Cursor used to write to eph. table */
int csrInput = p->pSrc->a[0].iCursor; /* Cursor of sub-select */
int nInput = p->pSrc->a[0].pTab->nCol; /* Number of cols returned by sub */
int iInput; /* To iterate through sub cols */
int addrIfNot; /* Address of OP_IfNot */
int addrGosubFlush; /* Address of OP_Gosub to flush: */
int addrInteger; /* Address of OP_Integer */
int addrEmpty; /* Address of OP_Rewind in flush: */
int regStart = 0; /* Value of <expr> PRECEDING */
int regEnd = 0; /* Value of <expr> FOLLOWING */
int regNew; /* Array of registers holding new input row */
int regRecord; /* regNew array in record form */
................................................................................
VdbeComment((v, "call flush_partition"));
sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart-1);
}
/* Insert the new row into the ephemeral table */
sqlite3VdbeAddOp2(v, OP_NewRowid, csrWrite, regRowid);
sqlite3VdbeAddOp3(v, OP_Insert, csrWrite, regRecord, regRowid);
addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst);
/* This block is run for the first row of each partition */
s.regArg = windowInitAccum(pParse, pMWin);
if( regStart ){
sqlite3ExprCode(pParse, pMWin->pStart, regStart);
windowCheckValue(pParse, regStart, 0 + (pMWin->eType==TK_RANGE ? 3 : 0));
................................................................................
if( regPeer && pOrderBy ){
sqlite3VdbeAddOp3(v, OP_Copy, regNewPeer, regPeer, pOrderBy->nExpr-1);
sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.start.reg, pOrderBy->nExpr-1);
sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.current.reg, pOrderBy->nExpr-1);
sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.end.reg, pOrderBy->nExpr-1);
}
sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst);
sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
sqlite3VdbeJumpHere(v, addrIfNot);
if( regPeer ){
windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer, lblWhereEnd);
}
if( pMWin->eStart==TK_FOLLOWING ){
windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
if( pMWin->eEnd!=TK_UNBOUNDED ){
if( pMWin->eType==TK_RANGE ){
................................................................................
windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
sqlite3VdbeJumpHere(v, addrBreak);
}
sqlite3VdbeJumpHere(v, addrEmpty);
sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
if( pMWin->pPartition ){
if( pMWin->regStartRowid ){
sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid);
sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid);
}
sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
}
}
#endif /* SQLITE_OMIT_WINDOWFUNC */
|
| | | | < < | < |
1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 .... 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 .... 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 .... 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 .... 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 |
if( pMWin->pPartition ){
int nExpr = pMWin->pPartition->nExpr;
pMWin->regPart = pParse->nMem+1;
pParse->nMem += nExpr;
sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nExpr-1);
}
pMWin->regOne = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regOne);
if( pMWin->eExclude ){
pMWin->regStartRowid = ++pParse->nMem;
pMWin->regEndRowid = ++pParse->nMem;
pMWin->csrApp = pParse->nTab++;
sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid);
sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid);
................................................................................
Window *pMWin = p->pWin;
ExprList *pOrderBy = pMWin->pOrderBy;
Vdbe *v = sqlite3GetVdbe(pParse);
int csrWrite; /* Cursor used to write to eph. table */
int csrInput = p->pSrc->a[0].iCursor; /* Cursor of sub-select */
int nInput = p->pSrc->a[0].pTab->nCol; /* Number of cols returned by sub */
int iInput; /* To iterate through sub cols */
int addrNe; /* Address of OP_Ne */
int addrGosubFlush; /* Address of OP_Gosub to flush: */
int addrInteger; /* Address of OP_Integer */
int addrEmpty; /* Address of OP_Rewind in flush: */
int regStart = 0; /* Value of <expr> PRECEDING */
int regEnd = 0; /* Value of <expr> FOLLOWING */
int regNew; /* Array of registers holding new input row */
int regRecord; /* regNew array in record form */
................................................................................
VdbeComment((v, "call flush_partition"));
sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart-1);
}
/* Insert the new row into the ephemeral table */
sqlite3VdbeAddOp2(v, OP_NewRowid, csrWrite, regRowid);
sqlite3VdbeAddOp3(v, OP_Insert, csrWrite, regRecord, regRowid);
addrNe = sqlite3VdbeAddOp3(v, OP_Ne, pMWin->regOne, 0, regRowid);
/* This block is run for the first row of each partition */
s.regArg = windowInitAccum(pParse, pMWin);
if( regStart ){
sqlite3ExprCode(pParse, pMWin->pStart, regStart);
windowCheckValue(pParse, regStart, 0 + (pMWin->eType==TK_RANGE ? 3 : 0));
................................................................................
if( regPeer && pOrderBy ){
sqlite3VdbeAddOp3(v, OP_Copy, regNewPeer, regPeer, pOrderBy->nExpr-1);
sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.start.reg, pOrderBy->nExpr-1);
sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.current.reg, pOrderBy->nExpr-1);
sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.end.reg, pOrderBy->nExpr-1);
}
sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
sqlite3VdbeJumpHere(v, addrNe);
if( regPeer ){
windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer, lblWhereEnd);
}
if( pMWin->eStart==TK_FOLLOWING ){
windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
if( pMWin->eEnd!=TK_UNBOUNDED ){
if( pMWin->eType==TK_RANGE ){
................................................................................
windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
sqlite3VdbeJumpHere(v, addrBreak);
}
sqlite3VdbeJumpHere(v, addrEmpty);
sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
if( pMWin->pPartition ){
if( pMWin->regStartRowid ){
sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid);
sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid);
}
sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
}
}
#endif /* SQLITE_OMIT_WINDOWFUNC */
|