diff options
Diffstat (limited to 'test')
| -rw-r--r-- | test/window1.test | 42 |
1 files changed, 42 insertions, 0 deletions
diff --git a/test/window1.test b/test/window1.test index 8daa97c1b..ad486f68f 100644 --- a/test/window1.test +++ b/test/window1.test @@ -269,6 +269,7 @@ do_execsql_test 7.3 { SELECT row_number() OVER (ORDER BY x) FROM t1 } {1 2 3 4 5} +breakpoint do_execsql_test 7.4 { SELECT row_number() OVER win, @@ -277,5 +278,46 @@ do_execsql_test 7.4 { 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} + + + finish_test |