xref: /sqlite-3.40.0/test/filter1.test (revision b0c4ef71) 
1# 2018 May 8
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
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16set testprefix filter1
17
18ifcapable !windowfunc {
19  finish_test
20  return
21}
22
23do_execsql_test 1.0 {
24  CREATE TABLE t1(a);
25  CREATE INDEX i1 ON t1(a);
26  INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9);
27}
28
29do_execsql_test 1.1 { SELECT sum(a) FROM t1; } 45
30do_execsql_test 1.2 { SELECT sum(a) FILTER( WHERE a<5 ) FROM t1; } 10
31
32do_execsql_test 1.3 {
33  SELECT sum(a) FILTER( WHERE a>9 ),
34         sum(a) FILTER( WHERE a>8 ),
35         sum(a) FILTER( WHERE a>7 ),
36         sum(a) FILTER( WHERE a>6 ),
37         sum(a) FILTER( WHERE a>5 ),
38         sum(a) FILTER( WHERE a>4 ),
39         sum(a) FILTER( WHERE a>3 ),
40         sum(a) FILTER( WHERE a>2 ),
41         sum(a) FILTER( WHERE a>1 ),
42         sum(a) FILTER( WHERE a>0 )
43  FROM t1;
44} {{} 9 17 24 30 35 39 42 44 45}
45
46do_execsql_test 1.4 {
47  SELECT max(a) FILTER (WHERE (a % 2)==0) FROM t1
48} {8}
49
50do_execsql_test 1.5 {
51  SELECT min(a) FILTER (WHERE a>4) FROM t1
52} {5}
53
54do_execsql_test 1.6 {
55  SELECT count(*) FILTER (WHERE a!=5) FROM t1
56} {8}
57
58do_execsql_test 1.7 {
59  SELECT min(a) FILTER (WHERE a>3) FROM t1 GROUP BY (a%2) ORDER BY 1;
60} {4 5}
61
62do_execsql_test 1.8 {
63  CREATE VIEW vv AS
64  SELECT sum(a) FILTER( WHERE a>9 ),
65         sum(a) FILTER( WHERE a>8 ),
66         sum(a) FILTER( WHERE a>7 ),
67         sum(a) FILTER( WHERE a>6 ),
68         sum(a) FILTER( WHERE a>5 ),
69         sum(a) FILTER( WHERE a>4 ),
70         sum(a) FILTER( WHERE a>3 ),
71         sum(a) FILTER( WHERE a>2 ),
72         sum(a) FILTER( WHERE a>1 ),
73         sum(a) FILTER( WHERE a>0 )
74  FROM t1;
75  SELECT * FROM vv;
76} {{} 9 17 24 30 35 39 42 44 45}
77
78
79#-------------------------------------------------------------------------
80# Test some errors:
81#
82#   .1 FILTER on a non-aggregate function,
83#   .2 Window function in FILTER clause,
84#   .3 Aggregate function in FILTER clause,
85#
86reset_db
87do_execsql_test 2.0 {
88  CREATE TABLE t1(a);
89  INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9);
90}
91
92do_catchsql_test 2.1 {
93  SELECT upper(a) FILTER (WHERE a=1) FROM t1
94} {1 {FILTER may not be used with non-aggregate upper()}}
95
96do_catchsql_test 2.2 {
97  SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
98} {1 {misuse of window function max()}}
99
100do_catchsql_test 2.3 {
101  SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
102} {1 {misuse of aggregate function count()}}
103
104#-------------------------------------------------------------------------
105reset_db
106do_execsql_test 3.0 {
107  CREATE TABLE t1(a,b);
108  INSERT INTO t1 VALUES(1, 1);
109}
110do_execsql_test 3.1 {
111  SELECT b, max(a) FILTER (WHERE b='x') FROM t1;
112} {1 {}}
113
114do_execsql_test 3.2 {
115  CREATE TABLE t2(a, b, c);
116  INSERT INTO t2 VALUES(1, 2, 3);
117  INSERT INTO t2 VALUES(1, 3, 4);
118  INSERT INTO t2 VALUES(2, 5, 6);
119  INSERT INTO t2 VALUES(2, 7, 8);
120}
121do_execsql_test 3.3 {
122  SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a;
123} {1 3 {} 2 6 {}}
124
125do_execsql_test 3.4 {
126  DELETE FROM t2;
127  INSERT INTO t2 VALUES(1, 5, 'x');
128  INSERT INTO t2 VALUES(1, 2, 3);
129  INSERT INTO t2 VALUES(1, 4, 'x');
130  INSERT INTO t2 VALUES(2, 5, 6);
131  INSERT INTO t2 VALUES(2, 7, 8);
132}
133do_execsql_test 3.5 {
134  SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a;
135} {1 x 5 2 6 {}}
136
137#-------------------------------------------------------------------------
138reset_db
139do_execsql_test 4.0 {
140  CREATE TABLE t1(a, b, c);
141  INSERT INTO t1 VALUES('a', 0, 5);
142  INSERT INTO t1 VALUES('a', 1, 10);
143  INSERT INTO t1 VALUES('a', 0, 15);
144
145  INSERT INTO t1 VALUES('b', 0, 5);
146  INSERT INTO t1 VALUES('b', 1, 1000);
147  INSERT INTO t1 VALUES('b', 0, 5);
148
149  INSERT INTO t1 VALUES('c', 0, 1);
150  INSERT INTO t1 VALUES('c', 1, 2);
151  INSERT INTO t1 VALUES('c', 0, 3);
152}
153
154do_execsql_test 4.1 {
155  SELECT avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY h;
156} {2.0 5.0 10.0}
157do_execsql_test 4.2 {
158  SELECT avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY (h+1.0);
159} {2.0 5.0 10.0}
160do_execsql_test 4.3 {
161  SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c);
162} {c 2.0 a 10.0 b 5.0}
163do_execsql_test 4.4 {
164  SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2
165} {c 2.0 b 5.0 a 10.0}
166
167#-------------------------------------------------------------------------
168reset_db
169do_execsql_test 5.0 {
170  CREATE TABLE t1(a, b);
171  INSERT INTO t1 VALUES(1, 2);
172  INSERT INTO t1 VALUES(1, 3);
173}
174
175do_execsql_test 5.1 {
176  SELECT count(*) FILTER (WHERE b>2) FROM (SELECT * FROM t1)
177} {1}
178
179do_execsql_test 5.2 {
180  SELECT count(*) FILTER (WHERE b>2) OVER () FROM (SELECT * FROM t1)
181} {1 1}
182
183do_execsql_test 5.3 {
184  SELECT count(*) FILTER (WHERE b>2) OVER (ORDER BY b) FROM (SELECT * FROM t1)
185} {0 1}
186
187#-------------------------------------------------------------------------
188reset_db
189do_execsql_test 6.0 {
190  CREATE TABLE t1(a,b);
191  INSERT INTO t1 VALUES(1,1);
192  INSERT INTO t1 VALUES(2,2);
193  CREATE TABLE t2(x,y);
194  INSERT INTO t2 VALUES(1,1);
195}
196
197do_execsql_test 6.1 {
198  SELECT (SELECT COUNT(a) FILTER(WHERE x) FROM t2) FROM t1;
199} {1 1}
200do_execsql_test 6.2 {
201  SELECT (SELECT COUNT(a+x) FROM t2) FROM t1;
202} {1 1}
203do_execsql_test 6.3 {
204  SELECT (SELECT COUNT(a) FROM t2) FROM t1;
205} {2}
206
207finish_test
208