aboutsummaryrefslogtreecommitdiff
path: root/test
diff options
context:
space:
mode:
Diffstat (limited to 'test')
-rw-r--r--test/window8.tcl64
-rw-r--r--test/window8.test253
2 files changed, 312 insertions, 5 deletions
diff --git a/test/window8.tcl b/test/window8.tcl
index aa934a826..a8333f190 100644
--- a/test/window8.tcl
+++ b/test/window8.tcl
@@ -351,15 +351,75 @@ execsql_test 7.0 {
INSERT INTO t2 VALUES(2, NULL);
INSERT INTO t2 VALUES(3, NULL);
INSERT INTO t2 VALUES(4, NULL);
+ INSERT INTO t2 VALUES(5, 66);
+ INSERT INTO t2 VALUES(6, 67);
}
-execsql_test 7.1 {
- SELECT sum(a) OVER win FROM t2
+foreach {tn f ex} {
+ 1 sum ""
+ 2 min ""
+ 3 sum "EXCLUDE CURRENT ROW"
+ 4 max "EXCLUDE CURRENT ROW"
+} {
+execsql_test 7.$tn.1 "
+ SELECT $f (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
);
+"
+execsql_test 7.$tn.2 "
+ SELECT $f (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
+ );
+"
+execsql_test 7.$tn.3 "
+ SELECT $f (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
+ );
+"
+execsql_test 7.$tn.4 "
+ SELECT $f (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
+ );
+"
+execsql_test 7.$tn.5 "
+ SELECT $f (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
+ );
+"
+
+execsql_test 7.$tn.6 "
+ SELECT $f (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
+ );
+"
+execsql_test 7.$tn.7 "
+ SELECT $f (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
+ );
+"
+execsql_test 7.$tn.8 "
+ SELECT $f (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
+ );
+"
+execsql_test 7.$tn.9 "
+ SELECT $f (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
+ );
+"
}
+
+
finish_test
diff --git a/test/window8.test b/test/window8.test
index 0c7656b9a..ed720ebd4 100644
--- a/test/window8.test
+++ b/test/window8.test
@@ -6213,13 +6213,260 @@ do_execsql_test 7.0 {
INSERT INTO t2 VALUES(2, NULL);
INSERT INTO t2 VALUES(3, NULL);
INSERT INTO t2 VALUES(4, NULL);
+ INSERT INTO t2 VALUES(5, 66);
+ INSERT INTO t2 VALUES(6, 67);
} {}
-do_execsql_test 7.1 {
- SELECT sum(a) OVER win FROM t2
+do_execsql_test 7.1.1 {
+ SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
);
-} {9 9 9 9}
+} {9 9 9 9 9 9}
+
+do_execsql_test 7.1.2 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
+ );
+} {{} {} {} 9 9 9}
+
+do_execsql_test 7.1.3 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
+ );
+} {{} {} {} 9 9 9}
+
+do_execsql_test 7.1.4 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
+ );
+} {9 9 9 {} {} {}}
+
+do_execsql_test 7.1.5 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
+ );
+} {9 9 9 {} {} {}}
+
+do_execsql_test 7.1.6 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
+ );
+} {{} {} 1 9 9 9}
+
+do_execsql_test 7.1.7 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
+ );
+} {{} {} {} 9 9 9}
+
+do_execsql_test 7.1.8 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
+ );
+} {9 9 9 {} {} {}}
+
+do_execsql_test 7.1.9 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
+ );
+} {9 9 9 {} {} {}}
+
+do_execsql_test 7.2.1 {
+ SELECT min (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
+ );
+} {2 2 2 2 2 2}
+
+do_execsql_test 7.2.2 {
+ SELECT min (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
+ );
+} {{} {} {} 2 2 2}
+
+do_execsql_test 7.2.3 {
+ SELECT min (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
+ );
+} {{} {} {} 2 2 2}
+
+do_execsql_test 7.2.4 {
+ SELECT min (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
+ );
+} {2 2 2 {} {} {}}
+
+do_execsql_test 7.2.5 {
+ SELECT min (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
+ );
+} {2 2 2 {} {} {}}
+
+do_execsql_test 7.2.6 {
+ SELECT min (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
+ );
+} {{} {} 1 2 2 2}
+
+do_execsql_test 7.2.7 {
+ SELECT min (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
+ );
+} {{} {} {} 2 2 2}
+
+do_execsql_test 7.2.8 {
+ SELECT min (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
+ );
+} {2 2 2 {} {} {}}
+
+do_execsql_test 7.2.9 {
+ SELECT min (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
+ );
+} {2 2 2 {} {} {}}
+
+do_execsql_test 7.3.1 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
+ );
+} {9 9 9 9 9 9}
+
+do_execsql_test 7.3.2 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
+ );
+} {{} {} {} 9 9 9}
+
+do_execsql_test 7.3.3 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
+ );
+} {{} {} {} 9 9 9}
+
+do_execsql_test 7.3.4 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
+ );
+} {9 9 9 {} {} {}}
+
+do_execsql_test 7.3.5 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
+ );
+} {9 9 9 {} {} {}}
+
+do_execsql_test 7.3.6 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
+ );
+} {{} {} 1 9 9 9}
+
+do_execsql_test 7.3.7 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
+ );
+} {{} {} {} 9 9 9}
+
+do_execsql_test 7.3.8 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
+ );
+} {9 9 9 {} {} {}}
+
+do_execsql_test 7.3.9 {
+ SELECT sum (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
+ );
+} {9 9 9 {} {} {}}
+
+do_execsql_test 7.4.1 {
+ SELECT max (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
+ );
+} {4 4 4 4 4 4}
+
+do_execsql_test 7.4.2 {
+ SELECT max (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
+ );
+} {{} {} {} 4 4 4}
+
+do_execsql_test 7.4.3 {
+ SELECT max (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
+ );
+} {{} {} {} 4 4 4}
+
+do_execsql_test 7.4.4 {
+ SELECT max (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
+ );
+} {4 4 4 {} {} {}}
+
+do_execsql_test 7.4.5 {
+ SELECT max (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
+ );
+} {4 4 4 {} {} {}}
+
+do_execsql_test 7.4.6 {
+ SELECT max (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
+ );
+} {{} {} 1 4 4 4}
+
+do_execsql_test 7.4.7 {
+ SELECT max (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
+ );
+} {{} {} {} 4 4 4}
+
+do_execsql_test 7.4.8 {
+ SELECT max (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
+ );
+} {4 4 4 {} {} {}}
+
+do_execsql_test 7.4.9 {
+ SELECT max (a) OVER win FROM t2
+ WINDOW win AS (
+ ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
+ );
+} {4 4 4 {} {} {}}
finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-18 11:21:28 +0000