| blob | 441e2b126a9243e1f86ffdc3477e95d0b7868fa4 |
1 # 2019 March 01
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 #
14 ####################################################
15 # DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED!
16 ####################################################
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 set testprefix window7
22 ifcapable !windowfunc { finish_test ; return }
23 do_execsql_test 1.0 {
24 DROP TABLE IF EXISTS t3;
25 CREATE TABLE t3(a INTEGER, b INTEGER);
26 INSERT INTO t3 VALUES
27 (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8),
28 (9, 9), (0, 10), (1, 11), (2, 12), (3, 13), (4, 14), (5, 15), (6, 16),
29 (7, 17), (8, 18), (9, 19), (0, 20), (1, 21), (2, 22), (3, 23), (4, 24),
30 (5, 25), (6, 26), (7, 27), (8, 28), (9, 29), (0, 30), (1, 31), (2, 32),
31 (3, 33), (4, 34), (5, 35), (6, 36), (7, 37), (8, 38), (9, 39), (0, 40),
32 (1, 41), (2, 42), (3, 43), (4, 44), (5, 45), (6, 46), (7, 47), (8, 48),
33 (9, 49), (0, 50), (1, 51), (2, 52), (3, 53), (4, 54), (5, 55), (6, 56),
34 (7, 57), (8, 58), (9, 59), (0, 60), (1, 61), (2, 62), (3, 63), (4, 64),
35 (5, 65), (6, 66), (7, 67), (8, 68), (9, 69), (0, 70), (1, 71), (2, 72),
36 (3, 73), (4, 74), (5, 75), (6, 76), (7, 77), (8, 78), (9, 79), (0, 80),
37 (1, 81), (2, 82), (3, 83), (4, 84), (5, 85), (6, 86), (7, 87), (8, 88),
38 (9, 89), (0, 90), (1, 91), (2, 92), (3, 93), (4, 94), (5, 95), (6, 96),
39 (7, 97), (8, 98), (9, 99), (0, 100);
40 } {}
42 do_execsql_test 1.1 {
43 SELECT a, sum(b) FROM t3 GROUP BY a ORDER BY 1;
44 } {0 550 1 460 2 470 3 480 4 490 5 500 6 510 7 520 8 530
45 9 540}
47 do_execsql_test 1.2 {
48 SELECT a, sum(b) OVER (
49 ORDER BY a GROUPS BETWEEN CURRENT ROW AND CURRENT ROW
50 ) FROM t3 ORDER BY 1;
51 } {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550
52 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460
53 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470
54 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480
55 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490
56 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500
57 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510
58 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520
59 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530
60 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530
61 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540
62 9 540}
64 do_execsql_test 1.3 {
65 SELECT a, sum(b) OVER (
66 ORDER BY a GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING
67 ) FROM t3 ORDER BY 1;
68 } {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550
69 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460
70 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470
71 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480
72 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490
73 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500
74 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510
75 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520
76 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530
77 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530
78 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540
79 9 540}
81 do_execsql_test 1.4 {
82 SELECT a, sum(b) OVER (
83 ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING
84 ) FROM t3 ORDER BY 1;
85 } {0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480
86 0 1480 0 1480 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960
87 1 1960 1 1960 1 1960 1 1960 2 2450 2 2450 2 2450 2 2450
88 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 3 2400 3 2400
89 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400
90 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450
91 4 2450 4 2450 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500
92 5 2500 5 2500 5 2500 5 2500 6 2550 6 2550 6 2550 6 2550
93 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 7 2600 7 2600
94 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600
95 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100
96 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590
97 9 1590 9 1590 9 1590 9 1590}
99 do_execsql_test 1.5 {
100 SELECT a, sum(b) OVER (
101 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING
102 ) FROM t3 ORDER BY 1;
103 } {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550
104 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460
105 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470
106 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480
107 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490
108 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500
109 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510
110 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520
111 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530
112 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530
113 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540
114 9 540}
116 do_execsql_test 1.6 {
117 SELECT a, sum(b) OVER (
118 ORDER BY a RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
119 ) FROM t3 ORDER BY 1;
120 } {0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480
121 0 1480 0 1480 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960
122 1 1960 1 1960 1 1960 1 1960 2 2450 2 2450 2 2450 2 2450
123 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 3 2400 3 2400
124 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400
125 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450
126 4 2450 4 2450 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500
127 5 2500 5 2500 5 2500 5 2500 6 2550 6 2550 6 2550 6 2550
128 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 7 2600 7 2600
129 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600
130 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100
131 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590
132 9 1590 9 1590 9 1590 9 1590}
134 do_execsql_test 1.7 {
135 SELECT a, sum(b) OVER (
136 ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING
137 ) FROM t3 ORDER BY 1;
138 } {0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010
139 0 1010 0 1010 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480
140 1 1480 1 1480 1 1480 1 1480 2 1960 2 1960 2 1960 2 1960
141 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 3 1900 3 1900
142 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900
143 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940
144 4 1940 4 1940 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980
145 5 1980 5 1980 5 1980 5 1980 6 2020 6 2020 6 2020 6 2020
146 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 7 2060 7 2060
147 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060
148 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100
149 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590
150 9 1590 9 1590 9 1590 9 1590}
152 do_execsql_test 1.8.1 {
153 SELECT a, sum(b) OVER (
154 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING
155 ) FROM t3 ORDER BY 1;
156 } {0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010
157 0 1010 0 1010 1 930 1 930 1 930 1 930 1 930 1 930 1 930
158 1 930 1 930 1 930 2 950 2 950 2 950 2 950 2 950 2 950
159 2 950 2 950 2 950 2 950 3 970 3 970 3 970 3 970 3 970
160 3 970 3 970 3 970 3 970 3 970 4 990 4 990 4 990 4 990
161 4 990 4 990 4 990 4 990 4 990 4 990 5 1010 5 1010 5 1010
162 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 6 1030
163 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030
164 6 1030 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050
165 7 1050 7 1050 7 1050 8 1070 8 1070 8 1070 8 1070 8 1070
166 8 1070 8 1070 8 1070 8 1070 8 1070 9 540 9 540 9 540 9 540
167 9 540 9 540 9 540 9 540 9 540 9 540}
169 do_execsql_test 1.8.2 {
170 SELECT a, sum(b) OVER (
171 ORDER BY a DESC RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING
172 ) FROM t3 ORDER BY 1;
173 } {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550
174 0 550 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010
175 1 1010 1 1010 1 1010 2 930 2 930 2 930 2 930 2 930 2 930
176 2 930 2 930 2 930 2 930 3 950 3 950 3 950 3 950 3 950
177 3 950 3 950 3 950 3 950 3 950 4 970 4 970 4 970 4 970
178 4 970 4 970 4 970 4 970 4 970 4 970 5 990 5 990 5 990
179 5 990 5 990 5 990 5 990 5 990 5 990 5 990 6 1010 6 1010
180 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010
181 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030
182 7 1030 7 1030 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050
183 8 1050 8 1050 8 1050 8 1050 9 1070 9 1070 9 1070 9 1070
184 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070}
186 finish_test