test/filter2.test

   1 # 2019 July 2
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 # This file implements regression tests for SQLite library.
  12 #
  13
  14 ####################################################
  15 # DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED!
  16 ####################################################
  17
  18 set testdir [file dirname $argv0]
  19 source $testdir/tester.tcl
  20 set testprefix filter2
  21
  22 ifcapable !windowfunc { finish_test ; return }
  23 do_execsql_test 1.0 {
  24   DROP TABLE IF EXISTS t1;
  25   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
  26   INSERT INTO t1 VALUES
  27    (1, 7), (2, 3), (3, 5), (4, 30), (5, 26), (6, 23), (7, 27),
  28    (8, 3), (9, 17), (10, 26), (11, 33), (12, 25), (13, NULL), (14, 47),
  29    (15, 36), (16, 13), (17, 45), (18, 31), (19, 11), (20, 36), (21, 37),
  30    (22, 21), (23, 22), (24, 14), (25, 16), (26, 3), (27, 7), (28, 29),
  31    (29, 50), (30, 38), (31, 3), (32, 36), (33, 12), (34, 4), (35, 46),
  32    (36, 3), (37, 48), (38, 23), (39, NULL), (40, 24), (41, 5), (42, 46),
  33    (43, 11), (44, NULL), (45, 18), (46, 25), (47, 15), (48, 18), (49, 23);
  34 } {}
  35
  36 do_execsql_test 1.1 {
  37   SELECT sum(b) FROM t1
  38 } {1041}
  39
  40 do_execsql_test 1.2 {
  41   SELECT sum(b) FILTER (WHERE a<10) FROM t1
  42 } {141}
  43
  44 do_execsql_test 1.3 {
  45   SELECT count(DISTINCT b) FROM t1
  46 } {31}
  47
  48 do_execsql_test 1.4 {
  49   SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1
  50 } {31}
  51
  52 do_execsql_test 1.5 {
  53   SELECT min(b) FILTER (WHERE a>19),
  54          min(b) FILTER (WHERE a>0),
  55          max(a+b) FILTER (WHERE a>19),
  56          max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
  57   FROM t1;
  58 } {3 3 88 85}
  59
  60 do_execsql_test 1.6 {
  61   SELECT min(b),
  62          min(b),
  63          max(a+b),
  64          max(b+a)
  65   FROM t1
  66   GROUP BY (a%10)
  67   ORDER BY 1, 2, 3, 4;
  68 } {3 3 58 58   3 3 66 66   3 3 71 71   3 3 88 88   4 4 61 61   5 5 54 54
  69   7 7 85 85   11 11 79 79   16 16 81 81   24 24 68 68}
  70
  71 do_execsql_test 1.7 {
  72   SELECT min(b) FILTER (WHERE a>19),
  73          min(b) FILTER (WHERE a>0),
  74          max(a+b) FILTER (WHERE a>19),
  75          max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
  76   FROM t1
  77   GROUP BY (a%10)
  78   ORDER BY 1, 2, 3, 4;
  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   18 3 66 61   21 3 88 68   23 11 79 79   24 24 68 68}
  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     string_agg(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
 156 finish_test