/ Check-in [28aa1702]
Login

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

Overview
Comment:Add tests for the FILTER clause. And a bugfix.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | filter-clause
Files: files | file ages | folders
SHA3-256: 28aa1702f7f0334abd1b30e7aa48ea3679539b11bfbba32bc9f0d6049cf18a7b
User & Date: dan 2019-07-03 18:31:20
Context
2019-07-05
17:38
Minor tweak to patch on this branch to reclaim some cycles. check-in: 81eed055 user: dan tags: filter-clause
2019-07-03
18:31
Add tests for the FILTER clause. And a bugfix. check-in: 28aa1702 user: dan tags: filter-clause
2019-07-02
11:56
Experimental implementation of FILTER clause for aggregate functions. check-in: 1f1ae2d6 user: dan tags: filter-clause
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

   822    822             no_such_func = 1;
   823    823             pDef = 0;
   824    824           }
   825    825         }
   826    826   
   827    827         if( 0==IN_RENAME_OBJECT ){
   828    828   #ifndef SQLITE_OMIT_WINDOWFUNC
          829  +        int is_win = ExprHasProperty(pExpr, EP_WinFunc);
   829    830           assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX)
   830    831             || (pDef->xValue==0 && pDef->xInverse==0)
   831    832             || (pDef->xValue && pDef->xInverse && pDef->xSFunc && pDef->xFinalize)
   832    833           );
   833         -        if( pDef && pDef->xValue==0 && ExprHasProperty(pExpr, EP_WinFunc) ){
          834  +        if( pDef && pDef->xValue==0 && is_win ){
   834    835             sqlite3ErrorMsg(pParse, 
   835    836                 "%.*s() may not be used as a window function", nId, zId
   836    837             );
   837    838             pNC->nErr++;
   838    839           }else if( 
   839    840                 (is_agg && (pNC->ncFlags & NC_AllowAgg)==0)
   840         -           || (is_agg && (pDef->funcFlags&SQLITE_FUNC_WINDOW) && !pExpr->y.pWin)
   841         -           || (is_agg && pExpr->y.pWin && (pNC->ncFlags & NC_AllowWin)==0)
          841  +           || (is_agg && (pDef->funcFlags&SQLITE_FUNC_WINDOW) && !is_win)
          842  +           || (is_agg && is_win && (pNC->ncFlags & NC_AllowWin)==0)
   842    843           ){
   843    844             const char *zType;
   844         -          if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || pExpr->y.pWin ){
          845  +          if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || is_win ){
   845    846               zType = "window";
   846    847             }else{
   847    848               zType = "aggregate";
   848    849             }
   849    850             sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId);
   850    851             pNC->nErr++;
   851    852             is_agg = 0;
................................................................................
   876    877             pNC->nErr++;
   877    878           }
   878    879           if( is_agg ){
   879    880             /* Window functions may not be arguments of aggregate functions.
   880    881             ** Or arguments of other window functions. But aggregate functions
   881    882             ** may be arguments for window functions.  */
   882    883   #ifndef SQLITE_OMIT_WINDOWFUNC
   883         -          pNC->ncFlags &= ~(NC_AllowWin | (!pExpr->y.pWin ? NC_AllowAgg : 0));
          884  +          pNC->ncFlags &= ~(NC_AllowWin | (!is_win ? NC_AllowAgg : 0));
   884    885   #else
   885    886             pNC->ncFlags &= ~NC_AllowAgg;
   886    887   #endif
   887    888           }
   888    889         }
   889    890         sqlite3WalkExprList(pWalker, pList);
   890    891         if( is_agg ){

Changes to test/filter1.test.

    79     79   
    80     80   do_catchsql_test 2.2 {
    81     81     SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
    82     82   } {1 {misuse of window function max()}}
    83     83   
    84     84   do_catchsql_test 2.3 {
    85     85     SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
    86         -} {1 {misuse of aggregate: count()}}
           86  +} {1 {misuse of aggregate function count()}}
    87     87   
    88     88   finish_test
    89     89   
    90     90   

