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