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 | SQL 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 |
Context
|
2018-06-25
| ||
| 20:34 | Add documentation comment for sqlite3_create_window_function(). And further tests. check-in: 3f2705b9 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: e954145a user: dan tags: exp-window-functions | |
|
2018-06-23
| ||
| 19:29 | Fix problems with using window-functions in correlated sub-queries. check-in: 3e23cfc8 user: dan tags: exp-window-functions | |
Changes
Changes to src/walker.c.
50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
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;
}
if( 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;
}
}
break;
}
return WRC_Continue;
}
int sqlite3WalkExpr(Walker *pWalker, Expr *pExpr){
return pExpr ? walkExpr(pWalker,pExpr) : WRC_Continue;
|
| > > |
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
492
493
494
495
496
497
498
499
...
503
504
505
506
507
508
509
510
511
512
|
Darrell South 254Darrell Grant South 254Grant Brad North 254Brad Elizabeth South 254Elizabeth Horace East 254Horace } breakpoint 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; } { ................................................................................ Darrell South 246 Grant South 231 Brad North 232 Elizabeth South 155 Horace East 253 } finish_test |
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
485
486
487
488
489
490
491
492
493
494
495
496
497
498
...
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;
} {
................................................................................
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
|