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 1270db close 1271sqlite3 db :memory: 1272do_catchsql_test 32.10 { 1273 CREATE VIEW a AS SELECT NULL INTERSECT SELECT NULL ORDER BY s() OVER R; 1274 CREATE TABLE a0 AS SELECT 0; 1275 ALTER TABLE a0 RENAME TO S; 1276} {1 {error in view a: 1st ORDER BY term does not match any column in the result set}} 1277 1278reset_db 1279do_execsql_test 33.1 { 1280 CREATE TABLE t1(aa, bb); 1281 INSERT INTO t1 VALUES(1, 2); 1282 INSERT INTO t1 VALUES(5, 6); 1283 CREATE TABLE t2(x); 1284 INSERT INTO t2 VALUES(1); 1285} 1286do_execsql_test 33.2 { 1287 SELECT (SELECT DISTINCT sum(aa) OVER() FROM t1 ORDER BY 1), x FROM t2 1288 ORDER BY 1; 1289} {6 1} 1290 1291reset_db 1292do_execsql_test 34.1 { 1293 CREATE TABLE t1(a,b,c); 1294} 1295do_execsql_test 34.2 { 1296 SELECT avg(a) OVER ( 1297 ORDER BY (SELECT sum(b) OVER () 1298 FROM t1 ORDER BY ( 1299 SELECT total(d) OVER (ORDER BY c) 1300 FROM (SELECT 1 AS d) ORDER BY 1 1301 ) 1302 ) 1303 ) 1304 FROM t1; 1305} 1306 1307#------------------------------------------------------------------------- 1308reset_db 1309do_catchsql_test 35.0 { 1310 SELECT * WINDOW f AS () ORDER BY name COLLATE nocase; 1311} {1 {no tables specified}} 1312 1313do_catchsql_test 35.1 { 1314 VALUES(1) INTERSECT SELECT * WINDOW f AS () ORDER BY x COLLATE nocase; 1315} {1 {no tables specified}} 1316 1317do_execsql_test 35.2 { 1318 CREATE TABLE t1(x); 1319 INSERT INTO t1 VALUES(1), (2), (3); 1320 VALUES(1) INTERSECT 1321 SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1; 1322} {1} 1323 1324do_execsql_test 35.3 { 1325 VALUES(8) EXCEPT 1326 SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1; 1327} {8} 1328 1329do_execsql_test 35.4 { 1330 VALUES(1) UNION 1331 SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1; 1332} {1 3 6} 1333 1334# 2019-12-07 gramfuzz find 1335# 1336do_execsql_test 36.10 { 1337 VALUES(count(*)OVER()); 1338} {1} 1339do_execsql_test 36.20 { 1340 VALUES(count(*)OVER()),(2); 1341} {1 2} 1342do_execsql_test 36.30 { 1343 VALUES(2),(count(*)OVER()); 1344} {2 1} 1345do_execsql_test 36.40 { 1346 VALUES(2),(3),(count(*)OVER()),(4),(5); 1347} {2 3 1 4 5} 1348 1349# 2019-12-17 crash test case found by Yongheng and Rui 1350# See check-in 1ca0bd982ab1183b 1351# 1352reset_db 1353do_execsql_test 37.10 { 1354 CREATE TABLE t0(a UNIQUE, b PRIMARY KEY); 1355 CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1))) FROM t0; 1356 SELECT c FROM v0 WHERE c BETWEEN 10 AND 20; 1357} {} 1358do_execsql_test 37.20 { 1359 DROP VIEW v0; 1360 CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1234))) FROM t0; 1361 SELECT c FROM v0 WHERE c BETWEEN -10 AND 20; 1362} {} 1363 1364# 2019-12-20 mrigger reported problem with a FILTER clause on an aggregate 1365# in a join. 1366# 1367reset_db 1368do_catchsql_test 38.10 { 1369 CREATE TABLE t0(c0); 1370 CREATE TABLE t1(c0, c1 UNIQUE); 1371 INSERT INTO t0(c0) VALUES(1); 1372 INSERT INTO t1(c0,c1) VALUES(2,3); 1373 SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(0) FILTER(WHERE t1.c1)); 1374} {1 {misuse of aggregate: AVG()}} 1375do_execsql_test 38.20 { 1376 SELECT COUNT(*), AVG(1) FILTER(WHERE t1.c1) FROM t0, t1; 1377} {1 1.0} 1378do_catchsql_test 38.30 { 1379 SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(1) FILTER(WHERE t1.c1)); 1380} {1 {misuse of aggregate: AVG()}} 1381 1382reset_db 1383do_execsql_test 39.1 { 1384 CREATE TABLE t0(c0 UNIQUE); 1385} 1386do_execsql_test 39.2 { 1387 SELECT FIRST_VALUE(0) OVER(); 1388} {0} 1389do_execsql_test 39.3 { 1390 SELECT * FROM t0 WHERE(c0, 0) IN(SELECT FIRST_VALUE(0) OVER(), 0); 1391} 1392do_execsql_test 39.4 { 1393 SELECT * FROM t0 WHERE (t0.c0, 1) IN(SELECT NTILE(1) OVER(), 0 FROM t0); 1394} 1395 1396ifcapable rtree { 1397 # 2019-12-25 ticket d87336c81c7d0873 1398 # 1399 reset_db 1400 do_catchsql_test 40.1 { 1401 CREATE VIRTUAL TABLE t0 USING rtree(c0, c1, c2); 1402 SELECT * FROM t0 1403 WHERE ((0,0) IN (SELECT COUNT(*),LAG(5)OVER(PARTITION BY 0) FROM t0),0)<=(c1,0); 1404 } {0 {}} 1405} 1406 1407#------------------------------------------------------------------------- 1408reset_db 1409do_execsql_test 41.1 { 1410 CREATE TABLE t1(a, b, c); 1411 INSERT INTO t1 VALUES(NULL,'bb',355); 1412 INSERT INTO t1 VALUES('CC','aa',158); 1413 INSERT INTO t1 VALUES('GG','bb',929); 1414 INSERT INTO t1 VALUES('FF','Rb',574); 1415} 1416 1417do_execsql_test 41.2 { 1418 SELECT min(c) OVER ( 1419 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING 1420 ) FROM t1 1421} {355 158 574 929} 1422 1423do_execsql_test 41.2 { 1424 SELECT min(c) OVER ( 1425 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING 1426 ) << 100 FROM t1 1427} {0 0 0 0} 1428 1429do_execsql_test 41.3 { 1430 SELECT 1431 min(c) OVER win3 << first_value(c) OVER win3, 1432 min(c) OVER win3 << first_value(c) OVER win3 1433 FROM t1 1434 WINDOW win3 AS ( 1435 PARTITION BY 6 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING 1436 ); 1437} {0 0 0 0 0 0 0 0} 1438 1439#------------------------------------------------------------------------- 1440reset_db 1441do_execsql_test 42.1 { 1442 CREATE TABLE t1(a, b, c); 1443 INSERT INTO t1 VALUES(1, 1, 1); 1444 INSERT INTO t1 VALUES(2, 2, 2); 1445} 1446do_execsql_test 42.2 { 1447 SELECT * FROM t1 WHERE (0, 0) IN ( SELECT count(*), 0 FROM t1 ) 1448} {} 1449do_execsql_test 42.3 { 1450 SELECT * FROM t1 WHERE (2, 0) IN ( SELECT count(*), 0 FROM t1 ) 1451} {1 1 1 2 2 2} 1452 1453do_execsql_test 42.3 { 1454 SELECT count(*), max(a) OVER () FROM t1 GROUP BY c; 1455} {1 2 1 2} 1456 1457do_execsql_test 42.4 { 1458 SELECT sum(a), max(b) OVER () FROM t1; 1459} {3 1} 1460 1461do_execsql_test 42.5 { 1462 CREATE TABLE t2(a, b); 1463 INSERT INTO t2 VALUES('a', 1); 1464 INSERT INTO t2 VALUES('a', 2); 1465 INSERT INTO t2 VALUES('a', 3); 1466 INSERT INTO t2 VALUES('b', 4); 1467 INSERT INTO t2 VALUES('b', 5); 1468 INSERT INTO t2 VALUES('b', 6); 1469} 1470 1471do_execsql_test 42.6 { 1472 SELECT a, sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2 GROUP BY a; 1473} {a 6 6 b 15 21} 1474 1475do_execsql_test 42.7 { 1476 SELECT sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2; 1477} {21 21} 1478 1479#------------------------------------------------------------------------- 1480reset_db 1481do_execsql_test 43.1.1 { 1482 CREATE TABLE t1(x INTEGER PRIMARY KEY); 1483 INSERT INTO t1 VALUES (10); 1484} 1485do_catchsql_test 43.1.2 { 1486 SELECT count() OVER() AS m FROM t1 ORDER BY (SELECT m); 1487} {1 {misuse of aliased window function m}} 1488 1489reset_db 1490do_execsql_test 43.2.1 { 1491 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); 1492 INSERT INTO t1(a, b) VALUES(1, 10); -- 10 1493 INSERT INTO t1(a, b) VALUES(2, 15); -- 25 1494 INSERT INTO t1(a, b) VALUES(3, -5); -- 20 1495 INSERT INTO t1(a, b) VALUES(4, -5); -- 15 1496 INSERT INTO t1(a, b) VALUES(5, 20); -- 35 1497 INSERT INTO t1(a, b) VALUES(6, -11); -- 24 1498} 1499 1500do_execsql_test 43.2.2 { 1501 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY 2 1502} { 1503 1 10 4 15 3 20 6 24 2 25 5 35 1504} 1505 1506do_execsql_test 43.2.3 { 1507 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc 1508} { 1509 1 10 4 15 3 20 6 24 2 25 5 35 1510} 1511 1512do_execsql_test 43.2.4 { 1513 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc+5 1514} { 1515 1 10 4 15 3 20 6 24 2 25 5 35 1516} 1517 1518do_catchsql_test 43.2.5 { 1519 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc) 1520} {1 {misuse of aliased window function abc}} 1521 1522do_catchsql_test 43.2.6 { 1523 SELECT a, 1+sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc) 1524} {1 {misuse of aliased window function abc}} 1525 1526#------------------------------------------------------------------------- 1527reset_db 1528do_execsql_test 44.1 { 1529 CREATE TABLE t0(c0); 1530} 1531 1532do_catchsql_test 44.2.1 { 1533 SELECT ntile(0) OVER (); 1534} {1 {argument of ntile must be a positive integer}} 1535do_catchsql_test 44.2.2 { 1536 SELECT (0, 0) IN(SELECT MIN(c0), NTILE(0) OVER()) FROM t0; 1537} {1 {argument of ntile must be a positive integer}} 1538 1539do_execsql_test 44.3.1 { 1540 SELECT ntile(1) OVER (); 1541} {1} 1542do_execsql_test 44.3.2 { 1543 SELECT (0, 0) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0; 1544} {0} 1545 1546do_execsql_test 44.4.2 { 1547 INSERT INTO t0 VALUES(2), (1), (0); 1548 SELECT (0, 1) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0; 1549} {1} 1550 1551#------------------------------------------------------------------------- 1552reset_db 1553do_execsql_test 45.1 { 1554 CREATE TABLE t0(x); 1555 CREATE TABLE t1(a); 1556 INSERT INTO t1 VALUES(1000); 1557 INSERT INTO t1 VALUES(1000); 1558 INSERT INTO t0 VALUES(10000); 1559} 1560do_execsql_test 45.2 { 1561 SELECT * FROM ( 1562 SELECT sum (a) OVER() FROM t1 UNION ALL SELECT x FROM t0 1563 ); 1564} {2000 2000 10000} 1565 1566#------------------------------------------------------------------------- 1567reset_db 1568do_execsql_test 46.1 { 1569 CREATE TABLE t1 (a); 1570 CREATE INDEX i1 ON t1(a); 1571 1572 INSERT INTO t1 VALUES (10); 1573} 1574 1575do_execsql_test 46.2 { 1576 SELECT (SELECT sum(a) OVER(ORDER BY a)) FROM t1 1577} 10 1578 1579do_execsql_test 46.3 { 1580 SELECT * FROM t1 WHERE (SELECT sum(a) OVER(ORDER BY a)); 1581} 10 1582 1583do_execsql_test 46.4 { 1584 SELECT * FROM t1 NATURAL JOIN t1 1585 WHERE a=1 1586 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10) 1587} 10 1588 1589#------------------------------------------------------------------------- 1590reset_db 1591do_execsql_test 47.0 { 1592 CREATE TABLE t1( 1593 a, 1594 e, 1595 f, 1596 g UNIQUE, 1597 h UNIQUE 1598 ); 1599} 1600 1601do_execsql_test 47.1 { 1602 CREATE VIEW t2(k) AS 1603 SELECT e FROM t1 WHERE g = 'abc' OR h BETWEEN 10 AND f; 1604} 1605 1606do_catchsql_test 47.2 { 1607 SELECT 234 FROM t2 1608 WHERE k=1 1609 OR (SELECT k FROM t2 WHERE (SELECT sum(a) OVER() FROM t1 GROUP BY 1)); 1610} {1 {misuse of window function sum()}} 1611 1612#------------------------------------------------------------------------- 1613reset_db 1614do_execsql_test 48.0 { 1615 CREATE TABLE t1(a); 1616 INSERT INTO t1 VALUES(1); 1617 INSERT INTO t1 VALUES(2); 1618 INSERT INTO t1 VALUES(3); 1619 SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x)) 1620 FROM (SELECT (SELECT sum(a) FROM t1) AS x FROM t1); 1621} {12 12 12} 1622 1623do_execsql_test 48.1 { 1624 SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x)) 1625 FROM (SELECT (SELECT sum(a) FROM t1 GROUP BY a) AS x FROM t1); 1626} {2 2 2} 1627 1628#------------------------------------------------------------------------- 1629reset_db 1630do_execsql_test 49.1 { 1631 CREATE TABLE t1 (a PRIMARY KEY); 1632 INSERT INTO t1 VALUES(1); 1633} 1634 1635do_execsql_test 49.2 { 1636 SELECT b AS c FROM ( 1637 SELECT a AS b FROM ( 1638 SELECT a FROM t1 WHERE a=1 OR (SELECT sum(a) OVER ()) 1639 ) 1640 WHERE b=1 OR b<10 1641 ) 1642 WHERE c=1 OR c>=10; 1643} {1} 1644 1645 1646#------------------------------------------------------------------------- 1647reset_db 1648do_execsql_test 50.0 { 1649 CREATE TABLE t1 (a DOUBLE PRIMARY KEY); 1650 INSERT INTO t1 VALUES(10.0); 1651} 1652 1653do_execsql_test 50.1 { 1654 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) 1655} {10.0} 1656 1657do_execsql_test 50.2 { 1658 SELECT * FROM ( 1659 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) 1660 ) 1661 WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 ) 1662} {10.0} 1663 1664do_execsql_test 50.3 { 1665 SELECT a FROM ( 1666 SELECT * FROM ( 1667 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) 1668 ) 1669 WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 ) 1670 ) 1671 WHERE a=1 OR a=10.0 1672} {10.0} 1673 1674do_execsql_test 50.4 { 1675 SELECT a FROM ( 1676 SELECT * FROM ( 1677 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2)) 1678 ) 1679 WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 ) 1680 ) 1681 WHERE a=1 OR ((SELECT sum(a) OVER(ORDER BY a%8)) AND 10<=a) 1682} {10.0} 1683 1684do_execsql_test 50.5 { 1685SELECT * 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); 1686} {10.0} 1687 1688# 2020-04-03 ticket af4556bb5c285c08 1689# 1690reset_db 1691do_catchsql_test 51.1 { 1692 CREATE TABLE a(b, c); 1693 SELECT c FROM a GROUP BY c 1694 HAVING(SELECT(sum(b) OVER(ORDER BY b), 1695 sum(b) OVER(PARTITION BY min(DISTINCT c), c ORDER BY b))); 1696} {1 {row value misused}} 1697 1698#------------------------------------------------------------------------- 1699reset_db 1700do_execsql_test 52.1 { 1701 CREATE TABLE t1(a, b, c); 1702 INSERT INTO t1 VALUES('AA','bb',356); 1703 INSERT INTO t1 VALUES('CC','aa',158); 1704 INSERT INTO t1 VALUES('BB','aa',399); 1705 INSERT INTO t1 VALUES('FF','bb',938); 1706} 1707 1708do_execsql_test 52.2 { 1709 SELECT 1710 count() OVER win1, 1711 sum(c) OVER win2, 1712 first_value(c) OVER win2, 1713 count(a) OVER (ORDER BY b) 1714 FROM t1 1715 WINDOW 1716 win1 AS (ORDER BY a), 1717 win2 AS (PARTITION BY 6 ORDER BY a 1718 RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING ); 1719} { 1720 1 356 356 4 1721 2 399 399 2 1722 3 158 158 2 1723 4 938 938 4 1724} 1725 1726do_execsql_test 52.3 { 1727SELECT 1728 count() OVER (), 1729 sum(c) OVER win2, 1730 first_value(c) OVER win2, 1731 count(a) OVER (ORDER BY b) 1732FROM t1 1733WINDOW 1734 win1 AS (ORDER BY a), 1735 win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a 1736 RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING ); 1737} { 1738 4 356 356 4 1739 4 399 399 2 1740 4 158 158 2 1741 4 938 938 4 1742} 1743 1744do_execsql_test 52.4 { 1745 SELECT 1746 count() OVER win1, 1747 sum(c) OVER win2, 1748 first_value(c) OVER win2, 1749 count(a) OVER (ORDER BY b) 1750 FROM t1 1751 WINDOW 1752 win1 AS (ORDER BY a), 1753 win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a 1754 RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING ); 1755} { 1756 1 356 356 4 1757 2 399 399 2 1758 3 158 158 2 1759 4 938 938 4 1760} 1761 1762# 2020-05-23 1763# ticket 7a5279a25c57adf1 1764# 1765reset_db 1766do_execsql_test 53.0 { 1767 CREATE TABLE a(c UNIQUE); 1768 INSERT INTO a VALUES(4),(0),(9),(-9); 1769 SELECT a.c 1770 FROM a 1771 JOIN a AS b ON a.c=4 1772 JOIN a AS e ON a.c=e.c 1773 WHERE a.c=(SELECT (SELECT coalesce(lead(2) OVER(),0) + sum(d.c)) 1774 FROM a AS d 1775 WHERE a.c); 1776} {4 4 4 4} 1777 1778#------------------------------------------------------------------------- 1779reset_db 1780do_execsql_test 54.1 { 1781 CREATE TABLE t1(a VARCHAR(20), b FLOAT); 1782 INSERT INTO t1 VALUES('1',10.0); 1783} 1784 1785do_catchsql_test 54.2 { 1786 SELECT * FROM ( 1787 SELECT sum(b) OVER() AS c FROM t1 1788 UNION 1789 SELECT b AS c FROM t1 1790 ) WHERE c>10; 1791} {0 {}} 1792 1793do_execsql_test 54.3 { 1794 INSERT INTO t1 VALUES('2',5.0); 1795 INSERT INTO t1 VALUES('3',15.0); 1796} 1797 1798do_catchsql_test 54.4 { 1799 SELECT * FROM ( 1800 SELECT sum(b) OVER() AS c FROM t1 1801 UNION 1802 SELECT b AS c FROM t1 1803 ) WHERE c>10; 1804} {0 {15.0 30.0}} 1805 1806# 2020-06-05 ticket c8d3b9f0a750a529 1807reset_db 1808do_execsql_test 55.1 { 1809 CREATE TABLE a(b); 1810 SELECT 1811 (SELECT b FROM a 1812 GROUP BY b 1813 HAVING (SELECT COUNT()OVER() + lead(b)OVER(ORDER BY SUM(DISTINCT b) + b)) 1814 ) 1815 FROM a 1816 UNION 1817 SELECT 99 1818 ORDER BY 1; 1819} {99} 1820 1821#------------------------------------------------------------------------ 1822reset_db 1823do_execsql_test 56.1 { 1824 CREATE TABLE t1(a, b INTEGER); 1825 CREATE TABLE t2(c, d); 1826} 1827do_catchsql_test 56.2 { 1828 SELECT avg(b) FROM t1 1829 UNION ALL 1830 SELECT min(c) OVER () FROM t2 1831 ORDER BY nosuchcolumn; 1832} {1 {1st ORDER BY term does not match any column in the result set}} 1833 1834reset_db 1835do_execsql_test 57.1 { 1836 CREATE TABLE t4(a, b, c, d, e); 1837} 1838 1839do_catchsql_test 57.2 { 1840 SELECT b FROM t4 1841 UNION 1842 SELECT a FROM t4 1843 ORDER BY ( 1844 SELECT sum(x) OVER() FROM ( 1845 SELECT c AS x FROM t4 1846 UNION 1847 SELECT d FROM t4 1848 ORDER BY (SELECT e FROM t4) 1849 ) 1850 ); 1851} {1 {1st ORDER BY term does not match any column in the result set}} 1852 1853# 2020-06-06 various dbsqlfuzz finds and 1854# ticket 0899cf62f597d7e7 1855# 1856reset_db 1857do_execsql_test 57.1 { 1858 CREATE TABLE t1(a, b, c); 1859 INSERT INTO t1 VALUES(NULL,NULL,NULL); 1860 SELECT 1861 sum(a), 1862 min(b) OVER (), 1863 count(c) OVER (ORDER BY b) 1864 FROM t1; 1865} {{} {} 0} 1866do_execsql_test 57.2 { 1867 CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ; 1868 INSERT INTO v0 VALUES ( 10 ) ; 1869 SELECT DISTINCT v1, lead(v1) OVER() FROM v0 GROUP BY v1 ORDER BY 2; 1870} {10 {}} 1871do_catchsql_test 57.3 { 1872 DROP TABLE t1; 1873 CREATE TABLE t1(a); 1874 INSERT INTO t1(a) VALUES(22); 1875 CREATE TABLE t3(y); 1876 INSERT INTO t3(y) VALUES(5),(11),(-9); 1877 SELECT ( 1878 SELECT max(y) OVER( ORDER BY (SELECT x FROM (SELECT sum(y) AS x FROM t1))) 1879 ) 1880 FROM t3; 1881} {1 {misuse of aggregate: sum()}} 1882 1883# 2020-06-06 ticket 1f6f353b684fc708 1884reset_db 1885do_execsql_test 58.1 { 1886 CREATE TABLE a(a, b, c); 1887 INSERT INTO a VALUES(1, 2, 3); 1888 INSERT INTO a VALUES(4, 5, 6); 1889 SELECT sum(345+b) OVER (ORDER BY b), 1890 sum(avg(678)) OVER (ORDER BY c) FROM a; 1891} {347 678.0} 1892 1893# 2020-06-06 ticket e5504e987e419fb0 1894do_catchsql_test 59.1 { 1895 DROP TABLE IF EXISTS t1; 1896 CREATE TABLE t1(x INTEGER PRIMARY KEY); 1897 INSERT INTO t1 VALUES (123); 1898 SELECT 1899 ntile( (SELECT sum(x)) ) OVER(ORDER BY x), 1900 min(x) OVER(ORDER BY x) 1901 FROM t1; 1902} {1 {misuse of aggregate: sum()}} 1903 1904# 2020-06-07 ticket f7d890858f361402 1905do_execsql_test 60.1 { 1906 DROP TABLE IF EXISTS t1; 1907 CREATE TABLE t1 (x INTEGER PRIMARY KEY); 1908 INSERT INTO t1 VALUES (99); 1909 SELECT EXISTS(SELECT count(*) OVER() FROM t1 ORDER BY sum(x) OVER()); 1910} {1} 1911 1912# 2020-06-07 test case generated by dbsqlfuzz showing how an AggInfo 1913# object might be referenced after the sqlite3Select() call that created 1914# it returns. This proves the need to persist all AggInfo objects until 1915# the Parse object is destroyed. 1916# 1917reset_db 1918do_catchsql_test 61.1 { 1919CREATE TABLE t1(a); 1920INSERT INTO t1 VALUES(5),(NULL),('seventeen'); 1921SELECT (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); 1922} {1 {misuse of aggregate: sum()}} 1923 1924#------------------------------------------------------------------------- 1925reset_db 1926do_execsql_test 62.1 { 1927 CREATE TABLE t1(a VARCHAR(20), b FLOAT); 1928 INSERT INTO t1 VALUES('1',10.0); 1929} 1930 1931do_execsql_test 62.2 { 1932 SELECT * FROM ( 1933 SELECT sum(b) OVER() AS c FROM t1 1934 UNION 1935 SELECT b AS c FROM t1 1936 ) WHERE c>10; 1937} 1938 1939do_execsql_test 62.3 { 1940 INSERT INTO t1 VALUES('2',5.0); 1941 INSERT INTO t1 VALUES('3',15.0); 1942} 1943 1944do_execsql_test 62.4 { 1945 SELECT * FROM ( 1946 SELECT sum(b) OVER() AS c FROM t1 1947 UNION 1948 SELECT b AS c FROM t1 1949 ) WHERE c>10; 1950} {15.0 30.0} 1951 1952#------------------------------------------------------------------------- 1953reset_db 1954do_execsql_test 63.1 { 1955 CREATE TABLE t1(b, x); 1956 CREATE TABLE t2(c, d); 1957 CREATE TABLE t3(e, f); 1958} 1959 1960do_execsql_test 63.2 { 1961 SELECT max(b) OVER( 1962 ORDER BY SUM( 1963 (SELECT c FROM t2 UNION SELECT x ORDER BY c) 1964 ) 1965 ) FROM t1; 1966} {{}} 1967 1968do_execsql_test 63.3 { 1969 SELECT sum(b) over( 1970 ORDER BY ( 1971 SELECT max(b) OVER( 1972 ORDER BY sum( 1973 (SELECT x AS c UNION SELECT 1234 ORDER BY c) 1974 ) 1975 ) AS e 1976 ORDER BY e 1977 ) 1978 ) 1979 FROM t1; 1980} {{}} 1981 1982#------------------------------------------------------------------------- 1983reset_db 1984do_execsql_test 64.1 { 1985 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 1986 INSERT INTO t1 VALUES(1, 'abcd'); 1987 INSERT INTO t1 VALUES(2, 'BCDE'); 1988 INSERT INTO t1 VALUES(3, 'cdef'); 1989 INSERT INTO t1 VALUES(4, 'DEFG'); 1990} 1991 1992do_execsql_test 64.2 { 1993 SELECT rowid, max(b COLLATE nocase)||'' 1994 FROM t1 1995 GROUP BY rowid 1996 ORDER BY max(b COLLATE nocase)||''; 1997} {1 abcd 2 BCDE 3 cdef 4 DEFG} 1998 1999do_execsql_test 64.3 { 2000 SELECT count() OVER (), rowid, max(b COLLATE nocase)||'' 2001 FROM t1 2002 GROUP BY rowid 2003 ORDER BY max(b COLLATE nocase)||''; 2004} {4 1 abcd 4 2 BCDE 4 3 cdef 4 4 DEFG} 2005 2006do_execsql_test 64.4 { 2007 SELECT count() OVER (), rowid, max(b COLLATE nocase) 2008 FROM t1 2009 GROUP BY rowid 2010 ORDER BY max(b COLLATE nocase); 2011} {4 1 abcd 4 2 BCDE 4 3 cdef 4 4 DEFG} 2012 2013#------------------------------------------------------------------------- 2014reset_db 2015do_execsql_test 65.1 { 2016 CREATE TABLE t1(c1); 2017 INSERT INTO t1 VALUES('abcd'); 2018} 2019do_execsql_test 65.2 { 2020 SELECT max(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1; 2021} {1} 2022 2023do_execsql_test 65.3 { 2024 SELECT 2025 count() OVER (), 2026 group_concat(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1; 2027} {1 1} 2028 2029do_execsql_test 65.4 { 2030 SELECT COUNT() OVER () LIKE lead(102030) OVER( 2031 ORDER BY sum('abcdef' COLLATE nocase) IN (SELECT 54321) 2032 ) 2033 FROM t1; 2034} {{}} 2035 2036#------------------------------------------------------------------------- 2037reset_db 2038 2039do_execsql_test 66.1 { 2040 CREATE TABLE t1(a INTEGER); 2041 INSERT INTO t1 VALUES(3578824042033200656); 2042 INSERT INTO t1 VALUES(3029012920382354029); 2043} 2044 2045foreach {tn spec} { 2046 1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" 2047 2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING" 2048 3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING" 2049 4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" 2050 5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" 2051 6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING" 2052} { 2053 do_execsql_test 66.2.$tn " 2054 SELECT total(a) OVER ( $spec ) FROM t1 ORDER BY a 2055 " { 2056 3.02901292038235e+18 3.5788240420332e+18 2057 } 2058} 2059 2060 2061do_execsql_test 66.3 { 2062 CREATE TABLE t2(a INTEGER); 2063 INSERT INTO t2 VALUES(45); 2064 INSERT INTO t2 VALUES(30); 2065} 2066 2067foreach {tn spec res} { 2068 1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0} 2069 2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING" {0.0 0.0} 2070 3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING" {0.0 0.0} 2071 4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0} 2072 5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0} 2073 6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING" {0.0 0.0} 2074} { 2075 do_execsql_test 66.2.$tn " 2076 SELECT total(a) OVER ( $spec ) FROM t2 ORDER BY a 2077 " $res 2078} 2079 2080 2081#------------------------------------------------------------------------- 2082reset_db 2083do_execsql_test 67.0 { 2084 CREATE TABLE t1(a, b, c); 2085 CREATE TABLE t2(a, b, c); 2086} 2087 2088do_catchsql_test 67.1 { 2089 SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY ( 2090 SELECT nth_value(a,2) OVER w1 2091 WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM v1)) ) 2092 ) 2093} {1 {1st ORDER BY term does not match any column in the result set}} 2094 2095# 2021-05-07 2096# Do not allow aggregate functions in the ORDER BY clause even if 2097# there are window functions in the result set. 2098# Forum: /forumpost/540fdfef77 2099# 2100reset_db 2101do_catchsql_test 68.0 { 2102 CREATE TABLE t1(a,b); 2103 INSERT INTO t1(a,b) VALUES(0,0),(1,1),(2,4),(3,9),(4,99); 2104 SELECT rowid, a, b, sum(a)OVER() FROM t1 ORDER BY count(b); 2105} {1 {misuse of aggregate: count()}} 2106 2107finish_test 2108