Debian Codegraph @

test/filter2.test

raw
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