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