Debian Codegraph @

test/filter2.tcl

raw
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