summaryrefslogtreecommitdiffstats
path: root/test/windowA.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/windowA.test')
-rw-r--r--test/windowA.test309
1 files changed, 309 insertions, 0 deletions
diff --git a/test/windowA.test b/test/windowA.test
new file mode 100644
index 0000000..e94ae57
--- /dev/null
+++ b/test/windowA.test
@@ -0,0 +1,309 @@
+# 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 windowA
+
+ifcapable !windowfunc {
+ finish_test
+ return
+}
+
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), d FLOAT);
+ INSERT INTO t1 VALUES
+ (1, 'A', 5.4),
+ (2, 'B', 5.55),
+ (3, 'C', 8.0),
+ (4, 'D', 10.25),
+ (5, 'E', 10.26),
+ (6, 'N', NULL),
+ (7, 'N', NULL);
+} {}
+
+do_execsql_test 1.1 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS LAST
+ RANGE BETWEEN 2.50 PRECEDING AND 2.25 FOLLOWING)
+ ORDER BY +d DESC NULLS LAST, +a;
+} [list \
+ 5 E 10.26 ED \
+ 4 D 10.25 EDC \
+ 3 C 8.0 EDC \
+ 2 B 5.55 CBA \
+ 1 A 5.4 BA \
+ 6 N NULL NN \
+ 7 N NULL NN \
+]
+
+do_execsql_test 1.2 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS FIRST
+ RANGE BETWEEN 2.50 PRECEDING AND 2.25 FOLLOWING)
+ ORDER BY +d DESC NULLS FIRST, +a;
+} [list \
+ 6 N NULL NN \
+ 7 N NULL NN \
+ 5 E 10.26 ED \
+ 4 D 10.25 EDC \
+ 3 C 8.0 EDC \
+ 2 B 5.55 CBA \
+ 1 A 5.4 BA \
+]
+
+do_execsql_test 1.3 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS LAST
+ RANGE BETWEEN 2.50 PRECEDING AND UNBOUNDED FOLLOWING)
+ ORDER BY +d DESC NULLS LAST, +a;
+} [list \
+ 5 E 10.26 EDCBANN \
+ 4 D 10.25 EDCBANN \
+ 3 C 8.0 EDCBANN \
+ 2 B 5.55 CBANN \
+ 1 A 5.4 BANN \
+ 6 N NULL NN \
+ 7 N NULL NN \
+]
+
+do_execsql_test 1.4 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS FIRST
+ RANGE BETWEEN 2.50 PRECEDING AND UNBOUNDED FOLLOWING)
+ ORDER BY +d DESC NULLS FIRST, +a;
+} [list \
+ 6 N NULL NNEDCBA \
+ 7 N NULL NNEDCBA \
+ 5 E 10.26 EDCBA \
+ 4 D 10.25 EDCBA \
+ 3 C 8.0 EDCBA \
+ 2 B 5.55 CBA \
+ 1 A 5.4 BA \
+]
+
+do_execsql_test 1.5 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS LAST
+ RANGE BETWEEN 2.50 PRECEDING AND CURRENT ROW)
+ ORDER BY +d DESC NULLS LAST, +a;
+} [list \
+ 5 E 10.26 E \
+ 4 D 10.25 ED \
+ 3 C 8.0 EDC \
+ 2 B 5.55 CB \
+ 1 A 5.4 BA \
+ 6 N NULL NN \
+ 7 N NULL NN \
+]
+
+do_execsql_test 1.6 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS FIRST
+ RANGE BETWEEN 2.50 PRECEDING AND CURRENT ROW)
+ ORDER BY +d DESC NULLS FIRST, +a;
+} [list \
+ 6 N NULL NN \
+ 7 N NULL NN \
+ 5 E 10.26 E \
+ 4 D 10.25 ED \
+ 3 C 8.0 EDC \
+ 2 B 5.55 CB \
+ 1 A 5.4 BA \
+]
+
+do_execsql_test 2.1 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS LAST
+ RANGE BETWEEN UNBOUNDED PRECEDING AND 2.25 FOLLOWING)
+ ORDER BY +d DESC NULLS LAST, +a;
+} [list \
+ 5 E 10.26 ED \
+ 4 D 10.25 EDC \
+ 3 C 8.0 EDC \
+ 2 B 5.55 EDCBA \
+ 1 A 5.4 EDCBA \
+ 6 N NULL EDCBANN \
+ 7 N NULL EDCBANN \
+]
+
+do_execsql_test 2.2 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS FIRST
+ RANGE BETWEEN UNBOUNDED PRECEDING AND 2.25 FOLLOWING)
+ ORDER BY +d DESC NULLS FIRST, +a;
+} [list \
+ 6 N NULL NN \
+ 7 N NULL NN \
+ 5 E 10.26 NNED \
+ 4 D 10.25 NNEDC \
+ 3 C 8.0 NNEDC \
+ 2 B 5.55 NNEDCBA \
+ 1 A 5.4 NNEDCBA \
+]
+
+do_execsql_test 2.3 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS LAST
+ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ ORDER BY +d DESC NULLS LAST, +a;
+} [list \
+ 5 E 10.26 EDCBANN \
+ 4 D 10.25 EDCBANN \
+ 3 C 8.0 EDCBANN \
+ 2 B 5.55 EDCBANN \
+ 1 A 5.4 EDCBANN \
+ 6 N NULL EDCBANN \
+ 7 N NULL EDCBANN \
+]
+
+do_execsql_test 2.4 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS FIRST
+ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ ORDER BY +d DESC NULLS FIRST, +a;
+} [list \
+ 6 N NULL NNEDCBA \
+ 7 N NULL NNEDCBA \
+ 5 E 10.26 NNEDCBA \
+ 4 D 10.25 NNEDCBA \
+ 3 C 8.0 NNEDCBA \
+ 2 B 5.55 NNEDCBA \
+ 1 A 5.4 NNEDCBA \
+]
+
+do_execsql_test 2.5 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS LAST
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ ORDER BY +d DESC NULLS LAST, +a;
+} [list \
+ 5 E 10.26 E \
+ 4 D 10.25 ED \
+ 3 C 8.0 EDC \
+ 2 B 5.55 EDCB \
+ 1 A 5.4 EDCBA \
+ 6 N NULL EDCBANN \
+ 7 N NULL EDCBANN \
+]
+
+do_execsql_test 2.6 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS FIRST
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ ORDER BY +d DESC NULLS FIRST, +a;
+} [list \
+ 6 N NULL NN \
+ 7 N NULL NN \
+ 5 E 10.26 NNE \
+ 4 D 10.25 NNED \
+ 3 C 8.0 NNEDC \
+ 2 B 5.55 NNEDCB \
+ 1 A 5.4 NNEDCBA \
+]
+
+
+do_execsql_test 3.1 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS LAST
+ RANGE BETWEEN CURRENT ROW AND 2.25 FOLLOWING)
+ ORDER BY +d DESC NULLS LAST, +a;
+} [list \
+ 5 E 10.26 ED \
+ 4 D 10.25 DC \
+ 3 C 8.0 C \
+ 2 B 5.55 BA \
+ 1 A 5.4 A \
+ 6 N NULL NN \
+ 7 N NULL NN \
+]
+
+do_execsql_test 3.2 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS FIRST
+ RANGE BETWEEN CURRENT ROW AND 2.25 FOLLOWING)
+ ORDER BY +d DESC NULLS FIRST, +a;
+} [list \
+ 6 N NULL NN \
+ 7 N NULL NN \
+ 5 E 10.26 ED \
+ 4 D 10.25 DC \
+ 3 C 8.0 C \
+ 2 B 5.55 BA \
+ 1 A 5.4 A \
+]
+
+do_execsql_test 3.3 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS LAST
+ RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ ORDER BY +d DESC NULLS LAST, +a;
+} [list \
+ 5 E 10.26 EDCBANN \
+ 4 D 10.25 DCBANN \
+ 3 C 8.0 CBANN \
+ 2 B 5.55 BANN \
+ 1 A 5.4 ANN \
+ 6 N NULL NN \
+ 7 N NULL NN \
+]
+
+do_execsql_test 3.4 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS FIRST
+ RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ ORDER BY +d DESC NULLS FIRST, +a;
+} [list \
+ 6 N NULL NNEDCBA \
+ 7 N NULL NNEDCBA \
+ 5 E 10.26 EDCBA \
+ 4 D 10.25 DCBA \
+ 3 C 8.0 CBA \
+ 2 B 5.55 BA \
+ 1 A 5.4 A \
+]
+
+do_execsql_test 4.0 {
+ SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1
+ WINDOW w1 AS
+ (ORDER BY d DESC NULLS FIRST
+ RANGE BETWEEN 2.50 PRECEDING AND 0.5 PRECEDING)
+ ORDER BY +d DESC NULLS FIRST, +a;
+} [list \
+ 6 N NULL NN \
+ 7 N NULL NN \
+ 5 E 10.26 {} \
+ 4 D 10.25 {} \
+ 3 C 8.0 ED \
+ 2 B 5.55 C \
+ 1 A 5.4 {} \
+]
+
+
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2024-09-21 16:26:04 +0000