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 529# 2018-09-17 ticket 510cde277783b5fb5de628393959849dff377eb3 530# Endless loop on a query with window functions and a limit 531# 532do_execsql_test 12.100 { 533 DROP TABLE IF EXISTS t1; 534 CREATE TABLE t1(id INT, b VARCHAR, c VARCHAR); 535 INSERT INTO t1 VALUES(1, 'A', 'one'); 536 INSERT INTO t1 VALUES(2, 'B', 'two'); 537 INSERT INTO t1 VALUES(3, 'C', 'three'); 538 INSERT INTO t1 VALUES(4, 'D', 'one'); 539 INSERT INTO t1 VALUES(5, 'E', 'two'); 540 SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x 541 FROM t1 WHERE id>1 542 ORDER BY b LIMIT 1; 543} {2 B two} 544do_execsql_test 12.110 { 545 INSERT INTO t1 VALUES(6, 'F', 'three'); 546 INSERT INTO t1 VALUES(7, 'G', 'one'); 547 SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x 548 FROM t1 WHERE id>1 549 ORDER BY b LIMIT 2; 550} {2 B two 3 C three} 551 552#------------------------------------------------------------------------- 553 554do_execsql_test 13.1 { 555 DROP TABLE IF EXISTS t1; 556 CREATE TABLE t1(a int, b int); 557 INSERT INTO t1 VALUES(1,11); 558 INSERT INTO t1 VALUES(2,12); 559} 560 561do_execsql_test 13.2.1 { 562 SELECT a, rank() OVER(ORDER BY b) FROM t1; 563 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; 564} { 565 1 1 2 2 2 1 1 2 566} 567do_execsql_test 13.2.2 { 568 SELECT a, rank() OVER(ORDER BY b) FROM t1 569 UNION ALL 570 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; 571} { 572 1 1 2 2 2 1 1 2 573} 574do_execsql_test 13.3 { 575 SELECT a, rank() OVER(ORDER BY b) FROM t1 576 UNION 577 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; 578} { 579 1 1 1 2 2 1 2 2 580} 581 582do_execsql_test 13.4 { 583 SELECT a, rank() OVER(ORDER BY b) FROM t1 584 EXCEPT 585 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; 586} { 587 1 1 2 2 588} 589 590do_execsql_test 13.5 { 591 SELECT a, rank() OVER(ORDER BY b) FROM t1 592 INTERSECT 593 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; 594} {} 595 596# 2018-12-06 597# https://www.sqlite.org/src/info/f09fcd17810f65f7 598# Assertion fault when window functions are used. 599# 600# Root cause is the query flattener invoking sqlite3ExprDup() on 601# expressions that contain subqueries with window functions. The 602# sqlite3ExprDup() routine is not making correctly initializing 603# Select.pWin field of the subqueries. 604# 605sqlite3 db :memory: 606do_execsql_test 14.0 { 607 SELECT * FROM( 608 SELECT * FROM (SELECT 1 AS c) WHERE c IN ( 609 SELECT (row_number() OVER()) FROM (VALUES (0)) 610 ) 611 ); 612} {1} 613do_execsql_test 14.1 { 614 CREATE TABLE t1(x); INSERT INTO t1(x) VALUES(12345); 615 CREATE TABLE t2(c); INSERT INTO t2(c) VALUES(1); 616 SELECT y, y+1, y+2 FROM ( 617 SELECT c IN ( 618 SELECT (row_number() OVER()) FROM t1 619 ) AS y FROM t2 620 ); 621} {1 2 3} 622 623# 2018-12-31 624# https://www.sqlite.org/src/info/d0866b26f83e9c55 625# Window function in correlated subquery causes assertion fault 626# 627do_catchsql_test 15.0 { 628 WITH t(id, parent) AS ( 629 SELECT CAST(1 AS INT), CAST(NULL AS INT) 630 UNION ALL 631 SELECT 2, NULL 632 UNION ALL 633 SELECT 3, 1 634 UNION ALL 635 SELECT 4, 1 636 UNION ALL 637 SELECT 5, 2 638 UNION ALL 639 SELECT 6, 2 640 ), q AS ( 641 SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn 642 FROM t 643 WHERE parent IS NULL 644 UNION ALL 645 SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn 646 FROM q 647 JOIN t 648 ON t.parent = q.id 649 ) 650 SELECT * 651 FROM q; 652} {1 {cannot use window functions in recursive queries}} 653do_execsql_test 15.1 { 654 DROP TABLE IF EXISTS t1; 655 DROP TABLE IF EXISTS t2; 656 CREATE TABLE t1(x); 657 INSERT INTO t1 VALUES('a'), ('b'), ('c'); 658 CREATE TABLE t2(a, b); 659 INSERT INTO t2 VALUES('X', 1), ('X', 2), ('Y', 2), ('Y', 3); 660 SELECT x, ( 661 SELECT sum(b) 662 OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING 663 AND UNBOUNDED FOLLOWING) 664 FROM t2 WHERE b<x 665 ) FROM t1; 666} {a 3 b 3 c 3} 667 668do_execsql_test 15.2 { 669 SELECT( 670 WITH c AS( 671 VALUES(1) 672 ) SELECT '' FROM c,c 673 ) x WHERE x+x; 674} {} 675 676#------------------------------------------------------------------------- 677 678do_execsql_test 16.0 { 679 CREATE TABLE t7(a,b); 680 INSERT INTO t7(rowid, a, b) VALUES 681 (1, 1, 3), 682 (2, 10, 4), 683 (3, 100, 2); 684} 685 686do_execsql_test 16.1 { 687 SELECT rowid, sum(a) OVER (PARTITION BY b IN (SELECT rowid FROM t7)) FROM t7; 688} { 689 2 10 690 1 101 691 3 101 692} 693 694do_execsql_test 16.2 { 695 SELECT rowid, sum(a) OVER w1 FROM t7 696 WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7)); 697} { 698 2 10 699 1 101 700 3 101 701} 702 703#------------------------------------------------------------------------- 704do_execsql_test 17.0 { 705 CREATE TABLE t8(a); 706 INSERT INTO t8 VALUES(1), (2), (3); 707} 708 709do_execsql_test 17.1 { 710 SELECT +sum(0) OVER () ORDER BY +sum(0) OVER (); 711} {0} 712 713do_execsql_test 17.2 { 714 select +sum(a) OVER () FROM t8 ORDER BY +sum(a) OVER () DESC; 715} {6 6 6} 716 717do_execsql_test 17.3 { 718 SELECT 10+sum(a) OVER (ORDER BY a) 719 FROM t8 720 ORDER BY 10+sum(a) OVER (ORDER BY a) DESC; 721} {16 13 11} 722 723#------------------------------------------------------------------------- 724# 725reset_db 726do_execsql_test 18.0 { 727 CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ); 728 CREATE TABLE t2 ( t2_id INTEGER PRIMARY KEY ); 729 CREATE TABLE t3 ( t3_id INTEGER PRIMARY KEY ); 730 731 INSERT INTO t1 VALUES(1), (3), (5); 732 INSERT INTO t2 VALUES (3), (5); 733 INSERT INTO t3 VALUES(10), (11), (12); 734} 735 736do_execsql_test 18.1 { 737 SELECT t1.* FROM t1, t2 WHERE 738 t1_id=t2_id AND t1_id IN ( 739 SELECT t1_id + row_number() OVER ( ORDER BY t1_id ) FROM t3 740 ) 741} 742 743do_execsql_test 18.2 { 744 SELECT t1.* FROM t1, t2 WHERE 745 t1_id=t2_id AND t1_id IN ( 746 SELECT row_number() OVER ( ORDER BY t1_id ) FROM t3 747 ) 748} {3} 749 750 751finish_test 752