xref: /sqlite-3.40.0/test/filter2.tcl (revision 16e12c57) 
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
13source [file join [file dirname $argv0] pg_common.tcl]
14
15#=========================================================================
16
17
18start_test filter2 "2019 July 2"
19
20ifcapable !windowfunc
21
22execsql_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
35execsql_test 1.1 { SELECT sum(b) FROM t1 }
36
37execsql_test 1.2 { SELECT sum(b) FILTER (WHERE a<10) FROM t1 }
38
39execsql_test 1.3 { SELECT count(DISTINCT b) FROM t1 }
40
41execsql_test 1.4 { SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1 }
42
43execsql_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
51execsql_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
61execsql_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
71execsql_test 1.8 {
72  SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1
73}
74
75execsql_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
81execsql_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
88execsql_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
95execsql_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
103execsql_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
112execsql_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
119execsql_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
130finish_test
131
132
133