xref: /sqlite-3.40.0/test/windowB.test (revision e2ba6df9) 
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#
13
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16set testprefix windowB
17
18ifcapable !windowfunc {
19  finish_test
20  return
21}
22
23do_execsql_test 1.0 {
24  CREATE TABLE t1(a, b);
25  INSERT INTO t1 VALUES(NULL, 1);
26  INSERT INTO t1 VALUES(NULL, 2);
27  INSERT INTO t1 VALUES(NULL, 3);
28} {}
29
30foreach {tn win} {
31  1 { ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
32  2 { ORDER BY a NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
33  3 { ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
34  4 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
35
36  5 { ORDER BY a      NULLS LAST  RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING }
37  6 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING }
38
39  7 { ORDER BY a      NULLS LAST  RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING }
40  8 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING }
41} {
42  do_execsql_test 1.$tn "
43    SELECT sum(b) OVER win FROM t1
44    WINDOW win AS ( $win )
45  " {6 6 6}
46}
47
48do_execsql_test 1.2 {
49  SELECT sum(b) OVER win FROM t1
50  WINDOW win AS (
51    ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
52  )
53} {6 6 6}
54
55#-------------------------------------------------------------------------
56reset_db
57do_execsql_test 2.0 {
58  CREATE TABLE t1(a, b);
59  INSERT INTO t1 VALUES(1, NULL);
60  INSERT INTO t1 VALUES(2, 45);
61  INSERT INTO t1 VALUES(3, 66.2);
62  INSERT INTO t1 VALUES(4, 'hello world');
63  INSERT INTO t1 VALUES(5, 'hello world');
64  INSERT INTO t1 VALUES(6, X'1234');
65  INSERT INTO t1 VALUES(7, X'1234');
66  INSERT INTO t1 VALUES(8, NULL);
67}
68
69foreach {tn win} {
70  1 "ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING"
71  2 "ORDER BY b RANGE BETWEEN 2 FOLLOWING AND 2 FOLLOWING"
72  3 "ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING"
73  4 "ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 2 FOLLOWING"
74} {
75  do_execsql_test 2.1.$tn "
76    SELECT a, sum(a) OVER win FROM t1
77    WINDOW win AS ( $win )
78    ORDER BY 1
79  " {1 9   2 {}  3 {}  4 9  5 9  6 13  7 13  8 9}
80}
81
82#-------------------------------------------------------------------------
83reset_db
84do_execsql_test 3.0 {
85  CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT);
86  INSERT INTO testjson VALUES(1, '{"a":1}');
87  INSERT INTO testjson VALUES(2, '{"b":2}');
88}
89
90do_execsql_test 3.1 {
91  SELECT json_group_array(json(j)) FROM testjson;
92} {
93  {[{"a":1},{"b":2}]}
94}
95
96breakpoint
97do_execsql_test 3.2 {
98  SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson;
99} {
100  {[{"a":1}]}
101  {[{"a":1},{"b":2}]}
102}
103
104
105finish_test
106
107