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