# 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_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<x
) FROM t1;
} {a 3 b 3 c 3}
do_execsql_test 15.2 {
SELECT(
WITH c AS(
VALUES(1)
) SELECT '' FROM c,c
) x WHERE x+x;
} {}
finish_test