aboutsummaryrefslogtreecommitdiff
path: root/testdata/tcl/windowB.test
diff options
context:
space:
mode:
Diffstat (limited to 'testdata/tcl/windowB.test')
-rw-r--r--testdata/tcl/windowB.test339
1 files changed, 339 insertions, 0 deletions
diff --git a/testdata/tcl/windowB.test b/testdata/tcl/windowB.test
new file mode 100644
index 0000000..321c481
--- /dev/null
+++ b/testdata/tcl/windowB.test
@@ -0,0 +1,339 @@
+# 2019-08-30
+#
+# 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.
+#
+#***********************************************************************
+# Test cases for RANGE BETWEEN and especially with NULLS LAST
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix windowB
+
+ifcapable !windowfunc {
+ finish_test
+ return
+}
+
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a, b);
+ INSERT INTO t1 VALUES(NULL, 1);
+ INSERT INTO t1 VALUES(NULL, 2);
+ INSERT INTO t1 VALUES(NULL, 3);
+} {}
+
+foreach {tn win} {
+ 1 { ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
+ 2 { ORDER BY a NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
+ 3 { ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
+ 4 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
+
+ 5 { ORDER BY a NULLS LAST RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING }
+ 6 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING }
+
+ 7 { ORDER BY a NULLS LAST RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING }
+ 8 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING }
+} {
+ do_execsql_test 1.$tn "
+ SELECT sum(b) OVER win FROM t1
+ WINDOW win AS ( $win )
+ " {6 6 6}
+}
+
+do_execsql_test 1.2 {
+ SELECT sum(b) OVER win FROM t1
+ WINDOW win AS (
+ ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
+ )
+} {6 6 6}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 2.0 {
+ CREATE TABLE t1(a, b);
+ INSERT INTO t1 VALUES(1, NULL);
+ INSERT INTO t1 VALUES(2, 45);
+ INSERT INTO t1 VALUES(3, 66.2);
+ INSERT INTO t1 VALUES(4, 'hello world');
+ INSERT INTO t1 VALUES(5, 'hello world');
+ INSERT INTO t1 VALUES(6, X'1234');
+ INSERT INTO t1 VALUES(7, X'1234');
+ INSERT INTO t1 VALUES(8, NULL);
+}
+
+foreach {tn win} {
+ 1 "ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING"
+ 2 "ORDER BY b RANGE BETWEEN 2 FOLLOWING AND 2 FOLLOWING"
+ 3 "ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING"
+ 4 "ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 2 FOLLOWING"
+} {
+ do_execsql_test 2.1.$tn "
+ SELECT a, sum(a) OVER win FROM t1
+ WINDOW win AS ( $win )
+ ORDER BY 1
+ " {1 9 2 {} 3 {} 4 9 5 9 6 13 7 13 8 9}
+}
+
+#-------------------------------------------------------------------------
+ifcapable json1 {
+ reset_db
+ do_execsql_test 3.0 {
+ CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT, x TEXT);
+ INSERT INTO testjson VALUES(1, '{"a":1}', 'a');
+ INSERT INTO testjson VALUES(2, '{"b":2}', 'b');
+ INSERT INTO testjson VALUES(3, '{"c":3}', 'c');
+ INSERT INTO testjson VALUES(4, '{"d":4}', 'd');
+ }
+
+ do_execsql_test 3.1 {
+ SELECT json_group_array(json(j)) FROM testjson;
+ } {
+ {[{"a":1},{"b":2},{"c":3},{"d":4}]}
+ }
+
+ do_execsql_test 3.2 {
+ SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson;
+ } {
+ {[{"a":1}]}
+ {[{"a":1},{"b":2}]}
+ {[{"a":1},{"b":2},{"c":3}]}
+ {[{"a":1},{"b":2},{"c":3},{"d":4}]}
+ }
+
+ do_execsql_test 3.3 {
+ SELECT json_group_array(json(j)) OVER (
+ ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE TIES
+ ) FROM testjson;
+ } {
+ {[{"a":1}]}
+ {[{"a":1},{"b":2}]}
+ {[{"a":1},{"b":2},{"c":3}]}
+ {[{"a":1},{"b":2},{"c":3},{"d":4}]}
+ }
+
+ do_execsql_test 3.4 {
+ SELECT json_group_array(json(j)) OVER (
+ ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
+ ) FROM testjson;
+ } {
+ {[{"a":1},{"b":2}]}
+ {[{"a":1},{"b":2},{"c":3}]}
+ {[{"b":2},{"c":3},{"d":4}]}
+ {[{"c":3},{"d":4}]}
+ }
+
+ do_execsql_test 3.5 {
+ SELECT json_group_array(json(j)) OVER (
+ ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
+ ) FROM testjson;
+ } {
+ {[]}
+ {[{"a":1}]}
+ {[{"a":1},{"b":2}]}
+ {[{"b":2},{"c":3}]}
+ }
+
+ do_execsql_test 3.5a {
+ UPDATE testjson SET j = replace(j,char(125),',"e":9'||char(125));
+ SELECT j FROM testjson;
+ } {
+ {{"a":1,"e":9}}
+ {{"b":2,"e":9}}
+ {{"c":3,"e":9}}
+ {{"d":4,"e":9}}
+ }
+ do_execsql_test 3.5b {
+ SELECT group_concat(x,'') OVER (
+ ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
+ ) FROM testjson ORDER BY id;
+ } {bc cd d {}}
+ do_execsql_test 3.5c {
+ SELECT json_group_array(json(j)) OVER (
+ ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
+ ) FROM testjson;
+ } {
+ {[{"b":2,"e":9},{"c":3,"e":9}]}
+ {[{"c":3,"e":9},{"d":4,"e":9}]}
+ {[{"d":4,"e":9}]}
+ {[]}
+ }
+ do_execsql_test 3.5d {
+ SELECT json_group_object(x,json(j)) OVER (
+ ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
+ ) FROM testjson;
+ } {
+ {{"b":{"b":2,"e":9},"c":{"c":3,"e":9}}}
+ {{"c":{"c":3,"e":9},"d":{"d":4,"e":9}}}
+ {{"d":{"d":4,"e":9}}}
+ {{}}
+ }
+
+ do_execsql_test 3.7b {
+ SELECT group_concat(x,'') FILTER (WHERE id!=2) OVER (
+ ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
+ ) FROM testjson;
+ } {{} a a c}
+
+ do_execsql_test 3.7c {
+ SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
+ ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
+ ) FROM testjson
+ } {
+ {[]}
+ {[{"a":1,"e":9}]}
+ {[{"a":1,"e":9}]}
+ {[{"c":3,"e":9}]}
+ }
+ do_execsql_test 3.7d {
+ SELECT json_group_object(x,json(j)) FILTER (WHERE id!=2) OVER (
+ ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
+ ) FROM testjson
+ } {
+ {{}}
+ {{"a":{"a":1,"e":9}}}
+ {{"a":{"a":1,"e":9}}}
+ {{"c":{"c":3,"e":9}}}
+ }
+}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 4.0 {
+ CREATE TABLE x(a);
+ INSERT INTO x VALUES(1);
+ INSERT INTO x VALUES(2);
+}
+
+do_execsql_test 4.1 {
+ WITH y AS (
+ SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a)
+ )
+ SELECT * FROM y;
+} {
+ 1 1
+}
+
+do_catchsql_test 4.2 {
+ WITH y AS (
+ SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION
+ BY fake_column))
+ SELECT * FROM y;
+} {1 {no such column: fake_column}}
+
+do_catchsql_test 4.3 {
+ SELECT 1 WINDOW win AS (PARTITION BY fake_column);
+} {0 1}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 5.0 {
+ CREATE TABLE t1(a, c);
+ CREATE INDEX i1 ON t1(a);
+
+ INSERT INTO t1 VALUES(0, 421);
+ INSERT INTO t1 VALUES(1, 844);
+ INSERT INTO t1 VALUES(2, 1001);
+}
+
+do_execsql_test 5.1 {
+ SELECT a, sum(c) OVER (
+ ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING
+ ) FROM t1;
+} {0 {} 1 {} 2 {}}
+
+do_execsql_test 5.2 {
+ INSERT INTO t1 VALUES(NULL, 123);
+ INSERT INTO t1 VALUES(NULL, 111);
+ INSERT INTO t1 VALUES('xyz', 222);
+ INSERT INTO t1 VALUES('xyz', 333);
+
+ SELECT a, sum(c) OVER (
+ ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING
+ ) FROM t1;
+} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
+
+do_execsql_test 5.3 {
+ SELECT a, sum(c) OVER (
+ ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
+ ) FROM t1;
+} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
+
+do_execsql_test 5.4 {
+ SELECT a, sum(c) OVER (
+ ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING EXCLUDE NO OTHERS
+ ) FROM t1;
+} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
+
+do_execsql_test 5.5 {
+ SELECT a, sum(c) OVER (
+ ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS
+ ) FROM t1;
+} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 6.0 {
+ CREATE TABLE t1(a, c);
+ CREATE INDEX i1 ON t1(a);
+
+ INSERT INTO t1 VALUES(7, 997);
+ INSERT INTO t1 VALUES(8, 997);
+ INSERT INTO t1 VALUES('abc', 1001);
+}
+do_execsql_test 6.1 {
+ SELECT a, sum(c) OVER (
+ ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
+ ) FROM t1;
+} {7 {} 8 {} abc 1001}
+do_execsql_test 6.2 {
+ SELECT a, sum(c) OVER (
+ ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS
+ ) FROM t1;
+} {7 {} 8 {} abc 1001}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 7.0 {
+ CREATE TABLE t1(a, c);
+ CREATE INDEX i1 ON t1(a);
+
+ INSERT INTO t1 VALUES(NULL, 46);
+ INSERT INTO t1 VALUES(NULL, 45);
+ INSERT INTO t1 VALUES(7, 997);
+ INSERT INTO t1 VALUES(7, 1000);
+ INSERT INTO t1 VALUES(8, 997);
+ INSERT INTO t1 VALUES(8, 1000);
+ INSERT INTO t1 VALUES('abc', 1001);
+ INSERT INTO t1 VALUES('abc', 1004);
+ INSERT INTO t1 VALUES('xyz', 3333);
+}
+
+do_execsql_test 7.1 {
+ SELECT a, max(c) OVER (
+ ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
+ ) FROM t1;
+} {{} 46 {} 46 7 {} 7 {} 8 {} 8 {} abc 1004 abc 1004 xyz 3333}
+do_execsql_test 7.2 {
+ SELECT a, min(c) OVER (
+ ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
+ ) FROM t1;
+} {{} 45 {} 45 7 {} 7 {} 8 {} 8 {} abc 1001 abc 1001 xyz 3333}
+
+do_execsql_test 7.3 {
+ SELECT a, max(c) OVER (
+ ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING
+ ) FROM t1;
+} {{} 46 {} 46 7 {} 7 {} 8 {} 8 {} abc 1004 abc 1004 xyz 3333}
+do_execsql_test 7.4 {
+ SELECT a, min(c) OVER (
+ ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING
+ ) FROM t1;
+} {{} 45 {} 45 7 {} 7 {} 8 {} 8 {} abc 1001 abc 1001 xyz 3333}
+
+finish_test
generated by cgit v1.2.3-70-g09d2 (git 2.49.0) at 2025-05-15 22:48:06 +0000