github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/windowB.test (about) 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 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix windowB 17 18 ifcapable !windowfunc { 19 finish_test 20 return 21 } 22 23 do_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 30 foreach {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 48 do_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 #------------------------------------------------------------------------- 56 reset_db 57 do_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 69 foreach {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 #------------------------------------------------------------------------- 83 ifcapable 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 #------------------------------------------------------------------------- 206 reset_db 207 do_execsql_test 4.0 { 208 CREATE TABLE x(a); 209 INSERT INTO x VALUES(1); 210 INSERT INTO x VALUES(2); 211 } 212 213 do_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 222 do_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 229 do_catchsql_test 4.3 { 230 SELECT 1 WINDOW win AS (PARTITION BY fake_column); 231 } {0 1} 232 233 #------------------------------------------------------------------------- 234 reset_db 235 do_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 244 do_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 250 do_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 261 do_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 267 do_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 273 do_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 #------------------------------------------------------------------------- 280 reset_db 281 do_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 } 289 do_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} 294 do_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 #------------------------------------------------------------------------- 301 reset_db 302 do_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 317 do_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} 322 do_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 328 do_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} 333 do_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 339 #------------------------------------------------------------------------- 340 reset_db 341 do_execsql_test 8.0 { 342 BEGIN TRANSACTION; 343 CREATE TABLE t1(a, c); 344 INSERT INTO t1 VALUES('aa', 111); 345 INSERT INTO t1 VALUES('BB', 660); 346 INSERT INTO t1 VALUES('CC', 938); 347 INSERT INTO t1 VALUES('dd', 979); 348 COMMIT; 349 350 CREATE INDEX i1 ON t1(a COLLATE nocase); 351 } 352 353 do_execsql_test 8.1 { 354 SELECT sum(c) OVER 355 (ORDER BY a COLLATE nocase RANGE BETWEEN 10.0 PRECEDING AND 5.0 PRECEDING) 356 FROM t1; 357 } {111 660 938 979} 358 359 360 finish_test