release v1.4.0-beta1v1.4.0-beta1

1 files changed, 309 insertions, 0 deletions

diff --git a/testdata/tcl/windowA.test b/testdata/tcl/windowA.test
new file mode 100644
index 0000000..e94ae57
--- /dev/null
+++ b/testdata/tcl/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