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