1# 2018 May 8 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# This file implements regression tests for SQLite library. 12# 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix window1 17 18ifcapable !windowfunc { 19 finish_test 20 return 21} 22 23do_execsql_test 1.0 { 24 CREATE TABLE t1(a, b, c, d); 25 INSERT INTO t1 VALUES(1, 2, 3, 4); 26 INSERT INTO t1 VALUES(5, 6, 7, 8); 27 INSERT INTO t1 VALUES(9, 10, 11, 12); 28} 29 30do_execsql_test 1.1 { 31 SELECT sum(b) OVER () FROM t1 32} {18 18 18} 33 34do_execsql_test 1.2 { 35 SELECT a, sum(b) OVER () FROM t1 36} {1 18 5 18 9 18} 37 38do_execsql_test 1.3 { 39 SELECT a, 4 + sum(b) OVER () FROM t1 40} {1 22 5 22 9 22} 41 42do_execsql_test 1.4 { 43 SELECT a + 4 + sum(b) OVER () FROM t1 44} {23 27 31} 45 46do_execsql_test 1.5 { 47 SELECT a, sum(b) OVER (PARTITION BY c) FROM t1 48} {1 2 5 6 9 10} 49 50foreach {tn sql} { 51 1 "SELECT sum(b) OVER () FROM t1" 52 2 "SELECT sum(b) OVER (PARTITION BY c) FROM t1" 53 3 "SELECT sum(b) OVER (ORDER BY c) FROM t1" 54 4 "SELECT sum(b) OVER (PARTITION BY d ORDER BY c) FROM t1" 55 5 "SELECT sum(b) FILTER (WHERE a>0) OVER (PARTITION BY d ORDER BY c) FROM t1" 56 6 "SELECT sum(b) OVER (ORDER BY c RANGE UNBOUNDED PRECEDING) FROM t1" 57 7 "SELECT sum(b) OVER (ORDER BY c ROWS 45 PRECEDING) FROM t1" 58 8 "SELECT sum(b) OVER (ORDER BY c RANGE CURRENT ROW) FROM t1" 59 9 "SELECT sum(b) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING 60 AND CURRENT ROW) FROM t1" 61 10 "SELECT sum(b) OVER (ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING 62 AND UNBOUNDED FOLLOWING) FROM t1" 63} { 64 do_test 2.$tn { lindex [catchsql $sql] 0 } 0 65} 66 67foreach {tn sql} { 68 1 "SELECT * FROM t1 WHERE sum(b) OVER ()" 69 2 "SELECT * FROM t1 GROUP BY sum(b) OVER ()" 70 3 "SELECT * FROM t1 GROUP BY a HAVING sum(b) OVER ()" 71} { 72 do_catchsql_test 3.$tn $sql {1 {misuse of window function sum()}} 73} 74 75do_execsql_test 4.0 { 76 CREATE TABLE t2(a, b, c); 77 INSERT INTO t2 VALUES(0, 0, 0); 78 INSERT INTO t2 VALUES(1, 1, 1); 79 INSERT INTO t2 VALUES(2, 0, 2); 80 INSERT INTO t2 VALUES(3, 1, 0); 81 INSERT INTO t2 VALUES(4, 0, 1); 82 INSERT INTO t2 VALUES(5, 1, 2); 83 INSERT INTO t2 VALUES(6, 0, 0); 84} 85 86do_execsql_test 4.1 { 87 SELECT a, sum(a) OVER (PARTITION BY b) FROM t2; 88} { 89 0 12 2 12 4 12 6 12 1 9 3 9 5 9 90} 91 92do_execsql_test 4.2 { 93 SELECT a, sum(a) OVER (PARTITION BY b) FROM t2 ORDER BY a; 94} { 95 0 12 1 9 2 12 3 9 4 12 5 9 6 12 96} 97 98do_execsql_test 4.3 { 99 SELECT a, sum(a) OVER () FROM t2 ORDER BY a; 100} { 101 0 21 1 21 2 21 3 21 4 21 5 21 6 21 102} 103 104do_execsql_test 4.4 { 105 SELECT a, sum(a) OVER (ORDER BY a) FROM t2; 106} { 107 0 0 1 1 2 3 3 6 4 10 5 15 6 21 108} 109 110do_execsql_test 4.5 { 111 SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a) FROM t2 ORDER BY a 112} { 113 0 0 1 1 2 2 3 4 4 6 5 9 6 12 114} 115 116do_execsql_test 4.6 { 117 SELECT a, sum(a) OVER (PARTITION BY c ORDER BY a) FROM t2 ORDER BY a 118} { 119 0 0 1 1 2 2 3 3 4 5 5 7 6 9 120} 121 122do_execsql_test 4.7 { 123 SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a DESC) FROM t2 ORDER BY a 124} { 125 0 12 1 9 2 12 3 8 4 10 5 5 6 6 126} 127 128do_execsql_test 4.8 { 129 SELECT a, 130 sum(a) OVER (PARTITION BY b ORDER BY a DESC), 131 sum(a) OVER (PARTITION BY c ORDER BY a) 132 FROM t2 ORDER BY a 133} { 134 0 12 0 135 1 9 1 136 2 12 2 137 3 8 3 138 4 10 5 139 5 5 7 140 6 6 9 141} 142 143do_execsql_test 4.9 { 144 SELECT a, 145 sum(a) OVER (ORDER BY a), 146 avg(a) OVER (ORDER BY a) 147 FROM t2 ORDER BY a 148} { 149 0 0 0.0 150 1 1 0.5 151 2 3 1.0 152 3 6 1.5 153 4 10 2.0 154 5 15 2.5 155 6 21 3.0 156} 157 158do_execsql_test 4.10.1 { 159 SELECT a, 160 count() OVER (ORDER BY a DESC), 161 group_concat(a, '.') OVER (ORDER BY a DESC) 162 FROM t2 ORDER BY a DESC 163} { 164 6 1 6 165 5 2 6.5 166 4 3 6.5.4 167 3 4 6.5.4.3 168 2 5 6.5.4.3.2 169 1 6 6.5.4.3.2.1 170 0 7 6.5.4.3.2.1.0 171} 172 173do_execsql_test 4.10.2 { 174 SELECT a, 175 count(*) OVER (ORDER BY a DESC), 176 group_concat(a, '.') OVER (ORDER BY a DESC) 177 FROM t2 ORDER BY a DESC 178} { 179 6 1 6 180 5 2 6.5 181 4 3 6.5.4 182 3 4 6.5.4.3 183 2 5 6.5.4.3.2 184 1 6 6.5.4.3.2.1 185 0 7 6.5.4.3.2.1.0 186} 187 188do_catchsql_test 5.1 { 189 SELECT ntile(0) OVER (ORDER BY a) FROM t2; 190} {1 {argument of ntile must be a positive integer}} 191do_catchsql_test 5.2 { 192 SELECT ntile(-1) OVER (ORDER BY a) FROM t2; 193} {1 {argument of ntile must be a positive integer}} 194do_catchsql_test 5.3 { 195 SELECT ntile('zbc') OVER (ORDER BY a) FROM t2; 196} {1 {argument of ntile must be a positive integer}} 197do_execsql_test 5.4 { 198 CREATE TABLE t4(a, b); 199 SELECT ntile(1) OVER (ORDER BY a) FROM t4; 200} {} 201 202#------------------------------------------------------------------------- 203reset_db 204do_execsql_test 6.1 { 205 CREATE TABLE t1(x); 206 INSERT INTO t1 VALUES(7), (6), (5), (4), (3), (2), (1); 207 208 CREATE TABLE t2(x); 209 INSERT INTO t2 VALUES('b'), ('a'); 210 211 SELECT x, count(*) OVER (ORDER BY x) FROM t1; 212} {1 1 2 2 3 3 4 4 5 5 6 6 7 7} 213 214do_execsql_test 6.2 { 215 SELECT * FROM t2, (SELECT x, count(*) OVER (ORDER BY x) FROM t1); 216} { 217 b 1 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 b 7 7 218 a 1 1 a 2 2 a 3 3 a 4 4 a 5 5 a 6 6 a 7 7 219} 220 221do_catchsql_test 6.3 { 222 SELECT x, lag(x) FILTER (WHERE (x%2)=0) OVER w FROM t1 223 WINDOW w AS (ORDER BY x) 224} {1 {FILTER clause may only be used with aggregate window functions}} 225 226#------------------------------------------------------------------------- 227# Attempt to use a window function as an aggregate. And other errors. 228# 229reset_db 230do_execsql_test 7.0 { 231 CREATE TABLE t1(x, y); 232 INSERT INTO t1 VALUES(1, 2); 233 INSERT INTO t1 VALUES(3, 4); 234 INSERT INTO t1 VALUES(5, 6); 235 INSERT INTO t1 VALUES(7, 8); 236 INSERT INTO t1 VALUES(9, 10); 237} 238 239do_catchsql_test 7.1.1 { 240 SELECT nth_value(x, 1) FROM t1; 241} {1 {misuse of window function nth_value()}} 242do_catchsql_test 7.1.2 { 243 SELECT * FROM t1 WHERE nth_value(x, 1) OVER (ORDER BY y); 244} {1 {misuse of window function nth_value()}} 245do_catchsql_test 7.1.3 { 246 SELECT count(*) FROM t1 GROUP BY y HAVING nth_value(x, 1) OVER (ORDER BY y); 247} {1 {misuse of window function nth_value()}} 248do_catchsql_test 7.1.4 { 249 SELECT count(*) FROM t1 GROUP BY nth_value(x, 1) OVER (ORDER BY y); 250} {1 {misuse of window function nth_value()}} 251do_catchsql_test 7.1.5 { 252 SELECT count(*) FROM t1 LIMIT nth_value(x, 1) OVER (); 253} {1 {no such column: x}} 254do_catchsql_test 7.1.6 { 255 SELECT trim(x) OVER (ORDER BY y) FROM t1; 256} {1 {trim() may not be used as a window function}} 257do_catchsql_test 7.1.7 { 258 SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y); 259} {1 {no such window: abc}} 260 261do_execsql_test 7.2 { 262 SELECT 263 lead(y) OVER win, 264 lead(y, 2) OVER win, 265 lead(y, 3, 'default') OVER win 266 FROM t1 267 WINDOW win AS (ORDER BY x) 268} { 269 4 6 8 6 8 10 8 10 default 10 {} default {} {} default 270} 271 272do_execsql_test 7.3 { 273 SELECT row_number() OVER (ORDER BY x) FROM t1 274} {1 2 3 4 5} 275 276do_execsql_test 7.4 { 277 SELECT 278 row_number() OVER win, 279 lead(x) OVER win 280 FROM t1 281 WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 282} {1 3 2 5 3 7 4 9 5 {}} 283 284#------------------------------------------------------------------------- 285# Attempt to use a window function in a view. 286# 287do_execsql_test 8.0 { 288 CREATE TABLE t3(a, b, c); 289 290 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 ) 291 INSERT INTO t3 SELECT i, i, i FROM s; 292 293 CREATE VIEW v1 AS SELECT 294 sum(b) OVER (ORDER BY c), 295 min(b) OVER (ORDER BY c), 296 max(b) OVER (ORDER BY c) 297 FROM t3; 298 299 CREATE VIEW v2 AS SELECT 300 sum(b) OVER win, 301 min(b) OVER win, 302 max(b) OVER win 303 FROM t3 304 WINDOW win AS (ORDER BY c); 305} 306 307do_execsql_test 8.1.1 { 308 SELECT * FROM v1 309} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} 310do_execsql_test 8.1.2 { 311 SELECT * FROM v2 312} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} 313 314db close 315sqlite3 db test.db 316do_execsql_test 8.2.1 { 317 SELECT * FROM v1 318} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} 319do_execsql_test 8.2.2 { 320 SELECT * FROM v2 321} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} 322 323#------------------------------------------------------------------------- 324# Attempt to use a window function in a trigger. 325# 326do_execsql_test 9.0 { 327 CREATE TABLE t4(x, y); 328 INSERT INTO t4 VALUES(1, 'g'); 329 INSERT INTO t4 VALUES(2, 'i'); 330 INSERT INTO t4 VALUES(3, 'l'); 331 INSERT INTO t4 VALUES(4, 'g'); 332 INSERT INTO t4 VALUES(5, 'a'); 333 334 CREATE TABLE t5(x, y, m); 335 CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN 336 DELETE FROM t5; 337 INSERT INTO t5 338 SELECT x, y, max(y) OVER xyz FROM t4 339 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x); 340 END; 341} 342 343do_execsql_test 9.1.1 { 344 SELECT x, y, max(y) OVER xyz FROM t4 345 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1 346} {1 g g 2 i i 3 l l 4 g i 5 a l} 347 348do_execsql_test 9.1.2 { 349 INSERT INTO t4 VALUES(6, 'm'); 350 SELECT x, y, max(y) OVER xyz FROM t4 351 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1 352} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} 353 354do_execsql_test 9.1.3 { 355 SELECT * FROM t5 ORDER BY 1 356} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} 357 358do_execsql_test 9.2 { 359 WITH aaa(x, y, z) AS ( 360 SELECT x, y, max(y) OVER xyz FROM t4 361 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) 362 ) 363 SELECT * FROM aaa ORDER BY 1; 364} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} 365 366do_execsql_test 9.3 { 367 WITH aaa(x, y, z) AS ( 368 SELECT x, y, max(y) OVER xyz FROM t4 369 WINDOW xyz AS (ORDER BY x) 370 ) 371 SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1; 372} {1 g g g 2 i i g 3 l l g 4 g l g 5 a l g 6 m m g} 373 374#------------------------------------------------------------------------- 375# 376do_execsql_test 10.0 { 377 CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total); 378 INSERT INTO sales VALUES 379 ('Alice', 'North', 34), 380 ('Frank', 'South', 22), 381 ('Charles', 'North', 45), 382 ('Darrell', 'South', 8), 383 ('Grant', 'South', 23), 384 ('Brad' , 'North', 22), 385 ('Elizabeth', 'South', 99), 386 ('Horace', 'East', 1); 387} 388 389# Best two salespeople from each region 390# 391do_execsql_test 10.1 { 392 SELECT emp, region, total FROM ( 393 SELECT 394 emp, region, total, 395 row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank 396 FROM sales 397 ) WHERE rank<=2 ORDER BY region, total DESC 398} { 399 Horace East 1 400 Charles North 45 401 Alice North 34 402 Elizabeth South 99 403 Grant South 23 404} 405 406do_execsql_test 10.2 { 407 SELECT emp, region, sum(total) OVER win FROM sales 408 WINDOW win AS (PARTITION BY region ORDER BY total) 409} { 410 Horace East 1 411 Brad North 22 412 Alice North 56 413 Charles North 101 414 Darrell South 8 415 Frank South 30 416 Grant South 53 417 Elizabeth South 152 418} 419 420do_execsql_test 10.3 { 421 SELECT emp, region, sum(total) OVER win FROM sales 422 WINDOW win AS (PARTITION BY region ORDER BY total) 423 LIMIT 5 424} { 425 Horace East 1 426 Brad North 22 427 Alice North 56 428 Charles North 101 429 Darrell South 8 430} 431 432do_execsql_test 10.4 { 433 SELECT emp, region, sum(total) OVER win FROM sales 434 WINDOW win AS (PARTITION BY region ORDER BY total) 435 LIMIT 5 OFFSET 2 436} { 437 Alice North 56 438 Charles North 101 439 Darrell South 8 440 Frank South 30 441 Grant South 53 442} 443 444do_execsql_test 10.5 { 445 SELECT emp, region, sum(total) OVER win FROM sales 446 WINDOW win AS ( 447 PARTITION BY region ORDER BY total 448 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 449 ) 450} { 451 Horace East 1 452 Brad North 101 453 Alice North 79 454 Charles North 45 455 Darrell South 152 456 Frank South 144 457 Grant South 122 458 Elizabeth South 99 459} 460 461do_execsql_test 10.6 { 462 SELECT emp, region, sum(total) OVER win FROM sales 463 WINDOW win AS ( 464 PARTITION BY region ORDER BY total 465 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 466 ) LIMIT 5 OFFSET 2 467} { 468 Alice North 79 469 Charles North 45 470 Darrell South 152 471 Frank South 144 472 Grant South 122 473} 474 475do_execsql_test 10.7 { 476 SELECT emp, region, ( 477 SELECT sum(total) OVER ( 478 ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 479 ) || outer.emp FROM sales 480 ) FROM sales AS outer; 481} { 482 Alice North 254Alice 483 Frank South 254Frank 484 Charles North 254Charles 485 Darrell South 254Darrell 486 Grant South 254Grant 487 Brad North 254Brad 488 Elizabeth South 254Elizabeth 489 Horace East 254Horace 490} 491 492do_execsql_test 10.8 { 493 SELECT emp, region, ( 494 SELECT sum(total) FILTER (WHERE sales.emp!=outer.emp) OVER ( 495 ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 496 ) FROM sales 497 ) FROM sales AS outer; 498} { 499 Alice North 220 500 Frank South 232 501 Charles North 209 502 Darrell South 246 503 Grant South 231 504 Brad North 232 505 Elizabeth South 155 506 Horace East 253 507} 508 509#------------------------------------------------------------------------- 510# Check that it is not possible to use a window function in a CREATE INDEX 511# statement. 512# 513do_execsql_test 11.0 { CREATE TABLE t6(a, b, c); } 514 515do_catchsql_test 11.1 { 516 CREATE INDEX t6i ON t6(a) WHERE sum(b) OVER (); 517} {1 {misuse of window function sum()}} 518do_catchsql_test 11.2 { 519 CREATE INDEX t6i ON t6(a) WHERE lead(b) OVER (); 520} {1 {misuse of window function lead()}} 521 522do_catchsql_test 11.3 { 523 CREATE INDEX t6i ON t6(sum(b) OVER ()); 524} {1 {misuse of window function sum()}} 525do_catchsql_test 11.4 { 526 CREATE INDEX t6i ON t6(lead(b) OVER ()); 527} {1 {misuse of window function lead()}} 528 529finish_test 530 531