xref: /sqlite-3.40.0/test/filter2.test (revision 6ba7ab0d) 
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
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20set testprefix filter2
21
22ifcapable !windowfunc { finish_test ; return }
23do_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
36do_execsql_test 1.1 {
37  SELECT sum(b) FROM t1
38} {1041}
39
40do_execsql_test 1.2 {
41  SELECT sum(b) FILTER (WHERE a<10) FROM t1
42} {141}
43
44do_execsql_test 1.3 {
45  SELECT count(DISTINCT b) FROM t1
46} {31}
47
48do_execsql_test 1.4 {
49  SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1
50} {31}
51
52do_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
60do_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
71do_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
82finish_test
83