| blob | e94ae57e43f74d793f46560dddda40220d0d8828 |
1 # 2019-08-30
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 # Test cases for RANGE BETWEEN and especially with NULLS LAST
12 #
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix windowA
18 ifcapable !windowfunc {
19 finish_test
20 return
21 }
23 do_execsql_test 1.0 {
24 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), d FLOAT);
25 INSERT INTO t1 VALUES
26 (1, 'A', 5.4),
27 (2, 'B', 5.55),
28 (3, 'C', 8.0),
29 (4, 'D', 10.25),
30 (5, 'E', 10.26),
31 (6, 'N', NULL),
32 (7, 'N', NULL);
33 } {}
35 do_execsql_test 1.1 {
36 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
37 WINDOW w1 AS
38 (ORDER BY d DESC NULLS LAST
39 RANGE BETWEEN 2.50 PRECEDING AND 2.25 FOLLOWING)
40 ORDER BY +d DESC NULLS LAST, +a;
41 } [list \
42 5 E 10.26 ED \
43 4 D 10.25 EDC \
44 3 C 8.0 EDC \
45 2 B 5.55 CBA \
46 1 A 5.4 BA \
47 6 N NULL NN \
48 7 N NULL NN \
49 ]
51 do_execsql_test 1.2 {
52 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
53 WINDOW w1 AS
54 (ORDER BY d DESC NULLS FIRST
55 RANGE BETWEEN 2.50 PRECEDING AND 2.25 FOLLOWING)
56 ORDER BY +d DESC NULLS FIRST, +a;
57 } [list \
58 6 N NULL NN \
59 7 N NULL NN \
60 5 E 10.26 ED \
61 4 D 10.25 EDC \
62 3 C 8.0 EDC \
63 2 B 5.55 CBA \
64 1 A 5.4 BA \
65 ]
67 do_execsql_test 1.3 {
68 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
69 WINDOW w1 AS
70 (ORDER BY d DESC NULLS LAST
71 RANGE BETWEEN 2.50 PRECEDING AND UNBOUNDED FOLLOWING)
72 ORDER BY +d DESC NULLS LAST, +a;
73 } [list \
74 5 E 10.26 EDCBANN \
75 4 D 10.25 EDCBANN \
76 3 C 8.0 EDCBANN \
77 2 B 5.55 CBANN \
78 1 A 5.4 BANN \
79 6 N NULL NN \
80 7 N NULL NN \
81 ]
83 do_execsql_test 1.4 {
84 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
85 WINDOW w1 AS
86 (ORDER BY d DESC NULLS FIRST
87 RANGE BETWEEN 2.50 PRECEDING AND UNBOUNDED FOLLOWING)
88 ORDER BY +d DESC NULLS FIRST, +a;
89 } [list \
90 6 N NULL NNEDCBA \
91 7 N NULL NNEDCBA \
92 5 E 10.26 EDCBA \
93 4 D 10.25 EDCBA \
94 3 C 8.0 EDCBA \
95 2 B 5.55 CBA \
96 1 A 5.4 BA \
97 ]
99 do_execsql_test 1.5 {
100 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
101 WINDOW w1 AS
102 (ORDER BY d DESC NULLS LAST
103 RANGE BETWEEN 2.50 PRECEDING AND CURRENT ROW)
104 ORDER BY +d DESC NULLS LAST, +a;
105 } [list \
106 5 E 10.26 E \
107 4 D 10.25 ED \
108 3 C 8.0 EDC \
109 2 B 5.55 CB \
110 1 A 5.4 BA \
111 6 N NULL NN \
112 7 N NULL NN \
113 ]
115 do_execsql_test 1.6 {
116 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
117 WINDOW w1 AS
118 (ORDER BY d DESC NULLS FIRST
119 RANGE BETWEEN 2.50 PRECEDING AND CURRENT ROW)
120 ORDER BY +d DESC NULLS FIRST, +a;
121 } [list \
122 6 N NULL NN \
123 7 N NULL NN \
124 5 E 10.26 E \
125 4 D 10.25 ED \
126 3 C 8.0 EDC \
127 2 B 5.55 CB \
128 1 A 5.4 BA \
129 ]
131 do_execsql_test 2.1 {
132 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
133 WINDOW w1 AS
134 (ORDER BY d DESC NULLS LAST
135 RANGE BETWEEN UNBOUNDED PRECEDING AND 2.25 FOLLOWING)
136 ORDER BY +d DESC NULLS LAST, +a;
137 } [list \
138 5 E 10.26 ED \
139 4 D 10.25 EDC \
140 3 C 8.0 EDC \
141 2 B 5.55 EDCBA \
142 1 A 5.4 EDCBA \
143 6 N NULL EDCBANN \
144 7 N NULL EDCBANN \
145 ]
147 do_execsql_test 2.2 {
148 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
149 WINDOW w1 AS
150 (ORDER BY d DESC NULLS FIRST
151 RANGE BETWEEN UNBOUNDED PRECEDING AND 2.25 FOLLOWING)
152 ORDER BY +d DESC NULLS FIRST, +a;
153 } [list \
154 6 N NULL NN \
155 7 N NULL NN \
156 5 E 10.26 NNED \
157 4 D 10.25 NNEDC \
158 3 C 8.0 NNEDC \
159 2 B 5.55 NNEDCBA \
160 1 A 5.4 NNEDCBA \
161 ]
163 do_execsql_test 2.3 {
164 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
165 WINDOW w1 AS
166 (ORDER BY d DESC NULLS LAST
167 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
168 ORDER BY +d DESC NULLS LAST, +a;
169 } [list \
170 5 E 10.26 EDCBANN \
171 4 D 10.25 EDCBANN \
172 3 C 8.0 EDCBANN \
173 2 B 5.55 EDCBANN \
174 1 A 5.4 EDCBANN \
175 6 N NULL EDCBANN \
176 7 N NULL EDCBANN \
177 ]
179 do_execsql_test 2.4 {
180 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
181 WINDOW w1 AS
182 (ORDER BY d DESC NULLS FIRST
183 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
184 ORDER BY +d DESC NULLS FIRST, +a;
185 } [list \
186 6 N NULL NNEDCBA \
187 7 N NULL NNEDCBA \
188 5 E 10.26 NNEDCBA \
189 4 D 10.25 NNEDCBA \
190 3 C 8.0 NNEDCBA \
191 2 B 5.55 NNEDCBA \
192 1 A 5.4 NNEDCBA \
193 ]
195 do_execsql_test 2.5 {
196 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
197 WINDOW w1 AS
198 (ORDER BY d DESC NULLS LAST
199 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
200 ORDER BY +d DESC NULLS LAST, +a;
201 } [list \
202 5 E 10.26 E \
203 4 D 10.25 ED \
204 3 C 8.0 EDC \
205 2 B 5.55 EDCB \
206 1 A 5.4 EDCBA \
207 6 N NULL EDCBANN \
208 7 N NULL EDCBANN \
209 ]
211 do_execsql_test 2.6 {
212 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
213 WINDOW w1 AS
214 (ORDER BY d DESC NULLS FIRST
215 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
216 ORDER BY +d DESC NULLS FIRST, +a;
217 } [list \
218 6 N NULL NN \
219 7 N NULL NN \
220 5 E 10.26 NNE \
221 4 D 10.25 NNED \
222 3 C 8.0 NNEDC \
223 2 B 5.55 NNEDCB \
224 1 A 5.4 NNEDCBA \
225 ]
228 do_execsql_test 3.1 {
229 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
230 WINDOW w1 AS
231 (ORDER BY d DESC NULLS LAST
232 RANGE BETWEEN CURRENT ROW AND 2.25 FOLLOWING)
233 ORDER BY +d DESC NULLS LAST, +a;
234 } [list \
235 5 E 10.26 ED \
236 4 D 10.25 DC \
237 3 C 8.0 C \
238 2 B 5.55 BA \
239 1 A 5.4 A \
240 6 N NULL NN \
241 7 N NULL NN \
242 ]
244 do_execsql_test 3.2 {
245 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
246 WINDOW w1 AS
247 (ORDER BY d DESC NULLS FIRST
248 RANGE BETWEEN CURRENT ROW AND 2.25 FOLLOWING)
249 ORDER BY +d DESC NULLS FIRST, +a;
250 } [list \
251 6 N NULL NN \
252 7 N NULL NN \
253 5 E 10.26 ED \
254 4 D 10.25 DC \
255 3 C 8.0 C \
256 2 B 5.55 BA \
257 1 A 5.4 A \
258 ]
260 do_execsql_test 3.3 {
261 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
262 WINDOW w1 AS
263 (ORDER BY d DESC NULLS LAST
264 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
265 ORDER BY +d DESC NULLS LAST, +a;
266 } [list \
267 5 E 10.26 EDCBANN \
268 4 D 10.25 DCBANN \
269 3 C 8.0 CBANN \
270 2 B 5.55 BANN \
271 1 A 5.4 ANN \
272 6 N NULL NN \
273 7 N NULL NN \
274 ]
276 do_execsql_test 3.4 {
277 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
278 WINDOW w1 AS
279 (ORDER BY d DESC NULLS FIRST
280 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
281 ORDER BY +d DESC NULLS FIRST, +a;
282 } [list \
283 6 N NULL NNEDCBA \
284 7 N NULL NNEDCBA \
285 5 E 10.26 EDCBA \
286 4 D 10.25 DCBA \
287 3 C 8.0 CBA \
288 2 B 5.55 BA \
289 1 A 5.4 A \
290 ]
292 do_execsql_test 4.0 {
293 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
294 WINDOW w1 AS
295 (ORDER BY d DESC NULLS FIRST
296 RANGE BETWEEN 2.50 PRECEDING AND 0.5 PRECEDING)
297 ORDER BY +d DESC NULLS FIRST, +a;
298 } [list \
299 6 N NULL NN \
300 7 N NULL NN \
301 5 E 10.26 {} \
302 4 D 10.25 {} \
303 3 C 8.0 ED \
304 2 B 5.55 C \
305 1 A 5.4 {} \
306 ]
309 finish_test