Debian Codegraph @

test/windowB.test

raw
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 # and for varying separator handling by group_concat().
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix windowB
18
19 ifcapable !windowfunc {
20 finish_test
21 return
22 }
23
24 do_execsql_test 1.0 {
25 CREATE TABLE t1(a, b);
26 INSERT INTO t1 VALUES(NULL, 1);
27 INSERT INTO t1 VALUES(NULL, 2);
28 INSERT INTO t1 VALUES(NULL, 3);
29 } {}
30
31 foreach {tn win} {
32 1 { ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
33 2 { ORDER BY a NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
34 3 { ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
35 4 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
36
37 5 { ORDER BY a NULLS LAST RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING }
38 6 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING }
39
40 7 { ORDER BY a NULLS LAST RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING }
41 8 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING }
42 } {
43 do_execsql_test 1.$tn "
44 SELECT sum(b) OVER win FROM t1
45 WINDOW win AS ( $win )
46 " {6 6 6}
47 }
48
49 do_execsql_test 1.2 {
50 SELECT sum(b) OVER win FROM t1
51 WINDOW win AS (
52 ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
53 )
54 } {6 6 6}
55
56 #-------------------------------------------------------------------------
57 reset_db
58 do_execsql_test 2.0 {
59 CREATE TABLE t1(a, b);
60 INSERT INTO t1 VALUES(1, NULL);
61 INSERT INTO t1 VALUES(2, 45);
62 INSERT INTO t1 VALUES(3, 66.2);
63 INSERT INTO t1 VALUES(4, 'hello world');
64 INSERT INTO t1 VALUES(5, 'hello world');
65 INSERT INTO t1 VALUES(6, X'1234');
66 INSERT INTO t1 VALUES(7, X'1234');
67 INSERT INTO t1 VALUES(8, NULL);
68 }
69
70 foreach {tn win} {
71 1 "ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING"
72 2 "ORDER BY b RANGE BETWEEN 2 FOLLOWING AND 2 FOLLOWING"
73 3 "ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING"
74 4 "ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 2 FOLLOWING"
75 } {
76 do_execsql_test 2.1.$tn "
77 SELECT a, sum(a) OVER win FROM t1
78 WINDOW win AS ( $win )
79 ORDER BY 1
80 " {1 9 2 {} 3 {} 4 9 5 9 6 13 7 13 8 9}
81 }
82
83 #-------------------------------------------------------------------------
84 ifcapable json1 {
85 reset_db
86 do_execsql_test 3.0 {
87 CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT, x TEXT);
88 INSERT INTO testjson VALUES(1, '{"a":1}', 'a');
89 INSERT INTO testjson VALUES(2, '{"b":2}', 'b');
90 INSERT INTO testjson VALUES(3, '{"c":3}', 'c');
91 INSERT INTO testjson VALUES(4, '{"d":4}', 'd');
92 }
93
94 do_execsql_test 3.1 {
95 SELECT json_group_array(json(j)) FROM testjson;
96 } {
97 {[{"a":1},{"b":2},{"c":3},{"d":4}]}
98 }
99
100 do_execsql_test 3.2 {
101 SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson;
102 } {
103 {[{"a":1}]}
104 {[{"a":1},{"b":2}]}
105 {[{"a":1},{"b":2},{"c":3}]}
106 {[{"a":1},{"b":2},{"c":3},{"d":4}]}
107 }
108
109 do_execsql_test 3.3 {
110 SELECT json_group_array(json(j)) OVER (
111 ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
112 EXCLUDE TIES
113 ) FROM testjson;
114 } {
115 {[{"a":1}]}
116 {[{"a":1},{"b":2}]}
117 {[{"a":1},{"b":2},{"c":3}]}
118 {[{"a":1},{"b":2},{"c":3},{"d":4}]}
119 }
120
121 do_execsql_test 3.4 {
122 SELECT json_group_array(json(j)) OVER (
123 ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
124 ) FROM testjson;
125 } {
126 {[{"a":1},{"b":2}]}
127 {[{"a":1},{"b":2},{"c":3}]}
128 {[{"b":2},{"c":3},{"d":4}]}
129 {[{"c":3},{"d":4}]}
130 }
131
132 do_execsql_test 3.5 {
133 SELECT json_group_array(json(j)) OVER (
134 ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
135 ) FROM testjson;
136 } {
137 {[]}
138 {[{"a":1}]}
139 {[{"a":1},{"b":2}]}
140 {[{"b":2},{"c":3}]}
141 }
142
143 do_execsql_test 3.5a {
144 UPDATE testjson SET j = replace(j,char(125),',"e":9'||char(125));
145 SELECT j FROM testjson;
146 } {
147 {{"a":1,"e":9}}
148 {{"b":2,"e":9}}
149 {{"c":3,"e":9}}
150 {{"d":4,"e":9}}
151 }
152 do_execsql_test 3.5b {
153 SELECT group_concat(x,'') OVER (
154 ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
155 ) FROM testjson ORDER BY id;
156 } {bc cd d {}}
157 do_execsql_test 3.5c {
158 SELECT json_group_array(json(j)) OVER (
159 ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
160 ) FROM testjson;
161 } {
162 {[{"b":2,"e":9},{"c":3,"e":9}]}
163 {[{"c":3,"e":9},{"d":4,"e":9}]}
164 {[{"d":4,"e":9}]}
165 {[]}
166 }
167 do_execsql_test 3.5d {
168 SELECT json_group_object(x,json(j)) OVER (
169 ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
170 ) FROM testjson;
171 } {
172 {{"b":{"b":2,"e":9},"c":{"c":3,"e":9}}}
173 {{"c":{"c":3,"e":9},"d":{"d":4,"e":9}}}
174 {{"d":{"d":4,"e":9}}}
175 {{}}
176 }
177
178 do_execsql_test 3.7b {
179 SELECT group_concat(x,'') FILTER (WHERE id!=2) OVER (
180 ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
181 ) FROM testjson;
182 } {{} a a c}
183
184 do_execsql_test 3.7c {
185 SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
186 ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
187 ) FROM testjson
188 } {
189 {[]}
190 {[{"a":1,"e":9}]}
191 {[{"a":1,"e":9}]}
192 {[{"c":3,"e":9}]}
193 }
194 do_execsql_test 3.7d {
195 SELECT json_group_object(x,json(j)) FILTER (WHERE id!=2) OVER (
196 ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
197 ) FROM testjson
198 } {
199 {{}}
200 {{"a":{"a":1,"e":9}}}
201 {{"a":{"a":1,"e":9}}}
202 {{"c":{"c":3,"e":9}}}
203 }
204
205 #-----------------------------------------------------------------------
206 # Bug report 2026-05-18T12:34:42Z
207 #
208 # The xInverse() method used by json_group_object() should correctly
209 # account for NULL entries.
210 #
211 do_execsql_test 3.8 {
212 CREATE TABLE t1(id INT, k TEXT, v INT);
213 INSERT INTO t1 VALUES
214 (1, 'a', 1),
215 (2, 'b', 2),
216 (3, 'c', 3),
217 (4, 'd', 4),
218 (5, 'f', 5),
219 (6, 'g', 6),
220 (7, 'h', 7);
221 }
222 do_execsql_test 3.9 {
223 SELECT id, json_group_object(if(id<>1,k),v) OVER
224 (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS jx
225 FROM t1;
226 } {
227 1 {{"b":2}}
228 2 {{"b":2,"c":3}}
229 3 {{"b":2,"c":3,"d":4}}
230 4 {{"c":3,"d":4,"f":5}}
231 5 {{"d":4,"f":5,"g":6}}
232 6 {{"f":5,"g":6,"h":7}}
233 7 {{"g":6,"h":7}}
234 }
235 do_execsql_test 3.10 {
236 SELECT id, json_group_object(if(id>4,k),v) OVER
237 (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS jx
238 FROM t1;
239 } {
240 1 {{}}
241 2 {{}}
242 3 {{}}
243 4 {{"f":5}}
244 5 {{"f":5,"g":6}}
245 6 {{"f":5,"g":6,"h":7}}
246 7 {{"g":6,"h":7}}
247 }
248 do_execsql_test 3.11 {
249 SELECT id, json_group_object(if(id>4,k||'@'),v) OVER
250 (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS jx
251 FROM t1;
252 } {
253 1 {{}}
254 2 {{}}
255 3 {{}}
256 4 {{"f@":5}}
257 5 {{"f@":5,"g@":6}}
258 6 {{"f@":5,"g@":6,"h@":7}}
259 7 {{"g@":6,"h@":7}}
260 }
261 do_execsql_test 3.12 {
262 SELECT id, json_group_object(k,v) OVER
263 (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS jx
264 FROM t1;
265 } {
266 1 {{"a":1,"b":2}}
267 2 {{"a":1,"b":2,"c":3}}
268 3 {{"b":2,"c":3,"d":4}}
269 4 {{"c":3,"d":4,"f":5}}
270 5 {{"d":4,"f":5,"g":6}}
271 6 {{"f":5,"g":6,"h":7}}
272 7 {{"g":6,"h":7}}
273 }
274 do_execsql_test 3.13 {
275 SELECT id, json_group_object(if(id>1 AND id<7,k),v) OVER
276 (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS jx
277 FROM t1;
278 } {
279 1 {{"b":2}}
280 2 {{"b":2,"c":3}}
281 3 {{"b":2,"c":3,"d":4}}
282 4 {{"c":3,"d":4,"f":5}}
283 5 {{"d":4,"f":5,"g":6}}
284 6 {{"f":5,"g":6}}
285 7 {{"g":6}}
286 }
287 do_execsql_test 3.14 {
288 SELECT id, json_group_object(if(id>2 AND id<6,k),v) OVER
289 (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS jx
290 FROM t1;
291 } {
292 1 {{}}
293 2 {{"c":3}}
294 3 {{"c":3,"d":4}}
295 4 {{"c":3,"d":4,"f":5}}
296 5 {{"d":4,"f":5}}
297 6 {{"f":5}}
298 7 {{}}
299 }
300 do_execsql_test 3.15 {
301 SELECT id, json_group_object(if(id<2 OR id>6,k),v) OVER
302 (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS jx
303 FROM t1;
304 } {
305 1 {{"a":1}}
306 2 {{"a":1}}
307 3 {{}}
308 4 {{}}
309 5 {{}}
310 6 {{"h":7}}
311 7 {{"h":7}}
312 }
313 do_execsql_test 3.16 {
314 SELECT id, json_group_object(if(id<3 OR id>5,k),v) OVER
315 (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS jx
316 FROM t1;
317 } {
318 1 {{"a":1,"b":2}}
319 2 {{"a":1,"b":2}}
320 3 {{"b":2}}
321 4 {{}}
322 5 {{"g":6}}
323 6 {{"g":6,"h":7}}
324 7 {{"g":6,"h":7}}
325 }
326 }
327
328 #-------------------------------------------------------------------------
329 reset_db
330 do_execsql_test 4.0 {
331 CREATE TABLE x(a);
332 INSERT INTO x VALUES(1);
333 INSERT INTO x VALUES(2);
334 }
335
336 do_execsql_test 4.1 {
337 WITH y AS (
338 SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a)
339 )
340 SELECT * FROM y;
341 } {
342 1 1
343 }
344
345 do_catchsql_test 4.2 {
346 WITH y AS (
347 SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION
348 BY fake_column))
349 SELECT * FROM y;
350 } {1 {no such column: fake_column}}
351
352 do_catchsql_test 4.3 {
353 SELECT 1 WINDOW win AS (PARTITION BY fake_column);
354 } {0 1}
355
356 #-------------------------------------------------------------------------
357 reset_db
358 do_execsql_test 5.0 {
359 CREATE TABLE t1(a, c);
360 CREATE INDEX i1 ON t1(a);
361
362 INSERT INTO t1 VALUES(0, 421);
363 INSERT INTO t1 VALUES(1, 844);
364 INSERT INTO t1 VALUES(2, 1001);
365 }
366
367 do_execsql_test 5.1 {
368 SELECT a, sum(c) OVER (
369 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING
370 ) FROM t1;
371 } {0 {} 1 {} 2 {}}
372
373 do_execsql_test 5.2 {
374 INSERT INTO t1 VALUES(NULL, 123);
375 INSERT INTO t1 VALUES(NULL, 111);
376 INSERT INTO t1 VALUES('xyz', 222);
377 INSERT INTO t1 VALUES('xyz', 333);
378
379 SELECT a, sum(c) OVER (
380 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING
381 ) FROM t1;
382 } {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
383
384 do_execsql_test 5.3 {
385 SELECT a, sum(c) OVER (
386 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
387 ) FROM t1;
388 } {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
389
390 do_execsql_test 5.4 {
391 SELECT a, sum(c) OVER (
392 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING EXCLUDE NO OTHERS
393 ) FROM t1;
394 } {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
395
396 do_execsql_test 5.5 {
397 SELECT a, sum(c) OVER (
398 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS
399 ) FROM t1;
400 } {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
401
402 #-------------------------------------------------------------------------
403 reset_db
404 do_execsql_test 6.0 {
405 CREATE TABLE t1(a, c);
406 CREATE INDEX i1 ON t1(a);
407
408 INSERT INTO t1 VALUES(7, 997);
409 INSERT INTO t1 VALUES(8, 997);
410 INSERT INTO t1 VALUES('abc', 1001);
411 }
412 do_execsql_test 6.1 {
413 SELECT a, sum(c) OVER (
414 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
415 ) FROM t1;
416 } {7 {} 8 {} abc 1001}
417 do_execsql_test 6.2 {
418 SELECT a, sum(c) OVER (
419 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS
420 ) FROM t1;
421 } {7 {} 8 {} abc 1001}
422
423 #-------------------------------------------------------------------------
424 reset_db
425 do_execsql_test 7.0 {
426 CREATE TABLE t1(a, c);
427 CREATE INDEX i1 ON t1(a);
428
429 INSERT INTO t1 VALUES(NULL, 46);
430 INSERT INTO t1 VALUES(NULL, 45);
431 INSERT INTO t1 VALUES(7, 997);
432 INSERT INTO t1 VALUES(7, 1000);
433 INSERT INTO t1 VALUES(8, 997);
434 INSERT INTO t1 VALUES(8, 1000);
435 INSERT INTO t1 VALUES('abc', 1001);
436 INSERT INTO t1 VALUES('abc', 1004);
437 INSERT INTO t1 VALUES('xyz', 3333);
438 }
439
440 do_execsql_test 7.1 {
441 SELECT a, max(c) OVER (
442 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
443 ) FROM t1;
444 } {{} 46 {} 46 7 {} 7 {} 8 {} 8 {} abc 1004 abc 1004 xyz 3333}
445 do_execsql_test 7.2 {
446 SELECT a, min(c) OVER (
447 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
448 ) FROM t1;
449 } {{} 45 {} 45 7 {} 7 {} 8 {} 8 {} abc 1001 abc 1001 xyz 3333}
450
451 do_execsql_test 7.3 {
452 SELECT a, max(c) OVER (
453 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING
454 ) FROM t1;
455 } {{} 46 {} 46 7 {} 7 {} 8 {} 8 {} abc 1004 abc 1004 xyz 3333}
456 do_execsql_test 7.4 {
457 SELECT a, min(c) OVER (
458 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING
459 ) FROM t1;
460 } {{} 45 {} 45 7 {} 7 {} 8 {} 8 {} abc 1001 abc 1001 xyz 3333}
461
462 #-------------------------------------------------------------------------
463 reset_db
464 do_execsql_test 8.0 {
465 BEGIN TRANSACTION;
466 CREATE TABLE t1(a, c);
467 INSERT INTO t1 VALUES('aa', 111);
468 INSERT INTO t1 VALUES('BB', 660);
469 INSERT INTO t1 VALUES('CC', 938);
470 INSERT INTO t1 VALUES('dd', 979);
471 COMMIT;
472
473 CREATE INDEX i1 ON t1(a COLLATE nocase);
474 }
475
476 do_execsql_test 8.1 {
477 SELECT sum(c) OVER
478 (ORDER BY a COLLATE nocase RANGE BETWEEN 10.0 PRECEDING AND 5.0 PRECEDING)
479 FROM t1;
480 } {111 660 938 979}
481
482 do_execsql_test 9.0 {
483 CREATE TABLE seps(x);
484 INSERT INTO seps(x) VALUES ('1'), ('22'), ('333'), ('4444');
485 SELECT group_concat('-', x)
486 OVER ( ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING )
487 FROM seps;
488 } {-22- -22-333- -333-4444- -4444-}
489
490 #-------------------------------------------------------------------------
491 reset_db
492 do_execsql_test 10.1 {
493 CREATE TABLE t1(i INTEGER PRIMARY KEY, v);
494 INSERT INTO t1 VALUES( 1, 'one' );
495 INSERT INTO t1 VALUES( 2, 'two' );
496 }
497
498 do_execsql_test 10.2 {
499 SELECT
500 json_group_array( v ) OVER w,
501 json_group_array( v ) OVER w
502 FROM t1
503 window w as (
504 range between unbounded preceding and unbounded following
505 )
506 } {
507 {["one","two"]}
508 {["one","two"]}
509 {["one","two"]}
510 {["one","two"]}
511 }
512
513 do_execsql_test 10.3 {
514 SELECT
515 group_concat( v ) OVER w,
516 json_group_array( v ) OVER w,
517 json_group_array( v ) OVER w,
518 group_concat( v ) OVER w
519 FROM t1
520 window w as (
521 range between unbounded preceding and unbounded following
522 )
523 } {
524 one,two
525 {["one","two"]}
526 {["one","two"]}
527 one,two
528
529 one,two
530 {["one","two"]}
531 {["one","two"]}
532 one,two
533 }
534
535 ifcapable json1&&vtab {
536 if {[permutation]!="no_optimization"} {
537
538 do_execsql_test 11.0 {
539 SELECT value FROM json_each('[1,2,3,4,5]');
540 } {1 2 3 4 5}
541
542 do_execsql_test 11.1 {
543 SELECT key, value FROM json_each('[1,2,3,4,5]');
544 } {0 1 1 2 2 3 3 4 4 5}
545 do_execsql_test 11.2 {
546 SELECT rowid, value FROM json_each('[1,2,3,4,5]');
547 } {0 1 1 2 2 3 3 4 4 5}
548
549 do_execsql_test 11.3 {
550 SELECT sum(value) OVER (ORDER BY rowid) FROM json_each('[1,2,3,4,5]')
551 } {1 3 6 10 15}
552
553 do_execsql_test 11.4 {
554 SELECT sum(value) OVER (
555 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
556 ) FROM json_each('[1,2,3,4,5]')
557 } {1 3 6 10 15}
558
559 do_eqp_test 11.5 {
560 SELECT sum(value) OVER (
561 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
562 ) FROM json_each('[1,2,3,4,5]')
563 } {
564 QUERY PLAN
565 |--CO-ROUTINE (subquery-xxxxxx)
566 | `--SCAN json_each VIRTUAL TABLE INDEX 1:
567 `--SCAN (subquery-xxxxxx)
568 }
569
570 do_eqp_test 11.6 {
571 SELECT sum(value) OVER (ORDER BY rowid) FROM json_each('[1,2,3,4,5]')
572 } {
573 QUERY PLAN
574 |--CO-ROUTINE (subquery-xxxxxx)
575 | `--SCAN json_each VIRTUAL TABLE INDEX 1:
576 `--SCAN (subquery-xxxxxx)
577 }
578
579 do_eqp_test 11.8 {
580 SELECT sum(value) OVER (ORDER BY rowid DESC) FROM json_each('[1,2,3,4,5]')
581 } {
582 QUERY PLAN
583 |--CO-ROUTINE (subquery-xxxxxx)
584 | |--SCAN json_each VIRTUAL TABLE INDEX 1:
585 | `--USE TEMP B-TREE FOR ORDER BY
586 `--SCAN (subquery-xxxxxx)
587 }
588
589 do_execsql_test 11.9 {
590 SELECT sum(value) OVER (ORDER BY rowid DESC) FROM json_each('[1,2,3,4,5]')
591 } {5 9 12 14 15}
592
593 do_execsql_test 11.10 {
594 SELECT sum(value) OVER (ORDER BY value ASC) FROM json_each('[2,1,4,3,5]')
595 } {1 3 6 10 15}
596 do_eqp_test 11.11 {
597 SELECT sum(value) OVER (ORDER BY value ASC) FROM json_each('[2,1,4,3,5]')
598 } {
599 QUERY PLAN
600 |--CO-ROUTINE (subquery-xxxxxx)
601 | |--SCAN json_each VIRTUAL TABLE INDEX 1:
602 | `--USE TEMP B-TREE FOR ORDER BY
603 `--SCAN (subquery-xxxxxx)
604 }
605 }}
606
607 finish_test