/ Check-in [f41b6b73]
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 | SQL 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
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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