Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add tests to improve coverage of code in window.c. Fix a problem with "SELECT row_number() OVER ()". |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | exp-window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
f41b6b7317e2b5ac5721a3adff49f298 |
| User & Date: | dan 2018-06-20 09:23:49 |
Context
|
2018-06-21
| ||
| 19:20 | Fix a problem with handling single row partitions in the percent_rank() window function. check-in: b84fbf16 user: dan tags: exp-window-functions | |
|
2018-06-20
| ||
| 09:23 | Add tests to improve coverage of code in window.c. Fix a problem with "SELECT row_number() OVER ()". check-in: f41b6b73 user: dan tags: exp-window-functions | |
|
2018-06-19
| ||
| 19:15 | Merge latest trunk changes with this branch. check-in: 6ad0e64b user: dan tags: exp-window-functions | |
Changes
Changes to src/vdbe.h.
239 239 void sqlite3VdbeResolveLabel(Vdbe*, int); 240 240 #ifdef SQLITE_COVERAGE_TEST 241 241 int sqlite3VdbeLabelHasBeenResolved(Vdbe*,int); 242 242 #endif 243 243 int sqlite3VdbeCurrentAddr(Vdbe*); 244 244 #ifdef SQLITE_DEBUG 245 245 int sqlite3VdbeAssertMayAbort(Vdbe *, int); 246 + int sqlite3VdbeAssertAggContext(sqlite3_context*); 246 247 #endif 247 248 void sqlite3VdbeResetStepResult(Vdbe*); 248 249 void sqlite3VdbeRewind(Vdbe*); 249 250 int sqlite3VdbeReset(Vdbe*); 250 251 void sqlite3VdbeSetNumCols(Vdbe*,int); 251 252 int sqlite3VdbeSetColName(Vdbe*, int, int, const char *, void(*)(void*)); 252 253 void sqlite3VdbeCountChanges(Vdbe*);
Changes to src/vdbeapi.c.
820 820 if( (p->pMem->flags & MEM_Agg)==0 ){ 821 821 return createAggContext(p, nByte); 822 822 }else{ 823 823 return (void*)p->pMem->z; 824 824 } 825 825 } 826 826 827 +/* 828 +** This function is only used within assert() statements to check that the 829 +** aggregate context has already been allocated. i.e.: 830 +** 831 +** assert( sqlite3VdbeAssertAggContext(p) ); 832 +*/ 833 +#ifdef SQLITE_DEBUG 834 +int sqlite3VdbeAssertAggContext(sqlite3_context *p){ 835 + return ((p->pMem->flags & MEM_Agg)!=0); 836 +} 837 +#endif /* SQLITE_DEBUG */ 838 + 827 839 /* 828 840 ** Return the auxiliary data pointer, if any, for the iArg'th argument to 829 841 ** the user-function defined by pCtx. 830 842 ** 831 843 ** The left-most argument is 0. 832 844 ** 833 845 ** Undocumented behavior: If iArg is negative then access a cache of
Changes to src/window.c.
168 168 struct CallCount { 169 169 i64 nValue; 170 170 i64 nStep; 171 171 i64 nTotal; 172 172 }; 173 173 174 174 /* 175 -** Implementation of built-in window function dense_rank(). 175 +** Implementation of built-in window function dense_rank(). Assumes that 176 +** the window frame has been set to: 177 +** 178 +** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 176 179 */ 177 180 static void dense_rankStepFunc( 178 181 sqlite3_context *pCtx, 179 182 int nArg, 180 183 sqlite3_value **apArg 181 184 ){ 182 185 struct CallCount *p; ................................................................................ 198 201 p->nStep = 0; 199 202 } 200 203 sqlite3_result_int64(pCtx, p->nValue); 201 204 } 202 205 } 203 206 204 207 /* 205 -** Implementation of built-in window function rank(). 208 +** Implementation of built-in window function rank(). Assumes that 209 +** the window frame has been set to: 210 +** 211 +** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 206 212 */ 207 213 static void rankStepFunc( 208 214 sqlite3_context *pCtx, 209 215 int nArg, 210 216 sqlite3_value **apArg 211 217 ){ 212 218 struct CallCount *p; ................................................................................ 230 236 if( p ){ 231 237 sqlite3_result_int64(pCtx, p->nValue); 232 238 p->nValue = 0; 233 239 } 234 240 } 235 241 236 242 /* 237 -** Implementation of built-in window function percent_rank(). 243 +** Implementation of built-in window function percent_rank(). Assumes that 244 +** the window frame has been set to: 245 +** 246 +** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 238 247 */ 239 248 static void percent_rankStepFunc( 240 249 sqlite3_context *pCtx, 241 250 int nArg, 242 251 sqlite3_value **apArg 243 252 ){ 244 253 struct CallCount *p; 245 254 assert( nArg==1 ); 246 255 256 + assert( sqlite3VdbeAssertAggContext(pCtx) ); 247 257 p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p)); 248 - if( p ){ 258 + if( ALWAYS(p) ){ 249 259 if( p->nTotal==0 ){ 250 260 p->nTotal = sqlite3_value_int64(apArg[0]); 251 261 } 252 262 p->nStep++; 253 263 if( p->nValue==0 ){ 254 264 p->nValue = p->nStep; 255 265 } ................................................................................ 271 281 }else{ 272 282 sqlite3_result_double(pCtx, 100.0); 273 283 } 274 284 p->nValue = 0; 275 285 } 276 286 } 277 287 288 +/* 289 +** Implementation of built-in window function cume_dist(). Assumes that 290 +** the window frame has been set to: 291 +** 292 +** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 293 +*/ 278 294 static void cume_distStepFunc( 279 295 sqlite3_context *pCtx, 280 296 int nArg, 281 297 sqlite3_value **apArg 282 298 ){ 283 299 struct CallCount *p; 284 300 assert( nArg==1 ); 285 301 302 + assert( sqlite3VdbeAssertAggContext(pCtx) ); 286 303 p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p)); 287 - if( p ){ 304 + if( ALWAYS(p) ){ 288 305 if( p->nTotal==0 ){ 289 306 p->nTotal = sqlite3_value_int64(apArg[0]); 290 307 } 291 308 p->nStep++; 292 309 } 293 310 } 294 311 static void cume_distInvFunc( ................................................................................ 324 341 static void ntileStepFunc( 325 342 sqlite3_context *pCtx, 326 343 int nArg, 327 344 sqlite3_value **apArg 328 345 ){ 329 346 struct NtileCtx *p; 330 347 assert( nArg==2 ); 348 + assert( sqlite3VdbeAssertAggContext(pCtx) ); 331 349 p = (struct NtileCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); 332 - if( p ){ 350 + if( ALWAYS(p) ){ 333 351 if( p->nTotal==0 ){ 334 352 p->nParam = sqlite3_value_int64(apArg[0]); 335 353 p->nTotal = sqlite3_value_int64(apArg[1]); 336 354 if( p->nParam<=0 ){ 337 355 sqlite3_result_error( 338 356 pCtx, "argument of ntile must be a positive integer", -1 339 357 ); ................................................................................ 384 402 */ 385 403 static void last_valueStepFunc( 386 404 sqlite3_context *pCtx, 387 405 int nArg, 388 406 sqlite3_value **apArg 389 407 ){ 390 408 struct LastValueCtx *p; 391 - p = (struct LastValueCtx *)sqlite3_aggregate_context(pCtx, sizeof(*p)); 409 + p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); 392 410 if( p ){ 393 411 sqlite3_value_free(p->pVal); 394 412 p->pVal = sqlite3_value_dup(apArg[0]); 395 413 if( p->pVal==0 ){ 396 414 sqlite3_result_error_nomem(pCtx); 397 415 }else{ 398 416 p->nVal++; ................................................................................ 401 419 } 402 420 static void last_valueInvFunc( 403 421 sqlite3_context *pCtx, 404 422 int nArg, 405 423 sqlite3_value **apArg 406 424 ){ 407 425 struct LastValueCtx *p; 408 - p = (struct LastValueCtx *)sqlite3_aggregate_context(pCtx, sizeof(*p)); 409 - if( p ){ 426 + p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); 427 + if( ALWAYS(p) ){ 410 428 p->nVal--; 411 429 if( p->nVal==0 ){ 412 430 sqlite3_value_free(p->pVal); 413 431 p->pVal = 0; 414 432 } 415 433 } 416 434 } 417 435 static void last_valueValueFunc(sqlite3_context *pCtx){ 418 436 struct LastValueCtx *p; 419 - p = (struct LastValueCtx *)sqlite3_aggregate_context(pCtx, sizeof(*p)); 437 + p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); 420 438 if( p && p->pVal ){ 421 439 sqlite3_result_value(pCtx, p->pVal); 422 440 } 423 441 } 424 442 static void last_valueFinalizeFunc(sqlite3_context *pCtx){ 425 443 struct LastValueCtx *p; 426 - p = (struct LastValueCtx *)sqlite3_aggregate_context(pCtx, sizeof(*p)); 444 + p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); 427 445 if( p && p->pVal ){ 428 446 sqlite3_result_value(pCtx, p->pVal); 429 447 sqlite3_value_free(p->pVal); 430 448 p->pVal = 0; 431 449 } 432 450 } 433 451 ................................................................................ 732 750 Expr *pFilter = sqlite3ExprDup(db, pWin->pFilter, 0); 733 751 pSublist = sqlite3ExprListAppend(pParse, pSublist, pFilter); 734 752 } 735 753 pWin->regAccum = ++pParse->nMem; 736 754 pWin->regResult = ++pParse->nMem; 737 755 sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); 738 756 } 757 + 758 + /* If there is no ORDER BY or PARTITION BY clause, and the window 759 + ** function accepts zero arguments, and there are no other columns 760 + ** selected (e.g. "SELECT row_number() OVER () FROM t1"), it is possible 761 + ** that pSublist is still NULL here. Add a constant expression here to 762 + ** keep everything legal in this case. 763 + */ 764 + if( pSublist==0 ){ 765 + pSublist = sqlite3ExprListAppend(pParse, 0, 766 + sqlite3ExprAlloc(db, TK_INTEGER, &sqlite3IntTokens[0], 0) 767 + ); 768 + } 739 769 740 770 pSub = sqlite3SelectNew( 741 771 pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0 742 772 ); 743 773 p->pSrc = sqlite3SrcListAppend(db, 0, 0, 0); 744 774 assert( p->pSrc || db->mallocFailed ); 745 775 if( p->pSrc ){
Changes to test/window3.tcl.
78 78 79 79 execsql_test 1.$tn.3.1 " 80 80 SELECT row_number() OVER ( ORDER BY a $window ) FROM t2 81 81 " 82 82 execsql_test 1.$tn.3.2 " 83 83 SELECT row_number() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2 84 84 " 85 + execsql_test 1.$tn.3.3 " 86 + SELECT row_number() OVER ( $window ) FROM t2 87 + " 85 88 86 89 execsql_test 1.$tn.4.1 " 87 90 SELECT dense_rank() OVER ( ORDER BY a $window ) FROM t2 88 91 " 89 92 execsql_test 1.$tn.4.2 " 90 93 SELECT dense_rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2 91 94 " ................................................................................ 181 184 " 182 185 execsql_float_test 1.$tn.8.5 " 183 186 SELECT ntile(104) OVER ( ORDER BY b%10,a $window ) FROM t2 184 187 " 185 188 execsql_float_test 1.$tn.8.6 " 186 189 SELECT ntile(105) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2 187 190 " 191 + execsql_float_test 1.$tn.8.7 " 192 + SELECT ntile(105) OVER ( $window ) FROM t2 193 + " 188 194 189 195 execsql_test 1.$tn.9.1 " 190 196 SELECT last_value(a+b) OVER ( ORDER BY a $window ) FROM t2 191 197 " 192 198 execsql_test 1.$tn.9.2 " 193 199 SELECT last_value(a+b) OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2 194 200 "
Changes to test/window3.test.
cannot compute difference between binary files
Changes to test/windowfault.test.
42 42 FROM t1 43 43 WINDOW win AS (ORDER BY a) 44 44 } 45 45 } -test { 46 46 faultsim_test_result {0 {1 1 1 1 4 4 {} 8 {} 4 4 2 2 2 1 4 8 8 12 4 8 4 3 3 3 2 4 12 8 {} 8 12 4}} 47 47 } 48 48 49 -do_faultsim_test 2 -faults oom-* -prep { 49 +do_faultsim_test 1.1 -faults oom-t* -prep { 50 + faultsim_restore_and_reopen 51 +} -body { 52 + execsql { 53 + SELECT row_number() OVER win, 54 + rank() OVER win, 55 + dense_rank() OVER win 56 + FROM t1 57 + WINDOW win AS (PARTITION BY c<7 ORDER BY a) 58 + } 59 +} -test { 60 + faultsim_test_result {0 {1 1 1 2 2 2 1 1 1}} 61 +} 62 + 63 +do_faultsim_test 2 -start 1 -faults oom-* -prep { 64 + faultsim_restore_and_reopen 65 +} -body { 66 + execsql { 67 + SELECT round(percent_rank() OVER win, 2), 68 + round(cume_dist() OVER win, 2) 69 + FROM t1 70 + WINDOW win AS (ORDER BY a) 71 + } 72 +} -test { 73 + faultsim_test_result {0 {0.0 0.33 0.5 0.67 1.0 1.0}} 74 +} 75 + 76 +do_faultsim_test 3 -faults oom-* -prep { 50 77 faultsim_restore_and_reopen 51 78 } -body { 52 79 execsql { 53 80 SELECT min(d) OVER win, max(d) OVER win 54 81 FROM t1 55 82 WINDOW win AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 56 83 } 57 84 } -test { 58 85 faultsim_test_result {0 {4 12 8 12 12 12}} 59 86 } 60 87 88 +do_faultsim_test 4 -faults oom-* -prep { 89 + faultsim_restore_and_reopen 90 +} -body { 91 + execsql { 92 + CREATE VIEW aaa AS 93 + SELECT min(d) OVER w, max(d) OVER w 94 + FROM t1 95 + WINDOW w AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); 96 + SELECT * FROM aaa; 97 + } 98 +} -test { 99 + faultsim_test_result {0 {4 12 8 12 12 12}} 100 +} 101 + 102 +do_faultsim_test 5 -start 1 -faults oom-* -prep { 103 + faultsim_restore_and_reopen 104 +} -body { 105 + execsql { 106 + SELECT last_value(a) OVER win1, 107 + last_value(a) OVER win2 108 + FROM t1 109 + WINDOW win1 AS (ORDER BY a ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), 110 + win2 AS (ORDER BY a) 111 + } 112 +} -test { 113 + faultsim_test_result {0 {5 1 9 5 9 9}} 114 +} 61 115 62 116 finish_test 63 117