xref: /sqlite-3.40.0/test/windowB.test (revision 51a75aaa) 
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#-------------------------------------------------------------------------
83ifcapable json1 {
84  reset_db
85  do_execsql_test 3.0 {
86    CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT);
87    INSERT INTO testjson VALUES(1, '{"a":1}');
88    INSERT INTO testjson VALUES(2, '{"b":2}');
89    INSERT INTO testjson VALUES(3, '{"c":3}');
90    INSERT INTO testjson VALUES(4, '{"d":4}');
91  }
92
93  do_execsql_test 3.1 {
94    SELECT json_group_array(json(j)) FROM testjson;
95  } {
96    {[{"a":1},{"b":2},{"c":3},{"d":4}]}
97  }
98
99  do_execsql_test 3.2 {
100    SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson;
101  } {
102    {[{"a":1}]}
103    {[{"a":1},{"b":2}]}
104    {[{"a":1},{"b":2},{"c":3}]}
105    {[{"a":1},{"b":2},{"c":3},{"d":4}]}
106  }
107
108  do_execsql_test 3.3 {
109    SELECT json_group_array(json(j)) OVER (
110      ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
111    ) FROM testjson;
112  } {
113    {[{"a":1}]}
114    {[{"a":1},{"b":2}]}
115    {[{"a":1},{"b":2},{"c":3}]}
116    {[{"a":1},{"b":2},{"c":3},{"d":4}]}
117  }
118
119  do_execsql_test 3.4 {
120    SELECT json_group_array(json(j)) OVER (
121      ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
122    ) FROM testjson;
123  } {
124    {[{"a":1},{"b":2}]}
125    {[{"a":1},{"b":2},{"c":3}]}
126    {[{"b":2},{"c":3},{"d":4}]}
127    {[{"c":3},{"d":4}]}
128  }
129
130  do_execsql_test 3.5 {
131    SELECT json_group_array(json(j)) OVER (
132      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
133    ) FROM testjson;
134  } {
135    {[]}
136    {[{"a":1}]}
137    {[{"a":1},{"b":2}]}
138    {[{"b":2},{"c":3}]}
139  }
140
141  if 0 {
142
143  do_execsql_test 3.5 {
144    SELECT json_group_array(json(j)) OVER (
145      ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
146    ) FROM testjson;
147  } {
148    {[]}
149    {[{"a":1}]}
150    {[{"a":1},{"b":2}]}
151    {[{"b":2},{"c":3}]}
152  }
153
154  explain_i {
155    SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
156      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
157    ) FROM testjson;
158  }
159  do_execsql_test 3.7 {
160  PRAGMA vdbe_trace = 1;
161    SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
162      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
163    ) FROM testjson;
164  } {
165    {[]}
166    {[{"a":1}]}
167    {[{"a":1}]}
168    {[{"c":3}]}
169  }
170
171  }
172}
173
174finish_test
175