Debian Codegraph @

test/window6.test

raw
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. Specifically,
12 # it tests the sqlite3_create_window_function() API.
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix window6
18
19 ifcapable !windowfunc {
20 finish_test
21 return
22 }
23
24 set setup {
25 CREATE TABLE %t1(%x, %y %typename);
26 INSERT INTO %t1 VALUES(1, 'a');
27 INSERT INTO %t1 VALUES(2, 'b');
28 INSERT INTO %t1 VALUES(3, 'c');
29 INSERT INTO %t1 VALUES(4, 'd');
30 INSERT INTO %t1 VALUES(5, 'e');
31 }
32
33 foreach {tn vars} {
34 1 {}
35 2 { set A(%t1) over }
36 3 { set A(%x) over }
37 4 {
38 set A(%alias) over
39 set A(%x) following
40 set A(%y) over
41 }
42 5 {
43 set A(%t1) over
44 set A(%x) following
45 set A(%y) preceding
46 set A(%w) current
47 set A(%alias) filter
48 set A(%typename) window
49 }
50
51 6 {
52 set A(%x) window
53 }
54 } {
55 set A(%t1) t1
56 set A(%x) x
57 set A(%y) y
58 set A(%w) w
59 set A(%alias) alias
60 set A(%typename) integer
61 eval $vars
62
63 set MAP [array get A]
64 set setup_sql [string map $MAP $setup]
65 reset_db
66 execsql $setup_sql
67
68 do_execsql_test 1.$tn.1 [string map $MAP {
69 SELECT group_concat(%x, '.') OVER (ORDER BY %y) FROM %t1
70 }] {1 1.2 1.2.3 1.2.3.4 1.2.3.4.5}
71
72 do_execsql_test 1.$tn.2 [string map $MAP {
73 SELECT sum(%x) OVER %w FROM %t1 WINDOW %w AS (ORDER BY %y)
74 }] {1 3 6 10 15}
75
76 do_execsql_test 1.$tn.3 [string map $MAP {
77 SELECT sum(%alias.%x) OVER %w FROM %t1 %alias WINDOW %w AS (ORDER BY %y)
78 }] {1 3 6 10 15}
79
80 do_execsql_test 1.$tn.4 [string map $MAP {
81 SELECT sum(%x) %alias FROM %t1
82 }] {15}
83 }
84
85
86 proc winproc {args} { return "window: $args" }
87 db func window winproc
88 do_execsql_test 2.0 {
89 SELECT window('hello world');
90 } {{window: {hello world}}}
91
92 proc wincmp {a b} { string compare $b $a }
93 db collate window wincmp
94 do_execsql_test 3.0 {
95 CREATE TABLE window(x COLLATE window);
96 INSERT INTO window VALUES('bob'), ('alice'), ('cate');
97 SELECT * FROM window ORDER BY x COLLATE window;
98 } {cate bob alice}
99 do_execsql_test 3.1 {
100 DROP TABLE window;
101 CREATE TABLE x1(x);
102 INSERT INTO x1 VALUES('bob'), ('alice'), ('cate');
103 CREATE INDEX window ON x1(x COLLATE window);
104 SELECT * FROM x1 ORDER BY x COLLATE window;
105 } {cate bob alice}
106
107
108 do_execsql_test 4.0 { CREATE TABLE t4(x, y); }
109
110 # do_execsql_test 4.1 { PRAGMA parser_trace = 1 }
111 do_execsql_test 4.1 {
112 SELECT * FROM t4 window, t4;
113 }
114
115 #-------------------------------------------------------------------------
116 reset_db
117
118 do_execsql_test 5.0 {
119 CREATE TABLE over(x, over);
120 CREATE TABLE window(x, window);
121 INSERT INTO over VALUES(1, 2), (3, 4), (5, 6);
122 INSERT INTO window VALUES(1, 2), (3, 4), (5, 6);
123 SELECT sum(x) over FROM over
124 } {9}
125
126 do_execsql_test 5.1 {
127 SELECT sum(x) over over FROM over WINDOW over AS ()
128 } {9 9 9}
129
130 do_execsql_test 5.2 {
131 SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over)
132 } {2 6 12}
133
134 do_execsql_test 5.3 {
135 SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over);
136 } {2 6 12}
137
138 do_execsql_test 5.4 {
139 SELECT sum(window) OVER window window FROM window window window window AS (ORDER BY window);
140 } {2 6 12}
141
142 do_execsql_test 5.5 {
143 SELECT count(*) OVER win FROM over
144 WINDOW win AS (ORDER BY x ROWS BETWEEN +2 FOLLOWING AND +3 FOLLOWING)
145 } {1 0 0}
146
147 #-------------------------------------------------------------------------
148 #
149
150 ifcapable !icu {
151 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
152 do_execsql_test 6.0 {
153 SELECT LIKE('!', '', '!') x WHERE x;
154 } {}
155 do_execsql_test 6.1 {
156 SELECT LIKE("!","","!")""WHeRE"";
157 } {}
158 do_catchsql_test 6.2 {
159 SELECT LIKE("!","","!")""window"";
160 } {1 {near "window": syntax error}}
161 }
162
163 reset_db
164 do_execsql_test 7.0 {
165 CREATE TABLE t1(x TEXT);
166 CREATE INDEX i1 ON t1(x COLLATE nocase);
167 INSERT INTO t1 VALUES('');
168 }
169
170 ifcapable !icu {
171 do_execsql_test 7.1 {
172 SELECT count(*) FROM t1 WHERE x LIKE '!' ESCAPE '!';
173 } {0}
174 }
175
176 #-------------------------------------------------------------------------
177 #
178 do_execsql_test 8.0 {
179 CREATE TABLE IF NOT EXISTS "sample" (
180 "id" INTEGER NOT NULL PRIMARY KEY,
181 "counter" INTEGER NOT NULL,
182 "value" REAL NOT NULL
183 );
184
185 INSERT INTO "sample" (counter, value)
186 VALUES (1, 10.), (1, 20.), (2, 1.), (2, 3.), (3, 100.);
187 }
188
189 do_execsql_test 8.1 {
190 SELECT "counter", "value", RANK() OVER w AS "rank"
191 FROM "sample"
192 WINDOW w AS (PARTITION BY "counter" ORDER BY "value" DESC)
193 ORDER BY "counter", RANK() OVER w
194 } {
195 1 20.0 1 1 10.0 2 2 3.0 1 2 1.0 2 3 100.0 1
196 }
197
198 do_execsql_test 8.2 {
199 SELECT "counter", "value", SUM("value") OVER
200 (ORDER BY "id" ROWS 2 PRECEDING)
201 FROM "sample"
202 ORDER BY "id"
203 } {
204 1 10.0 10.0 1 20.0 30.0 2 1.0 31.0 2 3.0 24.0 3 100.0 104.0
205 }
206
207 do_execsql_test 8.3 {
208 SELECT SUM("value") OVER
209 (ORDER BY "id" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
210 FROM "sample"
211 ORDER BY "id"
212 } {
213 10.0 30.0 31.0 24.0 104.0
214 }
215
216 do_execsql_test 9.0 {
217 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
218 SELECT x, group_concat(x) OVER (ORDER BY x ROWS 2 PRECEDING)
219 FROM c;
220 } {
221 1 1 2 1,2 3 1,2,3 4 2,3,4 5 3,4,5
222 }
223 #do_catchsql_test 9.1 {
224 # WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
225 # SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
226 # FROM c;
227 #} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}
228 #
229 #do_catchsql_test 9.2 {
230 # WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
231 # SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
232 # FROM c;
233 #} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}
234
235 do_catchsql_test 9.3 {
236 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
237 SELECT count(DISTINCT x) OVER (ORDER BY x) FROM c;
238 } {1 {DISTINCT is not supported for window functions}}
239
240 do_catchsql_test 9.4 {
241 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
242 SELECT count() OVER (ORDER BY x RANGE UNBOUNDED FOLLOWING) FROM c;
243 } {1 {near "FOLLOWING": syntax error}}
244
245 do_catchsql_test 9.5 {
246 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
247 SELECT count() OVER (ORDER BY x RANGE BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING) FROM c;
248 } {1 {near "FOLLOWING": syntax error}}
249
250 do_catchsql_test 9.6 {
251 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
252 SELECT count() OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING) FROM c;
253 } {1 {near "PRECEDING": syntax error}}
254
255 foreach {tn frame} {
256 1 "BETWEEN CURRENT ROW AND 4 PRECEDING"
257 2 "4 FOLLOWING"
258 3 "BETWEEN 4 FOLLOWING AND CURRENT ROW"
259 4 "BETWEEN 4 FOLLOWING AND 2 PRECEDING"
260 } {
261 do_catchsql_test 9.7.$tn "
262 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
263 SELECT count() OVER (
264 ORDER BY x ROWS $frame
265 ) FROM c;
266 " {1 {unsupported frame specification}}
267 }
268
269 do_catchsql_test 9.8.1 {
270 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
271 SELECT count() OVER (
272 ORDER BY x ROWS BETWEEN a PRECEDING AND 2 FOLLOWING
273 ) FROM c;
274 } {1 {frame starting offset must be a non-negative integer}}
275 do_catchsql_test 9.8.2 {
276 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
277 SELECT count() OVER (
278 ORDER BY x ROWS BETWEEN 2 PRECEDING AND a FOLLOWING
279 ) FROM c;
280 } {1 {frame ending offset must be a non-negative integer}}
281
282 do_execsql_test 10.0 {
283 WITH t1(a,b) AS (VALUES(1,2))
284 SELECT count() FILTER (where b<>5) OVER w1
285 FROM t1
286 WINDOW w1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
287 } {1}
288
289 foreach {tn stmt} {
290 1 "SELECT nth_value(b, 0) OVER (ORDER BY a) FROM t1"
291 2 "SELECT nth_value(b, -1) OVER (ORDER BY a) FROM t1"
292 3 "SELECT nth_value(b, '4ab') OVER (ORDER BY a) FROM t1"
293 4 "SELECT nth_value(b, NULL) OVER (ORDER BY a) FROM t1"
294 5 "SELECT nth_value(b, 8.5) OVER (ORDER BY a) FROM t1"
295 } {
296 do_catchsql_test 10.1.$tn "
297 WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
298 $stmt
299 " {1 {second argument to nth_value must be a positive integer}}
300 }
301
302 foreach {tn stmt res} {
303 1 "SELECT nth_value(b, 1) OVER (ORDER BY a) FROM t1" {2 2 2}
304 2 "SELECT nth_value(b, 2) OVER (ORDER BY a) FROM t1" {{} 3 3}
305 3 "SELECT nth_value(b, '2') OVER (ORDER BY a) FROM t1" {{} 3 3}
306 4 "SELECT nth_value(b, 2.0) OVER (ORDER BY a) FROM t1" {{} 3 3}
307 5 "SELECT nth_value(b, '2.0') OVER (ORDER BY a) FROM t1" {{} 3 3}
308 6 "SELECT nth_value(b, 10000000) OVER (ORDER BY a) FROM t1" {{} {} {}}
309 } {
310 do_execsql_test 10.2.$tn "
311 WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
312 $stmt
313 " $res
314 }
315
316
317 #-------------------------------------------------------------------------
318 #
319 reset_db
320 do_execsql_test 11.0 {
321 CREATE TABLE t1(a INT);
322 INSERT INTO t1 VALUES(10),(15),(20),(20),(25),(30),(30),(50);
323 CREATE TABLE t3(x INT, y VARCHAR);
324 INSERT INTO t3(x,y) VALUES(10,'ten'),('15','fifteen'),(30,'thirty');
325 }
326
327 do_execsql_test 11.1 {
328 SELECT a, (SELECT y FROM t3 WHERE x=a) FROM t1 ORDER BY a;
329 } {
330 10 ten 15 fifteen 20 {} 20 {} 25 {} 30 thirty 30 thirty 50 {}
331 }
332
333 do_execsql_test 11.2 {
334 SELECT a, (SELECT y FROM t3 WHERE x=a), sum(a) OVER (ORDER BY a)
335 FROM t1 ORDER BY a;
336 } {
337 10 ten 10 15 fifteen 25 20 {} 65 20 {} 65
338 25 {} 90 30 thirty 150 30 thirty 150 50 {} 200
339 }
340
341 do_execsql_test 11.3.1 {
342 SELECT a, sum(a) OVER win FROM t1
343 WINDOW win AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
344 } {
345 10 10 15 25 20 45 20 65 25 90 30 120 30 150 50 200
346 }
347 do_execsql_test 11.3.2 {
348 SELECT a, sum(a) OVER win FROM t1
349 WINDOW win AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING)
350 } {
351 10 10 15 25 20 45 20 65 25 90 30 120 30 150 50 200
352 }
353 do_execsql_test 11.3.3 {
354 SELECT a, sum(a) OVER win FROM t1
355 WINDOW win AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)
356 } {
357 10 10 15 25 20 45 20 65 25 90 30 120 30 150 50 200
358 }
359
360 do_execsql_test 11.4.1 {
361 SELECT y, group_concat(y, '.') OVER win FROM t3
362 WINDOW win AS (
363 ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND 10 PRECEDING
364 );
365 } {
366 fifteen fifteen
367 ten fifteen.ten
368 thirty fifteen.ten.thirty
369 }
370
371 finish_test