Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| 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: |
28aa1702f7f0334abd1b30e7aa48ea36 |
| User & Date: | dan 2019-07-03 18:31:20 |
|
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 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