Debian Codegraph @

test/window9.test

raw
1 # 2019 June 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
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix window9
17
18 ifcapable !windowfunc {
19 finish_test
20 return
21 }
22
23 do_execsql_test 1.0 {
24 CREATE TABLE fruits(
25 name TEXT COLLATE NOCASE,
26 color TEXT COLLATE NOCASE
27 );
28 }
29
30 do_execsql_test 1.1 {
31 INSERT INTO fruits (name, color) VALUES ('apple', 'RED');
32 INSERT INTO fruits (name, color) VALUES ('APPLE', 'yellow');
33 INSERT INTO fruits (name, color) VALUES ('pear', 'YELLOW');
34 INSERT INTO fruits (name, color) VALUES ('PEAR', 'green');
35 }
36
37 do_execsql_test 1.2 {
38 SELECT name, color, dense_rank() OVER (ORDER BY name) FROM fruits;
39 } {
40 apple RED 1
41 APPLE yellow 1
42 pear YELLOW 2
43 PEAR green 2
44 }
45
46 do_execsql_test 1.3 {
47 SELECT name, color,
48 dense_rank() OVER (PARTITION BY name ORDER BY color)
49 FROM fruits;
50 } {
51 apple RED 1
52 APPLE yellow 2
53 PEAR green 1
54 pear YELLOW 2
55 }
56
57 do_execsql_test 1.4 {
58 SELECT name, color,
59 dense_rank() OVER (ORDER BY name),
60 dense_rank() OVER (PARTITION BY name ORDER BY color)
61 FROM fruits;
62 } {
63 apple RED 1 1
64 APPLE yellow 1 2
65 PEAR green 2 1
66 pear YELLOW 2 2
67 }
68
69 do_execsql_test 1.5 {
70 SELECT name, color,
71 dense_rank() OVER (ORDER BY name),
72 dense_rank() OVER (PARTITION BY name ORDER BY color)
73 FROM fruits ORDER BY color;
74 } {
75 PEAR green 2 1
76 apple RED 1 1
77 APPLE yellow 1 2
78 pear YELLOW 2 2
79 }
80
81 do_execsql_test 2.0 {
82 CREATE TABLE t1(a BLOB, b INTEGER, c COLLATE nocase);
83 INSERT INTO t1 VALUES(1, 2, 'abc');
84 INSERT INTO t1 VALUES(3, 4, 'ABC');
85 }
86
87 do_execsql_test 2.1.1 {
88 SELECT c=='Abc' FROM t1
89 } {1 1}
90 do_execsql_test 2.1.2 {
91 SELECT c=='Abc', rank() OVER (ORDER BY b) FROM t1
92 } {1 1 1 2}
93
94 do_execsql_test 2.2.1 {
95 SELECT b=='2' FROM t1
96 } {1 0}
97 do_execsql_test 2.2.2 {
98 SELECT b=='2', rank() OVER (ORDER BY a) FROM t1
99 } {1 1 0 2}
100
101 #-------------------------------------------------------------------------
102 reset_db
103 do_execsql_test 3.0 {
104 CREATE TABLE t1(a);
105 CREATE TABLE t2(a,b,c);
106 }
107
108 do_execsql_test 3.1 {
109 SELECT EXISTS(SELECT 1 FROM t1 ORDER BY sum(a) OVER ()) FROM t1;
110 }
111
112 do_execsql_test 3.2 {
113 SELECT sum(a) OVER () FROM t2
114 ORDER BY EXISTS(SELECT 1 FROM t2 ORDER BY sum(a) OVER ());
115 }
116
117 do_catchsql_test 3.3 {
118 SELECT a, sum(a) OVER (ORDER BY a DESC) FROM t2
119 ORDER BY EXISTS(
120 SELECT 1 FROM t2 ORDER BY sum(a) OVER (ORDER BY a)
121 ) OVER (ORDER BY a);
122 } {1 {near "OVER": syntax error}}
123
124 do_catchsql_test 3.4 {
125 SELECT y, y+1, y+2 FROM (
126 SELECT c IN (
127 SELECT min(a) OVER (),
128 (abs(row_number() OVER())+22)/19,
129 max(a) OVER () FROM t1
130 ) AS y FROM t2
131 );
132 } {1 {sub-select returns 3 columns - expected 1}}
133
134 #-------------------------------------------------------------------------
135 reset_db
136 do_execsql_test 4.0 {
137 CREATE TABLE t1(a, b TEXT);
138 INSERT INTO t1 VALUES('A', 1), ('A', 2), ('2', 1), ('2', 2);
139 }
140
141 do_execsql_test 4.1.1 {
142 SELECT b, b=count(*), '1,2' FROM t1 GROUP BY b;
143 } {1 0 1,2 2 1 1,2}
144 do_execsql_test 4.1.2 {
145 SELECT b, b=count(*), group_concat(b) OVER () FROM t1 GROUP BY b;
146 } {1 0 1,2 2 1 1,2}
147
148 #--------------------------------------------------------------------------
149 reset_db
150 do_execsql_test 5.0 {
151 CREATE TABLE t1(a, b, c, d, e);
152 CREATE INDEX i1 ON t1(a, b, c, d, e);
153 }
154
155 foreach {tn sql} {
156 1 {
157 SELECT
158 sum(e) OVER (),
159 sum(e) OVER (ORDER BY a),
160 sum(e) OVER (PARTITION BY a ORDER BY b),
161 sum(e) OVER (PARTITION BY a, b ORDER BY c),
162 sum(e) OVER (PARTITION BY a, b, c ORDER BY d)
163 FROM t1;
164 }
165 2 {
166 SELECT sum(e) OVER (PARTITION BY a ORDER BY b) FROM t1 ORDER BY a;
167 }
168 } {
169 do_test 5.1.$tn {
170 execsql "EXPLAIN QUERY PLAN $sql"
171 } {~/ORDER/}
172 }
173
174 #-------------------------------------------------------------------------
175 reset_db
176 do_execsql_test 6.0 {
177 CREATE TABLE t0(c0);
178 INSERT INTO t0(c0) VALUES (0);
179 }
180
181 do_execsql_test 6.1 {
182 SELECT * FROM t0 WHERE
183 EXISTS (
184 SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0
185 ) >=1 AND
186 EXISTS (
187 SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0
188 ) <=1;
189 } {0}
190
191 do_execsql_test 6.2 {
192 SELECT * FROM t0 WHERE EXISTS (
193 SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0
194 )
195 BETWEEN 1 AND 1;
196 } {0}
197
198 #-------------------------------------------------------------------------
199 reset_db
200 do_execsql_test 7.0 {
201 DROP TABLE IF EXISTS t1;
202 CREATE TABLE t1(x, y);
203 INSERT INTO t1 VALUES(10, 1);
204 INSERT INTO t1 VALUES(20, 2);
205 INSERT INTO t1 VALUES(3, 3);
206 INSERT INTO t1 VALUES(2, 4);
207 INSERT INTO t1 VALUES(1, 5);
208 } {}
209
210
211 do_execsql_test 7.1 {
212 SELECT avg(x) OVER (ORDER BY y) AS z FROM t1 ORDER BY z
213 } {
214 7.2 8.75 10.0 11.0 15.0
215 }
216
217 do_execsql_test 7.2 {
218 SELECT avg(x) OVER (ORDER BY y) z FROM t1 ORDER BY (z IS y);
219 } {
220 10.0 15.0 11.0 8.75 7.2
221 }
222
223 do_execsql_test 7.3 {
224 SELECT avg(x) OVER (ORDER BY y) z FROM t1 ORDER BY (y IS z);
225 } {
226 10.0 15.0 11.0 8.75 7.2
227 }
228
229 do_execsql_test 7.4 {
230 SELECT avg(x) OVER (ORDER BY y) z FROM t1 ORDER BY z + 0.0;
231 } {
232 7.2 8.75 10.0 11.0 15.0
233 }
234
235 #-------------------------------------------------------------------------
236 reset_db
237 do_execsql_test 8.1.1 {
238 CREATE TABLE t1(a, b);
239 INSERT INTO t1 VALUES(1, 2), (3, 4);
240 SELECT min( sum(a) ) OVER () FROM t1;
241 } {4}
242
243 do_execsql_test 8.1.2 {
244 SELECT min( sum(a) ) OVER () FROM t1 GROUP BY a;
245 } {1 1}
246
247 do_execsql_test 8.2 {
248 CREATE VIEW v1 AS
249 SELECT 0 AS x
250 UNION
251 SELECT count() OVER() FROM (SELECT 0)
252 ORDER BY 1
253 ;
254 }
255
256 do_catchsql_test 8.3 {
257 SELECT min( max((SELECT x FROM v1)) ) OVER()
258 } {0 0}
259
260 do_execsql_test 8.4 {
261 SELECT(
262 SELECT x UNION
263 SELECT sum( avg((SELECT x FROM v1)) ) OVER()
264 )
265 FROM v1;
266 } {0.0 0.0}
267
268 #--------------------------------------------------------------------------
269 reset_db
270 do_execsql_test 9.0 {
271 CREATE TABLE t1(a, b, c);
272 INSERT INTO t1 VALUES(NULL,'bb',356);
273 INSERT INTO t1 VALUES('CB','aa',158);
274 INSERT INTO t1 VALUES('BB','aa',399);
275 INSERT INTO t1 VALUES('FF','bb',938);
276 }
277
278 do_catchsql_test 9.1 {
279 SELECT sum(c) OVER (
280 ORDER BY c RANGE BETWEEN 0 PRECEDING AND '-700' PRECEDING
281 )
282 FROM t1
283 } {1 {frame ending offset must be a non-negative number}}
284
285 #--------------------------------------------------------------------------
286 reset_db
287
288 do_execsql_test 10.0 {
289 CREATE TABLE t1(a, b);
290 INSERT INTO t1 VALUES(1, 'a');
291 INSERT INTO t1 VALUES(2, 'b');
292 INSERT INTO t1 VALUES(3, 'c');
293 INSERT INTO t1 VALUES(4, 'd');
294 INSERT INTO t1 VALUES(5, 'e');
295 INSERT INTO t1 VALUES(6, 'f');
296 }
297
298 do_execsql_test 10.1 {
299 SELECT a, min(b) OVER win
300 FROM t1
301 WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
302 } {
303 1 a
304 2 a
305 3 a
306 4 b
307 5 c
308 6 d
309 }
310
311 do_execsql_test 10.2 {
312 SELECT a, min(b) FILTER (WHERE a%2) OVER win
313 FROM t1
314 WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
315 } {
316 1 a
317 2 a
318 3 a
319 4 c
320 5 c
321 6 e
322 }
323
324 do_execsql_test 10.3 {
325 SELECT a, min(b) FILTER (WHERE (a%2)=0) OVER win
326 FROM t1
327 WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
328 } {
329 1 b
330 2 b
331 3 b
332 4 b
333 5 d
334 6 d
335 }
336
337 do_catchsql_test 10.4 {
338 SELECT a, nth_value(b, 1) FILTER (WHERE (a%2)=0) OVER win
339 FROM t1
340 WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
341 } {1 {FILTER clause may only be used with aggregate window functions}}
342
343 finish_test