Changes to test/filter2.tcl.

    63     63            min(b) FILTER (WHERE a>0),
    64     64            max(a+b) FILTER (WHERE a>19),
    65     65            max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
    66     66     FROM t1
    67     67     GROUP BY (a%10)
    68     68     ORDER BY 1, 2, 3, 4;
    69     69   }
           70  +
           71  +execsql_test 1.8 { 
           72  +  SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1
           73  +}
           74  +
           75  +execsql_test 1.9 {
           76  +  SELECT (a%5) FROM t1 GROUP BY (a%5) 
           77  +  HAVING sum(b) FILTER (WHERE b<20) > 34
           78  +  ORDER BY 1
           79  +}
           80  +
           81  +execsql_test 1.10 {
           82  +  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
           83  +  FROM t1
           84  +  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
           85  +  ORDER BY 1
           86  +}
           87  +
           88  +execsql_test 1.11 {
           89  +  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
           90  +  FROM t1
           91  +  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
           92  +  ORDER BY 2
           93  +}
           94  +
           95  +execsql_test 1.12 {
           96  +  SELECT (a%5), 
           97  +    sum(b) FILTER (WHERE b<20) AS bbb,
           98  +    count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc
           99  +  FROM t1 GROUP BY (a%5)
          100  +  ORDER BY 2
          101  +}
          102  +
          103  +execsql_test 1.13 {
          104  +  SELECT 
          105  +    string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0),
          106  +    string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1),
          107  +    count(*) FILTER (WHERE b%2!=0),
          108  +    count(*) FILTER (WHERE b%2!=1)
          109  +  FROM t1;
          110  +}
          111  +
          112  +execsql_float_test 1.14 {
          113  +  SELECT 
          114  +    avg(b) FILTER (WHERE b>a),
          115  +    avg(b) FILTER (WHERE b<a)
          116  +  FROM t1 GROUP BY (a%2) ORDER BY 1,2;
          117  +}
          118  +
          119  +execsql_test 1.15 {
          120  +  SELECT 
          121  +    a/5,
          122  +    sum(b) FILTER (WHERE a%5=0),
          123  +    sum(b) FILTER (WHERE a%5=1),
          124  +    sum(b) FILTER (WHERE a%5=2),
          125  +    sum(b) FILTER (WHERE a%5=3),
          126  +    sum(b) FILTER (WHERE a%5=4)
          127  +  FROM t1 GROUP BY (a/5) ORDER BY 1;
          128  +}
    70    129   
    71    130   finish_test
    72    131   
    73    132   

Changes to test/filter2.test.

    75     75            max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
    76     76     FROM t1
    77     77     GROUP BY (a%10)
    78     78     ORDER BY 1, 2, 3, 4;
    79     79   } {3 3 58 58   3 3 71 39   4 4 38 61   7 7 85 85   11 5 54 45   16 16 81 81
    80     80     18 3 66 61   21 3 88 68   23 11 79 79   24 24 68 68}
    81     81   
           82  +do_execsql_test 1.8 {
           83  +  SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1
           84  +} {{}}
           85  +
           86  +do_execsql_test 1.9 {
           87  +  SELECT (a%5) FROM t1 GROUP BY (a%5) 
           88  +  HAVING sum(b) FILTER (WHERE b<20) > 34
           89  +  ORDER BY 1
           90  +} {3   4}
           91  +
           92  +do_execsql_test 1.10 {
           93  +  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
           94  +  FROM t1
           95  +  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
           96  +  ORDER BY 1
           97  +} {3 49   4 46}
           98  +
           99  +do_execsql_test 1.11 {
          100  +  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
          101  +  FROM t1
          102  +  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
          103  +  ORDER BY 2
          104  +} {4 46   3 49}
          105  +
          106  +do_execsql_test 1.12 {
          107  +  SELECT (a%5), 
          108  +    sum(b) FILTER (WHERE b<20) AS bbb,
          109  +    count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc
          110  +  FROM t1 GROUP BY (a%5)
          111  +  ORDER BY 2
          112  +} {2 25 3   0 34 2   1 34 4   4 46 4   3 49 5}
          113  +
          114  +do_execsql_test 1.13 {
          115  +  SELECT 
          116  +    group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0),
          117  +    group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1),
          118  +    count(*) FILTER (WHERE b%2!=0),
          119  +    count(*) FILTER (WHERE b%2!=1)
          120  +  FROM t1;
          121  +} {7_3_5_23_27_3_17_33_25_47_13_45_31_11_37_21_3_7_29_3_3_23_5_11_25_15_23 30_26_26_36_36_22_14_16_50_38_36_12_4_46_48_24_46_18_18 27 19}
          122  +
          123  +
          124  +do_test 1.14 {
          125  +  set myres {}
          126  +  foreach r [db eval {SELECT 
          127  +    avg(b) FILTER (WHERE b>a),
          128  +    avg(b) FILTER (WHERE b<a)
          129  +  FROM t1 GROUP BY (a%2) ORDER BY 1,2;}] {
          130  +    lappend myres [format %.4f [set r]]
          131  +  }
          132  +  set res2 {30.8333 13.7273 31.4167 13.0000}
          133  +  set i 0
          134  +  foreach r [set myres] r2 [set res2] {
          135  +    if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
          136  +      error "list element [set i] does not match: got=[set r] expected=[set r2]"
          137  +    }
          138  +    incr i
          139  +  }
          140  +  set {} {}
          141  +} {}
          142  +
          143  +do_execsql_test 1.15 {
          144  +  SELECT 
          145  +    a/5,
          146  +    sum(b) FILTER (WHERE a%5=0),
          147  +    sum(b) FILTER (WHERE a%5=1),
          148  +    sum(b) FILTER (WHERE a%5=2),
          149  +    sum(b) FILTER (WHERE a%5=3),
          150  +    sum(b) FILTER (WHERE a%5=4)
          151  +  FROM t1 GROUP BY (a/5) ORDER BY 1;
          152  +} {0 {} 7 3 5 30   1 26 23 27 3 17   2 26 33 25 {} 47   3 36 13 45 31 11
          153  +  4 36 37 21 22 14   5 16 3 7 29 50   6 38 3 36 12 4   7 46 3 48 23 {}
          154  +  8 24 5 46 11 {}   9 18 25 15 18 23}
          155  +
    82    156   finish_test