test/filter2.tcl

   1 # 2018 May 19
   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 #
  12
  13 source [file join [file dirname $argv0] pg_common.tcl]
  14
  15 #=========================================================================
  16
  17
  18 start_test filter2 "2019 July 2"
  19
  20 ifcapable !windowfunc
  21
  22 execsql_test 1.0 {
  23   DROP TABLE IF EXISTS t1;
  24   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
  25   INSERT INTO t1 VALUES
  26    (1, 7), (2, 3), (3, 5), (4, 30), (5, 26), (6, 23), (7, 27),
  27    (8, 3), (9, 17), (10, 26), (11, 33), (12, 25), (13, NULL), (14, 47),
  28    (15, 36), (16, 13), (17, 45), (18, 31), (19, 11), (20, 36), (21, 37),
  29    (22, 21), (23, 22), (24, 14), (25, 16), (26, 3), (27, 7), (28, 29),
  30    (29, 50), (30, 38), (31, 3), (32, 36), (33, 12), (34, 4), (35, 46),
  31    (36, 3), (37, 48), (38, 23), (39, NULL), (40, 24), (41, 5), (42, 46),
  32    (43, 11), (44, NULL), (45, 18), (46, 25), (47, 15), (48, 18), (49, 23);
  33 }
  34
  35 execsql_test 1.1 { SELECT sum(b) FROM t1 }
  36
  37 execsql_test 1.2 { SELECT sum(b) FILTER (WHERE a<10) FROM t1 }
  38
  39 execsql_test 1.3 { SELECT count(DISTINCT b) FROM t1 }
  40
  41 execsql_test 1.4 { SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1 }
  42
  43 execsql_test 1.5 {
  44   SELECT min(b) FILTER (WHERE a>19),
  45          min(b) FILTER (WHERE a>0),
  46          max(a+b) FILTER (WHERE a>19),
  47          max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
  48   FROM t1;
  49 }
  50
  51 execsql_test 1.6 {
  52   SELECT min(b),
  53          min(b),
  54          max(a+b),
  55          max(b+a)
  56   FROM t1
  57   GROUP BY (a%10)
  58   ORDER BY 1, 2, 3, 4;
  59 }
  60
  61 execsql_test 1.7 {
  62   SELECT min(b) FILTER (WHERE a>19),
  63          min(b) FILTER (WHERE a>0),
  64          max(a+b) FILTER (WHERE a>19),
  65          max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
  66   FROM t1
  67   GROUP BY (a%10)
  68   ORDER BY 1, 2, 3, 4;
  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 }
 129
 130 finish_test
 131
 132