Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix another problem that could cause a crash when a window function was used in a view. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | exp-window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
e954145a3addf60247954b9161473cd0 |
| User & Date: | dan 2018-06-25 11:42:08.845 |
Context
|
2018-06-25
| ||
| 20:34 | Add documentation comment for sqlite3_create_window_function(). And further tests. (check-in: 3f2705b933 user: dan tags: exp-window-functions) | |
| 11:42 | Fix another problem that could cause a crash when a window function was used in a view. (check-in: e954145a3a user: dan tags: exp-window-functions) | |
|
2018-06-23
| ||
| 19:29 | Fix problems with using window-functions in correlated sub-queries. (check-in: 3e23cfc813 user: dan tags: exp-window-functions) | |
Changes
Changes to src/walker.c.
| ︙ | ︙ | |||
50 51 52 53 54 55 56 |
pExpr = pExpr->pRight;
continue;
}else if( ExprHasProperty(pExpr, EP_xIsSelect) ){
if( sqlite3WalkSelect(pWalker, pExpr->x.pSelect) ) return WRC_Abort;
}else if( pExpr->x.pList ){
if( sqlite3WalkExprList(pWalker, pExpr->x.pList) ) return WRC_Abort;
}
| > | > | 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
pExpr = pExpr->pRight;
continue;
}else if( ExprHasProperty(pExpr, EP_xIsSelect) ){
if( sqlite3WalkSelect(pWalker, pExpr->x.pSelect) ) return WRC_Abort;
}else if( pExpr->x.pList ){
if( sqlite3WalkExprList(pWalker, pExpr->x.pList) ) return WRC_Abort;
}
#ifndef SQLITE_OMIT_WINDOWFUNC
if( !ExprHasProperty(pExpr, EP_Reduced) && pExpr->pWin ){
Window *pWin = pExpr->pWin;
if( sqlite3WalkExprList(pWalker, pWin->pPartition) ) return WRC_Abort;
if( sqlite3WalkExprList(pWalker, pWin->pOrderBy) ) return WRC_Abort;
if( sqlite3WalkExpr(pWalker, pWin->pFilter) ) return WRC_Abort;
}
#endif
}
break;
}
return WRC_Continue;
}
int sqlite3WalkExpr(Walker *pWalker, Expr *pExpr){
return pExpr ? walkExpr(pWalker,pExpr) : WRC_Continue;
|
| ︙ | ︙ |
Changes to test/window1.test.
| ︙ | ︙ | |||
485 486 487 488 489 490 491 | Darrell South 254Darrell Grant South 254Grant Brad North 254Brad Elizabeth South 254Elizabeth Horace East 254Horace } | < > > > > > > > > > > > > > > > > > > > | 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 |
Darrell South 254Darrell
Grant South 254Grant
Brad North 254Brad
Elizabeth South 254Elizabeth
Horace East 254Horace
}
do_execsql_test 10.8 {
SELECT emp, region, (
SELECT sum(total) FILTER (WHERE sales.emp!=outer.emp) OVER (
ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) FROM sales
) FROM sales AS outer;
} {
Alice North 220
Frank South 232
Charles North 209
Darrell South 246
Grant South 231
Brad North 232
Elizabeth South 155
Horace East 253
}
#-------------------------------------------------------------------------
# Check that it is not possible to use a window function in a CREATE INDEX
# statement.
#
do_execsql_test 11.0 { CREATE TABLE t6(a, b, c); }
do_catchsql_test 11.1 {
CREATE INDEX t6i ON t6(a) WHERE sum(b) OVER ();
} {1 {misuse of window function sum()}}
do_catchsql_test 11.2 {
CREATE INDEX t6i ON t6(a) WHERE lead(b) OVER ();
} {1 {misuse of window function lead()}}
do_catchsql_test 11.3 {
CREATE INDEX t6i ON t6(sum(b) OVER ());
} {1 {misuse of window function sum()}}
do_catchsql_test 11.4 {
CREATE INDEX t6i ON t6(lead(b) OVER ());
} {1 {misuse of window function lead()}}
finish_test
|
Changes to test/window4.tcl.
| ︙ | ︙ | |||
319 320 321 322 323 324 325 326 327 328 |
}
execsql_test 9.2 {
SELECT dense_rank() OVER (PARTITION BY x) FROM t2
}
execsql_float_test 9.3 {
SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2
}
finish_test
| > > > > > > > > | 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 |
}
execsql_test 9.2 {
SELECT dense_rank() OVER (PARTITION BY x) FROM t2
}
execsql_float_test 9.3 {
SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2
}
execsql_test 9.4 {
SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
}
execsql_test 9.5 {
SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
}
finish_test
|
Changes to test/window4.test.
| ︙ | ︙ | |||
1221 1222 1223 1224 1225 1226 1227 1228 |
set myres {}
foreach r [db eval {SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2}] {
lappend myres [format %.2f [set r]]
}
set myres
} {1.00 0.00 1.00 0.00 1.00 0.00 4.00 0.00 4.00 0.00 6.00 0.00 7.00 0.00}
finish_test
| > > > > > > > > | 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 |
set myres {}
foreach r [db eval {SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2}] {
lappend myres [format %.2f [set r]]
}
set myres
} {1.00 0.00 1.00 0.00 1.00 0.00 4.00 0.00 4.00 0.00 6.00 0.00 7.00 0.00}
do_execsql_test 9.4 {
SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1 1 1 1 1 4 4 4 4 6 6 7 7}
do_execsql_test 9.5 {
SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1 4 4 6 6 7 7}
finish_test
|