Add tests for the FILTER clause. And a bugfix.

FossilOrigin-Name: 28aa1702f7f0334abd1b30e7aa48ea3679539b11bfbba32bc9f0d6049cf18a7b

1 files changed, 74 insertions, 0 deletions

diff --git a/test/filter2.test b/test/filter2.test
index 5b98f2e38..21ee6659f 100644
--- a/test/filter2.test
+++ b/test/filter2.test
@@ -79,4 +79,78 @@ do_execsql_test 1.7 {
 } {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