xref: /sqlite-3.40.0/test/filter1.test (revision f71a243a) 
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
104finish_test
105
106
107