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}} 260do_catchsql_test 7.1.8 { 261 SELECT row_number(x) OVER () FROM t1 262} {1 {wrong number of arguments to function row_number()}} 263 264do_execsql_test 7.2 { 265 SELECT 266 lead(y) OVER win, 267 lead(y, 2) OVER win, 268 lead(y, 3, 'default') OVER win 269 FROM t1 270 WINDOW win AS (ORDER BY x) 271} { 272 4 6 8 6 8 10 8 10 default 10 {} default {} {} default 273} 274 275do_execsql_test 7.3 { 276 SELECT row_number() OVER (ORDER BY x) FROM t1 277} {1 2 3 4 5} 278 279do_execsql_test 7.4 { 280 SELECT 281 row_number() OVER win, 282 lead(x) OVER win 283 FROM t1 284 WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 285} {1 3 2 5 3 7 4 9 5 {}} 286 287#------------------------------------------------------------------------- 288# Attempt to use a window function in a view. 289# 290do_execsql_test 8.0 { 291 CREATE TABLE t3(a, b, c); 292 293 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 ) 294 INSERT INTO t3 SELECT i, i, i FROM s; 295 296 CREATE VIEW v1 AS SELECT 297 sum(b) OVER (ORDER BY c), 298 min(b) OVER (ORDER BY c), 299 max(b) OVER (ORDER BY c) 300 FROM t3; 301 302 CREATE VIEW v2 AS SELECT 303 sum(b) OVER win, 304 min(b) OVER win, 305 max(b) OVER win 306 FROM t3 307 WINDOW win AS (ORDER BY c); 308} 309 310do_execsql_test 8.1.1 { 311 SELECT * FROM v1 312} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} 313do_execsql_test 8.1.2 { 314 SELECT * FROM v2 315} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} 316 317db close 318sqlite3 db test.db 319do_execsql_test 8.2.1 { 320 SELECT * FROM v1 321} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} 322do_execsql_test 8.2.2 { 323 SELECT * FROM v2 324} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} 325 326#------------------------------------------------------------------------- 327# Attempt to use a window function in a trigger. 328# 329do_execsql_test 9.0 { 330 CREATE TABLE t4(x, y); 331 INSERT INTO t4 VALUES(1, 'g'); 332 INSERT INTO t4 VALUES(2, 'i'); 333 INSERT INTO t4 VALUES(3, 'l'); 334 INSERT INTO t4 VALUES(4, 'g'); 335 INSERT INTO t4 VALUES(5, 'a'); 336 337 CREATE TABLE t5(x, y, m); 338 CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN 339 DELETE FROM t5; 340 INSERT INTO t5 341 SELECT x, y, max(y) OVER xyz FROM t4 342 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x); 343 END; 344} 345 346do_execsql_test 9.1.1 { 347 SELECT x, y, max(y) OVER xyz FROM t4 348 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1 349} {1 g g 2 i i 3 l l 4 g i 5 a l} 350 351do_execsql_test 9.1.2 { 352 INSERT INTO t4 VALUES(6, 'm'); 353 SELECT x, y, max(y) OVER xyz FROM t4 354 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1 355} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} 356 357do_execsql_test 9.1.3 { 358 SELECT * FROM t5 ORDER BY 1 359} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} 360 361do_execsql_test 9.2 { 362 WITH aaa(x, y, z) AS ( 363 SELECT x, y, max(y) OVER xyz FROM t4 364 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) 365 ) 366 SELECT * FROM aaa ORDER BY 1; 367} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} 368 369do_execsql_test 9.3 { 370 WITH aaa(x, y, z) AS ( 371 SELECT x, y, max(y) OVER xyz FROM t4 372 WINDOW xyz AS (ORDER BY x) 373 ) 374 SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1; 375} {1 g g g 2 i i g 3 l l g 4 g l g 5 a l g 6 m m g} 376 377do_catchsql_test 9.4 { 378 -- 2021-04-17 dbsqlfuzz d9cf66100064952b66951845dfab41de1c124611 379 DROP TABLE IF EXISTS t1; 380 CREATE TABLE t1(a,b,c,d); 381 DROP TABLE IF EXISTS t2; 382 CREATE TABLE t2(x,y); 383 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN 384 INSERT INTO t2(x,y) 385 SELECT a, max(d) OVER w1 FROM t1 386 WINDOW w1 AS (PARTITION BY EXISTS(SELECT 1 FROM t1 WHERE c=?1) ); 387 END; 388} {1 {trigger cannot use variables}} 389 390#------------------------------------------------------------------------- 391# 392do_execsql_test 10.0 { 393 CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total); 394 INSERT INTO sales VALUES 395 ('Alice', 'North', 34), 396 ('Frank', 'South', 22), 397 ('Charles', 'North', 45), 398 ('Darrell', 'South', 8), 399 ('Grant', 'South', 23), 400 ('Brad' , 'North', 22), 401 ('Elizabeth', 'South', 99), 402 ('Horace', 'East', 1); 403} 404 405# Best two salespeople from each region 406# 407do_execsql_test 10.1 { 408 SELECT emp, region, total FROM ( 409 SELECT 410 emp, region, total, 411 row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank 412 FROM sales 413 ) WHERE rank<=2 ORDER BY region, total DESC 414} { 415 Horace East 1 416 Charles North 45 417 Alice North 34 418 Elizabeth South 99 419 Grant South 23 420} 421 422do_execsql_test 10.2 { 423 SELECT emp, region, sum(total) OVER win FROM sales 424 WINDOW win AS (PARTITION BY region ORDER BY total) 425} { 426 Horace East 1 427 Brad North 22 428 Alice North 56 429 Charles North 101 430 Darrell South 8 431 Frank South 30 432 Grant South 53 433 Elizabeth South 152 434} 435 436do_execsql_test 10.3 { 437 SELECT emp, region, sum(total) OVER win FROM sales 438 WINDOW win AS (PARTITION BY region ORDER BY total) 439 LIMIT 5 440} { 441 Horace East 1 442 Brad North 22 443 Alice North 56 444 Charles North 101 445 Darrell South 8 446} 447 448do_execsql_test 10.4 { 449 SELECT emp, region, sum(total) OVER win FROM sales 450 WINDOW win AS (PARTITION BY region ORDER BY total) 451 LIMIT 5 OFFSET 2 452} { 453 Alice North 56 454 Charles North 101 455 Darrell South 8 456 Frank South 30 457 Grant South 53 458} 459 460do_execsql_test 10.5 { 461 SELECT emp, region, sum(total) OVER win FROM sales 462 WINDOW win AS ( 463 PARTITION BY region ORDER BY total 464 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 465 ) 466} { 467 Horace East 1 468 Brad North 101 469 Alice North 79 470 Charles North 45 471 Darrell South 152 472 Frank South 144 473 Grant South 122 474 Elizabeth South 99 475} 476 477do_execsql_test 10.6 { 478 SELECT emp, region, sum(total) OVER win FROM sales 479 WINDOW win AS ( 480 PARTITION BY region ORDER BY total 481 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 482 ) LIMIT 5 OFFSET 2 483} { 484 Alice North 79 485 Charles North 45 486 Darrell South 152 487 Frank South 144 488 Grant South 122 489} 490 491do_execsql_test 10.7 { 492 SELECT emp, region, ( 493 SELECT sum(total) OVER ( 494 ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 495 ) || outer.emp FROM sales 496 ) FROM sales AS outer; 497} { 498 Alice North 254Alice 499 Frank South 254Frank 500 Charles North 254Charles 501 Darrell South 254Darrell 502 Grant South 254Grant 503 Brad North 254Brad 504 Elizabeth South 254Elizabeth 505 Horace East 254Horace 506} 507 508do_execsql_test 10.8 { 509 SELECT emp, region, ( 510 SELECT sum(total) FILTER (WHERE sales.emp!=outer.emp) OVER ( 511 ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 512 ) FROM sales 513 ) FROM sales AS outer; 514} { 515 Alice North 220 516 Frank South 232 517 Charles North 209 518 Darrell South 246 519 Grant South 231 520 Brad North 232 521 Elizabeth South 155 522 Horace East 253 523} 524 525#------------------------------------------------------------------------- 526# Check that it is not possible to use a window function in a CREATE INDEX 527# statement. 528# 529do_execsql_test 11.0 { CREATE TABLE t6(a, b, c); } 530 531do_catchsql_test 11.1 { 532 CREATE INDEX t6i ON t6(a) WHERE sum(b) OVER (); 533} {1 {misuse of window function sum()}} 534do_catchsql_test 11.2 { 535 CREATE INDEX t6i ON t6(a) WHERE lead(b) OVER (); 536} {1 {misuse of window function lead()}} 537 538do_catchsql_test 11.3 { 539 CREATE INDEX t6i ON t6(sum(b) OVER ()); 540} {1 {misuse of window function sum()}} 541do_catchsql_test 11.4 { 542 CREATE INDEX t6i ON t6(lead(b) OVER ()); 543} {1 {misuse of window function lead()}} 544 545# 2018-09-17 ticket 510cde277783b5fb5de628393959849dff377eb3 546# Endless loop on a query with window functions and a limit 547# 548do_execsql_test 12.100 { 549 DROP TABLE IF EXISTS t1; 550 CREATE TABLE t1(id INT, b VARCHAR, c VARCHAR); 551 INSERT INTO t1 VALUES(1, 'A', 'one'); 552 INSERT INTO t1 VALUES(2, 'B', 'two'); 553 INSERT INTO t1 VALUES(3, 'C', 'three'); 554 INSERT INTO t1 VALUES(4, 'D', 'one'); 555 INSERT INTO t1 VALUES(5, 'E', 'two'); 556 SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x 557 FROM t1 WHERE id>1 558 ORDER BY b LIMIT 1; 559} {2 B two} 560do_execsql_test 12.110 { 561 INSERT INTO t1 VALUES(6, 'F', 'three'); 562 INSERT INTO t1 VALUES(7, 'G', 'one'); 563 SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x 564 FROM t1 WHERE id>1 565 ORDER BY b LIMIT 2; 566} {2 B two 3 C three} 567 568#------------------------------------------------------------------------- 569 570do_execsql_test 13.1 { 571 DROP TABLE IF EXISTS t1; 572 CREATE TABLE t1(a int, b int); 573 INSERT INTO t1 VALUES(1,11); 574 INSERT INTO t1 VALUES(2,12); 575} 576 577do_execsql_test 13.2.1 { 578 SELECT a, rank() OVER(ORDER BY b) FROM t1; 579 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; 580} { 581 1 1 2 2 2 1 1 2 582} 583do_execsql_test 13.2.2 { 584 SELECT a, rank() OVER(ORDER BY b) FROM t1 585 UNION ALL 586 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; 587} { 588 1 1 2 2 2 1 1 2 589} 590do_execsql_test 13.3 { 591 SELECT a, rank() OVER(ORDER BY b) FROM t1 592 UNION 593 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; 594} { 595 1 1 1 2 2 1 2 2 596} 597 598do_execsql_test 13.4 { 599 SELECT a, rank() OVER(ORDER BY b) FROM t1 600 EXCEPT 601 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; 602} { 603 1 1 2 2 604} 605 606do_execsql_test 13.5 { 607 SELECT a, rank() OVER(ORDER BY b) FROM t1 608 INTERSECT 609 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; 610} {} 611 612# 2018-12-06 613# https://www.sqlite.org/src/info/f09fcd17810f65f7 614# Assertion fault when window functions are used. 615# 616# Root cause is the query flattener invoking sqlite3ExprDup() on 617# expressions that contain subqueries with window functions. The 618# sqlite3ExprDup() routine is not making correctly initializing 619# Select.pWin field of the subqueries. 620# 621sqlite3 db :memory: 622do_execsql_test 14.0 { 623 SELECT * FROM( 624 SELECT * FROM (SELECT 1 AS c) WHERE c IN ( 625 SELECT (row_number() OVER()) FROM (VALUES (0)) 626 ) 627 ); 628} {1} 629do_execsql_test 14.1 { 630 CREATE TABLE t1(x); INSERT INTO t1(x) VALUES(12345); 631 CREATE TABLE t2(c); INSERT INTO t2(c) VALUES(1); 632 SELECT y, y+1, y+2 FROM ( 633 SELECT c IN ( 634 SELECT (row_number() OVER()) FROM t1 635 ) AS y FROM t2 636 ); 637} {1 2 3} 638 639# 2018-12-31 640# https://www.sqlite.org/src/info/d0866b26f83e9c55 641# Window function in correlated subquery causes assertion fault 642# 643do_catchsql_test 15.0 { 644 WITH t(id, parent) AS ( 645 SELECT CAST(1 AS INT), CAST(NULL AS INT) 646 UNION ALL 647 SELECT 2, NULL 648 UNION ALL 649 SELECT 3, 1 650 UNION ALL 651 SELECT 4, 1 652 UNION ALL 653 SELECT 5, 2 654 UNION ALL 655 SELECT 6, 2 656 ), q AS ( 657 SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn 658 FROM t 659 WHERE parent IS NULL 660 UNION ALL 661 SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn 662 FROM q 663 JOIN t 664 ON t.parent = q.id 665 ) 666 SELECT * 667 FROM q; 668} {1 {cannot use window functions in recursive queries}} 669do_execsql_test 15.1 { 670 DROP TABLE IF EXISTS t1; 671 DROP TABLE IF EXISTS t2; 672 CREATE TABLE t1(x); 673 INSERT INTO t1 VALUES('a'), ('b'), ('c'); 674 CREATE TABLE t2(a, b); 675 INSERT INTO t2 VALUES('X', 1), ('X', 2), ('Y', 2), ('Y', 3); 676 SELECT x, ( 677 SELECT sum(b) 678 OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING 679 AND UNBOUNDED FOLLOWING) 680 FROM t2 WHERE b<x 681 ) FROM t1; 682} {a 3 b 3 c 3} 683 684do_execsql_test 15.2 { 685 SELECT( 686 WITH c AS( 687 VALUES(1) 688 ) SELECT '' FROM c,c 689 ) x WHERE x+x; 690} {} 691 692#------------------------------------------------------------------------- 693 694do_execsql_test 16.0 { 695 CREATE TABLE t7(a,b); 696 INSERT INTO t7(rowid, a, b) VALUES 697 (1, 1, 3), 698 (2, 10, 4), 699 (3, 100, 2); 700} 701 702do_execsql_test 16.1 { 703 SELECT rowid, sum(a) OVER (PARTITION BY b IN (SELECT rowid FROM t7)) FROM t7; 704} { 705 2 10 706 1 101 707 3 101 708} 709 710do_execsql_test 16.2 { 711 SELECT rowid, sum(a) OVER w1 FROM t7 712 WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7)); 713} { 714 2 10 715 1 101 716 3 101 717} 718 719#------------------------------------------------------------------------- 720do_execsql_test 17.0 { 721 CREATE TABLE t8(a); 722 INSERT INTO t8 VALUES(1), (2), (3); 723} 724 725do_execsql_test 17.1 { 726 SELECT +sum(0) OVER () ORDER BY +sum(0) OVER (); 727} {0} 728 729do_execsql_test 17.2 { 730 select +sum(a) OVER () FROM t8 ORDER BY +sum(a) OVER () DESC; 731} {6 6 6} 732 733do_execsql_test 17.3 { 734 SELECT 10+sum(a) OVER (ORDER BY a) 735 FROM t8 736 ORDER BY 10+sum(a) OVER (ORDER BY a) DESC; 737} {16 13 11} 738 739 740#------------------------------------------------------------------------- 741# Test error cases from chaining window definitions. 742# 743reset_db 744do_execsql_test 18.0 { 745 DROP TABLE IF EXISTS t1; 746 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER); 747 INSERT INTO t1 VALUES(1, 'odd', 'one', 1); 748 INSERT INTO t1 VALUES(2, 'even', 'two', 2); 749 INSERT INTO t1 VALUES(3, 'odd', 'three', 3); 750 INSERT INTO t1 VALUES(4, 'even', 'four', 4); 751 INSERT INTO t1 VALUES(5, 'odd', 'five', 5); 752 INSERT INTO t1 VALUES(6, 'even', 'six', 6); 753} 754 755foreach {tn sql error} { 756 1 { 757 SELECT c, sum(d) OVER win2 FROM t1 758 WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 759 win2 AS (win1 ORDER BY b) 760 } {cannot override frame specification of window: win1} 761 762 2 { 763 SELECT c, sum(d) OVER win2 FROM t1 764 WINDOW win1 AS (), 765 win2 AS (win4 ORDER BY b) 766 } {no such window: win4} 767 768 3 { 769 SELECT c, sum(d) OVER win2 FROM t1 770 WINDOW win1 AS (), 771 win2 AS (win1 PARTITION BY d) 772 } {cannot override PARTITION clause of window: win1} 773 774 4 { 775 SELECT c, sum(d) OVER win2 FROM t1 776 WINDOW win1 AS (ORDER BY b), 777 win2 AS (win1 ORDER BY d) 778 } {cannot override ORDER BY clause of window: win1} 779} { 780 do_catchsql_test 18.1.$tn $sql [list 1 $error] 781} 782 783foreach {tn sql error} { 784 1 { 785 SELECT c, sum(d) OVER (win1 ORDER BY b) FROM t1 786 WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 787 } {cannot override frame specification of window: win1} 788 789 2 { 790 SELECT c, sum(d) OVER (win4 ORDER BY b) FROM t1 791 WINDOW win1 AS () 792 } {no such window: win4} 793 794 3 { 795 SELECT c, sum(d) OVER (win1 PARTITION BY d) FROM t1 796 WINDOW win1 AS () 797 } {cannot override PARTITION clause of window: win1} 798 799 4 { 800 SELECT c, sum(d) OVER (win1 ORDER BY d) FROM t1 801 WINDOW win1 AS (ORDER BY b) 802 } {cannot override ORDER BY clause of window: win1} 803} { 804 do_catchsql_test 18.2.$tn $sql [list 1 $error] 805} 806 807do_execsql_test 18.3.1 { 808 SELECT group_concat(c, '.') OVER (PARTITION BY b ORDER BY c) 809 FROM t1 810} {four four.six four.six.two five five.one five.one.three} 811 812do_execsql_test 18.3.2 { 813 SELECT group_concat(c, '.') OVER (win1 ORDER BY c) 814 FROM t1 815 WINDOW win1 AS (PARTITION BY b) 816} {four four.six four.six.two five five.one five.one.three} 817 818do_execsql_test 18.3.3 { 819 SELECT group_concat(c, '.') OVER win2 820 FROM t1 821 WINDOW win1 AS (PARTITION BY b), 822 win2 AS (win1 ORDER BY c) 823} {four four.six four.six.two five five.one five.one.three} 824 825do_execsql_test 18.3.4 { 826 SELECT group_concat(c, '.') OVER (win2) 827 FROM t1 828 WINDOW win1 AS (PARTITION BY b), 829 win2 AS (win1 ORDER BY c) 830} {four four.six four.six.two five five.one five.one.three} 831 832do_execsql_test 18.3.5 { 833 SELECT group_concat(c, '.') OVER win5 834 FROM t1 835 WINDOW win1 AS (PARTITION BY b), 836 win2 AS (win1), 837 win3 AS (win2), 838 win4 AS (win3), 839 win5 AS (win4 ORDER BY c) 840} {four four.six four.six.two five five.one five.one.three} 841 842#------------------------------------------------------------------------- 843# Test RANGE <expr> PRECEDING/FOLLOWING when there are string, blob 844# and NULL values in the dataset. 845# 846reset_db 847do_execsql_test 19.0 { 848 CREATE TABLE t1(a, b); 849 INSERT INTO t1 VALUES 850 (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), 851 ('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10); 852} 853do_execsql_test 19.1 { 854 SELECT a, sum(b) OVER (ORDER BY a) FROM t1; 855} {1 1 2 3 3 6 4 10 5 15 a 21 b 28 c 36 d 45 e 55} 856 857do_execsql_test 19.2.1 { 858 SELECT a, sum(b) OVER ( 859 ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING 860 ) FROM t1; 861} {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} 862do_execsql_test 19.2.2 { 863 SELECT a, sum(b) OVER ( 864 ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING 865 ) FROM t1 ORDER BY a ASC; 866} {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} 867 868do_execsql_test 19.3.1 { 869 SELECT a, sum(b) OVER ( 870 ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING 871 ) FROM t1; 872} {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} 873do_execsql_test 19.3.2 { 874 SELECT a, sum(b) OVER ( 875 ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING 876 ) FROM t1 ORDER BY a ASC; 877} {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} 878 879 880reset_db 881do_execsql_test 20.0 { 882 CREATE TABLE t1(a, b); 883 INSERT INTO t1 VALUES 884 (NULL, 100), (NULL, 100), 885 (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), 886 ('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10); 887} 888do_execsql_test 20.1 { 889 SELECT a, sum(b) OVER (ORDER BY a) FROM t1; 890} { 891 {} 200 {} 200 1 201 2 203 3 206 4 210 5 215 892 a 221 b 228 c 236 d 245 e 255 893} 894 895do_execsql_test 20.2.1 { 896 SELECT a, sum(b) OVER ( 897 ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING 898 ) FROM t1; 899} {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} 900do_execsql_test 20.2.2 { 901 SELECT a, sum(b) OVER ( 902 ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING 903 ) FROM t1 ORDER BY a ASC; 904} {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} 905 906do_execsql_test 20.3.1 { 907 SELECT a, sum(b) OVER ( 908 ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING 909 ) FROM t1; 910} {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} 911do_execsql_test 20.3.2 { 912 SELECT a, sum(b) OVER ( 913 ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING 914 ) FROM t1 ORDER BY a ASC; 915} {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} 916 917#------------------------------------------------------------------------- 918do_execsql_test 21.0 { 919 CREATE TABLE keyword_tab( 920 current, exclude, filter, following, groups, no, others, over, 921 partition, preceding, range, ties, unbounded, window 922 ); 923} 924do_execsql_test 21.1 { 925 SELECT 926 current, exclude, filter, following, groups, no, others, over, 927 partition, preceding, range, ties, unbounded, window 928 FROM keyword_tab 929} 930 931#------------------------------------------------------------------------- 932foreach {tn expr err} { 933 1 4.5 0 934 2 NULL 1 935 3 0.0 0 936 4 0.1 0 937 5 -0.1 1 938 6 '' 1 939 7 '2.0' 0 940 8 '2.0x' 1 941 9 x'1234' 1 942 10 '1.2' 0 943} { 944 set res {0 1} 945 if {$err} {set res {1 {frame starting offset must be a non-negative number}} } 946 do_catchsql_test 22.$tn.1 " 947 WITH a(x, y) AS ( VALUES(1, 2) ) 948 SELECT sum(x) OVER ( 949 ORDER BY y RANGE BETWEEN $expr PRECEDING AND UNBOUNDED FOLLOWING 950 ) FROM a 951 " $res 952 953 set res {0 1} 954 if {$err} {set res {1 {frame ending offset must be a non-negative number}} } 955 do_catchsql_test 22.$tn.2 " 956 WITH a(x, y) AS ( VALUES(1, 2) ) 957 SELECT sum(x) OVER ( 958 ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND $expr FOLLOWING 959 ) FROM a 960 " $res 961} 962 963#------------------------------------------------------------------------- 964reset_db 965do_execsql_test 23.0 { 966 CREATE TABLE t5(a, b, c); 967 CREATE INDEX t5ab ON t5(a, b); 968} 969 970proc do_ordercount_test {tn sql nOrderBy} { 971 set plan [execsql "EXPLAIN QUERY PLAN $sql"] 972 uplevel [list do_test $tn [list regexp -all ORDER $plan] $nOrderBy] 973} 974 975do_ordercount_test 23.1 { 976 SELECT 977 sum(c) OVER (ORDER BY a, b), 978 sum(c) OVER (PARTITION BY a ORDER BY b) 979 FROM t5 980} 0 981 982do_ordercount_test 23.2 { 983 SELECT 984 sum(c) OVER (ORDER BY b, a), 985 sum(c) OVER (PARTITION BY b ORDER BY a) 986 FROM t5 987} 1 988 989do_ordercount_test 23.3 { 990 SELECT 991 sum(c) OVER (ORDER BY b, a), 992 sum(c) OVER (ORDER BY c, b) 993 FROM t5 994} 2 995 996do_ordercount_test 23.4 { 997 SELECT 998 sum(c) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 999 sum(c) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1000 sum(c) OVER (ORDER BY b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 1001 FROM t5 1002} 1 1003 1004do_ordercount_test 23.5 { 1005 SELECT 1006 sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING), 1007 sum(c) OVER (ORDER BY b+1 RANGE UNBOUNDED PRECEDING), 1008 sum(c) OVER (ORDER BY b+1 GROUPS UNBOUNDED PRECEDING) 1009 FROM t5 1010} 1 1011 1012do_ordercount_test 23.6 { 1013 SELECT 1014 sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING), 1015 sum(c) OVER (ORDER BY b+2 RANGE UNBOUNDED PRECEDING), 1016 sum(c) OVER (ORDER BY b+3 GROUPS UNBOUNDED PRECEDING) 1017 FROM t5 1018} 3 1019 1020do_execsql_test 24.1 { 1021 SELECT sum(44) OVER () 1022} {44} 1023 1024do_execsql_test 24.2 { 1025 SELECT lead(44) OVER () 1026} {{}} 1027 1028#------------------------------------------------------------------------- 1029# 1030reset_db 1031do_execsql_test 25.0 { 1032 CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ); 1033 CREATE TABLE t2 ( t2_id INTEGER PRIMARY KEY ); 1034 CREATE TABLE t3 ( t3_id INTEGER PRIMARY KEY ); 1035 1036 INSERT INTO t1 VALUES(1), (3), (5); 1037 INSERT INTO t2 VALUES (3), (5); 1038 INSERT INTO t3 VALUES(10), (11), (12); 1039} 1040 1041do_execsql_test 25.1 { 1042 SELECT t1.* FROM t1, t2 WHERE 1043 t1_id=t2_id AND t1_id IN ( 1044 SELECT t1_id + row_number() OVER ( ORDER BY t1_id ) FROM t3 1045 ) 1046} 1047 1048do_execsql_test 25.2 { 1049 SELECT t1.* FROM t1, t2 WHERE 1050 t1_id=t2_id AND t1_id IN ( 1051 SELECT row_number() OVER ( ORDER BY t1_id ) FROM t3 1052 ) 1053} {3} 1054 1055#------------------------------------------------------------------------- 1056reset_db 1057do_execsql_test 26.0 { 1058 CREATE TABLE t1(x); 1059 CREATE TABLE t2(c); 1060} 1061 1062do_execsql_test 26.1 { 1063 SELECT ( SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) ) FROM t2 1064} {} 1065 1066do_execsql_test 26.2 { 1067 INSERT INTO t1 VALUES(1), (2), (3), (4); 1068 INSERT INTO t2 VALUES(2), (6), (8), (4); 1069 SELECT c, c IN ( 1070 SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) 1071 ) FROM t2 1072} {2 1 6 0 8 0 4 1} 1073 1074do_execsql_test 26.3 { 1075 DELETE FROM t1; 1076 DELETE FROM t2; 1077 1078 INSERT INTO t2 VALUES(1), (2), (3), (4); 1079 INSERT INTO t1 VALUES(1), (1), (2), (3), (3), (3), (3), (4), (4); 1080 1081 SELECT c, c IN ( 1082 SELECT row_number() OVER () FROM ( SELECT 1 FROM t1 WHERE x=c ) 1083 ) FROM t2 1084} {1 1 2 0 3 1 4 0} 1085 1086#------------------------------------------------------------------------- 1087reset_db 1088do_execsql_test 27.0 { 1089 CREATE TABLE t1(x); 1090 INSERT INTO t1 VALUES(NULL), (1), (2), (3), (4), (5); 1091} 1092do_execsql_test 27.1 { 1093 SELECT min(x) FROM t1; 1094} {1} 1095do_execsql_test 27.2 { 1096 SELECT min(x) OVER win FROM t1 1097 WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 1098} {1 1 1 2 3 4} 1099 1100#------------------------------------------------------------------------- 1101 1102reset_db 1103do_execsql_test 28.1.1 { 1104 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY); 1105 INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0); 1106 INSERT INTO t1 VALUES (13,'M', 'cc', NULL); 1107} 1108 1109do_execsql_test 28.1.2 { 1110 SELECT group_concat(b,'') OVER w1 FROM t1 1111 WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING) 1112} { 1113 {} {} 1114} 1115 1116do_execsql_test 28.2.1 { 1117 CREATE TABLE t2(a TEXT, b INTEGER); 1118 INSERT INTO t2 VALUES('A', NULL); 1119 INSERT INTO t2 VALUES('B', NULL); 1120} 1121 1122do_execsql_test 28.2.1 { 1123 DROP TABLE IF EXISTS t1; 1124 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY); 1125 INSERT INTO t1 VALUES 1126 (10,'J', 'cc', NULL), 1127 (11,'K', 'cc', 'xyz'), 1128 (13,'M', 'cc', NULL); 1129} 1130 1131do_execsql_test 28.2.2 { 1132 SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1 1133 WINDOW w1 AS 1134 (ORDER BY d DESC RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING) 1135 ORDER BY c, d, a; 1136} { 1137 10 J cc NULL JM | 1138 13 M cc NULL JM | 1139 11 K cc 'xyz' K | 1140} 1141 1142#------------------------------------------------------------------------- 1143reset_db 1144 1145do_execsql_test 29.1 { 1146 DROP TABLE IF EXISTS t1; 1147 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY); 1148 INSERT INTO t1 VALUES 1149 (1, 'A', 'aa', 2.5), 1150 (2, 'B', 'bb', 3.75), 1151 (3, 'C', 'cc', 1.0), 1152 (4, 'D', 'cc', 8.25), 1153 (5, 'E', 'bb', 6.5), 1154 (6, 'F', 'aa', 6.5), 1155 (7, 'G', 'aa', 6.0), 1156 (8, 'H', 'bb', 9.0), 1157 (9, 'I', 'aa', 3.75), 1158 (10,'J', 'cc', NULL), 1159 (11,'K', 'cc', 'xyz'), 1160 (12,'L', 'cc', 'xyZ'), 1161 (13,'M', 'cc', NULL); 1162} 1163 1164do_execsql_test 29.2 { 1165 SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1 1166 WINDOW w1 AS 1167 (PARTITION BY c ORDER BY d DESC 1168 RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING) 1169 ORDER BY c, d, a; 1170} { 1171 1 A aa 2.5 FG | 1172 9 I aa 3.75 F | 1173 7 G aa 6 {} | 1174 6 F aa 6.5 {} | 1175 2 B bb 3.75 HE | 1176 5 E bb 6.5 H | 1177 8 H bb 9 {} | 1178 10 J cc NULL JM | 1179 13 M cc NULL JM | 1180 3 C cc 1 {} | 1181 4 D cc 8.25 {} | 1182 12 L cc 'xyZ' L | 1183 11 K cc 'xyz' K | 1184} 1185 1186# 2019-07-18 1187# Check-in [7ef7b23cbb1b9ace] (which was itself a fix for ticket 1188# https://www.sqlite.org/src/info/1be72aab9) introduced a new problem 1189# if the LHS of a BETWEEN operator is a WINDOW function. The problem 1190# was found by (the recently enhanced) dbsqlfuzz. 1191# 1192do_execsql_test 30.0 { 1193 DROP TABLE IF EXISTS t1; 1194 CREATE TABLE t1(a, b, c); 1195 INSERT INTO t1 VALUES('BB','aa',399); 1196 SELECT 1197 count () OVER win1 NOT BETWEEN 'a' AND 'mmm', 1198 count () OVER win3 1199 FROM t1 1200 WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING 1201 EXCLUDE CURRENT ROW), 1202 win2 AS (PARTITION BY b ORDER BY a), 1203 win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING ); 1204} {1 1} 1205 1206#------------------------------------------------------------------------- 1207reset_db 1208do_execsql_test 31.1 { 1209 CREATE TABLE t1(a, b); 1210 CREATE TABLE t2(c, d); 1211 CREATE TABLE t3(e, f); 1212 1213 INSERT INTO t1 VALUES(1, 1); 1214 INSERT INTO t2 VALUES(1, 1); 1215 INSERT INTO t3 VALUES(1, 1); 1216} 1217 1218do_execsql_test 31.2 { 1219 SELECT d IN (SELECT sum(c) OVER (ORDER BY e+c) FROM t3) FROM ( 1220 SELECT * FROM t2 1221 ); 1222} {1} 1223 1224do_execsql_test 31.3 { 1225 SELECT d IN (SELECT sum(c) OVER (PARTITION BY d ORDER BY e+c) FROM t3) FROM ( 1226 SELECT * FROM t2 1227 ); 1228} {1} 1229 1230do_catchsql_test 31.3 { 1231 SELECT d IN ( 1232 SELECT sum(c) OVER ( ROWS BETWEEN d FOLLOWING AND UNBOUNDED FOLLOWING) 1233 FROM t3 1234 ) 1235 FROM ( 1236 SELECT * FROM t2 1237 ); 1238} {1 {frame starting offset must be a non-negative integer}} 1239 1240do_catchsql_test 31.3 { 1241 SELECT d IN ( 1242 SELECT sum(c) OVER ( ROWS BETWEEN CURRENT ROW AND c FOLLOWING) 1243 FROM t3 1244 ) 1245 FROM ( 1246 SELECT * FROM t2 1247 ); 1248} {1 {frame ending offset must be a non-negative integer}} 1249 1250# 2019-11-16 chromium issue 1025467 1251db close 1252sqlite3 db :memory: 1253do_catchsql_test 32.10 { 1254 CREATE VIEW a AS SELECT NULL INTERSECT SELECT NULL ORDER BY s() OVER R; 1255 CREATE TABLE a0 AS SELECT 0; 1256 ALTER TABLE a0 RENAME TO S; 1257} {1 {error in view a: 1st ORDER BY term does not match any column in the result set}} 1258 1259reset_db 1260do_execsql_test 33.1 { 1261 CREATE TABLE t1(aa, bb); 1262 INSERT INTO t1 VALUES(1, 2); 1263 INSERT INTO t1 VALUES(5, 6); 1264 CREATE TABLE t2(x); 1265 INSERT INTO t2 VALUES(1); 1266} 1267do_execsql_test 33.2 { 1268 SELECT (SELECT DISTINCT sum(aa) OVER() FROM t1 ORDER BY 1), x FROM t2 1269 ORDER BY 1; 1270} {6 1} 1271 1272reset_db 1273do_execsql_test 34.1 { 1274 CREATE TABLE t1(a,b,c); 1275} 1276do_execsql_test 34.2 { 1277 SELECT avg(a) OVER ( 1278 ORDER BY (SELECT sum(b) OVER () 1279 FROM t1 ORDER BY ( 1280 SELECT total(d) OVER (ORDER BY c) 1281 FROM (SELECT 1 AS d) ORDER BY 1 1282 ) 1283 ) 1284 ) 1285 FROM t1; 1286} 1287 1288#------------------------------------------------------------------------- 1289reset_db 1290do_catchsql_test 35.0 { 1291 SELECT * WINDOW f AS () ORDER BY name COLLATE nocase; 1292} {1 {no tables specified}} 1293 1294do_catchsql_test 35.1 { 1295 VALUES(1) INTERSECT SELECT * WINDOW f AS () ORDER BY x COLLATE nocase; 1296} {1 {no tables specified}} 1297 1298do_execsql_test 35.2 { 1299 CREATE TABLE t1(x); 1300 INSERT INTO t1 VALUES(1), (2), (3); 1301 VALUES(1) INTERSECT 1302 SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1; 1303} {1} 1304 1305do_execsql_test 35.3 { 1306 VALUES(8) EXCEPT 1307 SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1; 1308} {8} 1309 1310do_execsql_test 35.4 { 1311 VALUES(1) UNION 1312 SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1; 1313} {1 3 6} 1314 1315# 2019-12-07 gramfuzz find 1316# 1317do_execsql_test 36.10 { 1318 VALUES(count(*)OVER()); 1319} {1} 1320do_execsql_test 36.20 { 1321 VALUES(count(*)OVER()),(2); 1322} {1 2} 1323do_execsql_test 36.30 { 1324 VALUES(2),(count(*)OVER()); 1325} {2 1} 1326do_execsql_test 36.40 { 1327 VALUES(2),(3),(count(*)OVER()),(4),(5); 1328} {2 3 1 4 5} 1329 1330# 2019-12-17 crash test case found by Yongheng and Rui 1331# See check-in 1ca0bd982ab1183b 1332# 1333reset_db 1334do_execsql_test 37.10 { 1335 CREATE TABLE t0(a UNIQUE, b PRIMARY KEY); 1336 CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1))) FROM t0; 1337 SELECT c FROM v0 WHERE c BETWEEN 10 AND 20; 1338} {} 1339do_execsql_test 37.20 { 1340 DROP VIEW v0; 1341 CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1234))) FROM t0; 1342 SELECT c FROM v0 WHERE c BETWEEN -10 AND 20; 1343} {} 1344 1345# 2019-12-20 mrigger reported problem with a FILTER clause on an aggregate 1346# in a join. 1347# 1348reset_db 1349do_catchsql_test 38.10 { 1350 CREATE TABLE t0(c0); 1351 CREATE TABLE t1(c0, c1 UNIQUE); 1352 INSERT INTO t0(c0) VALUES(1); 1353 INSERT INTO t1(c0,c1) VALUES(2,3); 1354 SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(0) FILTER(WHERE t1.c1)); 1355} {1 {misuse of aggregate: AVG()}} 1356do_execsql_test 38.20 { 1357 SELECT COUNT(*), AVG(1) FILTER(WHERE t1.c1) FROM t0, t1; 1358} {1 1.0} 1359do_catchsql_test 38.30 { 1360 SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(1) FILTER(WHERE t1.c1)); 1361} {1 {misuse of aggregate: AVG()}} 1362 1363reset_db 1364do_execsql_test 39.1 { 1365 CREATE TABLE t0(c0 UNIQUE); 1366} 1367do_execsql_test 39.2 { 1368 SELECT FIRST_VALUE(0) OVER(); 1369} {0} 1370do_execsql_test 39.3 { 1371 SELECT * FROM t0 WHERE(c0, 0) IN(SELECT FIRST_VALUE(0) OVER(), 0); 1372} 1373do_execsql_test 39.4 { 1374 SELECT * FROM t0 WHERE (t0.c0, 1) IN(SELECT NTILE(1) OVER(), 0 FROM t0); 1375} 1376 1377ifcapable rtree { 1378 # 2019-12-25 ticket d87336c81c7d0873 1379 # 1380 reset_db 1381 do_catchsql_test 40.1 { 1382 CREATE VIRTUAL TABLE t0 USING rtree(c0, c1, c2); 1383 SELECT * FROM t0 1384 WHERE ((0,0) IN (SELECT COUNT(*),LAG(5)OVER(PARTITION BY 0) FROM t0),0)<=(c1,0); 1385 } {0 {}} 1386} 1387 1388#------------------------------------------------------------------------- 1389reset_db 1390do_execsql_test 41.1 { 1391 CREATE TABLE t1(a, b, c); 1392 INSERT INTO t1 VALUES(NULL,'bb',355); 1393 INSERT INTO t1 VALUES('CC','aa',158); 1394 INSERT INTO t1 VALUES('GG','bb',929); 1395 INSERT INTO t1 VALUES('FF','Rb',574); 1396} 1397 1398do_execsql_test 41.2 { 1399 SELECT min(c) OVER ( 1400 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING 1401 ) FROM t1 1402} {355 158 574 929} 1403 1404do_execsql_test 41.2 { 1405 SELECT min(c) OVER ( 1406 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING 1407 ) << 100 FROM t1 1408} {0 0 0 0} 1409 1410do_execsql_test 41.3 { 1411 SELECT 1412 min(c) OVER win3 << first_value(c) OVER win3, 1413 min(c) OVER win3 << first_value(c) OVER win3 1414 FROM t1 1415 WINDOW win3 AS ( 1416 PARTITION BY 6 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING 1417 ); 1418} {0 0 0 0 0 0 0 0} 1419 1420#------------------------------------------------------------------------- 1421reset_db 1422do_execsql_test 42.1 { 1423 CREATE TABLE t1(a, b, c); 1424 INSERT INTO t1 VALUES(1, 1, 1); 1425 INSERT INTO t1 VALUES(2, 2, 2); 1426} 1427do_execsql_test 42.2 { 1428 SELECT * FROM t1 WHERE (0, 0) IN ( SELECT count(*), 0 FROM t1 ) 1429} {} 1430do_execsql_test 42.3 { 1431 SELECT * FROM t1 WHERE (2, 0) IN ( SELECT count(*), 0 FROM t1 ) 1432} {1 1 1 2 2 2} 1433 1434do_execsql_test 42.3 { 1435 SELECT count(*), max(a) OVER () FROM t1 GROUP BY c; 1436} {1 2 1 2} 1437 1438do_execsql_test 42.4 { 1439 SELECT sum(a), max(b) OVER () FROM t1; 1440} {3 1} 1441 1442do_execsql_test 42.5 { 1443 CREATE TABLE t2(a, b); 1444 INSERT INTO t2 VALUES('a', 1); 1445 INSERT INTO t2 VALUES('a', 2); 1446 INSERT INTO t2 VALUES('a', 3); 1447 INSERT INTO t2 VALUES('b', 4); 1448 INSERT INTO t2 VALUES('b', 5); 1449 INSERT INTO t2 VALUES('b', 6); 1450} 1451 1452do_execsql_test 42.6 { 1453 SELECT a, sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2 GROUP BY a; 1454} {a 6 6 b 15 21} 1455 1456do_execsql_test 42.7 { 1457 SELECT sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2; 1458} {21 21} 1459 1460#------------------------------------------------------------------------- 1461reset_db 1462do_execsql_test 43.1.1 { 1463 CREATE TABLE t1(x INTEGER PRIMARY KEY); 1464 INSERT INTO t1 VALUES (10); 1465} 1466do_catchsql_test 43.1.2 { 1467 SELECT count() OVER() AS m FROM t1 ORDER BY (SELECT m); 1468} {1 {misuse of aliased window function m}} 1469 1470reset_db 1471do_execsql_test 43.2.1 { 1472 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); 1473 INSERT INTO t1(a, b) VALUES(1, 10); -- 10 1474 INSERT INTO t1(a, b) VALUES(2, 15); -- 25 1475 INSERT INTO t1(a, b) VALUES(3, -5); -- 20 1476 INSERT INTO t1(a, b) VALUES(4, -5); -- 15 1477 INSERT INTO t1(a, b) VALUES(5, 20); -- 35 1478 INSERT INTO t1(a, b) VALUES(6, -11); -- 24 1479} 1480 1481do_execsql_test 43.2.2 { 1482 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY 2 1483} { 1484 1 10 4 15 3 20 6 24 2 25 5 35 1485} 1486 1487do_execsql_test 43.2.3 { 1488 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc 1489} { 1490 1 10 4 15 3 20 6 24 2 25 5 35 1491} 1492 1493do_execsql_test 43.2.4 { 1494 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc+5 1495} { 1496 1 10 4 15 3 20 6 24 2 25 5 35 1497} 1498 1499do_catchsql_test 43.2.5 { 1500 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc) 1501} {1 {misuse of aliased window function abc}} 1502 1503do_catchsql_test 43.2.6 { 1504 SELECT a, 1+sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc) 1505} {1 {misuse of aliased window function abc}} 1506 1507#------------------------------------------------------------------------- 1508reset_db 1509do_execsql_test 44.1 { 1510 CREATE TABLE t0(c0); 1511} 1512 1513do_catchsql_test 44.2.1 { 1514 SELECT ntile(0) OVER (); 1515} {1 {argument of ntile must be a positive integer}} 1516do_catchsql_test 44.2.2 { 1517 SELECT (0, 0) IN(SELECT MIN(c0), NTILE(0) OVER()) FROM t0; 1518} {1 {argument of ntile must be a positive integer}} 1519 1520do_execsql_test 44.3.1 { 1521 SELECT ntile(1) OVER (); 1522} {1} 1523do_execsql_test 44.3.2 { 1524 SELECT (0, 0) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0; 1525} {0} 1526 1527do_execsql_test 44.4.2 { 1528 INSERT INTO t0 VALUES(2), (1), (0); 1529 SELECT (0, 1) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0; 1530} {1} 1531 1532#------------------------------------------------------------------------- 1533reset_db 1534do_execsql_test 45.1 { 1535 CREATE TABLE t0(x); 1536 CREATE TABLE t1(a); 1537 INSERT INTO t1 VALUES(1000); 1538 INSERT INTO t1 VALUES(1000); 1539 INSERT INTO t0 VALUES(10000); 1540} 1541do_execsql_test 45.2 { 1542 SELECT * FROM ( 1543 SELECT sum (a) OVER() FROM t1 UNION ALL SELECT x FROM t0 1544 ); 1545} {2000 2000 10000} 1546 1547#------------------------------------------------------------------------- 1548reset_db 1549do_execsql_test 46.1 { 1550 CREATE TABLE t1 (a); 1551 CREATE INDEX i1 ON t1(a); 1552 1553 INSERT INTO t1 VALUES (10); 1554} 1555 1556do_execsql_test 46.2 { 1557 SELECT (SELECT sum(a) OVER(ORDER BY a)) FROM t1 1558} 10 1559 1560do_execsql_test 46.3 { 1561 SELECT * FROM t1 WHERE (SELECT sum(a) OVER(ORDER BY a)); 1562} 10 1563 1564do_execsql_test 46.4 { 1565 SELECT * FROM t1 NATURAL JOIN t1 1566 WHERE a=1 1567 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10) 1568} 10 1569 1570#------------------------------------------------------------------------- 1571reset_db 1572do_execsql_test 47.0 { 1573 CREATE TABLE t1( 1574 a, 1575 e, 1576 f, 1577 g UNIQUE, 1578 h UNIQUE 1579 ); 1580} 1581 1582do_execsql_test 47.1 { 1583 CREATE VIEW t2(k) AS 1584 SELECT e FROM t1 WHERE g = 'abc' OR h BETWEEN 10 AND f; 1585} 1586 1587do_catchsql_test 47.2 { 1588 SELECT 234 FROM t2 1589 WHERE k=1 1590 OR (SELECT k FROM t2 WHERE (SELECT sum(a) OVER() FROM t1 GROUP BY 1)); 1591} {1 {misuse of window function sum()}} 1592 1593#------------------------------------------------------------------------- 1594reset_db 1595do_execsql_test 48.0 { 1596 CREATE TABLE t1(a); 1597 INSERT INTO t1 VALUES(1); 1598 INSERT INTO t1 VALUES(2); 1599 INSERT INTO t1 VALUES(3); 1600 SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x)) 1601 FROM (SELECT (SELECT sum(a) FROM t1) AS x FROM t1); 1602} {12 12 12} 1603 1604do_execsql_test 48.1 { 1605 SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x)) 1606 FROM (SELECT (SELECT sum(a) FROM t1 GROUP BY a) AS x FROM t1); 1607} {2 2 2} 1608 1609#------------------------------------------------------------------------- 1610reset_db 1611do_execsql_test 49.1 { 1612 CREATE TABLE t1 (a PRIMARY KEY); 1613 INSERT INTO t1 VALUES(1); 1614} 1615 1616do_execsql_test 49.2 { 1617 SELECT b AS c FROM ( 1618 SELECT a AS b FROM ( 1619 SELECT a FROM t1 WHERE a=1 OR (SELECT sum(a) OVER ()) 1620 ) 1621 WHERE b=1 OR b<10 1622 ) 1623 WHERE c=1 OR c>=10; 1624} {1} 1625 1626 1627#------------------------------------------------------------------------- 1628reset_db 1629do_execsql_test 50.0 { 1630 CREATE TABLE t1 (a DOUBLE PRIMARY KEY); 1631 INSERT INTO t1 VALUES(10.0); 1632} 1633 1634do_execsql_test 50.1 { 1635 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) 1636} {10.0} 1637 1638do_execsql_test 50.2 { 1639 SELECT * FROM ( 1640 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) 1641 ) 1642 WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 ) 1643} {10.0} 1644 1645do_execsql_test 50.3 { 1646 SELECT a FROM ( 1647 SELECT * FROM ( 1648 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) 1649 ) 1650 WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 ) 1651 ) 1652 WHERE a=1 OR a=10.0 1653} {10.0} 1654 1655do_execsql_test 50.4 { 1656 SELECT a FROM ( 1657 SELECT * FROM ( 1658 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) 1659 ) 1660 WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 ) 1661 ) 1662 WHERE a=1 OR ((SELECT sum(a) OVER(ORDER BY a%8)) AND 10<=a) 1663} {10.0} 1664 1665do_execsql_test 50.5 { 1666SELECT * FROM (SELECT * FROM t1 NATURAL JOIN t1 WHERE a%1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum((SELECT * FROM (SELECT * FROM (SELECT * FROM t1 NATURAL JOIN t1 WHERE a%1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum((SELECT * FROM t1 NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)))OVER(ORDER BY a% 1 )) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND 10<=a)))OVER(ORDER BY a%5)) AND a<=10); 1667} {10.0} 1668 1669# 2020-04-03 ticket af4556bb5c285c08 1670# 1671reset_db 1672do_catchsql_test 51.1 { 1673 CREATE TABLE a(b, c); 1674 SELECT c FROM a GROUP BY c 1675 HAVING(SELECT(sum(b) OVER(ORDER BY b), 1676 sum(b) OVER(PARTITION BY min(DISTINCT c), c ORDER BY b))); 1677} {1 {row value misused}} 1678 1679#------------------------------------------------------------------------- 1680reset_db 1681do_execsql_test 52.1 { 1682 CREATE TABLE t1(a, b, c); 1683 INSERT INTO t1 VALUES('AA','bb',356); 1684 INSERT INTO t1 VALUES('CC','aa',158); 1685 INSERT INTO t1 VALUES('BB','aa',399); 1686 INSERT INTO t1 VALUES('FF','bb',938); 1687} 1688 1689do_execsql_test 52.2 { 1690 SELECT 1691 count() OVER win1, 1692 sum(c) OVER win2, 1693 first_value(c) OVER win2, 1694 count(a) OVER (ORDER BY b) 1695 FROM t1 1696 WINDOW 1697 win1 AS (ORDER BY a), 1698 win2 AS (PARTITION BY 6 ORDER BY a 1699 RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING ); 1700} { 1701 1 356 356 4 1702 2 399 399 2 1703 3 158 158 2 1704 4 938 938 4 1705} 1706 1707do_execsql_test 52.3 { 1708SELECT 1709 count() OVER (), 1710 sum(c) OVER win2, 1711 first_value(c) OVER win2, 1712 count(a) OVER (ORDER BY b) 1713FROM t1 1714WINDOW 1715 win1 AS (ORDER BY a), 1716 win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a 1717 RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING ); 1718} { 1719 4 356 356 4 1720 4 399 399 2 1721 4 158 158 2 1722 4 938 938 4 1723} 1724 1725do_execsql_test 52.4 { 1726 SELECT 1727 count() OVER win1, 1728 sum(c) OVER win2, 1729 first_value(c) OVER win2, 1730 count(a) OVER (ORDER BY b) 1731 FROM t1 1732 WINDOW 1733 win1 AS (ORDER BY a), 1734 win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a 1735 RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING ); 1736} { 1737 1 356 356 4 1738 2 399 399 2 1739 3 158 158 2 1740 4 938 938 4 1741} 1742 1743# 2020-05-23 1744# ticket 7a5279a25c57adf1 1745# 1746reset_db 1747do_execsql_test 53.0 { 1748 CREATE TABLE a(c UNIQUE); 1749 INSERT INTO a VALUES(4),(0),(9),(-9); 1750 SELECT a.c 1751 FROM a 1752 JOIN a AS b ON a.c=4 1753 JOIN a AS e ON a.c=e.c 1754 WHERE a.c=(SELECT (SELECT coalesce(lead(2) OVER(),0) + sum(d.c)) 1755 FROM a AS d 1756 WHERE a.c); 1757} {4 4 4 4} 1758 1759#------------------------------------------------------------------------- 1760reset_db 1761do_execsql_test 54.1 { 1762 CREATE TABLE t1(a VARCHAR(20), b FLOAT); 1763 INSERT INTO t1 VALUES('1',10.0); 1764} 1765 1766do_catchsql_test 54.2 { 1767 SELECT * FROM ( 1768 SELECT sum(b) OVER() AS c FROM t1 1769 UNION 1770 SELECT b AS c FROM t1 1771 ) WHERE c>10; 1772} {0 {}} 1773 1774do_execsql_test 54.3 { 1775 INSERT INTO t1 VALUES('2',5.0); 1776 INSERT INTO t1 VALUES('3',15.0); 1777} 1778 1779do_catchsql_test 54.4 { 1780 SELECT * FROM ( 1781 SELECT sum(b) OVER() AS c FROM t1 1782 UNION 1783 SELECT b AS c FROM t1 1784 ) WHERE c>10; 1785} {0 {15.0 30.0}} 1786 1787# 2020-06-05 ticket c8d3b9f0a750a529 1788reset_db 1789do_execsql_test 55.1 { 1790 CREATE TABLE a(b); 1791 SELECT 1792 (SELECT b FROM a 1793 GROUP BY b 1794 HAVING (SELECT COUNT()OVER() + lead(b)OVER(ORDER BY SUM(DISTINCT b) + b)) 1795 ) 1796 FROM a 1797 UNION 1798 SELECT 99 1799 ORDER BY 1; 1800} {99} 1801 1802#------------------------------------------------------------------------ 1803reset_db 1804do_execsql_test 56.1 { 1805 CREATE TABLE t1(a, b INTEGER); 1806 CREATE TABLE t2(c, d); 1807} 1808do_catchsql_test 56.2 { 1809 SELECT avg(b) FROM t1 1810 UNION ALL 1811 SELECT min(c) OVER () FROM t2 1812 ORDER BY nosuchcolumn; 1813} {1 {1st ORDER BY term does not match any column in the result set}} 1814 1815reset_db 1816do_execsql_test 57.1 { 1817 CREATE TABLE t4(a, b, c, d, e); 1818} 1819 1820do_catchsql_test 57.2 { 1821 SELECT b FROM t4 1822 UNION 1823 SELECT a FROM t4 1824 ORDER BY ( 1825 SELECT sum(x) OVER() FROM ( 1826 SELECT c AS x FROM t4 1827 UNION 1828 SELECT d FROM t4 1829 ORDER BY (SELECT e FROM t4) 1830 ) 1831 ); 1832} {1 {1st ORDER BY term does not match any column in the result set}} 1833 1834# 2020-06-06 various dbsqlfuzz finds and 1835# ticket 0899cf62f597d7e7 1836# 1837reset_db 1838do_execsql_test 57.1 { 1839 CREATE TABLE t1(a, b, c); 1840 INSERT INTO t1 VALUES(NULL,NULL,NULL); 1841 SELECT 1842 sum(a), 1843 min(b) OVER (), 1844 count(c) OVER (ORDER BY b) 1845 FROM t1; 1846} {{} {} 0} 1847do_execsql_test 57.2 { 1848 CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ; 1849 INSERT INTO v0 VALUES ( 10 ) ; 1850 SELECT DISTINCT v1, lead(v1) OVER() FROM v0 GROUP BY v1 ORDER BY 2; 1851} {10 {}} 1852do_catchsql_test 57.3 { 1853 DROP TABLE t1; 1854 CREATE TABLE t1(a); 1855 INSERT INTO t1(a) VALUES(22); 1856 CREATE TABLE t3(y); 1857 INSERT INTO t3(y) VALUES(5),(11),(-9); 1858 SELECT ( 1859 SELECT max(y) OVER( ORDER BY (SELECT x FROM (SELECT sum(y) AS x FROM t1))) 1860 ) 1861 FROM t3; 1862} {1 {misuse of aggregate: sum()}} 1863 1864# 2020-06-06 ticket 1f6f353b684fc708 1865reset_db 1866do_execsql_test 58.1 { 1867 CREATE TABLE a(a, b, c); 1868 INSERT INTO a VALUES(1, 2, 3); 1869 INSERT INTO a VALUES(4, 5, 6); 1870 SELECT sum(345+b) OVER (ORDER BY b), 1871 sum(avg(678)) OVER (ORDER BY c) FROM a; 1872} {347 678.0} 1873 1874# 2020-06-06 ticket e5504e987e419fb0 1875do_catchsql_test 59.1 { 1876 DROP TABLE IF EXISTS t1; 1877 CREATE TABLE t1(x INTEGER PRIMARY KEY); 1878 INSERT INTO t1 VALUES (123); 1879 SELECT 1880 ntile( (SELECT sum(x)) ) OVER(ORDER BY x), 1881 min(x) OVER(ORDER BY x) 1882 FROM t1; 1883} {1 {misuse of aggregate: sum()}} 1884 1885# 2020-06-07 ticket f7d890858f361402 1886do_execsql_test 60.1 { 1887 DROP TABLE IF EXISTS t1; 1888 CREATE TABLE t1 (x INTEGER PRIMARY KEY); 1889 INSERT INTO t1 VALUES (99); 1890 SELECT EXISTS(SELECT count(*) OVER() FROM t1 ORDER BY sum(x) OVER()); 1891} {1} 1892 1893# 2020-06-07 test case generated by dbsqlfuzz showing how an AggInfo 1894# object might be referenced after the sqlite3Select() call that created 1895# it returns. This proves the need to persist all AggInfo objects until 1896# the Parse object is destroyed. 1897# 1898reset_db 1899do_execsql_test 61.1 { 1900CREATE TABLE t1(a); 1901INSERT INTO t1 VALUES(5),(NULL),('seventeen'); 1902SELECT (SELECT max(x)OVER(ORDER BY x) % min(x)OVER(ORDER BY CASE x WHEN 889 THEN x WHEN x THEN x END)) FROM (SELECT (SELECT sum(CAST(a IN(SELECT (SELECT max(x)OVER(ORDER BY CASE x WHEN 889 THEN 299 WHEN 863 THEN 863 END)) FROM (SELECT (SELECT sum(CAST((SELECT (SELECT max(x)OVER(ORDER BY x) / min(x)OVER(ORDER BY CASE x WHEN 889 THEN 299 WHEN -true THEN 863 END)) FROM (SELECT (SELECT sum(CAST(a IN(SELECT (SELECT max(x) & sum ( a )OVER(ORDER BY CASE x WHEN -8 THEN 299 WHEN 863 THEN 863 END)) FROM (SELECT (SELECT sum(CAST(a AS )) FROM t1) AS x FROM t1)) AS t1 )) FROM t1) AS x FROM t1)) AS x )) FROM t1) AS x FROM t1)) AS real)) FROM t1) AS x FROM t1); 1903} {{} {} {}} 1904 1905#------------------------------------------------------------------------- 1906reset_db 1907do_execsql_test 62.1 { 1908 CREATE TABLE t1(a VARCHAR(20), b FLOAT); 1909 INSERT INTO t1 VALUES('1',10.0); 1910} 1911 1912do_execsql_test 62.2 { 1913 SELECT * FROM ( 1914 SELECT sum(b) OVER() AS c FROM t1 1915 UNION 1916 SELECT b AS c FROM t1 1917 ) WHERE c>10; 1918} 1919 1920do_execsql_test 62.3 { 1921 INSERT INTO t1 VALUES('2',5.0); 1922 INSERT INTO t1 VALUES('3',15.0); 1923} 1924 1925do_execsql_test 62.4 { 1926 SELECT * FROM ( 1927 SELECT sum(b) OVER() AS c FROM t1 1928 UNION 1929 SELECT b AS c FROM t1 1930 ) WHERE c>10; 1931} {15.0 30.0} 1932 1933#------------------------------------------------------------------------- 1934reset_db 1935do_execsql_test 63.1 { 1936 CREATE TABLE t1(b, x); 1937 CREATE TABLE t2(c, d); 1938 CREATE TABLE t3(e, f); 1939} 1940 1941do_execsql_test 63.2 { 1942 SELECT max(b) OVER( 1943 ORDER BY SUM( 1944 (SELECT c FROM t2 UNION SELECT x ORDER BY c) 1945 ) 1946 ) FROM t1; 1947} {{}} 1948 1949do_execsql_test 63.3 { 1950 SELECT sum(b) over( 1951 ORDER BY ( 1952 SELECT max(b) OVER( 1953 ORDER BY sum( 1954 (SELECT x AS c UNION SELECT 1234 ORDER BY c) 1955 ) 1956 ) AS e 1957 ORDER BY e 1958 ) 1959 ) 1960 FROM t1; 1961} {{}} 1962 1963#------------------------------------------------------------------------- 1964reset_db 1965do_execsql_test 64.1 { 1966 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 1967 INSERT INTO t1 VALUES(1, 'abcd'); 1968 INSERT INTO t1 VALUES(2, 'BCDE'); 1969 INSERT INTO t1 VALUES(3, 'cdef'); 1970 INSERT INTO t1 VALUES(4, 'DEFG'); 1971} 1972 1973do_execsql_test 64.2 { 1974 SELECT rowid, max(b COLLATE nocase)||'' 1975 FROM t1 1976 GROUP BY rowid 1977 ORDER BY max(b COLLATE nocase)||''; 1978} {1 abcd 2 BCDE 3 cdef 4 DEFG} 1979 1980do_execsql_test 64.3 { 1981 SELECT count() OVER (), rowid, max(b COLLATE nocase)||'' 1982 FROM t1 1983 GROUP BY rowid 1984 ORDER BY max(b COLLATE nocase)||''; 1985} {4 1 abcd 4 2 BCDE 4 3 cdef 4 4 DEFG} 1986 1987do_execsql_test 64.4 { 1988 SELECT count() OVER (), rowid, max(b COLLATE nocase) 1989 FROM t1 1990 GROUP BY rowid 1991 ORDER BY max(b COLLATE nocase); 1992} {4 1 abcd 4 2 BCDE 4 3 cdef 4 4 DEFG} 1993 1994#------------------------------------------------------------------------- 1995reset_db 1996do_execsql_test 65.1 { 1997 CREATE TABLE t1(c1); 1998 INSERT INTO t1 VALUES('abcd'); 1999} 2000do_execsql_test 65.2 { 2001 SELECT max(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1; 2002} {1} 2003 2004do_execsql_test 65.3 { 2005 SELECT 2006 count() OVER (), 2007 group_concat(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1; 2008} {1 1} 2009 2010do_execsql_test 65.4 { 2011 SELECT COUNT() OVER () LIKE lead(102030) OVER( 2012 ORDER BY sum('abcdef' COLLATE nocase) IN (SELECT 54321) 2013 ) 2014 FROM t1; 2015} {{}} 2016 2017#------------------------------------------------------------------------- 2018reset_db 2019 2020do_execsql_test 66.1 { 2021 CREATE TABLE t1(a INTEGER); 2022 INSERT INTO t1 VALUES(3578824042033200656); 2023 INSERT INTO t1 VALUES(3029012920382354029); 2024} 2025 2026foreach {tn spec} { 2027 1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" 2028 2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING" 2029 3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING" 2030 4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" 2031 5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" 2032 6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING" 2033} { 2034 do_execsql_test 66.2.$tn " 2035 SELECT total(a) OVER ( $spec ) FROM t1 ORDER BY a 2036 " { 2037 3.02901292038235e+18 3.5788240420332e+18 2038 } 2039} 2040 2041 2042do_execsql_test 66.3 { 2043 CREATE TABLE t2(a INTEGER); 2044 INSERT INTO t2 VALUES(45); 2045 INSERT INTO t2 VALUES(30); 2046} 2047 2048foreach {tn spec res} { 2049 1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0} 2050 2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING" {0.0 0.0} 2051 3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING" {0.0 0.0} 2052 4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0} 2053 5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0} 2054 6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING" {0.0 0.0} 2055} { 2056 do_execsql_test 66.2.$tn " 2057 SELECT total(a) OVER ( $spec ) FROM t2 ORDER BY a 2058 " $res 2059} 2060 2061 2062#------------------------------------------------------------------------- 2063reset_db 2064do_execsql_test 67.0 { 2065 CREATE TABLE t1(a, b, c); 2066 CREATE TABLE t2(a, b, c); 2067} 2068 2069do_catchsql_test 67.1 { 2070 SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY ( 2071 SELECT nth_value(a,2) OVER w1 2072 WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM v1)) ) 2073 ) 2074} {1 {1st ORDER BY term does not match any column in the result set}} 2075 2076finish_test 2077