Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add support for window function first_value(). |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | exp-window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
060b26402880daab085ad01f5f0dbde9 |
| User & Date: | dan 2018-06-07 17:45:22 |
Context
|
2018-06-07
| ||
| 20:08 | Add window functions lag() and lead(). check-in: ef342070 user: dan tags: exp-window-functions | |
| 17:45 | Add support for window function first_value(). check-in: 060b2640 user: dan tags: exp-window-functions | |
| 15:54 | Fix problems with the nth_value() function. check-in: 63002b9a user: dan tags: exp-window-functions | |
Changes
Changes to src/window.c.
305 305 sqlite3_context *pCtx, 306 306 int nArg, 307 307 sqlite3_value **apArg 308 308 ){ 309 309 } 310 310 static void nth_valueValueFunc(sqlite3_context *pCtx){ 311 311 } 312 + 313 +static void first_valueStepFunc( 314 + sqlite3_context *pCtx, 315 + int nArg, 316 + sqlite3_value **apArg 317 +){ 318 +} 319 +static void first_valueInverseFunc( 320 + sqlite3_context *pCtx, 321 + int nArg, 322 + sqlite3_value **apArg 323 +){ 324 +} 325 +static void first_valueValueFunc(sqlite3_context *pCtx){ 326 +} 312 327 313 328 #define WINDOWFUNC(name,nArg,extra) { \ 314 329 nArg, (SQLITE_UTF8|SQLITE_FUNC_WINDOW|extra), 0, 0, \ 315 330 name ## StepFunc, name ## ValueFunc, name ## ValueFunc, \ 316 331 name ## InverseFunc, #name \ 317 332 } 318 333 ................................................................................ 331 346 WINDOWFUNC(dense_rank, 0, 0), 332 347 WINDOWFUNC(rank, 0, 0), 333 348 WINDOWFUNC(percent_rank, 0, SQLITE_FUNC_WINDOW_SIZE), 334 349 WINDOWFUNC(cume_dist, 0, SQLITE_FUNC_WINDOW_SIZE), 335 350 WINDOWFUNC(ntile, 1, SQLITE_FUNC_WINDOW_SIZE), 336 351 WINDOWFUNCF(last_value, 1, 0), 337 352 WINDOWFUNC(nth_value, 2, 0), 353 + WINDOWFUNC(first_value, 1, 0), 338 354 }; 339 355 sqlite3InsertBuiltinFuncs(aWindowFuncs, ArraySize(aWindowFuncs)); 340 356 } 341 357 342 358 void sqlite3WindowUpdate(Parse *pParse, Window *pWin, FuncDef *pFunc){ 343 359 if( pFunc->funcFlags & SQLITE_FUNC_WINDOW ){ 344 360 sqlite3 *db = pParse->db; ................................................................................ 630 646 assert( pKeyInfo->aSortOrder[0]==0 ); 631 647 pKeyInfo->aSortOrder[0] = 1; 632 648 } 633 649 sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pWin->csrApp, 2); 634 650 sqlite3VdbeAppendP4(v, pKeyInfo, P4_KEYINFO); 635 651 sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1); 636 652 } 637 - else if( p->xSFunc==nth_valueStepFunc ){ 653 + else if( p->xSFunc==nth_valueStepFunc || p->xSFunc==first_valueStepFunc ){ 638 654 /* Allocate two registers at pWin->regApp. These will be used to 639 655 ** store the start and end index of the current frame. */ 640 656 assert( pMWin->iEphCsr ); 641 657 pWin->regApp = pParse->nMem+1; 642 658 pWin->csrApp = pParse->nTab++; 643 659 pParse->nMem += 2; 644 660 sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr); ................................................................................ 722 738 sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2); 723 739 }else{ 724 740 sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1); 725 741 sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp); 726 742 sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2); 727 743 } 728 744 }else if( pWin->regApp ){ 729 - assert( pWin->pFunc->xSFunc==nth_valueStepFunc ); 745 + assert( pWin->pFunc->xSFunc==nth_valueStepFunc 746 + || pWin->pFunc->xSFunc==first_valueStepFunc 747 + ); 730 748 assert( bInverse==0 || bInverse==1 ); 731 749 sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1); 732 750 }else{ 733 751 if( pWin->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ 734 752 CollSeq *pColl; 735 753 pColl = sqlite3ExprCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr); 736 754 sqlite3VdbeAddOp4(v, OP_CollSeq, 0,0,0, (const char*)pColl, P4_COLLSEQ); ................................................................................ 835 853 int regGosub, 836 854 int addrGosub 837 855 ){ 838 856 Vdbe *v = sqlite3GetVdbe(pParse); 839 857 Window *pWin; 840 858 for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ 841 859 FuncDef *pFunc = pWin->pFunc; 842 - if( pFunc->xSFunc==nth_valueStepFunc ){ 860 + if( pFunc->xSFunc==nth_valueStepFunc 861 + || pFunc->xSFunc==first_valueStepFunc 862 + ){ 843 863 int csr = pWin->csrApp; 844 864 int lbl = sqlite3VdbeMakeLabel(v); 845 865 int tmpReg = sqlite3GetTempReg(pParse); 846 866 sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult); 847 - sqlite3VdbeAddOp3(v, OP_Column, pWin->iEphCsr, pWin->iArgCol+1, tmpReg); 867 + 868 + if( pFunc->xSFunc==nth_valueStepFunc ){ 869 + sqlite3VdbeAddOp3(v, OP_Column, pWin->iEphCsr, pWin->iArgCol+1, tmpReg); 870 + }else{ 871 + sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg); 872 + } 848 873 sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg); 849 874 sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg); 850 875 sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg); 851 876 sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult); 852 877 sqlite3VdbeResolveLabel(v, lbl); 853 878 sqlite3ReleaseTempReg(pParse, tmpReg); 854 879 } ................................................................................ 883 908 Vdbe *v = sqlite3GetVdbe(pParse); 884 909 int regArg; 885 910 int nArg = 0; 886 911 Window *pWin; 887 912 for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ 888 913 sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); 889 914 nArg = MAX(nArg, pWin->nArg); 890 - if( pWin->pFunc->xSFunc==nth_valueStepFunc ){ 915 + if( pWin->pFunc->xSFunc==nth_valueStepFunc 916 + || pWin->pFunc->xSFunc==first_valueStepFunc 917 + ){ 891 918 sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp); 892 919 sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1); 893 920 } 894 921 } 895 922 regArg = pParse->nMem+1; 896 923 pParse->nMem += nArg; 897 924 return regArg; ................................................................................ 1568 1595 return; 1569 1596 } 1570 1597 1571 1598 for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ 1572 1599 FuncDef *pFunc = pWin->pFunc; 1573 1600 if( (pFunc->funcFlags & SQLITE_FUNC_WINDOW_SIZE) 1574 1601 || (pFunc->xSFunc==nth_valueStepFunc) 1602 + || (pFunc->xSFunc==first_valueStepFunc) 1575 1603 ){ 1576 1604 windowCodeCacheStep(pParse, p, pWInfo, regGosub, addrGosub); 1577 1605 return; 1578 1606 } 1579 1607 } 1580 1608 1581 1609 *pbLoop = 1; 1582 1610 windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub); 1583 1611 } 1584 1612
Changes to test/window3.tcl.
219 219 " 220 220 execsql_test 1.$tn.10.5 " 221 221 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a $window ) FROM t2 222 222 " 223 223 execsql_test 1.$tn.10.6 " 224 224 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2 225 225 " 226 + 227 + execsql_test 1.$tn.11.1 " 228 + SELECT first_value(b) OVER (ORDER BY a $window) FROM t2 229 + " 230 + execsql_test 1.$tn.11.2 " 231 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2 232 + " 233 + execsql_test 1.$tn.11.3 " 234 + SELECT first_value(b) OVER ( ORDER BY b,a $window ) FROM t2 235 + " 236 + execsql_test 1.$tn.11.4 " 237 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2 238 + " 239 + execsql_test 1.$tn.11.5 " 240 + SELECT first_value(b) OVER ( ORDER BY b%10,a $window ) FROM t2 241 + " 242 + execsql_test 1.$tn.11.6 " 243 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2 244 + " 226 245 } 227 246 228 247 finish_test 229 248
Changes to test/window3.test.
317 317 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 318 318 } {0 {} {} {} {} {} {} {} {} {} {} {} {} 90 {} {} {} {} {} {} {} 90 {} {} {} 90 1 30 {} {} {} 31 30 {} {} {} 1 40 {} 50 11 81 42 40 {} 50 81 40 {} {} 50 {} 52 {} 41 81 {} 41 {} 2 30 2 81 82 53 {} 10 {} {} 81 {} 41 10 81 30 81 {} 3 3 23 {} 3 61 80 {} 94 3 91 91 72 3 63 30 91 94 94 72 91 73 91 84 84 33 41 1 33 84 73 73 91 20 41 84 33 33 84 33 41 84 21 44 22 90 22 81 81 74 93 93 93 81 21 83 44 44 21 21 21 13 21 21 34 11 34 73 74 2 60 2 34 2 34 74 60 23 2 2 2 11 91 60 62 73 74 70 51 65 74 93 65 70 34 70 93 93 93 62 35 44 43 12 35 41 43 44 44 41 80 54 72 43 41 43 91 12 80 80 35 33 12} 319 319 320 320 do_execsql_test 1.1.10.6 { 321 321 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 322 322 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 323 323 324 +do_execsql_test 1.1.11.1 { 325 + SELECT first_value(b) OVER (ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 326 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 327 + 328 +do_execsql_test 1.1.11.2 { 329 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 330 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 331 + 332 +do_execsql_test 1.1.11.3 { 333 + SELECT first_value(b) OVER ( ORDER BY b,a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 334 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 335 + 336 +do_execsql_test 1.1.11.4 { 337 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 338 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9} 339 + 340 +do_execsql_test 1.1.11.5 { 341 + SELECT first_value(b) OVER ( ORDER BY b%10,a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 342 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 343 + 344 +do_execsql_test 1.1.11.6 { 345 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 346 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 347 + 324 348 do_execsql_test 1.2.2.1 { 325 349 SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 326 350 } {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 327 351 328 352 do_execsql_test 1.2.2.2 { 329 353 SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 330 354 } {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} ................................................................................ 581 605 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 582 606 } {0 74 32 31 84 91 74 3 93 84 74 31 12 90 31 22 74 64 43 64 64 90 74 22 43 90 1 30 62 22 31 31 30 74 64 64 1 40 33 50 11 81 42 40 13 50 81 40 13 13 50 33 52 24 41 81 34 41 34 2 30 2 81 82 53 33 10 33 33 81 34 41 10 81 30 81 4 3 3 23 94 3 61 80 84 94 3 91 91 72 3 63 30 91 94 94 72 91 73 91 84 84 33 41 1 33 84 73 73 91 20 41 84 33 33 84 33 41 84 21 44 22 90 22 81 81 74 93 93 93 81 21 83 44 44 21 21 21 13 21 21 34 11 34 73 74 2 60 2 34 2 34 74 60 23 2 2 2 11 91 60 62 73 74 70 51 65 74 93 65 70 34 70 93 93 93 62 35 44 43 12 35 41 43 44 44 41 80 54 72 43 41 43 91 12 80 80 35 33 12} 583 607 584 608 do_execsql_test 1.2.10.6 { 585 609 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 586 610 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 587 611 612 +do_execsql_test 1.2.11.1 { 613 + SELECT first_value(b) OVER (ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 614 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 615 + 616 +do_execsql_test 1.2.11.2 { 617 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 618 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 619 + 620 +do_execsql_test 1.2.11.3 { 621 + SELECT first_value(b) OVER ( ORDER BY b,a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 622 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 623 + 624 +do_execsql_test 1.2.11.4 { 625 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 626 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9} 627 + 628 +do_execsql_test 1.2.11.5 { 629 + SELECT first_value(b) OVER ( ORDER BY b%10,a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 630 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 631 + 632 +do_execsql_test 1.2.11.6 { 633 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 634 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 635 + 588 636 do_execsql_test 1.3.2.1 { 589 637 SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 590 638 } {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} 591 639 592 640 do_execsql_test 1.3.2.2 { 593 641 SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 594 642 } {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} ................................................................................ 845 893 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a RANGE BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 846 894 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 847 895 848 896 do_execsql_test 1.3.10.6 { 849 897 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM t2 850 898 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 851 899 900 +do_execsql_test 1.3.11.1 { 901 + SELECT first_value(b) OVER (ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM t2 902 +} {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} 903 + 904 +do_execsql_test 1.3.11.2 { 905 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM t2 906 +} {0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39} 907 + 908 +do_execsql_test 1.3.11.3 { 909 + SELECT first_value(b) OVER ( ORDER BY b,a RANGE BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 910 +} {0 1 1 2 2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98 98 99 99 99} 911 + 912 +do_execsql_test 1.3.11.4 { 913 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a RANGE BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 914 +} {0 10 20 30 30 30 40 50 60 70 80 80 90 90 90 1 1 11 11 21 21 31 31 41 41 41 51 61 61 81 81 81 91 91 91 91 91 2 2 2 12 12 12 22 22 32 42 52 62 62 72 72 72 82 3 3 13 13 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 83 93 93 93 4 14 24 34 34 34 34 44 44 54 64 74 74 74 74 74 84 84 84 84 94 5 15 15 15 25 35 35 55 55 65 65 65 75 75 75 85 85 85 95 95 95 6 16 16 16 26 26 26 36 36 36 36 46 46 56 56 56 66 76 86 96 96 96 7 7 7 17 27 27 37 37 47 47 47 47 57 67 77 77 87 87 97 97 8 8 8 28 38 38 58 58 58 58 68 78 78 88 98 98 9 9 9 19 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89 89 99 99 99} 915 + 916 +do_execsql_test 1.3.11.5 { 917 + SELECT first_value(b) OVER ( ORDER BY b%10,a RANGE BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 918 +} {0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39} 919 + 920 +do_execsql_test 1.3.11.6 { 921 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM t2 922 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 923 + 852 924 do_execsql_test 1.4.2.1 { 853 925 SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 854 926 } {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 95 95 84 84 84 84 84 84 84 84 83 83 83 83 83 83 83 83 83 82 82 17 7} 855 927 856 928 do_execsql_test 1.4.2.2 { 857 929 SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 858 930 } {0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7} ................................................................................ 1109 1181 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 1110 1182 } {0 64 42 81 94 1 44 73 74 44 85 22 33 41 72 93 34 65 54 5 15 81 15 63 84 21 2 62 54 93 43 33 2 75 16 86 23 12 85 62 13 53 94 12 75 23 73 72 96 46 33 55 25 26 74 34 87 84 87 55 53 25 84 75 56 66 54 36 97 55 78 84 84 95 74 65 17 87 77 96 8 47 95 34 89 98 7 46 6 96 8 47 95 56 89 59 36 36 78 96 89 29 37 95 56 39 {} 8 58 67 85 86 {} 58 49 {} 29 76 {} 77 {} 78 56 98 36 97 {} 59 89 89 47 78 {} {} {} 38 68 58 {} 58 38 {} 98 {} {} {} 19 57 9 {} 9 {} {} 7 {} {} {} 39 89 38 78 39 {} {} 8 19 {} {} {} {} 89 {} 39 {} {} {} {} {} {} {} {} {} {} {} {} {} {} 9 {} {} {} {} {} {} {} {} {} {} {} {}} 1111 1183 1112 1184 do_execsql_test 1.4.10.6 { 1113 1185 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t2 1114 1186 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 1115 1187 1188 +do_execsql_test 1.4.11.1 { 1189 + SELECT first_value(b) OVER (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t2 1190 +} {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} 1191 + 1192 +do_execsql_test 1.4.11.2 { 1193 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t2 1194 +} {0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39} 1195 + 1196 +do_execsql_test 1.4.11.3 { 1197 + SELECT first_value(b) OVER ( ORDER BY b,a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 1198 +} {0 1 1 2 2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98 98 99 99 99} 1199 + 1200 +do_execsql_test 1.4.11.4 { 1201 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 1202 +} {0 10 20 30 30 30 40 50 60 70 80 80 90 90 90 1 1 11 11 21 21 31 31 41 41 41 51 61 61 81 81 81 91 91 91 91 91 2 2 2 12 12 12 22 22 32 42 52 62 62 72 72 72 82 3 3 13 13 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 83 93 93 93 4 14 24 34 34 34 34 44 44 54 64 74 74 74 74 74 84 84 84 84 94 5 15 15 15 25 35 35 55 55 65 65 65 75 75 75 85 85 85 95 95 95 6 16 16 16 26 26 26 36 36 36 36 46 46 56 56 56 66 76 86 96 96 96 7 7 7 17 27 27 37 37 47 47 47 47 57 67 77 77 87 87 97 97 8 8 8 28 38 38 58 58 58 58 68 78 78 88 98 98 9 9 9 19 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89 89 99 99 99} 1203 + 1204 +do_execsql_test 1.4.11.5 { 1205 + SELECT first_value(b) OVER ( ORDER BY b%10,a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 1206 +} {0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39} 1207 + 1208 +do_execsql_test 1.4.11.6 { 1209 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t2 1210 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 1211 + 1116 1212 do_execsql_test 1.5.2.1 { 1117 1213 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING ) FROM t2 1118 1214 } {{} {} {} {} 0 74 74 74 74 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 1119 1215 1120 1216 do_execsql_test 1.5.2.2 { 1121 1217 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING ) FROM t2 1122 1218 } {{} {} {} {} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} ................................................................................ 1373 1469 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING ) FROM t2 1374 1470 } {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 90 {} {} {} 90 1 30 {} {} {} {} 30 {} {} {} 1 40 {} 50 11 81 {} 40 {} 50 81 40 {} {} 50 {} {} {} 41 81 {} 41 {} 2 30 2 81 82 {} {} 10 {} {} 81 {} 41 10 81 30 81 {} {} 3 23 {} 3 61 80 {} {} 3 91 91 72 3 63 30 91 94 94 72 91 73 91 84 84 33 41 1 33 84 73 73 91 20 41 84 33 33 84 33 41 84 21 44 22 90 22 81 81 74 93 93 93 81 21 83 44 44 21 21 21 13 21 21 34 11 34 73 74 2 60 2 34 2 34 74 60 23 2 2 2 11 91 60 62 73 74 70 51 65 74 93 65 70 34 70 93 93 93 62 35 44 43 12 35 41 43 44 44 41 80 54 72 43 41 43 91 12 80 80 35 33 12} 1375 1471 1376 1472 do_execsql_test 1.5.10.6 { 1377 1473 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING) FROM t2 1378 1474 } {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 1379 1475 1476 +do_execsql_test 1.5.11.1 { 1477 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING) FROM t2 1478 +} {{} {} {} {} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 1479 + 1480 +do_execsql_test 1.5.11.2 { 1481 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING) FROM t2 1482 +} {{} {} {} {} 0 0 0 0 0 0 0 0 0 0 0 {} {} {} {} 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 {} {} {} {} 2 2 2 2 2 2 2 2 2 2 2 2 2 {} {} {} {} 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 {} {} {} {} 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 {} {} {} {} 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 {} {} {} {} 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 {} {} {} {} 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 {} {} {} {} 38 38 38 38 38 38 38 38 38 38 38 38 {} {} {} {} 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 1483 + 1484 +do_execsql_test 1.5.11.3 { 1485 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING ) FROM t2 1486 +} {{} {} {} {} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 1487 + 1488 +do_execsql_test 1.5.11.4 { 1489 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING ) FROM t2 1490 +} {{} {} {} {} 0 0 0 0 0 0 0 0 0 0 0 {} {} {} {} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 {} {} {} {} 2 2 2 2 2 2 2 2 2 2 2 2 2 {} {} {} {} 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 {} {} {} {} 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 {} {} {} {} 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 {} {} {} {} 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 {} {} {} {} 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 {} {} {} {} 8 8 8 8 8 8 8 8 8 8 8 8 {} {} {} {} 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9} 1491 + 1492 +do_execsql_test 1.5.11.5 { 1493 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING ) FROM t2 1494 +} {{} {} {} {} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 1495 + 1496 +do_execsql_test 1.5.11.6 { 1497 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING) FROM t2 1498 +} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 1499 + 1380 1500 do_execsql_test 1.6.2.1 { 1381 1501 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING ) FROM t2 1382 1502 } {{} {} 0 74 74 74 74 99 99 99 33 89 89 96 96 96 68 68 68 91 91 91 99 99 99 97 78 78 97 97 97 67 93 93 93 84 77 23 93 93 93 65 47 86 86 86 91 91 91 85 85 85 59 59 56 56 91 91 91 90 90 55 89 89 89 47 56 56 56 56 56 75 75 89 98 98 98 81 94 94 94 78 78 78 53 63 63 87 87 87 84 84 84 72 61 73 95 95 95 65 96 98 98 98 74 74 74 65 73 73 73 87 87 87 41 20 31 31 31 95 95 95 79 88 88 88 34 49 49 90 90 96 96 96 75 77 77 77 44 85 85 85 74 74 70 70 59 39 39 47 80 90 90 90 58 58 72 72 72 72 93 93 93 81 81 81 37 37 37 14 62 91 91 91 91 91 34 36 99 99 99 95 95 69 58 52 84 84 84 84 84 39 44 58 58 58 38 83 83 83} 1383 1503 1384 1504 do_execsql_test 1.6.2.2 { 1385 1505 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING ) FROM t2 1386 1506 } {{} {} 0 0 0 41 23 23 23 26 2 2 2 81 59 38 38 38 39 39 46 6 6 6 27 27 27 46 54 8 8 8 29 29 77 23 16 16 16 16 35 35 7 7 7 61 61 61 24 24 24 43 12 12 12 3 3 3 22 22 15 15 15 25 25 1 1 1 40 40 16 16 16 36 36 76 76 4 4 4 30 30 30 29 29 29 2 2 2 37 37 72 41 9 9 9 61 65 13 13 13 58 1 1 1 21 35 5 5 5 11 11 41 12 8 8 8 20 15 15 15 22 22 73 34 8 8 8 11 34 34 59 59 55 55 55 44 2 2 2 7 57 29 29 29 19 19 19 26 26 26 47 36 36 36 9 9 9 66 33 33 33 64 64 9 9 9 13 12 12 12 14 36 36 33 15 15 15 34 3 3 3 58 52 30 30 30 10 10 10 21 21 21 39 30 30 30 34 27 27} ................................................................................ 1637 1757 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING ) FROM t2 1638 1758 } {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 61 {} {} {} 81 {} {} {} {} {} {} {} {} {} {} {} 91 {} {} {} {} {} 22 {} {} {} 12 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 1639 1759 1640 1760 do_execsql_test 1.6.10.6 { 1641 1761 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) FROM t2 1642 1762 } {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 1643 1763 1764 +do_execsql_test 1.6.11.1 { 1765 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) FROM t2 1766 +} {{} {} 0 0 0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83} 1767 + 1768 +do_execsql_test 1.6.11.2 { 1769 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) FROM t2 1770 +} {{} {} 0 0 0 90 40 30 80 20 90 60 70 80 90 {} {} 41 41 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 {} {} 2 2 2 62 12 32 22 42 2 72 12 22 2 72 72 {} {} 23 23 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 {} {} 74 74 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 {} {} 65 65 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 {} {} 26 26 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 {} {} 97 97 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 {} {} 38 38 38 68 78 8 28 98 78 58 98 8 88 8 {} {} 99 99 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9} 1771 + 1772 +do_execsql_test 1.6.11.3 { 1773 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING ) FROM t2 1774 +} {{} {} 0 0 0 1 1 2 2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98} 1775 + 1776 +do_execsql_test 1.6.11.4 { 1777 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING ) FROM t2 1778 +} {{} {} 0 0 0 10 20 30 30 30 40 50 60 70 80 {} {} 1 1 1 1 11 11 21 21 31 31 41 41 41 51 61 61 81 81 81 91 {} {} 2 2 2 2 2 12 12 12 22 22 32 42 52 62 62 {} {} 3 3 3 3 13 13 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 {} {} 4 4 4 14 24 34 34 34 34 44 44 54 64 74 74 74 74 74 84 {} {} 5 5 5 15 15 15 25 35 35 55 55 65 65 65 75 75 75 85 85 {} {} 6 6 6 16 16 16 26 26 26 36 36 36 36 46 46 56 56 56 66 76 {} {} 7 7 7 7 7 17 27 27 37 37 47 47 47 47 57 67 77 77 {} {} 8 8 8 8 8 28 38 38 58 58 58 58 68 78 {} {} 9 9 9 9 9 19 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89} 1779 + 1780 +do_execsql_test 1.6.11.5 { 1781 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING ) FROM t2 1782 +} {{} {} 0 0 0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9} 1783 + 1784 +do_execsql_test 1.6.11.6 { 1785 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) FROM t2 1786 +} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 1787 + 1644 1788 do_execsql_test 1.7.2.1 { 1645 1789 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 1646 1790 } {0 74 74 74 74 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 1647 1791 1648 1792 do_execsql_test 1.7.2.2 { 1649 1793 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 1650 1794 } {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} ................................................................................ 1901 2045 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 1902 2046 } {0 {} {} {} {} {} {} {} {} {} {} {} {} 90 {} {} {} {} {} {} {} 90 {} {} {} 90 1 30 {} {} {} 31 30 {} {} {} 1 40 {} 50 11 81 42 40 {} 50 81 40 {} {} 50 {} 52 {} 41 81 {} 41 {} 2 30 2 81 82 53 {} 10 {} {} 81 {} 41 10 81 30 81 {} 3 3 23 {} 3 61 80 {} 94 3 91 91 72 3 63 30 91 94 94 72 91 73 91 84 84 33 41 1 33 84 73 73 91 20 41 84 33 33 84 33 41 84 21 44 22 90 22 81 81 74 93 93 93 81 21 83 44 44 21 21 21 13 21 21 34 11 34 73 74 2 60 2 34 2 34 74 60 23 2 2 2 11 91 60 62 73 74 70 51 65 74 93 65 70 34 70 93 93 93 62 35 44 43 12 35 41 43 44 44 41 80 54 72 43 41 43 91 12 80 80 35 33 12} 1903 2047 1904 2048 do_execsql_test 1.7.10.6 { 1905 2049 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 1906 2050 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 1907 2051 2052 +do_execsql_test 1.7.11.1 { 2053 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 2054 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 2055 + 2056 +do_execsql_test 1.7.11.2 { 2057 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 2058 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 2059 + 2060 +do_execsql_test 1.7.11.3 { 2061 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 2062 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 2063 + 2064 +do_execsql_test 1.7.11.4 { 2065 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 2066 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9} 2067 + 2068 +do_execsql_test 1.7.11.5 { 2069 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 2070 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 2071 + 2072 +do_execsql_test 1.7.11.6 { 2073 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 2074 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 2075 + 1908 2076 do_execsql_test 1.8.2.1 { 1909 2077 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) FROM t2 1910 2078 } {0 74 74 74 74 99 99 99 99 99 89 96 96 96 96 96 68 91 91 91 99 99 99 99 99 97 97 97 97 97 97 93 93 93 93 84 93 93 93 93 93 86 86 86 91 91 91 91 91 85 85 85 59 59 91 91 91 91 91 90 90 89 89 89 89 56 56 56 56 75 75 89 98 98 98 98 98 94 94 94 94 78 78 78 63 87 87 87 87 87 84 84 84 73 95 95 95 95 96 98 98 98 98 98 74 74 74 73 73 87 87 87 87 87 41 31 31 95 95 95 95 95 88 88 88 88 49 90 90 96 96 96 96 96 77 77 77 85 85 85 85 85 74 74 70 70 59 47 80 90 90 90 90 90 72 72 72 72 93 93 93 93 93 81 81 81 37 37 62 91 91 91 91 91 91 91 99 99 99 99 99 95 95 69 84 84 84 84 84 84 84 58 58 58 58 83 83 83 83 83} 1911 2079 1912 2080 do_execsql_test 1.8.2.2 { 1913 2081 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) FROM t2 1914 2082 } {0 0 0 0 0 23 23 23 2 2 2 2 2 38 38 38 38 38 39 6 6 6 6 6 27 27 27 8 8 8 8 8 29 23 16 16 16 16 16 16 7 7 7 7 7 61 24 24 24 24 12 12 12 3 3 3 3 3 15 15 15 15 15 1 1 1 1 1 16 16 16 16 16 36 36 4 4 4 4 4 30 29 29 29 2 2 2 2 2 37 37 9 9 9 9 9 13 13 13 13 1 1 1 1 1 5 5 5 5 5 11 11 8 8 8 8 8 15 15 15 15 22 22 8 8 8 8 8 11 34 34 55 55 55 44 2 2 2 2 2 7 29 29 19 19 19 19 19 26 26 26 36 36 9 9 9 9 9 33 33 33 33 9 9 9 9 9 12 12 12 12 14 33 15 15 15 15 3 3 3 3 3 30 30 30 10 10 10 10 10 21 21 21 30 30 30 27 27 17 7} ................................................................................ 2165 2333 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) FROM t2 2166 2334 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 61 {} {} {} 81 {} {} {} {} {} {} {} {} {} {} {} 91 {} {} {} {} {} 22 {} {} {} 12 {} {} {} {} {} {} {} {} {} {} {} {} 43 {} {} {} {} {} {} {} {} {} {} {} {} {} 33 {} {} {} {} {} {} {} {} 4 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 2167 2335 2168 2336 do_execsql_test 1.8.10.6 { 2169 2337 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) FROM t2 2170 2338 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 2171 2339 2340 +do_execsql_test 1.8.11.1 { 2341 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) FROM t2 2342 +} {0 0 0 0 0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83} 2343 + 2344 +do_execsql_test 1.8.11.2 { 2345 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) FROM t2 2346 +} {0 0 0 0 0 90 40 30 80 20 90 60 70 80 90 41 41 41 41 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 2 2 2 2 2 62 12 32 22 42 2 72 12 22 2 72 72 23 23 23 23 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 74 74 74 74 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 65 65 65 65 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 26 26 26 26 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 97 97 97 97 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 38 38 38 38 38 68 78 8 28 98 78 58 98 8 88 8 99 99 99 99 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9} 2347 + 2348 +do_execsql_test 1.8.11.3 { 2349 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) FROM t2 2350 +} {0 0 0 0 0 1 1 2 2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98} 2351 + 2352 +do_execsql_test 1.8.11.4 { 2353 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) FROM t2 2354 +} {0 0 0 0 0 10 20 30 30 30 40 50 60 70 80 1 1 1 1 1 1 11 11 21 21 31 31 41 41 41 51 61 61 81 81 81 91 2 2 2 2 2 2 2 12 12 12 22 22 32 42 52 62 62 3 3 3 3 3 3 13 13 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 4 4 4 4 4 14 24 34 34 34 34 44 44 54 64 74 74 74 74 74 84 5 5 5 5 5 15 15 15 25 35 35 55 55 65 65 65 75 75 75 85 85 6 6 6 6 6 16 16 16 26 26 26 36 36 36 36 46 46 56 56 56 66 76 7 7 7 7 7 7 7 17 27 27 37 37 47 47 47 47 57 67 77 77 8 8 8 8 8 8 8 28 38 38 58 58 58 58 68 78 9 9 9 9 9 9 9 19 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89} 2355 + 2356 +do_execsql_test 1.8.11.5 { 2357 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) FROM t2 2358 +} {0 0 0 0 0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9} 2359 + 2360 +do_execsql_test 1.8.11.6 { 2361 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) FROM t2 2362 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 2363 + 2172 2364 do_execsql_test 1.9.2.1 { 2173 2365 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 2174 2366 } {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} 2175 2367 2176 2368 do_execsql_test 1.9.2.2 { 2177 2369 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 2178 2370 } {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} ................................................................................ 2429 2621 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 2430 2622 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 2431 2623 2432 2624 do_execsql_test 1.9.10.6 { 2433 2625 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM t2 2434 2626 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 2435 2627 2628 +do_execsql_test 1.9.11.1 { 2629 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM t2 2630 +} {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} 2631 + 2632 +do_execsql_test 1.9.11.2 { 2633 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM t2 2634 +} {0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39} 2635 + 2636 +do_execsql_test 1.9.11.3 { 2637 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 2638 +} {0 1 1 2 2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98 98 99 99 99} 2639 + 2640 +do_execsql_test 1.9.11.4 { 2641 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 2642 +} {0 10 20 30 30 30 40 50 60 70 80 80 90 90 90 1 1 11 11 21 21 31 31 41 41 41 51 61 61 81 81 81 91 91 91 91 91 2 2 2 12 12 12 22 22 32 42 52 62 62 72 72 72 82 3 3 13 13 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 83 93 93 93 4 14 24 34 34 34 34 44 44 54 64 74 74 74 74 74 84 84 84 84 94 5 15 15 15 25 35 35 55 55 65 65 65 75 75 75 85 85 85 95 95 95 6 16 16 16 26 26 26 36 36 36 36 46 46 56 56 56 66 76 86 96 96 96 7 7 7 17 27 27 37 37 47 47 47 47 57 67 77 77 87 87 97 97 8 8 8 28 38 38 58 58 58 58 68 78 78 88 98 98 9 9 9 19 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89 89 99 99 99} 2643 + 2644 +do_execsql_test 1.9.11.5 { 2645 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 2646 +} {0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39} 2647 + 2648 +do_execsql_test 1.9.11.6 { 2649 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM t2 2650 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 2651 + 2436 2652 do_execsql_test 1.10.2.1 { 2437 2653 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING ) FROM t2 2438 2654 } {74 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 2439 2655 2440 2656 do_execsql_test 1.10.2.2 { 2441 2657 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING ) FROM t2 2442 2658 } {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} ................................................................................ 2693 2909 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING ) FROM t2 2694 2910 } {0 {} {} {} {} {} {} {} {} {} {} {} {} 90 {} {} {} {} {} {} {} 90 {} {} {} 90 1 30 {} {} 31 31 30 {} {} {} 1 40 {} 50 11 81 42 40 {} 50 81 40 {} {} 50 {} 52 {} 41 81 {} 41 {} 2 30 2 81 82 53 {} 10 {} {} 81 {} 41 10 81 30 81 {} 3 3 23 94 3 61 80 {} 94 3 91 91 72 3 63 30 91 94 94 72 91 73 91 84 84 33 41 1 33 84 73 73 91 20 41 84 33 33 84 33 41 84 21 44 22 90 22 81 81 74 93 93 93 81 21 83 44 44 21 21 21 13 21 21 34 11 34 73 74 2 60 2 34 2 34 74 60 23 2 2 2 11 91 60 62 73 74 70 51 65 74 93 65 70 34 70 93 93 93 62 35 44 43 12 35 41 43 44 44 41 80 54 72 43 41 43 91 12 80 80 35 33 12} 2695 2911 2696 2912 do_execsql_test 1.10.10.6 { 2697 2913 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING) FROM t2 2698 2914 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 2699 2915 2916 +do_execsql_test 1.10.11.1 { 2917 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING) FROM t2 2918 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 2919 + 2920 +do_execsql_test 1.10.11.2 { 2921 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING) FROM t2 2922 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 2923 + 2924 +do_execsql_test 1.10.11.3 { 2925 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING ) FROM t2 2926 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 2927 + 2928 +do_execsql_test 1.10.11.4 { 2929 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING ) FROM t2 2930 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9} 2931 + 2932 +do_execsql_test 1.10.11.5 { 2933 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING ) FROM t2 2934 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 2935 + 2936 +do_execsql_test 1.10.11.6 { 2937 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING) FROM t2 2938 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 2939 + 2700 2940 do_execsql_test 1.11.2.1 { 2701 2941 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING ) FROM t2 2702 2942 } {74 74 74 99 99 99 99 99 99 99 96 96 96 96 96 96 91 91 99 99 99 99 99 99 99 97 97 97 97 97 97 93 93 93 93 93 93 93 93 93 93 86 91 91 91 91 91 91 91 85 85 85 91 91 91 91 91 91 91 90 90 89 89 89 89 56 56 75 75 89 98 98 98 98 98 98 98 94 94 94 94 78 78 87 87 87 87 87 87 87 84 84 95 95 95 95 96 98 98 98 98 98 98 98 74 74 74 87 87 87 87 87 87 87 41 95 95 95 95 95 95 95 88 88 88 90 90 96 96 96 96 96 96 96 77 85 85 85 85 85 85 85 74 74 70 70 80 90 90 90 90 90 90 90 72 72 93 93 93 93 93 93 93 81 81 81 62 91 91 91 91 91 91 91 99 99 99 99 99 99 99 95 95 84 84 84 84 84 84 84 84 58 58 83 83 83 83 83 83 83} 2703 2943 2704 2944 do_execsql_test 1.11.2.2 { 2705 2945 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING ) FROM t2 2706 2946 } {0 0 0 0 0 23 2 2 2 2 2 2 2 38 38 38 38 6 6 6 6 6 6 6 27 8 8 8 8 8 8 8 16 16 16 16 16 16 7 7 7 7 7 7 7 24 24 24 12 12 12 3 3 3 3 3 3 3 15 15 15 1 1 1 1 1 1 1 16 16 16 16 16 4 4 4 4 4 4 4 29 29 2 2 2 2 2 2 2 9 9 9 9 9 9 9 13 13 1 1 1 1 1 1 1 5 5 5 5 5 8 8 8 8 8 8 8 15 15 15 15 8 8 8 8 8 8 8 11 34 34 55 44 2 2 2 2 2 2 2 7 19 19 19 19 19 19 19 26 26 26 9 9 9 9 9 9 9 33 33 9 9 9 9 9 9 9 12 12 12 12 14 15 15 15 3 3 3 3 3 3 3 30 10 10 10 10 10 10 10 21 21 21 30 27 27 17 7 7 7} ................................................................................ 2957 3197 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING ) FROM t2 2958 3198 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 61 {} {} {} 81 {} {} {} {} {} {} {} {} {} {} {} 91 {} {} {} {} {} 22 {} {} {} 12 {} {} {} {} {} {} {} {} {} {} {} {} 43 {} {} {} {} {} {} {} {} {} {} {} {} {} 33 {} {} {} {} {} {} {} {} 4 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 15 {} {} {} {} {} {} {} {} {} {} {} 16 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 2959 3199 2960 3200 do_execsql_test 1.11.10.6 { 2961 3201 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING) FROM t2 2962 3202 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 2963 3203 3204 +do_execsql_test 1.11.11.1 { 3205 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING) FROM t2 3206 +} {0 0 0 0 0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83} 3207 + 3208 +do_execsql_test 1.11.11.2 { 3209 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING) FROM t2 3210 +} {0 0 0 0 0 90 40 30 80 20 90 60 70 80 90 41 41 41 41 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 2 2 2 2 2 62 12 32 22 42 2 72 12 22 2 72 72 23 23 23 23 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 74 74 74 74 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 65 65 65 65 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 26 26 26 26 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 97 97 97 97 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 38 38 38 38 38 68 78 8 28 98 78 58 98 8 88 8 99 99 99 99 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9} 3211 + 3212 +do_execsql_test 1.11.11.3 { 3213 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING ) FROM t2 3214 +} {0 0 0 0 0 1 1 2 2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98} 3215 + 3216 +do_execsql_test 1.11.11.4 { 3217 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING ) FROM t2 3218 +} {0 0 0 0 0 10 20 30 30 30 40 50 60 70 80 1 1 1 1 1 1 11 11 21 21 31 31 41 41 41 51 61 61 81 81 81 91 2 2 2 2 2 2 2 12 12 12 22 22 32 42 52 62 62 3 3 3 3 3 3 13 13 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 4 4 4 4 4 14 24 34 34 34 34 44 44 54 64 74 74 74 74 74 84 5 5 5 5 5 15 15 15 25 35 35 55 55 65 65 65 75 75 75 85 85 6 6 6 6 6 16 16 16 26 26 26 36 36 36 36 46 46 56 56 56 66 76 7 7 7 7 7 7 7 17 27 27 37 37 47 47 47 47 57 67 77 77 8 8 8 8 8 8 8 28 38 38 58 58 58 58 68 78 9 9 9 9 9 9 9 19 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89} 3219 + 3220 +do_execsql_test 1.11.11.5 { 3221 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING ) FROM t2 3222 +} {0 0 0 0 0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9} 3223 + 3224 +do_execsql_test 1.11.11.6 { 3225 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING) FROM t2 3226 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 3227 + 2964 3228 do_execsql_test 1.12.2.1 { 2965 3229 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) FROM t2 2966 3230 } {74 99 99 99 99 99 89 96 96 96 96 96 68 91 91 91 99 99 99 99 99 97 97 97 97 97 97 93 93 93 93 84 93 93 93 93 93 86 86 86 91 91 91 91 91 85 85 85 59 59 91 91 91 91 91 90 90 89 89 89 89 56 56 56 56 75 75 89 98 98 98 98 98 94 94 94 94 78 78 78 63 87 87 87 87 87 84 84 84 73 95 95 95 95 96 98 98 98 98 98 74 74 74 73 73 87 87 87 87 87 41 31 31 95 95 95 95 95 88 88 88 88 49 90 90 96 96 96 96 96 77 77 77 85 85 85 85 85 74 74 70 70 59 47 80 90 90 90 90 90 72 72 72 72 93 93 93 93 93 81 81 81 37 37 62 91 91 91 91 91 91 91 99 99 99 99 99 95 95 69 84 84 84 84 84 84 84 58 58 58 58 83 83 83 83 83 82 82 17 7} 2967 3231 2968 3232 do_execsql_test 1.12.2.2 { 2969 3233 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) FROM t2 2970 3234 } {0 23 23 23 2 2 2 2 2 38 38 38 38 38 39 6 6 6 6 6 27 27 27 8 8 8 8 8 29 23 16 16 16 16 16 16 7 7 7 7 7 61 24 24 24 24 12 12 12 3 3 3 3 3 15 15 15 15 15 1 1 1 1 1 16 16 16 16 16 36 36 4 4 4 4 4 30 29 29 29 2 2 2 2 2 37 37 9 9 9 9 9 13 13 13 13 1 1 1 1 1 5 5 5 5 5 11 11 8 8 8 8 8 15 15 15 15 22 22 8 8 8 8 8 11 34 34 55 55 55 44 2 2 2 2 2 7 29 29 19 19 19 19 19 26 26 26 36 36 9 9 9 9 9 33 33 33 33 9 9 9 9 9 12 12 12 12 14 33 15 15 15 15 3 3 3 3 3 30 30 30 10 10 10 10 10 21 21 21 30 30 30 27 27 17 7 7 7 7 7} ................................................................................ 3221 3485 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) FROM t2 3222 3486 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 81 {} {} {} 21 {} {} {} {} {} {} {} {} {} {} {} 12 {} {} {} {} {} 12 {} {} {} 72 {} {} {} {} {} {} {} {} {} {} {} {} 53 {} {} {} {} {} {} {} {} {} {} {} {} {} 74 {} {} {} {} {} {} {} {} 34 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 3223 3487 3224 3488 do_execsql_test 1.12.10.6 { 3225 3489 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) FROM t2 3226 3490 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 3227 3491 3492 +do_execsql_test 1.12.11.1 { 3493 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) FROM t2 3494 +} {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} 3495 + 3496 +do_execsql_test 1.12.11.2 { 3497 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) FROM t2 3498 +} {0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39} 3499 + 3500 +do_execsql_test 1.12.11.3 { 3501 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) FROM t2 3502 +} {0 1 1 2 2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98 98 99 99 99} 3503 + 3504 +do_execsql_test 1.12.11.4 { 3505 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) FROM t2 3506 +} {0 10 20 30 30 30 40 50 60 70 80 80 90 90 90 1 1 11 11 21 21 31 31 41 41 41 51 61 61 81 81 81 91 91 91 91 91 2 2 2 12 12 12 22 22 32 42 52 62 62 72 72 72 82 3 3 13 13 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 83 93 93 93 4 14 24 34 34 34 34 44 44 54 64 74 74 74 74 74 84 84 84 84 94 5 15 15 15 25 35 35 55 55 65 65 65 75 75 75 85 85 85 95 95 95 6 16 16 16 26 26 26 36 36 36 36 46 46 56 56 56 66 76 86 96 96 96 7 7 7 17 27 27 37 37 47 47 47 47 57 67 77 77 87 87 97 97 8 8 8 28 38 38 58 58 58 58 68 78 78 88 98 98 9 9 9 19 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89 89 99 99 99} 3507 + 3508 +do_execsql_test 1.12.11.5 { 3509 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) FROM t2 3510 +} {0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39} 3511 + 3512 +do_execsql_test 1.12.11.6 { 3513 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) FROM t2 3514 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 3515 + 3228 3516 do_execsql_test 1.13.2.1 { 3229 3517 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING ) FROM t2 3230 3518 } {74 99 99 99 33 89 89 96 96 96 68 68 68 91 91 91 99 99 99 97 78 78 97 97 97 67 93 93 93 84 77 23 93 93 93 65 47 86 86 86 91 91 91 85 85 85 59 59 56 56 91 91 91 90 90 55 89 89 89 47 56 56 56 56 56 75 75 89 98 98 98 81 94 94 94 78 78 78 53 63 63 87 87 87 84 84 84 72 61 73 95 95 95 65 96 98 98 98 74 74 74 65 73 73 73 87 87 87 41 20 31 31 31 95 95 95 79 88 88 88 34 49 49 90 90 96 96 96 75 77 77 77 44 85 85 85 74 74 70 70 59 39 39 47 80 90 90 90 58 58 72 72 72 72 93 93 93 81 81 81 37 37 37 14 62 91 91 91 91 91 34 36 99 99 99 95 95 69 58 52 84 84 84 84 84 39 44 58 58 58 38 83 83 83 82 82 17 7 {} {}} 3231 3519 3232 3520 do_execsql_test 1.13.2.2 { 3233 3521 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING ) FROM t2 3234 3522 } {23 23 23 26 2 2 2 81 59 38 38 38 39 39 46 6 6 6 27 27 27 46 54 8 8 8 29 29 77 23 16 16 16 16 35 35 7 7 7 61 61 61 24 24 24 43 12 12 12 3 3 3 22 22 15 15 15 25 25 1 1 1 40 40 16 16 16 36 36 76 76 4 4 4 30 30 30 29 29 29 2 2 2 37 37 72 41 9 9 9 61 65 13 13 13 58 1 1 1 21 35 5 5 5 11 11 41 12 8 8 8 20 15 15 15 22 22 73 34 8 8 8 11 34 34 59 59 55 55 55 44 2 2 2 7 57 29 29 29 19 19 19 26 26 26 47 36 36 36 9 9 9 66 33 33 33 64 64 9 9 9 13 12 12 12 14 36 36 33 15 15 15 34 3 3 3 58 52 30 30 30 10 10 10 21 21 21 39 30 30 30 34 27 27 17 7 7 7 {} {}} ................................................................................ 3485 3773 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING ) FROM t2 3486 3774 } {40 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 61 {} {} {} 51 {} {} {} {} {} {} {} {} {} {} {} 22 {} {} {} {} {} 2 {} {} {} 62 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 3487 3775 3488 3776 do_execsql_test 1.13.10.6 { 3489 3777 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) FROM t2 3490 3778 } {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 3491 3779 3780 +do_execsql_test 1.13.11.1 { 3781 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) FROM t2 3782 +} {41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7 {} {}} 3783 + 3784 +do_execsql_test 1.13.11.2 { 3785 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) FROM t2 3786 +} {40 30 80 20 90 60 70 80 90 30 50 10 30 {} {} 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 {} {} 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 {} {} 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 {} {} 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 {} {} 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 {} {} 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 {} {} 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 {} {} 78 8 28 98 78 58 98 8 88 8 58 58 58 38 {} {} 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39 {} {}} 3787 + 3788 +do_execsql_test 1.13.11.3 { 3789 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING ) FROM t2 3790 +} {1 2 2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98 98 99 99 99 {} {}} 3791 + 3792 +do_execsql_test 1.13.11.4 { 3793 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING ) FROM t2 3794 +} {20 30 30 30 40 50 60 70 80 80 90 90 90 {} {} 11 11 21 21 31 31 41 41 41 51 61 61 81 81 81 91 91 91 91 91 {} {} 2 12 12 12 22 22 32 42 52 62 62 72 72 72 82 {} {} 13 13 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 83 93 93 93 {} {} 24 34 34 34 34 44 44 54 64 74 74 74 74 74 84 84 84 84 94 {} {} 15 15 25 35 35 55 55 65 65 65 75 75 75 85 85 85 95 95 95 {} {} 16 16 26 26 26 36 36 36 36 46 46 56 56 56 66 76 86 96 96 96 {} {} 7 17 27 27 37 37 47 47 47 47 57 67 77 77 87 87 97 97 {} {} 8 28 38 38 58 58 58 58 68 78 78 88 98 98 {} {} 9 19 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89 89 99 99 99 {} {}} 3795 + 3796 +do_execsql_test 1.13.11.5 { 3797 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING ) FROM t2 3798 +} {40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39 {} {}} 3799 + 3800 +do_execsql_test 1.13.11.6 { 3801 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) FROM t2 3802 +} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 3803 + 3492 3804 do_execsql_test 1.14.2.1 { 3493 3805 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 3494 3806 } {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 3495 3807 3496 3808 do_execsql_test 1.14.2.2 { 3497 3809 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 3498 3810 } {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} ................................................................................ 3749 4061 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 3750 4062 } {0 74 32 31 84 91 74 3 93 84 74 31 12 90 31 22 74 64 43 64 64 90 74 22 43 90 1 30 62 22 31 31 30 74 64 64 1 40 33 50 11 81 42 40 13 50 81 40 13 13 50 33 52 24 41 81 34 41 34 2 30 2 81 82 53 33 10 33 33 81 34 41 10 81 30 81 4 3 3 23 94 3 61 80 84 94 3 91 91 72 3 63 30 91 94 94 72 91 73 91 84 84 33 41 1 33 84 73 73 91 20 41 84 33 33 84 33 41 84 21 44 22 90 22 81 81 74 93 93 93 81 21 83 44 44 21 21 21 13 21 21 34 11 34 73 74 2 60 2 34 2 34 74 60 23 2 2 2 11 91 60 62 73 74 70 51 65 74 93 65 70 34 70 93 93 93 62 35 44 43 12 35 41 43 44 44 41 80 54 72 43 41 43 91 12 80 80 35 33 12} 3751 4063 3752 4064 do_execsql_test 1.14.10.6 { 3753 4065 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 3754 4066 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 3755 4067 4068 +do_execsql_test 1.14.11.1 { 4069 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 4070 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 4071 + 4072 +do_execsql_test 1.14.11.2 { 4073 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 4074 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 23 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 74 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 38 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 4075 + 4076 +do_execsql_test 1.14.11.3 { 4077 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 4078 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 4079 + 4080 +do_execsql_test 1.14.11.4 { 4081 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 4082 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9} 4083 + 4084 +do_execsql_test 1.14.11.5 { 4085 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 4086 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 4087 + 4088 +do_execsql_test 1.14.11.6 { 4089 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 4090 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 4091 + 3756 4092 do_execsql_test 1.15.2.1 { 3757 4093 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 3758 4094 } {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 95 95 84 84 84 84 84 84 84 84 83 83 83 83 83 83 83 83 83} 3759 4095 3760 4096 do_execsql_test 1.15.2.2 { 3761 4097 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 3762 4098 } {0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7} ................................................................................ 4013 4349 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 4014 4350 } {0 74 32 31 84 1 14 53 3 84 44 2 93 91 32 52 34 25 33 95 65 61 35 3 74 81 2 91 33 13 23 93 12 5 46 6 82 91 44 2 73 43 84 22 95 82 63 12 75 15 93 35 85 36 33 94 77 83 7 65 43 85 64 95 46 96 33 26 36 65 17 74 74 55 33 25 47 7 47 56 7 87 55 4 58 28 47 15 95 56 7 87 55 46 58 58 26 56 17 16 99 29 7 75 16 38 99 58 98 36 15 6 {} 8 89 9 9 56 {} 97 {} 78 16 28 26 36 {} 59 39 99 27 78 {} {} {} 37 27 98 {} 88 8 {} 28 {} {} {} 49 37 29 {} 59 {} {} 47 {} 69 39 59 99 8 78 9 {} {} 58 49 {} {} {} {} 58 {} 38 {} {} {} {} {} {} {} {} {} {} {} {} {} {} 59 {} {} {} {} {} {} {} {} {} {} {} {}} 4015 4351 4016 4352 do_execsql_test 1.15.10.6 { 4017 4353 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 4018 4354 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 4019 4355 4356 +do_execsql_test 1.15.11.1 { 4357 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 4358 +} {0 0 0 0 0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83} 4359 + 4360 +do_execsql_test 1.15.11.2 { 4361 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 4362 +} {0 0 0 0 0 90 40 30 80 20 90 60 70 80 90 41 41 41 41 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 2 2 2 2 2 62 12 32 22 42 2 72 12 22 2 72 72 23 23 23 23 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 74 74 74 74 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 65 65 65 65 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 26 26 26 26 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 97 97 97 97 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 38 38 38 38 38 68 78 8 28 98 78 58 98 8 88 8 99 99 99 99 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9} 4363 + 4364 +do_execsql_test 1.15.11.3 { 4365 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 4366 +} {0 0 0 0 0 1 1 2 2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98} 4367 + 4368 +do_execsql_test 1.15.11.4 { 4369 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 4370 +} {0 0 0 0 0 10 20 30 30 30 40 50 60 70 80 1 1 1 1 1 1 11 11 21 21 31 31 41 41 41 51 61 61 81 81 81 91 2 2 2 2 2 2 2 12 12 12 22 22 32 42 52 62 62 3 3 3 3 3 3 13 13 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 4 4 4 4 4 14 24 34 34 34 34 44 44 54 64 74 74 74 74 74 84 5 5 5 5 5 15 15 15 25 35 35 55 55 65 65 65 75 75 75 85 85 6 6 6 6 6 16 16 16 26 26 26 36 36 36 36 46 46 56 56 56 66 76 7 7 7 7 7 7 7 17 27 27 37 37 47 47 47 47 57 67 77 77 8 8 8 8 8 8 8 28 38 38 58 58 58 58 68 78 9 9 9 9 9 9 9 19 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89} 4371 + 4372 +do_execsql_test 1.15.11.5 { 4373 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 4374 +} {0 0 0 0 0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9} 4375 + 4376 +do_execsql_test 1.15.11.6 { 4377 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 4378 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 4379 + 4020 4380 do_execsql_test 1.16.2.1 { 4021 4381 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 4022 4382 } {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 95 95 84 84 84 84 84 84 84 84 83 83 83 83 83 83 83 83 83 82 82 17 7} 4023 4383 4024 4384 do_execsql_test 1.16.2.2 { 4025 4385 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 4026 4386 } {0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7} ................................................................................ 4277 4637 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 4278 4638 } {0 64 42 81 94 1 44 73 74 44 85 22 33 41 72 93 34 65 54 5 15 81 15 63 84 21 2 62 54 93 43 33 2 75 16 86 23 12 85 62 13 53 94 12 75 23 73 72 96 46 33 55 25 26 74 34 87 84 87 55 53 25 84 75 56 66 54 36 97 55 78 84 84 95 74 65 17 87 77 96 8 47 95 34 89 98 7 46 6 96 8 47 95 56 89 59 36 36 78 96 89 29 37 95 56 39 {} 8 58 67 85 86 {} 58 49 {} 29 76 {} 77 {} 78 56 98 36 97 {} 59 89 89 47 78 {} {} {} 38 68 58 {} 58 38 {} 98 {} {} {} 19 57 9 {} 9 {} {} 7 {} {} {} 39 89 38 78 39 {} {} 8 19 {} {} {} {} 89 {} 39 {} {} {} {} {} {} {} {} {} {} {} {} {} {} 9 {} {} {} {} {} {} {} {} {} {} {} {}} 4279 4639 4280 4640 do_execsql_test 1.16.10.6 { 4281 4641 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t2 4282 4642 } {0 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 4283 4643 4644 +do_execsql_test 1.16.11.1 { 4645 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t2 4646 +} {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} 4647 + 4648 +do_execsql_test 1.16.11.2 { 4649 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t2 4650 +} {0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39} 4651 + 4652 +do_execsql_test 1.16.11.3 { 4653 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 4654 +} {0 1 1 2 2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98 98 99 99 99} 4655 + 4656 +do_execsql_test 1.16.11.4 { 4657 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 4658 +} {0 10 20 30 30 30 40 50 60 70 80 80 90 90 90 1 1 11 11 21 21 31 31 41 41 41 51 61 61 81 81 81 91 91 91 91 91 2 2 2 12 12 12 22 22 32 42 52 62 62 72 72 72 82 3 3 13 13 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 83 93 93 93 4 14 24 34 34 34 34 44 44 54 64 74 74 74 74 74 84 84 84 84 94 5 15 15 15 25 35 35 55 55 65 65 65 75 75 75 85 85 85 95 95 95 6 16 16 16 26 26 26 36 36 36 36 46 46 56 56 56 66 76 86 96 96 96 7 7 7 17 27 27 37 37 47 47 47 47 57 67 77 77 87 87 97 97 8 8 8 28 38 38 58 58 58 58 68 78 78 88 98 98 9 9 9 19 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89 89 99 99 99} 4659 + 4660 +do_execsql_test 1.16.11.5 { 4661 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 4662 +} {0 90 40 30 80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39} 4663 + 4664 +do_execsql_test 1.16.11.6 { 4665 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t2 4666 +} {0 74 74 26 2 96 38 68 62 46 6 46 78 54 8 84 16 16 86 74 24 12 32 56 22 90 28 56 40 56 16 36 98 76 4 94 42 30 78 2 80 84 72 58 96 98 74 12 8 20 22 88 34 8 34 90 96 60 44 2 74 70 26 26 80 90 36 58 72 72 66 64 12 14 62 36 34 36 58 52 30 50 84 10 84 44 58 30 38 34 82 41 23 99 33 89 81 59 39 91 99 97 27 97 67 29 93 77 23 93 65 35 47 7 61 91 85 85 43 59 3 91 55 15 89 25 47 1 43 75 89 81 33 29 53 63 87 37 41 9 61 73 95 65 13 1 21 65 35 5 73 11 51 87 41 31 31 15 95 73 79 11 49 59 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} 4667 + 4284 4668 do_execsql_test 1.17.2.1 { 4285 4669 SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t2 4286 4670 } {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 95 95 84 84 84 84 84 84 84 84 83 83 83 83 83 83 83 83 83 82 82 17 7 {} {} {} {}} 4287 4671 4288 4672 do_execsql_test 1.17.2.2 { 4289 4673 SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t2 4290 4674 } {1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 {} {} {} {}} ................................................................................ 4541 4925 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t2 4542 4926 } {80 84 22 2 34 41 85 23 24 34 25 12 13 11 72 3 85 95 4 75 85 21 65 73 44 31 62 42 4 3 73 13 2 95 56 16 43 2 25 33 83 73 34 72 96 43 13 82 16 16 13 15 65 36 74 14 7 94 47 95 73 65 35 96 36 27 4 67 77 95 78 65 44 5 74 95 78 47 47 36 58 17 5 64 29 58 8 16 86 36 58 17 5 36 29 59 67 26 78 66 49 59 38 6 96 89 {} 38 99 47 26 16 {} 8 19 {} 9 26 {} 87 {} 88 76 58 67 77 {} 9 79 49 37 88 {} {} {} 28 98 99 {} 59 39 {} 58 {} {} {} 99 27 39 {} 29 {} {} 8 {} {} {} 69 49 39 88 {} {} {} 38 99 {} {} {} {} 29 {} 89 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 4543 4927 4544 4928 do_execsql_test 1.17.10.6 { 4545 4929 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t2 4546 4930 } {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 4547 4931 4932 +do_execsql_test 1.17.11.1 { 4933 + SELECT first_value(b) OVER (ORDER BY a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t2 4934 +} {23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7 {} {} {} {}} 4935 + 4936 +do_execsql_test 1.17.11.2 { 4937 + SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t2 4938 +} {80 20 90 60 70 80 90 30 50 10 30 {} {} {} {} 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 {} {} {} {} 22 42 2 72 12 22 2 72 72 12 62 52 82 {} {} {} {} 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 {} {} {} {} 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 {} {} {} {} 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 {} {} {} {} 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 {} {} {} {} 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 {} {} {} {} 28 98 78 58 98 8 88 8 58 58 58 38 {} {} {} {} 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39 {} {} {} {}} 4939 + 4940 +do_execsql_test 1.17.11.3 { 4941 + SELECT first_value(b) OVER ( ORDER BY b,a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t2 4942 +} {2 2 3 3 4 5 6 7 7 7 8 8 8 9 9 9 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 20 21 21 22 22 23 23 23 24 25 26 26 26 27 27 28 29 29 29 30 30 30 31 31 32 33 33 33 33 33 34 34 34 34 35 35 36 36 36 36 37 37 38 38 39 39 39 40 41 41 41 42 43 43 44 44 46 46 47 47 47 47 49 50 51 52 53 54 55 55 56 56 56 57 58 58 58 58 59 59 59 59 60 61 61 62 62 63 64 65 65 65 66 67 68 69 70 72 72 72 73 73 73 74 74 74 74 74 75 75 75 76 77 77 78 78 79 80 80 81 81 81 82 83 84 84 84 84 85 85 85 86 87 87 88 89 89 89 90 90 90 91 91 91 91 91 93 93 93 94 95 95 95 96 96 96 97 97 98 98 99 99 99 {} {} {} {}} 4943 + 4944 +do_execsql_test 1.17.11.4 { 4945 + SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t2 4946 +} {30 30 40 50 60 70 80 80 90 90 90 {} {} {} {} 21 21 31 31 41 41 41 51 61 61 81 81 81 91 91 91 91 91 {} {} {} {} 12 12 22 22 32 42 52 62 62 72 72 72 82 {} {} {} {} 23 23 23 33 33 33 33 33 43 43 53 63 73 73 73 83 93 93 93 {} {} {} {} 34 34 34 44 44 54 64 74 74 74 74 74 84 84 84 84 94 {} {} {} {} 25 35 35 55 55 65 65 65 75 75 75 85 85 85 95 95 95 {} {} {} {} 26 26 26 36 36 36 36 46 46 56 56 56 66 76 86 96 96 96 {} {} {} {} 27 27 37 37 47 47 47 47 57 67 77 77 87 87 97 97 {} {} {} {} 38 38 58 58 58 58 68 78 78 88 98 98 {} {} {} {} 29 29 29 39 39 39 49 59 59 59 59 69 79 89 89 89 99 99 99 {} {} {} {}} 4947 + 4948 +do_execsql_test 1.17.11.5 { 4949 + SELECT first_value(b) OVER ( ORDER BY b%10,a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t2 4950 +} {80 20 90 60 70 80 90 30 50 10 30 41 81 91 61 91 91 1 81 41 61 1 21 11 51 41 31 31 11 81 91 91 21 2 62 12 32 22 42 2 72 12 22 2 72 72 12 62 52 82 23 33 93 23 93 43 3 43 33 53 63 73 13 73 73 33 93 23 13 33 3 33 83 74 74 54 84 74 24 4 94 84 74 34 34 44 74 64 14 34 84 84 44 34 65 35 85 85 55 15 25 75 95 65 65 35 5 15 95 55 75 85 75 15 95 26 96 46 6 46 16 16 86 56 56 56 16 36 76 96 96 26 26 36 66 36 36 97 27 97 67 77 47 7 47 87 37 87 77 7 57 47 47 37 27 17 7 38 68 78 8 28 98 78 58 98 8 88 8 58 58 58 38 99 89 59 39 99 29 59 89 89 29 9 79 49 59 29 59 19 39 9 9 99 69 39 {} {} {} {}} 4951 + 4952 +do_execsql_test 1.17.11.6 { 4953 + SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING) FROM t2 4954 +} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 4955 + 4548 4956 finish_test