/ Check-in [28aa1702]
Login
☰Home Timeline Branches

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 Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c. 

822
823
824
825
826
827
828

829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
...
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890

          no_such_func = 1;
          pDef = 0;
        }
      }

      if( 0==IN_RENAME_OBJECT ){
#ifndef SQLITE_OMIT_WINDOWFUNC

        assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX)
          || (pDef->xValue==0 && pDef->xInverse==0)
          || (pDef->xValue && pDef->xInverse && pDef->xSFunc && pDef->xFinalize)
        );
        if( pDef && pDef->xValue==0 && ExprHasProperty(pExpr, EP_WinFunc) ){
          sqlite3ErrorMsg(pParse, 
              "%.*s() may not be used as a window function", nId, zId
          );
          pNC->nErr++;
        }else if( 
              (is_agg && (pNC->ncFlags & NC_AllowAgg)==0)
           || (is_agg && (pDef->funcFlags&SQLITE_FUNC_WINDOW) && !pExpr->y.pWin)
           || (is_agg && pExpr->y.pWin && (pNC->ncFlags & NC_AllowWin)==0)
        ){
          const char *zType;
          if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || pExpr->y.pWin ){
            zType = "window";
          }else{
            zType = "aggregate";
          }
          sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId);
          pNC->nErr++;
          is_agg = 0;
................................................................................
          pNC->nErr++;
        }
        if( is_agg ){
          /* Window functions may not be arguments of aggregate functions.
          ** Or arguments of other window functions. But aggregate functions
          ** may be arguments for window functions.  */
#ifndef SQLITE_OMIT_WINDOWFUNC
          pNC->ncFlags &= ~(NC_AllowWin | (!pExpr->y.pWin ? NC_AllowAgg : 0));
#else
          pNC->ncFlags &= ~NC_AllowAgg;
#endif
        }
      }
      sqlite3WalkExprList(pWalker, pList);
      if( is_agg ){








>




|






|
|


|







 







|








822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
...
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891

          no_such_func = 1;
          pDef = 0;
        }
      }

      if( 0==IN_RENAME_OBJECT ){
#ifndef SQLITE_OMIT_WINDOWFUNC
        int is_win = ExprHasProperty(pExpr, EP_WinFunc);
        assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX)
          || (pDef->xValue==0 && pDef->xInverse==0)
          || (pDef->xValue && pDef->xInverse && pDef->xSFunc && pDef->xFinalize)
        );
        if( pDef && pDef->xValue==0 && is_win ){
          sqlite3ErrorMsg(pParse, 
              "%.*s() may not be used as a window function", nId, zId
          );
          pNC->nErr++;
        }else if( 
              (is_agg && (pNC->ncFlags & NC_AllowAgg)==0)
           || (is_agg && (pDef->funcFlags&SQLITE_FUNC_WINDOW) && !is_win)
           || (is_agg && is_win && (pNC->ncFlags & NC_AllowWin)==0)
        ){
          const char *zType;
          if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || is_win ){
            zType = "window";
          }else{
            zType = "aggregate";
          }
          sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId);
          pNC->nErr++;
          is_agg = 0;
................................................................................
          pNC->nErr++;
        }
        if( is_agg ){
          /* Window functions may not be arguments of aggregate functions.
          ** Or arguments of other window functions. But aggregate functions
          ** may be arguments for window functions.  */
#ifndef SQLITE_OMIT_WINDOWFUNC
          pNC->ncFlags &= ~(NC_AllowWin | (!is_win ? NC_AllowAgg : 0));
#else
          pNC->ncFlags &= ~NC_AllowAgg;
#endif
        }
      }
      sqlite3WalkExprList(pWalker, pList);
      if( is_agg ){

Changes to test/filter1.test. 

79
80
81
82
83
84
85
86
87
88
89
90


do_catchsql_test 2.2 {
  SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
} {1 {misuse of window function max()}}

do_catchsql_test 2.3 {
  SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
} {1 {misuse of aggregate: count()}}

finish_test










|





79
80
81
82
83
84
85
86
87
88
89
90


do_catchsql_test 2.2 {
  SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
} {1 {misuse of window function max()}}

do_catchsql_test 2.3 {
  SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
} {1 {misuse of aggregate function count()}}

finish_test

Changes to test/filter2.tcl. 

63
64
65
66
67
68
69
70



























































71
72
73

         min(b) FILTER (WHERE a>0),
         max(a+b) FILTER (WHERE a>19),
         max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
  FROM t1
  GROUP BY (a%10)
  ORDER BY 1, 2, 3, 4;
}




























































finish_test











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




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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132

         min(b) FILTER (WHERE a>0),
         max(a+b) FILTER (WHERE a>19),
         max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
  FROM t1
  GROUP BY (a%10)
  ORDER BY 1, 2, 3, 4;
}

