SQLite

Check-in [f41b6b7317]
Login

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
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: f41b6b7317e2b5ac5721a3adff49f298ded29f9e0f887af98faeb0cb7e865ab6
User & Date: dan 2018-06-20 09:23:49.155
Context
2018-06-21
19:20
Fix a problem with handling single row partitions in the percent_rank() window function. (check-in: b84fbf16ea 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: f41b6b7317 user: dan tags: exp-window-functions)
2018-06-19
19:15
Merge latest trunk changes with this branch. (check-in: 6ad0e64b46 user: dan tags: exp-window-functions)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/vdbe.h.
239
240
241
242
243
244
245

246
247
248
249
250
251
252
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
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
168
169
170
171
172
173
174

175
176
177
178
179
180
181
182
183
184
185







-
+
+
+
+







struct CallCount {
  i64 nValue;
  i64 nStep;
  i64 nTotal;
};

/*
** Implementation of built-in window function dense_rank().
** 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;
198
199
200
201
202
203
204
205




206
207
208
209
210
211
212
201
202
203
204
205
206
207

208
209
210
211
212
213
214
215
216
217
218







-
+
+
+
+







      p->nStep = 0;
    }
    sqlite3_result_int64(pCtx, p->nValue);
  }
}

/*
** Implementation of built-in window function rank().
** 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;
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
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







-
+
+
+
+









+

-
+







  if( p ){
    sqlite3_result_int64(pCtx, p->nValue);
    p->nValue = 0;
  }
}

/*
** Implementation of built-in window function percent_rank().
** 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( 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;
    }
271
272
273
274
275
276
277






278
279
280
281
282
283
284
285

286
287

288
289
290
291
292
293
294
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







+
+
+
+
+
+








+

-
+







    }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( p ){
  if( ALWAYS(p) ){
    if( p->nTotal==0 ){
      p->nTotal = sqlite3_value_int64(apArg[0]);
    }
    p->nStep++;
  }
}
static void cume_distInvFunc(
324
325
326
327
328
329
330

331
332

333
334
335
336
337
338
339
341
342
343
344
345
346
347
348
349

350
351
352
353
354
355
356
357







+

-
+







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( 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
        );
384
385
386
387
388
389
390
391

392
393
394
395
396
397
398
399
400
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
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
434
435
436

437
438
439
440
441
442
443

444
445
446
447
448
449
450
451







-
+
















-
-
+
+









-
+






-
+







*/
static void last_valueStepFunc(
  sqlite3_context *pCtx, 
  int nArg,
  sqlite3_value **apArg
){
  struct LastValueCtx *p;
  p = (struct LastValueCtx *)sqlite3_aggregate_context(pCtx, sizeof(*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 = (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));
  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));
  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;
  }
}

732
733
734
735
736
737
738












739
740
741
742
743
744
745
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







+
+
+
+
+
+
+
+
+
+
+
+







        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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94







+
+
+








  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
  "
181
182
183
184
185
186
187



188
189
190
191
192
193
194
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200







+
+
+







  "
  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
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 2 -faults oom-* -prep {
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