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, x TEXT); 87 INSERT INTO testjson VALUES(1, '{"a":1}', 'a'); 88 INSERT INTO testjson VALUES(2, '{"b":2}', 'b'); 89 INSERT INTO testjson VALUES(3, '{"c":3}', 'c'); 90 INSERT INTO testjson VALUES(4, '{"d":4}', 'd'); 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 111 EXCLUDE TIES 112 ) FROM testjson; 113 } { 114 {[{"a":1}]} 115 {[{"a":1},{"b":2}]} 116 {[{"a":1},{"b":2},{"c":3}]} 117 {[{"a":1},{"b":2},{"c":3},{"d":4}]} 118 } 119 120 do_execsql_test 3.4 { 121 SELECT json_group_array(json(j)) OVER ( 122 ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 123 ) FROM testjson; 124 } { 125 {[{"a":1},{"b":2}]} 126 {[{"a":1},{"b":2},{"c":3}]} 127 {[{"b":2},{"c":3},{"d":4}]} 128 {[{"c":3},{"d":4}]} 129 } 130 131 do_execsql_test 3.5 { 132 SELECT json_group_array(json(j)) OVER ( 133 ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING 134 ) FROM testjson; 135 } { 136 {[]} 137 {[{"a":1}]} 138 {[{"a":1},{"b":2}]} 139 {[{"b":2},{"c":3}]} 140 } 141 142 do_execsql_test 3.5a { 143 UPDATE testjson SET j = replace(j,char(125),',"e":9'||char(125)); 144 SELECT j FROM testjson; 145 } { 146 {{"a":1,"e":9}} 147 {{"b":2,"e":9}} 148 {{"c":3,"e":9}} 149 {{"d":4,"e":9}} 150 } 151 do_execsql_test 3.5b { 152 SELECT group_concat(x,'') OVER ( 153 ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING 154 ) FROM testjson ORDER BY id; 155 } {bc cd d {}} 156 do_execsql_test 3.5c { 157 SELECT json_group_array(json(j)) OVER ( 158 ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING 159 ) FROM testjson; 160 } { 161 {[{"b":2,"e":9},{"c":3,"e":9}]} 162 {[{"c":3,"e":9},{"d":4,"e":9}]} 163 {[{"d":4,"e":9}]} 164 {[]} 165 } 166 do_execsql_test 3.5d { 167 SELECT json_group_object(x,json(j)) OVER ( 168 ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING 169 ) FROM testjson; 170 } { 171 {{"b":{"b":2,"e":9},"c":{"c":3,"e":9}}} 172 {{"c":{"c":3,"e":9},"d":{"d":4,"e":9}}} 173 {{"d":{"d":4,"e":9}}} 174 {{}} 175 } 176 177 do_execsql_test 3.7b { 178 SELECT group_concat(x,'') FILTER (WHERE id!=2) OVER ( 179 ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING 180 ) FROM testjson; 181 } {{} a a c} 182 183 do_execsql_test 3.7c { 184 SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER ( 185 ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING 186 ) FROM testjson 187 } { 188 {[]} 189 {[{"a":1,"e":9}]} 190 {[{"a":1,"e":9}]} 191 {[{"c":3,"e":9}]} 192 } 193 do_execsql_test 3.7d { 194 SELECT json_group_object(x,json(j)) FILTER (WHERE id!=2) OVER ( 195 ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING 196 ) FROM testjson 197 } { 198 {{}} 199 {{"a":{"a":1,"e":9}}} 200 {{"a":{"a":1,"e":9}}} 201 {{"c":{"c":3,"e":9}}} 202 } 203} 204 205#------------------------------------------------------------------------- 206reset_db 207do_execsql_test 4.0 { 208 CREATE TABLE x(a); 209 INSERT INTO x VALUES(1); 210 INSERT INTO x VALUES(2); 211} 212 213do_execsql_test 4.1 { 214 WITH y AS ( 215 SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a) 216 ) 217 SELECT * FROM y; 218} { 219 1 1 220} 221 222do_catchsql_test 4.2 { 223 WITH y AS ( 224 SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION 225 BY fake_column)) 226 SELECT * FROM y; 227} {1 {no such column: fake_column}} 228 229do_catchsql_test 4.3 { 230 SELECT 1 WINDOW win AS (PARTITION BY fake_column); 231} {0 1} 232 233#------------------------------------------------------------------------- 234reset_db 235do_execsql_test 5.0 { 236 CREATE TABLE t1(a, c); 237 CREATE INDEX i1 ON t1(a); 238 239 INSERT INTO t1 VALUES(0, 421); 240 INSERT INTO t1 VALUES(1, 844); 241 INSERT INTO t1 VALUES(2, 1001); 242} 243 244do_execsql_test 5.1 { 245 SELECT a, sum(c) OVER ( 246 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING 247 ) FROM t1; 248} {0 {} 1 {} 2 {}} 249 250do_execsql_test 5.2 { 251 INSERT INTO t1 VALUES(NULL, 123); 252 INSERT INTO t1 VALUES(NULL, 111); 253 INSERT INTO t1 VALUES('xyz', 222); 254 INSERT INTO t1 VALUES('xyz', 333); 255 256 SELECT a, sum(c) OVER ( 257 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING 258 ) FROM t1; 259} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} 260 261do_execsql_test 5.3 { 262 SELECT a, sum(c) OVER ( 263 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING 264 ) FROM t1; 265} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} 266 267do_execsql_test 5.4 { 268 SELECT a, sum(c) OVER ( 269 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING EXCLUDE NO OTHERS 270 ) FROM t1; 271} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} 272 273do_execsql_test 5.5 { 274 SELECT a, sum(c) OVER ( 275 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS 276 ) FROM t1; 277} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} 278 279#------------------------------------------------------------------------- 280reset_db 281do_execsql_test 6.0 { 282 CREATE TABLE t1(a, c); 283 CREATE INDEX i1 ON t1(a); 284 285 INSERT INTO t1 VALUES(7, 997); 286 INSERT INTO t1 VALUES(8, 997); 287 INSERT INTO t1 VALUES('abc', 1001); 288} 289do_execsql_test 6.1 { 290 SELECT a, sum(c) OVER ( 291 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING 292 ) FROM t1; 293} {7 {} 8 {} abc 1001} 294do_execsql_test 6.2 { 295 SELECT a, sum(c) OVER ( 296 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS 297 ) FROM t1; 298} {7 {} 8 {} abc 1001} 299 300#------------------------------------------------------------------------- 301reset_db 302do_execsql_test 7.0 { 303 CREATE TABLE t1(a, c); 304 CREATE INDEX i1 ON t1(a); 305 306 INSERT INTO t1 VALUES(NULL, 46); 307 INSERT INTO t1 VALUES(NULL, 45); 308 INSERT INTO t1 VALUES(7, 997); 309 INSERT INTO t1 VALUES(7, 1000); 310 INSERT INTO t1 VALUES(8, 997); 311 INSERT INTO t1 VALUES(8, 1000); 312 INSERT INTO t1 VALUES('abc', 1001); 313 INSERT INTO t1 VALUES('abc', 1004); 314 INSERT INTO t1 VALUES('xyz', 3333); 315} 316 317do_execsql_test 7.1 { 318 SELECT a, max(c) OVER ( 319 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING 320 ) FROM t1; 321} {{} 46 {} 46 7 {} 7 {} 8 {} 8 {} abc 1004 abc 1004 xyz 3333} 322do_execsql_test 7.2 { 323 SELECT a, min(c) OVER ( 324 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING 325 ) FROM t1; 326} {{} 45 {} 45 7 {} 7 {} 8 {} 8 {} abc 1001 abc 1001 xyz 3333} 327 328do_execsql_test 7.3 { 329 SELECT a, max(c) OVER ( 330 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING 331 ) FROM t1; 332} {{} 46 {} 46 7 {} 7 {} 8 {} 8 {} abc 1004 abc 1004 xyz 3333} 333do_execsql_test 7.4 { 334 SELECT a, min(c) OVER ( 335 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING 336 ) FROM t1; 337} {{} 45 {} 45 7 {} 7 {} 8 {} 8 {} abc 1001 abc 1001 xyz 3333} 338 339finish_test 340