execsql_test 1.8 { 
  SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1
}

execsql_test 1.9 {
  SELECT (a%5) FROM t1 GROUP BY (a%5) 
  HAVING sum(b) FILTER (WHERE b<20) > 34
  ORDER BY 1
}

execsql_test 1.10 {
  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
  FROM t1
  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
  ORDER BY 1
}

execsql_test 1.11 {
  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
  FROM t1
  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
  ORDER BY 2
}

execsql_test 1.12 {
  SELECT (a%5), 
    sum(b) FILTER (WHERE b<20) AS bbb,
    count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc
  FROM t1 GROUP BY (a%5)
  ORDER BY 2
}

execsql_test 1.13 {
  SELECT 
    string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0),
    string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1),
    count(*) FILTER (WHERE b%2!=0),
    count(*) FILTER (WHERE b%2!=1)
  FROM t1;
}

execsql_float_test 1.14 {
  SELECT 
    avg(b) FILTER (WHERE b>a),
    avg(b) FILTER (WHERE b<a)
  FROM t1 GROUP BY (a%2) ORDER BY 1,2;
}

execsql_test 1.15 {
  SELECT 
    a/5,
    sum(b) FILTER (WHERE a%5=0),
    sum(b) FILTER (WHERE a%5=1),
    sum(b) FILTER (WHERE a%5=2),
    sum(b) FILTER (WHERE a%5=3),
    sum(b) FILTER (WHERE a%5=4)
  FROM t1 GROUP BY (a/5) ORDER BY 1;
}

finish_test

Changes to test/filter2.test. 

75
76
77
78
79
80
81










































































82

         max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
  FROM t1
  GROUP BY (a%10)
  ORDER BY 1, 2, 3, 4;
} {3 3 58 58   3 3 71 39   4 4 38 61   7 7 85 85   11 5 54 45   16 16 81 81
  18 3 66 61   21 3 88 68   23 11 79 79   24 24 68 68}











































































finish_test








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


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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

         max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
  FROM t1
  GROUP BY (a%10)
  ORDER BY 1, 2, 3, 4;
} {3 3 58 58   3 3 71 39   4 4 38 61   7 7 85 85   11 5 54 45   16 16 81 81
  18 3 66 61   21 3 88 68   23 11 79 79   24 24 68 68}

do_execsql_test 1.8 {
  SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1
} {{}}

do_execsql_test 1.9 {
  SELECT (a%5) FROM t1 GROUP BY (a%5) 
  HAVING sum(b) FILTER (WHERE b<20) > 34
  ORDER BY 1
} {3   4}

do_execsql_test 1.10 {
  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
  FROM t1
  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
  ORDER BY 1
} {3 49   4 46}

do_execsql_test 1.11 {
  SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb
  FROM t1
  GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34
  ORDER BY 2
} {4 46   3 49}

do_execsql_test 1.12 {
  SELECT (a%5), 
    sum(b) FILTER (WHERE b<20) AS bbb,
    count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc
  FROM t1 GROUP BY (a%5)
  ORDER BY 2
} {2 25 3   0 34 2   1 34 4   4 46 4   3 49 5}

do_execsql_test 1.13 {
  SELECT 
    group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0),
    group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1),
    count(*) FILTER (WHERE b%2!=0),
    count(*) FILTER (WHERE b%2!=1)
  FROM t1;
} {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}


do_test 1.14 {
  set myres {}
  foreach r [db eval {SELECT 
    avg(b) FILTER (WHERE b>a),
    avg(b) FILTER (WHERE b<a)
  FROM t1 GROUP BY (a%2) ORDER BY 1,2;}] {
    lappend myres [format %.4f [set r]]
  }
  set res2 {30.8333 13.7273 31.4167 13.0000}
  set i 0
  foreach r [set myres] r2 [set res2] {
    if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
      error "list element [set i] does not match: got=[set r] expected=[set r2]"
    }
    incr i
  }
  set {} {}
} {}

do_execsql_test 1.15 {
  SELECT 
    a/5,
    sum(b) FILTER (WHERE a%5=0),
    sum(b) FILTER (WHERE a%5=1),
    sum(b) FILTER (WHERE a%5=2),
    sum(b) FILTER (WHERE a%5=3),
    sum(b) FILTER (WHERE a%5=4)
  FROM t1 GROUP BY (a/5) ORDER BY 1;
} {0 {} 7 3 5 30   1 26 23 27 3 17   2 26 33 25 {} 47   3 36 13 45 31 11
  4 36 37 21 22 14   5 16 3 7 29 50   6 38 3 36 12 4   7 46 3 48 23 {}
  8 24 5 46 11 {}   9 18 25 15 18 23}

finish_test

This page was generated in about 0.006s by Fossil 2.12 [b17aba9e20] 2020-05-28 14:20:09