Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix another problem in lead()/lag(). And some errors that could occur following OOM faults. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | exp-window-functions |
| Files: | files | file ages | folders |
| SHA3-256: | fadd4dc119d8df0d871f4d839b7a1107 |
| User & Date: | dan 2018-06-15 19:01:35 |
Context
|
2018-06-15
| ||
| 20:46 | Add extra OOM test. check-in: ac251f72 user: dan tags: exp-window-functions | |
| 19:01 | Fix another problem in lead()/lag(). And some errors that could occur following OOM faults. check-in: fadd4dc1 user: dan tags: exp-window-functions | |
| 16:10 | Fix a bug in the lead() and lag() window functions causing them to fail when used in queries featuring multiple window functions. check-in: 3839fb18 user: dan tags: exp-window-functions | |
Changes
Changes to src/parse.y.
531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 |
oneselect(A) ::= SELECT(S) distinct(D) selcollist(W) from(X) where_opt(Y)
groupby_opt(P) having_opt(Q) windowdefn_opt(R)
orderby_opt(Z) limit_opt(L). {
#if SELECTTRACE_ENABLED
Token s = S; /*A-overwrites-S*/
#endif
A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L);
if( A ) A->pWinDefn = R;
#if SELECTTRACE_ENABLED
/* Populate the Select.zSelName[] string that is used to help with
** query planner debugging, to differentiate between multiple Select
** objects in a complex query.
**
** If the SELECT keyword is immediately followed by a C-style comment
** then extract the first few alphanumeric characters from within that
|
> | > > > |
531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 |
oneselect(A) ::= SELECT(S) distinct(D) selcollist(W) from(X) where_opt(Y)
groupby_opt(P) having_opt(Q) windowdefn_opt(R)
orderby_opt(Z) limit_opt(L). {
#if SELECTTRACE_ENABLED
Token s = S; /*A-overwrites-S*/
#endif
A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L);
if( A ){
A->pWinDefn = R;
}else{
sqlite3WindowListDelete(pParse->db, R);
}
#if SELECTTRACE_ENABLED
/* Populate the Select.zSelName[] string that is used to help with
** query planner debugging, to differentiate between multiple Select
** objects in a complex query.
**
** If the SELECT keyword is immediately followed by a C-style comment
** then extract the first few alphanumeric characters from within that
|
Changes to src/window.c.
388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 ... 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 .... 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 |
sqlite3_value **apArg
){
struct LastValueCtx *p;
p = (struct LastValueCtx *)sqlite3_aggregate_context(pCtx, sizeof(*p));
if( p ){
sqlite3_value_free(p->pVal);
p->pVal = sqlite3_value_dup(apArg[0]);
p->nVal++;
}
}
static void last_valueInvFunc(
sqlite3_context *pCtx,
int nArg,
sqlite3_value **apArg
){
................................................................................
sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
}
pSub = sqlite3SelectNew(
pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
);
p->pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
if( p->pSrc ){
int iTab;
ExprList *pList = 0;
p->pSrc->a[0].pSelect = pSub;
sqlite3SrcListAssignCursors(pParse, p->pSrc);
if( sqlite3ExpandSubquery(pParse, &p->pSrc->a[0]) ){
rc = SQLITE_NOMEM;
}else{
pSub->selFlags |= SF_Expanded;
p->selFlags &= ~SF_Aggregate;
sqlite3SelectPrep(pParse, pSub, 0);
}
}
sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
}
return rc;
}
/*
** Free the Window object passed as the second argument.
................................................................................
){
int csr = pWin->csrApp;
int lbl = sqlite3VdbeMakeLabel(v);
int tmpReg = sqlite3GetTempReg(pParse);
sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
if( pFunc->xSFunc==nth_valueStepFunc ){
sqlite3VdbeAddOp3(v, OP_Column, pWin->iEphCsr, pWin->iArgCol+1, tmpReg);
}else{
sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
}
sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg);
sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);
|
> > > | > > | < | > > > > | |
388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 ... 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 .... 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 |
sqlite3_value **apArg
){
struct LastValueCtx *p;
p = (struct LastValueCtx *)sqlite3_aggregate_context(pCtx, sizeof(*p));
if( p ){
sqlite3_value_free(p->pVal);
p->pVal = sqlite3_value_dup(apArg[0]);
if( p->pVal==0 ){
sqlite3_result_error_nomem(pCtx);
}else{
p->nVal++;
}
}
}
static void last_valueInvFunc(
sqlite3_context *pCtx,
int nArg,
sqlite3_value **apArg
){
................................................................................
sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
}
pSub = sqlite3SelectNew(
pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
);
p->pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
assert( p->pSrc || db->mallocFailed );
if( p->pSrc ){
int iTab;
ExprList *pList = 0;
p->pSrc->a[0].pSelect = pSub;
sqlite3SrcListAssignCursors(pParse, p->pSrc);
if( sqlite3ExpandSubquery(pParse, &p->pSrc->a[0]) ){
rc = SQLITE_NOMEM;
}else{
pSub->selFlags |= SF_Expanded;
p->selFlags &= ~SF_Aggregate;
sqlite3SelectPrep(pParse, pSub, 0);
}
sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
}else{
sqlite3SelectDelete(db, pSub);
}
if( db->mallocFailed ) rc = SQLITE_NOMEM;
}
return rc;
}
/*
** Free the Window object passed as the second argument.
................................................................................
){
int csr = pWin->csrApp;
int lbl = sqlite3VdbeMakeLabel(v);
int tmpReg = sqlite3GetTempReg(pParse);
sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
if( pFunc->xSFunc==nth_valueStepFunc ){
sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg);
}else{
sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
}
sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg);
sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);
|
Changes to test/window4.tcl.
267 268 269 270 271 272 273 274 275 276 |
lead(y) OVER win,
lead(y, 2) OVER win,
lead(y, 3, -1) OVER win
FROM t1
WINDOW win AS (ORDER BY x)
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 |
lead(y) OVER win,
lead(y, 2) OVER win,
lead(y, 3, -1) OVER win
FROM t1
WINDOW win AS (ORDER BY x)
}
==========
execsql_test 8.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
INSERT INTO t1 VALUES(1, 2, 3, 4);
INSERT INTO t1 VALUES(5, 6, 7, 8);
INSERT INTO t1 VALUES(9, 10, 11, 12);
}
execsql_test 8.1 {
SELECT row_number() OVER win,
nth_value(d,2) OVER win,
lead(d) OVER win
FROM t1
WINDOW win AS (ORDER BY a)
}
execsql_test 8.2 {
SELECT row_number() OVER win,
rank() OVER win,
dense_rank() OVER win,
ntile(2) OVER win,
first_value(d) OVER win,
last_value(d) OVER win,
nth_value(d,2) OVER win,
lead(d) OVER win,
lag(d) OVER win,
max(d) OVER win,
min(d) OVER win
FROM t1
WINDOW win AS (ORDER BY a)
}
finish_test
|
Changes to test/window4.test.
1162 1163 1164 1165 1166 1167 1168 1169 |
lead(y) OVER win,
lead(y, 2) OVER win,
lead(y, 3, -1) OVER win
FROM t1
WINDOW win AS (ORDER BY x)
} {4 6 8 6 8 10 8 10 -1 10 {} -1 {} {} -1}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 |
lead(y) OVER win,
lead(y, 2) OVER win,
lead(y, 3, -1) OVER win
FROM t1
WINDOW win AS (ORDER BY x)
} {4 6 8 6 8 10 8 10 -1 10 {} -1 {} {} -1}
#==========================================================================
do_execsql_test 8.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
INSERT INTO t1 VALUES(1, 2, 3, 4);
INSERT INTO t1 VALUES(5, 6, 7, 8);
INSERT INTO t1 VALUES(9, 10, 11, 12);
} {}
do_execsql_test 8.1 {
SELECT row_number() OVER win,
nth_value(d,2) OVER win,
lead(d) OVER win
FROM t1
WINDOW win AS (ORDER BY a)
} {1 {} 8 2 8 12 3 8 {}}
do_execsql_test 8.2 {
SELECT row_number() OVER win,
rank() OVER win,
dense_rank() OVER win,
ntile(2) OVER win,
first_value(d) OVER win,
last_value(d) OVER win,
nth_value(d,2) OVER win,
lead(d) OVER win,
lag(d) OVER win,
max(d) OVER win,
min(d) OVER win
FROM t1
WINDOW win AS (ORDER BY a)
} {1 1 1 1 4 4 {} 8 {} 4 4 2 2 2 1 4 8 8 12 4 8 4 3 3 3 2 4 12 8 {} 8 12 4}
finish_test
|
Added test/windowfault.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
# 2018 May 8 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix windowfault do_execsql_test 1.0 { CREATE TABLE t1(a, b, c, d); INSERT INTO t1 VALUES(1, 2, 3, 4); INSERT INTO t1 VALUES(5, 6, 7, 8); INSERT INTO t1 VALUES(9, 10, 11, 12); } faultsim_save_and_close do_faultsim_test 1 -start 1 -faults oom-* -prep { faultsim_restore_and_reopen } -body { execsql { SELECT row_number() OVER win, rank() OVER win, dense_rank() OVER win, ntile(2) OVER win, first_value(d) OVER win, last_value(d) OVER win, nth_value(d,2) OVER win, lead(d) OVER win, lag(d) OVER win, max(d) OVER win, min(d) OVER win FROM t1 WINDOW win AS (ORDER BY a) } } -test { faultsim_test_result {0 {1 1 1 1 4 4 {} 8 {} 4 4 2 2 2 1 4 8 8 12 4 8 4 3 3 3 2 4 12 8 {} 8 12 4}} } finish_test |