# 2018 May 8 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix window1 ifcapable !windowfunc { finish_test return } do_execsql_test 1.0 { CREATE TABLE t1(a, b, c, d); INSERT INTO t1 VALUES(1, 2, 3, 4); INSERT INTO t1 VALUES(5, 6, 7, 8); INSERT INTO t1 VALUES(9, 10, 11, 12); } do_execsql_test 1.1 { SELECT sum(b) OVER () FROM t1 } {18 18 18} do_execsql_test 1.2 { SELECT a, sum(b) OVER () FROM t1 } {1 18 5 18 9 18} do_execsql_test 1.3 { SELECT a, 4 + sum(b) OVER () FROM t1 } {1 22 5 22 9 22} do_execsql_test 1.4 { SELECT a + 4 + sum(b) OVER () FROM t1 } {23 27 31} do_execsql_test 1.5 { SELECT a, sum(b) OVER (PARTITION BY c) FROM t1 } {1 2 5 6 9 10} foreach {tn sql} { 1 "SELECT sum(b) OVER () FROM t1" 2 "SELECT sum(b) OVER (PARTITION BY c) FROM t1" 3 "SELECT sum(b) OVER (ORDER BY c) FROM t1" 4 "SELECT sum(b) OVER (PARTITION BY d ORDER BY c) FROM t1" 5 "SELECT sum(b) FILTER (WHERE a>0) OVER (PARTITION BY d ORDER BY c) FROM t1" 6 "SELECT sum(b) OVER (ORDER BY c RANGE UNBOUNDED PRECEDING) FROM t1" 7 "SELECT sum(b) OVER (ORDER BY c ROWS 45 PRECEDING) FROM t1" 8 "SELECT sum(b) OVER (ORDER BY c RANGE CURRENT ROW) FROM t1" 9 "SELECT sum(b) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1" 10 "SELECT sum(b) OVER (ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t1" } { do_test 2.$tn { lindex [catchsql $sql] 0 } 0 } foreach {tn sql} { 1 "SELECT * FROM t1 WHERE sum(b) OVER ()" 2 "SELECT * FROM t1 GROUP BY sum(b) OVER ()" 3 "SELECT * FROM t1 GROUP BY a HAVING sum(b) OVER ()" } { do_catchsql_test 3.$tn $sql {1 {misuse of window function sum()}} } do_execsql_test 4.0 { CREATE TABLE t2(a, b, c); INSERT INTO t2 VALUES(0, 0, 0); INSERT INTO t2 VALUES(1, 1, 1); INSERT INTO t2 VALUES(2, 0, 2); INSERT INTO t2 VALUES(3, 1, 0); INSERT INTO t2 VALUES(4, 0, 1); INSERT INTO t2 VALUES(5, 1, 2); INSERT INTO t2 VALUES(6, 0, 0); } do_execsql_test 4.1 { SELECT a, sum(a) OVER (PARTITION BY b) FROM t2; } { 0 12 2 12 4 12 6 12 1 9 3 9 5 9 } do_execsql_test 4.2 { SELECT a, sum(a) OVER (PARTITION BY b) FROM t2 ORDER BY a; } { 0 12 1 9 2 12 3 9 4 12 5 9 6 12 } do_execsql_test 4.3 { SELECT a, sum(a) OVER () FROM t2 ORDER BY a; } { 0 21 1 21 2 21 3 21 4 21 5 21 6 21 } do_execsql_test 4.4 { SELECT a, sum(a) OVER (ORDER BY a) FROM t2; } { 0 0 1 1 2 3 3 6 4 10 5 15 6 21 } do_execsql_test 4.5 { SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a) FROM t2 ORDER BY a } { 0 0 1 1 2 2 3 4 4 6 5 9 6 12 } do_execsql_test 4.6 { SELECT a, sum(a) OVER (PARTITION BY c ORDER BY a) FROM t2 ORDER BY a } { 0 0 1 1 2 2 3 3 4 5 5 7 6 9 } do_execsql_test 4.7 { SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a DESC) FROM t2 ORDER BY a } { 0 12 1 9 2 12 3 8 4 10 5 5 6 6 } do_execsql_test 4.8 { SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a DESC), sum(a) OVER (PARTITION BY c ORDER BY a) FROM t2 ORDER BY a } { 0 12 0 1 9 1 2 12 2 3 8 3 4 10 5 5 5 7 6 6 9 } do_execsql_test 4.9 { SELECT a, sum(a) OVER (ORDER BY a), avg(a) OVER (ORDER BY a) FROM t2 ORDER BY a } { 0 0 0.0 1 1 0.5 2 3 1.0 3 6 1.5 4 10 2.0 5 15 2.5 6 21 3.0 } do_execsql_test 4.10.1 { SELECT a, count() OVER (ORDER BY a DESC), group_concat(a, '.') OVER (ORDER BY a DESC) FROM t2 ORDER BY a DESC } { 6 1 6 5 2 6.5 4 3 6.5.4 3 4 6.5.4.3 2 5 6.5.4.3.2 1 6 6.5.4.3.2.1 0 7 6.5.4.3.2.1.0 } do_execsql_test 4.10.2 { SELECT a, count(*) OVER (ORDER BY a DESC), group_concat(a, '.') OVER (ORDER BY a DESC) FROM t2 ORDER BY a DESC } { 6 1 6 5 2 6.5 4 3 6.5.4 3 4 6.5.4.3 2 5 6.5.4.3.2 1 6 6.5.4.3.2.1 0 7 6.5.4.3.2.1.0 } do_catchsql_test 5.1 { SELECT ntile(0) OVER (ORDER BY a) FROM t2; } {1 {argument of ntile must be a positive integer}} do_catchsql_test 5.2 { SELECT ntile(-1) OVER (ORDER BY a) FROM t2; } {1 {argument of ntile must be a positive integer}} do_catchsql_test 5.3 { SELECT ntile('zbc') OVER (ORDER BY a) FROM t2; } {1 {argument of ntile must be a positive integer}} do_execsql_test 5.4 { CREATE TABLE t4(a, b); SELECT ntile(1) OVER (ORDER BY a) FROM t4; } {} #------------------------------------------------------------------------- reset_db do_execsql_test 6.1 { CREATE TABLE t1(x); INSERT INTO t1 VALUES(7), (6), (5), (4), (3), (2), (1); CREATE TABLE t2(x); INSERT INTO t2 VALUES('b'), ('a'); SELECT x, count(*) OVER (ORDER BY x) FROM t1; } {1 1 2 2 3 3 4 4 5 5 6 6 7 7} do_execsql_test 6.2 { SELECT * FROM t2, (SELECT x, count(*) OVER (ORDER BY x) FROM t1); } { b 1 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 b 7 7 a 1 1 a 2 2 a 3 3 a 4 4 a 5 5 a 6 6 a 7 7 } do_catchsql_test 6.3 { SELECT x, lag(x) FILTER (WHERE (x%2)=0) OVER w FROM t1 WINDOW w AS (ORDER BY x) } {1 {FILTER clause may only be used with aggregate window functions}} #------------------------------------------------------------------------- # Attempt to use a window function as an aggregate. And other errors. # reset_db do_execsql_test 7.0 { CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(3, 4); INSERT INTO t1 VALUES(5, 6); INSERT INTO t1 VALUES(7, 8); INSERT INTO t1 VALUES(9, 10); } do_catchsql_test 7.1.1 { SELECT nth_value(x, 1) FROM t1; } {1 {misuse of window function nth_value()}} do_catchsql_test 7.1.2 { SELECT * FROM t1 WHERE nth_value(x, 1) OVER (ORDER BY y); } {1 {misuse of window function nth_value()}} do_catchsql_test 7.1.3 { SELECT count(*) FROM t1 GROUP BY y HAVING nth_value(x, 1) OVER (ORDER BY y); } {1 {misuse of window function nth_value()}} do_catchsql_test 7.1.4 { SELECT count(*) FROM t1 GROUP BY nth_value(x, 1) OVER (ORDER BY y); } {1 {misuse of window function nth_value()}} do_catchsql_test 7.1.5 { SELECT count(*) FROM t1 LIMIT nth_value(x, 1) OVER (); } {1 {no such column: x}} do_catchsql_test 7.1.6 { SELECT trim(x) OVER (ORDER BY y) FROM t1; } {1 {trim() may not be used as a window function}} do_catchsql_test 7.1.7 { SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y); } {1 {no such window: abc}} do_catchsql_test 7.1.8 { SELECT row_number(x) OVER () FROM t1 } {1 {wrong number of arguments to function row_number()}} do_execsql_test 7.2 { SELECT lead(y) OVER win, lead(y, 2) OVER win, lead(y, 3, 'default') OVER win FROM t1 WINDOW win AS (ORDER BY x) } { 4 6 8 6 8 10 8 10 default 10 {} default {} {} default } do_execsql_test 7.3 { SELECT row_number() OVER (ORDER BY x) FROM t1 } {1 2 3 4 5} do_execsql_test 7.4 { SELECT row_number() OVER win, lead(x) OVER win FROM t1 WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) } {1 3 2 5 3 7 4 9 5 {}} #------------------------------------------------------------------------- # Attempt to use a window function in a view. # do_execsql_test 8.0 { CREATE TABLE t3(a, b, c); WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 ) INSERT INTO t3 SELECT i, i, i FROM s; CREATE VIEW v1 AS SELECT sum(b) OVER (ORDER BY c), min(b) OVER (ORDER BY c), max(b) OVER (ORDER BY c) FROM t3; CREATE VIEW v2 AS SELECT sum(b) OVER win, min(b) OVER win, max(b) OVER win FROM t3 WINDOW win AS (ORDER BY c); } do_execsql_test 8.1.1 { SELECT * FROM v1 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} do_execsql_test 8.1.2 { SELECT * FROM v2 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} db close sqlite3 db test.db do_execsql_test 8.2.1 { SELECT * FROM v1 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} do_execsql_test 8.2.2 { SELECT * FROM v2 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6} #------------------------------------------------------------------------- # Attempt to use a window function in a trigger. # do_execsql_test 9.0 { CREATE TABLE t4(x, y); INSERT INTO t4 VALUES(1, 'g'); INSERT INTO t4 VALUES(2, 'i'); INSERT INTO t4 VALUES(3, 'l'); INSERT INTO t4 VALUES(4, 'g'); INSERT INTO t4 VALUES(5, 'a'); CREATE TABLE t5(x, y, m); CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN DELETE FROM t5; INSERT INTO t5 SELECT x, y, max(y) OVER xyz FROM t4 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x); END; } do_execsql_test 9.1.1 { SELECT x, y, max(y) OVER xyz FROM t4 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1 } {1 g g 2 i i 3 l l 4 g i 5 a l} do_execsql_test 9.1.2 { INSERT INTO t4 VALUES(6, 'm'); SELECT x, y, max(y) OVER xyz FROM t4 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1 } {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} do_execsql_test 9.1.3 { SELECT * FROM t5 ORDER BY 1 } {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} do_execsql_test 9.2 { WITH aaa(x, y, z) AS ( SELECT x, y, max(y) OVER xyz FROM t4 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ) SELECT * FROM aaa ORDER BY 1; } {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m} do_execsql_test 9.3 { WITH aaa(x, y, z) AS ( SELECT x, y, max(y) OVER xyz FROM t4 WINDOW xyz AS (ORDER BY x) ) SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1; } {1 g g g 2 i i g 3 l l g 4 g l g 5 a l g 6 m m g} #------------------------------------------------------------------------- # do_execsql_test 10.0 { CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total); INSERT INTO sales VALUES ('Alice', 'North', 34), ('Frank', 'South', 22), ('Charles', 'North', 45), ('Darrell', 'South', 8), ('Grant', 'South', 23), ('Brad' , 'North', 22), ('Elizabeth', 'South', 99), ('Horace', 'East', 1); } # Best two salespeople from each region # do_execsql_test 10.1 { SELECT emp, region, total FROM ( SELECT emp, region, total, row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank FROM sales ) WHERE rank<=2 ORDER BY region, total DESC } { Horace East 1 Charles North 45 Alice North 34 Elizabeth South 99 Grant South 23 } do_execsql_test 10.2 { SELECT emp, region, sum(total) OVER win FROM sales WINDOW win AS (PARTITION BY region ORDER BY total) } { Horace East 1 Brad North 22 Alice North 56 Charles North 101 Darrell South 8 Frank South 30 Grant South 53 Elizabeth South 152 } do_execsql_test 10.3 { SELECT emp, region, sum(total) OVER win FROM sales WINDOW win AS (PARTITION BY region ORDER BY total) LIMIT 5 } { Horace East 1 Brad North 22 Alice North 56 Charles North 101 Darrell South 8 } do_execsql_test 10.4 { SELECT emp, region, sum(total) OVER win FROM sales WINDOW win AS (PARTITION BY region ORDER BY total) LIMIT 5 OFFSET 2 } { Alice North 56 Charles North 101 Darrell South 8 Frank South 30 Grant South 53 } do_execsql_test 10.5 { SELECT emp, region, sum(total) OVER win FROM sales WINDOW win AS ( PARTITION BY region ORDER BY total ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) } { Horace East 1 Brad North 101 Alice North 79 Charles North 45 Darrell South 152 Frank South 144 Grant South 122 Elizabeth South 99 } do_execsql_test 10.6 { SELECT emp, region, sum(total) OVER win FROM sales WINDOW win AS ( PARTITION BY region ORDER BY total ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) LIMIT 5 OFFSET 2 } { Alice North 79 Charles North 45 Darrell South 152 Frank South 144 Grant South 122 } do_execsql_test 10.7 { SELECT emp, region, ( SELECT sum(total) OVER ( ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) || outer.emp FROM sales ) FROM sales AS outer; } { Alice North 254Alice Frank South 254Frank Charles North 254Charles Darrell South 254Darrell Grant South 254Grant Brad North 254Brad Elizabeth South 254Elizabeth Horace East 254Horace } do_execsql_test 10.8 { SELECT emp, region, ( SELECT sum(total) FILTER (WHERE sales.emp!=outer.emp) OVER ( ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM sales ) FROM sales AS outer; } { Alice North 220 Frank South 232 Charles North 209 Darrell South 246 Grant South 231 Brad North 232 Elizabeth South 155 Horace East 253 } #------------------------------------------------------------------------- # Check that it is not possible to use a window function in a CREATE INDEX # statement. # do_execsql_test 11.0 { CREATE TABLE t6(a, b, c); } do_catchsql_test 11.1 { CREATE INDEX t6i ON t6(a) WHERE sum(b) OVER (); } {1 {misuse of window function sum()}} do_catchsql_test 11.2 { CREATE INDEX t6i ON t6(a) WHERE lead(b) OVER (); } {1 {misuse of window function lead()}} do_catchsql_test 11.3 { CREATE INDEX t6i ON t6(sum(b) OVER ()); } {1 {misuse of window function sum()}} do_catchsql_test 11.4 { CREATE INDEX t6i ON t6(lead(b) OVER ()); } {1 {misuse of window function lead()}} # 2018-09-17 ticket 510cde277783b5fb5de628393959849dff377eb3 # Endless loop on a query with window functions and a limit # do_execsql_test 12.100 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(id INT, b VARCHAR, c VARCHAR); INSERT INTO t1 VALUES(1, 'A', 'one'); INSERT INTO t1 VALUES(2, 'B', 'two'); INSERT INTO t1 VALUES(3, 'C', 'three'); INSERT INTO t1 VALUES(4, 'D', 'one'); INSERT INTO t1 VALUES(5, 'E', 'two'); SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x FROM t1 WHERE id>1 ORDER BY b LIMIT 1; } {2 B two} do_execsql_test 12.110 { INSERT INTO t1 VALUES(6, 'F', 'three'); INSERT INTO t1 VALUES(7, 'G', 'one'); SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x FROM t1 WHERE id>1 ORDER BY b LIMIT 2; } {2 B two 3 C three} #------------------------------------------------------------------------- do_execsql_test 13.1 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a int, b int); INSERT INTO t1 VALUES(1,11); INSERT INTO t1 VALUES(2,12); } do_execsql_test 13.2.1 { SELECT a, rank() OVER(ORDER BY b) FROM t1; SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; } { 1 1 2 2 2 1 1 2 } do_execsql_test 13.2.2 { SELECT a, rank() OVER(ORDER BY b) FROM t1 UNION ALL SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; } { 1 1 2 2 2 1 1 2 } do_execsql_test 13.3 { SELECT a, rank() OVER(ORDER BY b) FROM t1 UNION SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; } { 1 1 1 2 2 1 2 2 } do_execsql_test 13.4 { SELECT a, rank() OVER(ORDER BY b) FROM t1 EXCEPT SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; } { 1 1 2 2 } do_execsql_test 13.5 { SELECT a, rank() OVER(ORDER BY b) FROM t1 INTERSECT SELECT a, rank() OVER(ORDER BY b DESC) FROM t1; } {} # 2018-12-06 # https://www.sqlite.org/src/info/f09fcd17810f65f7 # Assertion fault when window functions are used. # # Root cause is the query flattener invoking sqlite3ExprDup() on # expressions that contain subqueries with window functions. The # sqlite3ExprDup() routine is not making correctly initializing # Select.pWin field of the subqueries. # sqlite3 db :memory: do_execsql_test 14.0 { SELECT * FROM( SELECT * FROM (SELECT 1 AS c) WHERE c IN ( SELECT (row_number() OVER()) FROM (VALUES (0)) ) ); } {1} do_execsql_test 14.1 { CREATE TABLE t1(x); INSERT INTO t1(x) VALUES(12345); CREATE TABLE t2(c); INSERT INTO t2(c) VALUES(1); SELECT y, y+1, y+2 FROM ( SELECT c IN ( SELECT (row_number() OVER()) FROM t1 ) AS y FROM t2 ); } {1 2 3} # 2018-12-31 # https://www.sqlite.org/src/info/d0866b26f83e9c55 # Window function in correlated subquery causes assertion fault # do_catchsql_test 15.0 { WITH t(id, parent) AS ( SELECT CAST(1 AS INT), CAST(NULL AS INT) UNION ALL SELECT 2, NULL UNION ALL SELECT 3, 1 UNION ALL SELECT 4, 1 UNION ALL SELECT 5, 2 UNION ALL SELECT 6, 2 ), q AS ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn FROM t WHERE parent IS NULL UNION ALL SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn FROM q JOIN t ON t.parent = q.id ) SELECT * FROM q; } {1 {cannot use window functions in recursive queries}} do_execsql_test 15.1 { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(x); INSERT INTO t1 VALUES('a'), ('b'), ('c'); CREATE TABLE t2(a, b); INSERT INTO t2 VALUES('X', 1), ('X', 2), ('Y', 2), ('Y', 3); SELECT x, ( SELECT sum(b) OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t2 WHERE b PRECEDING/FOLLOWING when there are string, blob # and NULL values in the dataset. # reset_db do_execsql_test 19.0 { CREATE TABLE t1(a, b); INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), ('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10); } do_execsql_test 19.1 { SELECT a, sum(b) OVER (ORDER BY a) FROM t1; } {1 1 2 3 3 6 4 10 5 15 a 21 b 28 c 36 d 45 e 55} do_execsql_test 19.2.1 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) FROM t1; } {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} do_execsql_test 19.2.2 { SELECT a, sum(b) OVER ( ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) FROM t1 ORDER BY a ASC; } {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} do_execsql_test 19.3.1 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING ) FROM t1; } {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} do_execsql_test 19.3.2 { SELECT a, sum(b) OVER ( ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING ) FROM t1 ORDER BY a ASC; } {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} reset_db do_execsql_test 20.0 { CREATE TABLE t1(a, b); INSERT INTO t1 VALUES (NULL, 100), (NULL, 100), (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), ('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10); } do_execsql_test 20.1 { SELECT a, sum(b) OVER (ORDER BY a) FROM t1; } { {} 200 {} 200 1 201 2 203 3 206 4 210 5 215 a 221 b 228 c 236 d 245 e 255 } do_execsql_test 20.2.1 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) FROM t1; } {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} do_execsql_test 20.2.2 { SELECT a, sum(b) OVER ( ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) FROM t1 ORDER BY a ASC; } {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10} do_execsql_test 20.3.1 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING ) FROM t1; } {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} do_execsql_test 20.3.2 { SELECT a, sum(b) OVER ( ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING ) FROM t1 ORDER BY a ASC; } {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10} #------------------------------------------------------------------------- do_execsql_test 21.0 { CREATE TABLE keyword_tab( current, exclude, filter, following, groups, no, others, over, partition, preceding, range, ties, unbounded, window ); } do_execsql_test 21.1 { SELECT current, exclude, filter, following, groups, no, others, over, partition, preceding, range, ties, unbounded, window FROM keyword_tab } #------------------------------------------------------------------------- foreach {tn expr err} { 1 4.5 0 2 NULL 1 3 0.0 0 4 0.1 0 5 -0.1 1 6 '' 1 7 '2.0' 0 8 '2.0x' 1 9 x'1234' 1 10 '1.2' 0 } { set res {0 1} if {$err} {set res {1 {frame starting offset must be a non-negative number}} } do_catchsql_test 22.$tn.1 " WITH a(x, y) AS ( VALUES(1, 2) ) SELECT sum(x) OVER ( ORDER BY y RANGE BETWEEN $expr PRECEDING AND UNBOUNDED FOLLOWING ) FROM a " $res set res {0 1} if {$err} {set res {1 {frame ending offset must be a non-negative number}} } do_catchsql_test 22.$tn.2 " WITH a(x, y) AS ( VALUES(1, 2) ) SELECT sum(x) OVER ( ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND $expr FOLLOWING ) FROM a " $res } #------------------------------------------------------------------------- reset_db do_execsql_test 23.0 { CREATE TABLE t5(a, b, c); CREATE INDEX t5ab ON t5(a, b); } proc do_ordercount_test {tn sql nOrderBy} { set plan [execsql "EXPLAIN QUERY PLAN $sql"] uplevel [list do_test $tn [list regexp -all ORDER $plan] $nOrderBy] } do_ordercount_test 23.1 { SELECT sum(c) OVER (ORDER BY a, b), sum(c) OVER (PARTITION BY a ORDER BY b) FROM t5 } 0 do_ordercount_test 23.2 { SELECT sum(c) OVER (ORDER BY b, a), sum(c) OVER (PARTITION BY b ORDER BY a) FROM t5 } 1 do_ordercount_test 23.3 { SELECT sum(c) OVER (ORDER BY b, a), sum(c) OVER (ORDER BY c, b) FROM t5 } 2 do_ordercount_test 23.4 { SELECT sum(c) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), sum(c) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), sum(c) OVER (ORDER BY b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t5 } 1 do_ordercount_test 23.5 { SELECT sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING), sum(c) OVER (ORDER BY b+1 RANGE UNBOUNDED PRECEDING), sum(c) OVER (ORDER BY b+1 GROUPS UNBOUNDED PRECEDING) FROM t5 } 1 do_ordercount_test 23.6 { SELECT sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING), sum(c) OVER (ORDER BY b+2 RANGE UNBOUNDED PRECEDING), sum(c) OVER (ORDER BY b+3 GROUPS UNBOUNDED PRECEDING) FROM t5 } 3 do_execsql_test 24.1 { SELECT sum(44) OVER () } {44} do_execsql_test 24.2 { SELECT lead(44) OVER () } {{}} #------------------------------------------------------------------------- # reset_db do_execsql_test 25.0 { CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ); CREATE TABLE t2 ( t2_id INTEGER PRIMARY KEY ); CREATE TABLE t3 ( t3_id INTEGER PRIMARY KEY ); INSERT INTO t1 VALUES(1), (3), (5); INSERT INTO t2 VALUES (3), (5); INSERT INTO t3 VALUES(10), (11), (12); } do_execsql_test 25.1 { SELECT t1.* FROM t1, t2 WHERE t1_id=t2_id AND t1_id IN ( SELECT t1_id + row_number() OVER ( ORDER BY t1_id ) FROM t3 ) } do_execsql_test 25.2 { SELECT t1.* FROM t1, t2 WHERE t1_id=t2_id AND t1_id IN ( SELECT row_number() OVER ( ORDER BY t1_id ) FROM t3 ) } {3} #------------------------------------------------------------------------- reset_db do_execsql_test 26.0 { CREATE TABLE t1(x); CREATE TABLE t2(c); } do_execsql_test 26.1 { SELECT ( SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) ) FROM t2 } {} do_execsql_test 26.2 { INSERT INTO t1 VALUES(1), (2), (3), (4); INSERT INTO t2 VALUES(2), (6), (8), (4); SELECT c, c IN ( SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) ) FROM t2 } {2 1 6 0 8 0 4 1} do_execsql_test 26.3 { DELETE FROM t1; DELETE FROM t2; INSERT INTO t2 VALUES(1), (2), (3), (4); INSERT INTO t1 VALUES(1), (1), (2), (3), (3), (3), (3), (4), (4); SELECT c, c IN ( SELECT row_number() OVER () FROM ( SELECT 1 FROM t1 WHERE x=c ) ) FROM t2 } {1 1 2 0 3 1 4 0} #------------------------------------------------------------------------- reset_db do_execsql_test 27.0 { CREATE TABLE t1(x); INSERT INTO t1 VALUES(NULL), (1), (2), (3), (4), (5); } do_execsql_test 27.1 { SELECT min(x) FROM t1; } {1} do_execsql_test 27.2 { SELECT min(x) OVER win FROM t1 WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) } {1 1 1 2 3 4} #------------------------------------------------------------------------- reset_db do_execsql_test 28.1.1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY); INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0); INSERT INTO t1 VALUES (13,'M', 'cc', NULL); } do_execsql_test 28.1.2 { SELECT group_concat(b,'') OVER w1 FROM t1 WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING) } { {} {} } do_execsql_test 28.2.1 { CREATE TABLE t2(a TEXT, b INTEGER); INSERT INTO t2 VALUES('A', NULL); INSERT INTO t2 VALUES('B', NULL); } do_execsql_test 28.2.1 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY); INSERT INTO t1 VALUES (10,'J', 'cc', NULL), (11,'K', 'cc', 'xyz'), (13,'M', 'cc', NULL); } do_execsql_test 28.2.2 { SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1 WINDOW w1 AS (ORDER BY d DESC RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING) ORDER BY c, d, a; } { 10 J cc NULL JM | 13 M cc NULL JM | 11 K cc 'xyz' K | } #------------------------------------------------------------------------- reset_db do_execsql_test 29.1 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY); INSERT INTO t1 VALUES (1, 'A', 'aa', 2.5), (2, 'B', 'bb', 3.75), (3, 'C', 'cc', 1.0), (4, 'D', 'cc', 8.25), (5, 'E', 'bb', 6.5), (6, 'F', 'aa', 6.5), (7, 'G', 'aa', 6.0), (8, 'H', 'bb', 9.0), (9, 'I', 'aa', 3.75), (10,'J', 'cc', NULL), (11,'K', 'cc', 'xyz'), (12,'L', 'cc', 'xyZ'), (13,'M', 'cc', NULL); } do_execsql_test 29.2 { SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1 WINDOW w1 AS (PARTITION BY c ORDER BY d DESC RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING) ORDER BY c, d, a; } { 1 A aa 2.5 FG | 9 I aa 3.75 F | 7 G aa 6 {} | 6 F aa 6.5 {} | 2 B bb 3.75 HE | 5 E bb 6.5 H | 8 H bb 9 {} | 10 J cc NULL JM | 13 M cc NULL JM | 3 C cc 1 {} | 4 D cc 8.25 {} | 12 L cc 'xyZ' L | 11 K cc 'xyz' K | } # 2019-07-18 # Check-in [7ef7b23cbb1b9ace] (which was itself a fix for ticket # https://www.sqlite.org/src/info/1be72aab9) introduced a new problem # if the LHS of a BETWEEN operator is a WINDOW function. The problem # was found by (the recently enhanced) dbsqlfuzz. # do_execsql_test 30.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a, b, c); INSERT INTO t1 VALUES('BB','aa',399); SELECT count () OVER win1 NOT BETWEEN 'a' AND 'mmm', count () OVER win3 FROM t1 WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW), win2 AS (PARTITION BY b ORDER BY a), win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING ); } {1 1} finish_test