/ Check-in [443f0c28]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Remove some bad assert() statements from the implementations of window functions percent_rank() and cume_dist().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 443f0c286f1659937fd61b4ef2de17d0d015369e5ff5249a9e0c3d0ee4925158
User & Date: dan 2018-07-06 13:25:02
Context
2018-07-06
13:48
Prevent "UNBOUNDED FOLLOWING" from being used as the starting boundary of a window-frame. And "UNBOUNDED PRECEDING" from being used as the ending boundary. check-in: e51fdf66 user: dan tags: trunk
13:25
Remove some bad assert() statements from the implementations of window functions percent_rank() and cume_dist(). check-in: 443f0c28 user: dan tags: trunk
07:42
Return an error if DISTINCT is used with a window-function (e.g. "count(DISTINCT <expr>) OVER (...)"). check-in: d59bcc8e user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbe.h.

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*);







<







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*);

Changes to src/vdbeapi.c.

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







<
<
<
<
<
<
<
<
<
<
<
<







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

Changes to src/window.c.

251
252
253
254
255
256
257
258
259
260

261
262
263
264
265
266
267
...
297
298
299
300
301
302
303
304
305
306

307
308
309
310
311
312
313
  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;
    }
................................................................................
  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(







<

<
>







 







<

<
>







251
252
253
254
255
256
257

258

259
260
261
262
263
264
265
266
...
296
297
298
299
300
301
302

303

304
305
306
307
308
309
310
311
  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;
    }
................................................................................
  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(

Changes to test/window4.tcl.

327
328
329
330
331
332
333








334
335
336
execsql_test 9.4 {
  SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
}

execsql_test 9.5 {
  SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
}









finish_test








>
>
>
>
>
>
>
>



327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
execsql_test 9.4 {
  SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
}

execsql_test 9.5 {
  SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
}

execsql_float_test 9.6 {
  SELECT percent_rank() OVER () FROM t1
}

execsql_float_test 9.7 {
  SELECT cume_dist() OVER () FROM t1
}

finish_test

Changes to test/window4.test.

1229
1230
1231
1232
1233
1234
1235
















1236
  SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1   1 1   1 1   4 4   4 4   6 6   7 7}

do_execsql_test 9.5 {
  SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1   4 4   6 6   7 7}

















finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
  SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1   1 1   1 1   4 4   4 4   6 6   7 7}

do_execsql_test 9.5 {
  SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1   4 4   6 6   7 7}

do_test 9.6 {
  set myres {}
  foreach r [db eval {SELECT percent_rank() OVER () FROM t1}] {
    lappend myres [format %.2f [set r]]
  }
  set myres
} {0.00 0.00 0.00}

do_test 9.7 {
  set myres {}
  foreach r [db eval {SELECT cume_dist() OVER () FROM t1}] {
    lappend myres [format %.2f [set r]]
  }
  set myres
} {1.00 1.00 1.00}

finish_test

Changes to test/windowfault.test.

124
125
126
127
128
129
130










131
132
133
    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








>
>
>
>
>
>
>
>
>
>



124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
    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}}
}

do_faultsim_test 6 -faults oom-* -prep {
  faultsim_restore_and_reopen
} -body {
  execsql {
    SELECT percent_rank() OVER (), cume_dist() OVER () FROM t1
  }
} -test {
  faultsim_test_result {0 {0.0 1.0 0.0 1.0 0.0 1.0}}
}

finish_test