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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix windowB 18 19ifcapable !windowfunc { 20 finish_test 21 return 22} 23 24do_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 31foreach {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 49do_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#------------------------------------------------------------------------- 57reset_db 58do_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 70foreach {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#------------------------------------------------------------------------- 84ifcapable 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#------------------------------------------------------------------------- 207reset_db 208do_execsql_test 4.0 { 209 CREATE TABLE x(a); 210 INSERT INTO x VALUES(1); 211 INSERT INTO x VALUES(2); 212} 213 214do_execsql_test 4.1 { 215 WITH y AS ( 216 SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a) 217 ) 218 SELECT * FROM y; 219} { 220 1 1 221} 222 223do_catchsql_test 4.2 { 224 WITH y AS ( 225 SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION 226 BY fake_column)) 227 SELECT * FROM y; 228} {1 {no such column: fake_column}} 229 230do_catchsql_test 4.3 { 231 SELECT 1 WINDOW win AS (PARTITION BY fake_column); 232} {0 1} 233 234#------------------------------------------------------------------------- 235reset_db 236do_execsql_test 5.0 { 237 CREATE TABLE t1(a, c); 238 CREATE INDEX i1 ON t1(a); 239 240 INSERT INTO t1 VALUES(0, 421); 241 INSERT INTO t1 VALUES(1, 844); 242 INSERT INTO t1 VALUES(2, 1001); 243} 244 245do_execsql_test 5.1 { 246 SELECT a, sum(c) OVER ( 247 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING 248 ) FROM t1; 249} {0 {} 1 {} 2 {}} 250 251do_execsql_test 5.2 { 252 INSERT INTO t1 VALUES(NULL, 123); 253 INSERT INTO t1 VALUES(NULL, 111); 254 INSERT INTO t1 VALUES('xyz', 222); 255 INSERT INTO t1 VALUES('xyz', 333); 256 257 SELECT a, sum(c) OVER ( 258 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING 259 ) FROM t1; 260} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} 261 262do_execsql_test 5.3 { 263 SELECT a, sum(c) OVER ( 264 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING 265 ) FROM t1; 266} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} 267 268do_execsql_test 5.4 { 269 SELECT a, sum(c) OVER ( 270 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING EXCLUDE NO OTHERS 271 ) FROM t1; 272} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} 273 274do_execsql_test 5.5 { 275 SELECT a, sum(c) OVER ( 276 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS 277 ) FROM t1; 278} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} 279 280#------------------------------------------------------------------------- 281reset_db 282do_execsql_test 6.0 { 283 CREATE TABLE t1(a, c); 284 CREATE INDEX i1 ON t1(a); 285 286 INSERT INTO t1 VALUES(7, 997); 287 INSERT INTO t1 VALUES(8, 997); 288 INSERT INTO t1 VALUES('abc', 1001); 289} 290do_execsql_test 6.1 { 291 SELECT a, sum(c) OVER ( 292 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING 293 ) FROM t1; 294} {7 {} 8 {} abc 1001} 295do_execsql_test 6.2 { 296 SELECT a, sum(c) OVER ( 297 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS 298 ) FROM t1; 299} {7 {} 8 {} abc 1001} 300 301#------------------------------------------------------------------------- 302reset_db 303do_execsql_test 7.0 { 304 CREATE TABLE t1(a, c); 305 CREATE INDEX i1 ON t1(a); 306 307 INSERT INTO t1 VALUES(NULL, 46); 308 INSERT INTO t1 VALUES(NULL, 45); 309 INSERT INTO t1 VALUES(7, 997); 310 INSERT INTO t1 VALUES(7, 1000); 311 INSERT INTO t1 VALUES(8, 997); 312 INSERT INTO t1 VALUES(8, 1000); 313 INSERT INTO t1 VALUES('abc', 1001); 314 INSERT INTO t1 VALUES('abc', 1004); 315 INSERT INTO t1 VALUES('xyz', 3333); 316} 317 318do_execsql_test 7.1 { 319 SELECT a, max(c) OVER ( 320 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING 321 ) FROM t1; 322} {{} 46 {} 46 7 {} 7 {} 8 {} 8 {} abc 1004 abc 1004 xyz 3333} 323do_execsql_test 7.2 { 324 SELECT a, min(c) OVER ( 325 ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING 326 ) FROM t1; 327} {{} 45 {} 45 7 {} 7 {} 8 {} 8 {} abc 1001 abc 1001 xyz 3333} 328 329do_execsql_test 7.3 { 330 SELECT a, max(c) OVER ( 331 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING 332 ) FROM t1; 333} {{} 46 {} 46 7 {} 7 {} 8 {} 8 {} abc 1004 abc 1004 xyz 3333} 334do_execsql_test 7.4 { 335 SELECT a, min(c) OVER ( 336 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING 337 ) FROM t1; 338} {{} 45 {} 45 7 {} 7 {} 8 {} 8 {} abc 1001 abc 1001 xyz 3333} 339 340#------------------------------------------------------------------------- 341reset_db 342do_execsql_test 8.0 { 343 BEGIN TRANSACTION; 344 CREATE TABLE t1(a, c); 345 INSERT INTO t1 VALUES('aa', 111); 346 INSERT INTO t1 VALUES('BB', 660); 347 INSERT INTO t1 VALUES('CC', 938); 348 INSERT INTO t1 VALUES('dd', 979); 349 COMMIT; 350 351 CREATE INDEX i1 ON t1(a COLLATE nocase); 352} 353 354do_execsql_test 8.1 { 355 SELECT sum(c) OVER 356 (ORDER BY a COLLATE nocase RANGE BETWEEN 10.0 PRECEDING AND 5.0 PRECEDING) 357 FROM t1; 358} {111 660 938 979} 359 360do_execsql_test 9.0 { 361 CREATE TABLE seps(x); 362 INSERT INTO seps(x) VALUES ('1'), ('22'), ('333'), ('4444'); 363 SELECT group_concat('-', x) 364 OVER ( ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) 365 FROM seps; 366} {-22- -22-333- -333-4444- -4444-} 367 368#------------------------------------------------------------------------- 369reset_db 370do_execsql_test 10.1 { 371 CREATE TABLE t1(i INTEGER PRIMARY KEY, v); 372 INSERT INTO t1 VALUES( 1, 'one' ); 373 INSERT INTO t1 VALUES( 2, 'two' ); 374} 375 376do_execsql_test 10.2 { 377 SELECT 378 json_group_array( v ) OVER w, 379 json_group_array( v ) OVER w 380 FROM t1 381 window w as ( 382 range between unbounded preceding and unbounded following 383 ) 384} { 385 {["one","two"]} 386 {["one","two"]} 387 {["one","two"]} 388 {["one","two"]} 389} 390 391do_execsql_test 10.3 { 392 SELECT 393 group_concat( v ) OVER w, 394 json_group_array( v ) OVER w, 395 json_group_array( v ) OVER w, 396 group_concat( v ) OVER w 397 FROM t1 398 window w as ( 399 range between unbounded preceding and unbounded following 400 ) 401} { 402 one,two 403 {["one","two"]} 404 {["one","two"]} 405 one,two 406 407 one,two 408 {["one","two"]} 409 {["one","two"]} 410 one,two 411} 412 413finish_test 414