summaryrefslogtreecommitdiffstats
path: root/test/window4.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/window4.test')
-rw-r--r--test/window4.test1390
1 files changed, 1390 insertions, 0 deletions
diff --git a/test/window4.test b/test/window4.test
new file mode 100644
index 0000000..4194f75
--- /dev/null
+++ b/test/window4.test
@@ -0,0 +1,1390 @@
+# 2018 June 04
+#
+# 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.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+#
+
+####################################################
+# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED!
+####################################################
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix window4
+
+ifcapable !windowfunc { finish_test ; return }
+do_execsql_test 1.0 {
+ DROP TABLE IF EXISTS t3;
+ CREATE TABLE t3(a TEXT PRIMARY KEY);
+ INSERT INTO t3 VALUES('a'), ('b'), ('c'), ('d'), ('e');
+ INSERT INTO t3 VALUES('f'), ('g'), ('h'), ('i'), ('j');
+} {}
+
+do_execsql_test 1.1 {
+ SELECT a, ntile(1) OVER (ORDER BY a) FROM t3
+} {a 1 b 1 c 1 d 1 e 1 f 1 g 1 h 1 i 1 j 1}
+
+do_execsql_test 1.2 {
+ SELECT a, ntile(2) OVER (ORDER BY a) FROM t3
+} {a 1 b 1 c 1 d 1 e 1 f 2 g 2 h 2 i 2 j 2}
+
+do_execsql_test 1.3 {
+ SELECT a, ntile(3) OVER (ORDER BY a) FROM t3
+} {a 1 b 1 c 1 d 1 e 2 f 2 g 2 h 3 i 3 j 3}
+
+do_execsql_test 1.4 {
+ SELECT a, ntile(4) OVER (ORDER BY a) FROM t3
+} {a 1 b 1 c 1 d 2 e 2 f 2 g 3 h 3 i 4 j 4}
+
+do_execsql_test 1.5 {
+ SELECT a, ntile(5) OVER (ORDER BY a) FROM t3
+} {a 1 b 1 c 2 d 2 e 3 f 3 g 4 h 4 i 5 j 5}
+
+do_execsql_test 1.6 {
+ SELECT a, ntile(6) OVER (ORDER BY a) FROM t3
+} {a 1 b 1 c 2 d 2 e 3 f 3 g 4 h 4 i 5 j 6}
+
+do_execsql_test 1.7 {
+ SELECT a, ntile(7) OVER (ORDER BY a) FROM t3
+} {a 1 b 1 c 2 d 2 e 3 f 3 g 4 h 5 i 6 j 7}
+
+do_execsql_test 1.8 {
+ SELECT a, ntile(8) OVER (ORDER BY a) FROM t3
+} {a 1 b 1 c 2 d 2 e 3 f 4 g 5 h 6 i 7 j 8}
+
+do_execsql_test 1.9 {
+ SELECT a, ntile(9) OVER (ORDER BY a) FROM t3
+} {a 1 b 1 c 2 d 3 e 4 f 5 g 6 h 7 i 8 j 9}
+
+do_execsql_test 1.10 {
+ SELECT a, ntile(10) OVER (ORDER BY a) FROM t3
+} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
+
+do_execsql_test 1.11 {
+ SELECT a, ntile(11) OVER (ORDER BY a) FROM t3
+} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
+
+do_execsql_test 1.12 {
+ SELECT a, ntile(12) OVER (ORDER BY a) FROM t3
+} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
+
+do_execsql_test 1.13 {
+ SELECT a, ntile(13) OVER (ORDER BY a) FROM t3
+} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
+
+do_execsql_test 1.14 {
+ SELECT a, ntile(14) OVER (ORDER BY a) FROM t3
+} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
+
+do_execsql_test 1.15 {
+ SELECT a, ntile(15) OVER (ORDER BY a) FROM t3
+} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
+
+do_execsql_test 1.16 {
+ SELECT a, ntile(16) OVER (ORDER BY a) FROM t3
+} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
+
+do_execsql_test 1.17 {
+ SELECT a, ntile(17) OVER (ORDER BY a) FROM t3
+} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
+
+do_execsql_test 1.18 {
+ SELECT a, ntile(18) OVER (ORDER BY a) FROM t3
+} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
+
+do_execsql_test 1.19 {
+ SELECT a, ntile(19) OVER (ORDER BY a) FROM t3
+} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
+
+do_execsql_test 2.0 {
+ DROP TABLE IF EXISTS t4;
+ CREATE TABLE t4(a INTEGER PRIMARY KEY, b TEXT, c INTEGER);
+ INSERT INTO t4 VALUES(1, 'A', 9);
+ INSERT INTO t4 VALUES(2, 'B', 3);
+ INSERT INTO t4 VALUES(3, 'C', 2);
+ INSERT INTO t4 VALUES(4, 'D', 10);
+ INSERT INTO t4 VALUES(5, 'E', 5);
+ INSERT INTO t4 VALUES(6, 'F', 1);
+ INSERT INTO t4 VALUES(7, 'G', 1);
+ INSERT INTO t4 VALUES(8, 'H', 2);
+ INSERT INTO t4 VALUES(9, 'I', 10);
+ INSERT INTO t4 VALUES(10, 'J', 4);
+} {}
+
+do_execsql_test 2.1 {
+ SELECT a, nth_value(b, c) OVER (ORDER BY a) FROM t4
+} {1 {} 2 {} 3 B 4 {} 5 E 6 A 7 A 8 B 9 {} 10 D}
+
+do_execsql_test 2.2.1 {
+ SELECT a, lead(b) OVER (ORDER BY a) FROM t4
+} {1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 10 {}}
+
+do_execsql_test 2.2.2 {
+ SELECT a, lead(b, 2) OVER (ORDER BY a) FROM t4
+} {1 C 2 D 3 E 4 F 5 G 6 H 7 I 8 J 9 {} 10 {}}
+
+do_execsql_test 2.2.3 {
+ SELECT a, lead(b, 3, 'abc') OVER (ORDER BY a) FROM t4
+} {1 D 2 E 3 F 4 G 5 H 6 I 7 J 8 abc 9 abc 10 abc}
+
+do_execsql_test 2.3.1 {
+ SELECT a, lag(b) OVER (ORDER BY a) FROM t4
+} {1 {} 2 A 3 B 4 C 5 D 6 E 7 F 8 G 9 H 10 I}
+
+do_execsql_test 2.3.2 {
+ SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
+} {1 {} 2 {} 3 A 4 B 5 C 6 D 7 E 8 F 9 G 10 H}
+
+do_execsql_test 2.3.3 {
+ SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
+} {1 abc 2 abc 3 abc 4 A 5 B 6 C 7 D 8 E 9 F 10 G}
+
+do_execsql_test 2.4.1 {
+ SELECT group_concat(b, '.') OVER (
+ ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ ) FROM t4
+} {A.B.C.D.E.F.G.H.I.J B.C.D.E.F.G.H.I.J C.D.E.F.G.H.I.J D.E.F.G.H.I.J
+ E.F.G.H.I.J F.G.H.I.J G.H.I.J H.I.J I.J J}
+
+do_execsql_test 3.0 {
+ DROP TABLE IF EXISTS t5;
+ CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
+ INSERT INTO t5 VALUES(1, 'A', 'one', 5);
+ INSERT INTO t5 VALUES(2, 'B', 'two', 4);
+ INSERT INTO t5 VALUES(3, 'A', 'three', 3);
+ INSERT INTO t5 VALUES(4, 'B', 'four', 2);
+ INSERT INTO t5 VALUES(5, 'A', 'five', 1);
+} {}
+
+do_execsql_test 3.1 {
+ SELECT a, nth_value(c, d) OVER (ORDER BY b) FROM t5
+} {1 {} 3 five 5 one 2 two 4 three}
+
+do_execsql_test 3.2 {
+ SELECT a, nth_value(c, d) OVER (PARTITION BY b ORDER BY a) FROM t5
+} {1 {} 3 {} 5 one 2 {} 4 four}
+
+do_execsql_test 3.3 {
+ SELECT a, count(*) OVER abc, count(*) OVER def FROM t5
+ WINDOW abc AS (ORDER BY a),
+ def AS (ORDER BY a DESC)
+ ORDER BY a;
+} {1 1 5 2 2 4 3 3 3 4 4 2 5 5 1}
+
+do_execsql_test 3.4 {
+ SELECT a, max(a) FILTER (WHERE (a%2)=0) OVER w FROM t5
+ WINDOW w AS (ORDER BY a)
+} {1 {} 2 2 3 2 4 4 5 4}
+
+do_execsql_test 3.5.1 {
+ SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING)
+ FROM t5
+} {1 {} 2 {} 3 {} 4 {} 5 {}}
+
+do_execsql_test 3.5.2 {
+ SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM t5
+} {1 {} 2 one 3 two 4 three 5 four}
+
+do_execsql_test 3.5.3 {
+ SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING)
+ FROM t5
+} {1 one 2 two 3 three 4 four 5 five}
+
+do_execsql_test 3.6.1 {
+ SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING)
+ FROM t5
+} {1 {} 2 {} 3 {} 4 {} 5 {}}
+
+do_execsql_test 3.6.2 {
+ SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
+ FROM t5
+} {1 two 2 three 3 four 4 five 5 {}}
+
+do_execsql_test 3.6.3 {
+ SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)
+ FROM t5
+} {1 one 2 two 3 three 4 four 5 five}
+
+#==========================================================================
+
+do_execsql_test 4.0 {
+ DROP TABLE IF EXISTS ttt;
+ CREATE TABLE ttt(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
+ INSERT INTO ttt VALUES(1, 1, 1);
+ INSERT INTO ttt VALUES(2, 2, 2);
+ INSERT INTO ttt VALUES(3, 3, 3);
+
+ INSERT INTO ttt VALUES(4, 1, 2);
+ INSERT INTO ttt VALUES(5, 2, 3);
+ INSERT INTO ttt VALUES(6, 3, 4);
+
+ INSERT INTO ttt VALUES(7, 1, 3);
+ INSERT INTO ttt VALUES(8, 2, 4);
+ INSERT INTO ttt VALUES(9, 3, 5);
+} {}
+
+do_execsql_test 4.1 {
+ SELECT max(c), max(b) OVER (ORDER BY b) FROM ttt GROUP BY b;
+} {3 1 4 2 5 3}
+
+do_execsql_test 4.2 {
+ SELECT max(b) OVER (ORDER BY max(c)) FROM ttt GROUP BY b;
+} {1 2 3}
+
+do_execsql_test 4.3 {
+ SELECT abs(max(b) OVER (ORDER BY b)) FROM ttt GROUP BY b;
+} {1 2 3}
+
+do_execsql_test 4.4 {
+ SELECT sum(b) OVER (
+ ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ ) FROM ttt;
+} {18 17 15 12 11 9 6 5 3}
+
+do_execsql_test 4.5.1.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.1.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
+
+do_execsql_test 4.5.2.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.2.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 2 5 3 7 4 6 3 9 4 12 5}
+
+do_execsql_test 4.5.3.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1}
+
+do_execsql_test 4.5.3.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 3 3 6 3 8 5 11 7 15 6 18 9 22 12 27}
+
+do_execsql_test 4.5.4.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.4.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 2 5 3 7 4 6 3 9 4 12 5}
+
+do_execsql_test 4.5.5.1 {
+ SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.5.2 {
+ SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 3 3 5 4 7 3 6 4 9 5 12}
+
+do_execsql_test 4.5.6.1 {
+ SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.6.2 {
+ SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.7.1 {
+ SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1}
+
+do_execsql_test 4.5.7.2 {
+ SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 3 3 6 2 8 3 11 4 15 3 18 4 22 5 27}
+
+do_execsql_test 4.5.8.1 {
+ SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.8.2 {
+ SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.9.1 {
+ SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 1 3 2 4 3 4 1 4 2 5 3}
+
+do_execsql_test 4.5.9.2 {
+ SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 3 2 6 3 8 3 11 5 15 7 18 6 22 9 27 12}
+
+do_execsql_test 4.5.10.1 {
+ SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 2 3 3 4 4 4 3 4 4 5 5}
+
+do_execsql_test 4.5.10.2 {
+ SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 3 2 6 3 8 2 11 3 15 4 18 3 22 4 27 5}
+
+do_execsql_test 4.5.11.1 {
+ SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 1 3 1 3 1 3 1 4 1 4 1 4 1 5 1}
+
+do_execsql_test 4.5.11.2 {
+ SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 3 3 6 6 8 8 11 11 15 15 18 18 22 22 27 27}
+
+do_execsql_test 4.5.12.1 {
+ SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 2 3 3 4 4 4 3 4 4 5 5}
+
+do_execsql_test 4.5.12.2 {
+ SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 3 2 6 3 8 2 11 3 15 4 18 3 22 4 27 5}
+
+do_execsql_test 4.5.13.1 {
+ SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.13.2 {
+ SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 3 3 5 4 7 3 6 4 9 5 12}
+
+do_execsql_test 4.5.14.1 {
+ SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.14.2 {
+ SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.15.1 {
+ SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1}
+
+do_execsql_test 4.5.15.2 {
+ SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 3 3 6 2 8 3 11 4 15 3 18 4 22 5 27}
+
+do_execsql_test 4.5.16.1 {
+ SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.16.2 {
+ SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.17.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.17.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
+
+do_execsql_test 4.5.18.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.18.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {1 6 2 9 3 12 3 6 5 9 7 12 6 6 9 9 12 12}
+
+do_execsql_test 4.5.19.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.19.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 2 5 3 7 4 6 3 9 4 12 5}
+
+do_execsql_test 4.5.20.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.20.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {1 6 2 9 3 12 3 5 5 7 7 9 6 3 9 4 12 5}
+
+do_execsql_test 4.5.21.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.21.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 1 9 2 12 3 6 3 9 5 12 7 6 6 9 9 12 12}
+
+do_execsql_test 4.5.22.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.22.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {6 6 9 9 12 12 6 6 9 9 12 12 6 6 9 9 12 12}
+
+do_execsql_test 4.5.23.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.23.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 1 9 2 12 3 6 2 9 3 12 4 6 3 9 4 12 5}
+
+do_execsql_test 4.5.24.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.24.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {6 6 9 9 12 12 6 5 9 7 12 9 6 3 9 4 12 5}
+
+do_execsql_test 4.5.25.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.25.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 3 3 5 4 7 3 6 4 9 5 12}
+
+do_execsql_test 4.5.26.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.26.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {1 6 2 9 3 12 2 6 3 9 4 12 3 6 4 9 5 12}
+
+do_execsql_test 4.5.27.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.27.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.28.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.28.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {1 6 2 9 3 12 2 5 3 7 4 9 3 3 4 4 5 5}
+
+do_execsql_test 4.5.29.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.29.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 1 9 2 12 3 5 3 7 5 9 7 3 6 4 9 5 12}
+
+do_execsql_test 4.5.30.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.30.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {6 6 9 9 12 12 5 6 7 9 9 12 3 6 4 9 5 12}
+
+do_execsql_test 4.5.31.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.31.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 1 9 2 12 3 5 2 7 3 9 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.32.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.32.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ FROM ttt ORDER BY a
+} {6 6 9 9 12 12 5 5 7 7 9 9 3 3 4 4 5 5}
+
+do_execsql_test 4.5.33.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
+ FROM ttt ORDER BY a
+} {2 1 3 2 4 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.33.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 3 5 5 7 7 6 6 9 9 12 12 6 6 9 9 12 12}
+
+do_execsql_test 4.5.34.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
+ FROM ttt ORDER BY a
+} {2 1 3 2 4 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.34.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 6 5 9 7 12 6 6 9 9 12 12 6 6 9 9 12 12}
+
+do_execsql_test 4.5.35.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {2 {} 3 {} 4 {} 3 1 4 2 5 3 3 2 4 3 5 4}
+
+do_execsql_test 4.5.35.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {3 {} 5 {} 7 {} 6 1 9 2 12 3 6 2 9 3 12 4}
+
+do_execsql_test 4.5.36.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {2 {} 3 {} 4 {} 3 {} 4 {} 5 {} 3 {} 4 {} 5 {}}
+
+do_execsql_test 4.5.36.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {3 {} 5 {} 7 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12 {}}
+
+do_execsql_test 4.5.37.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
+ FROM ttt ORDER BY a
+} {2 2 3 3 4 4 3 3 4 4 5 5 3 {} 4 {} 5 {}}
+
+do_execsql_test 4.5.37.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 5 5 7 7 9 6 3 9 4 12 5 6 {} 9 {} 12 {}}
+
+do_execsql_test 4.5.38.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.38.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
+ FROM ttt ORDER BY a
+} {6 3 9 5 12 7 6 6 9 9 12 12 6 6 9 9 12 12}
+
+do_execsql_test 4.5.39.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.39.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
+ FROM ttt ORDER BY a
+} {6 6 9 9 12 12 6 6 9 9 12 12 6 6 9 9 12 12}
+
+do_execsql_test 4.5.40.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {3 {} 4 {} 5 {} 3 1 4 2 5 3 3 2 4 3 5 4}
+
+do_execsql_test 4.5.40.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {6 {} 9 {} 12 {} 6 1 9 2 12 3 6 2 9 3 12 4}
+
+do_execsql_test 4.5.41.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {3 {} 4 {} 5 {} 3 {} 4 {} 5 {} 3 {} 4 {} 5 {}}
+
+do_execsql_test 4.5.41.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {6 {} 9 {} 12 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12 {}}
+
+do_execsql_test 4.5.42.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 2 4 3 5 4 3 3 4 4 5 5 3 {} 4 {} 5 {}}
+
+do_execsql_test 4.5.42.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
+ FROM ttt ORDER BY a
+} {6 5 9 7 12 9 6 3 9 4 12 5 6 {} 9 {} 12 {}}
+
+do_execsql_test 4.5.43.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 1 {} 2 {} 3 1 1 2 2 3 3 2 1 3 2 4 3}
+
+do_execsql_test 4.5.43.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 3 {} 5 {} 7 1 6 2 9 3 12 2 6 3 9 4 12}
+
+do_execsql_test 4.5.44.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 1 {} 2 {} 3 1 1 2 2 3 3 2 1 3 2 4 3}
+
+do_execsql_test 4.5.44.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 6 {} 9 {} 12 1 6 2 9 3 12 2 6 3 9 4 12}
+
+do_execsql_test 4.5.45.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {{} {} {} {} {} {} 1 1 2 2 3 3 2 2 3 3 4 4}
+
+do_execsql_test 4.5.45.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {{} {} {} {} {} {} 1 1 2 2 3 3 2 2 3 3 4 4}
+
+do_execsql_test 4.5.46.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {{} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4 {}}
+
+do_execsql_test 4.5.46.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {{} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4 {}}
+
+do_execsql_test 4.5.47.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 2 {} 3 {} 4 1 3 2 4 3 5 2 {} 3 {} 4 {}}
+
+do_execsql_test 4.5.47.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 5 {} 7 {} 9 1 3 2 4 3 5 2 {} 3 {} 4 {}}
+
+do_execsql_test 4.5.48.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 1 {} 2 {} 3 {} 1 {} 2 {} 3 {} 1 {} 2 {} 3}
+
+do_execsql_test 4.5.48.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 3 {} 5 {} 7 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12}
+
+do_execsql_test 4.5.49.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 1 {} 2 {} 3 {} 1 {} 2 {} 3 {} 1 {} 2 {} 3}
+
+do_execsql_test 4.5.49.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 6 {} 9 {} 12 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12}
+
+do_execsql_test 4.5.50.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {{} {} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4}
+
+do_execsql_test 4.5.50.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {{} {} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4}
+
+do_execsql_test 4.5.51.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}}
+
+do_execsql_test 4.5.51.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}}
+
+do_execsql_test 4.5.52.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 2 {} 3 {} 4 {} 3 {} 4 {} 5 {} {} {} {} {} {}}
+
+do_execsql_test 4.5.52.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
+ FROM ttt ORDER BY a
+} {{} 5 {} 7 {} 9 {} 3 {} 4 {} 5 {} {} {} {} {} {}}
+
+do_execsql_test 4.5.53.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 {} 1 {} 2 {} 3}
+
+do_execsql_test 4.5.53.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
+ FROM ttt ORDER BY a
+} {5 3 7 5 9 7 3 6 4 9 5 12 {} 6 {} 9 {} 12}
+
+do_execsql_test 4.5.54.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 {} 1 {} 2 {} 3}
+
+do_execsql_test 4.5.54.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
+ FROM ttt ORDER BY a
+} {5 6 7 9 9 12 3 6 4 9 5 12 {} 6 {} 9 {} 12}
+
+do_execsql_test 4.5.55.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {3 {} 4 {} 5 {} 3 1 4 2 5 3 {} 2 {} 3 {} 4}
+
+do_execsql_test 4.5.55.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {5 {} 7 {} 9 {} 3 1 4 2 5 3 {} 2 {} 3 {} 4}
+
+do_execsql_test 4.5.56.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {3 {} 4 {} 5 {} 3 {} 4 {} 5 {} {} {} {} {} {} {}}
+
+do_execsql_test 4.5.56.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
+ FROM ttt ORDER BY a
+} {5 {} 7 {} 9 {} 3 {} 4 {} 5 {} {} {} {} {} {} {}}
+
+do_execsql_test 4.5.57.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
+ min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
+ FROM ttt ORDER BY a
+} {3 2 4 3 5 4 3 3 4 4 5 5 {} {} {} {} {} {}}
+
+do_execsql_test 4.5.57.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
+ sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
+ FROM ttt ORDER BY a
+} {5 5 7 7 9 9 3 3 4 4 5 5 {} {} {} {} {} {}}
+
+do_execsql_test 4.5.58.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.58.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
+
+do_execsql_test 4.5.59.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.59.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 6 2 9 3 12 3 5 5 7 7 9 6 3 9 4 12 5}
+
+do_execsql_test 4.5.60.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.60.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 6 2 9 3 12 3 6 5 9 7 12 6 6 9 9 12 12}
+
+do_execsql_test 4.5.61.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.61.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
+
+do_execsql_test 4.5.62.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.62.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 1 9 2 12 3 5 3 7 5 9 7 3 6 4 9 5 12}
+
+do_execsql_test 4.5.63.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.63.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 6 9 9 12 12 5 5 7 7 9 9 3 3 4 4 5 5}
+
+do_execsql_test 4.5.64.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.64.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 6 9 9 12 12 5 6 7 9 9 12 3 6 4 9 5 12}
+
+do_execsql_test 4.5.65.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.65.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 1 9 2 12 3 5 3 7 5 9 7 3 6 4 9 5 12}
+
+do_execsql_test 4.5.66.1 {
+ SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.66.2 {
+ SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 1 9 2 12 3 6 3 9 5 12 7 6 6 9 9 12 12}
+
+do_execsql_test 4.5.67.1 {
+ SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.67.2 {
+ SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 6 9 9 12 12 6 5 9 7 12 9 6 3 9 4 12 5}
+
+do_execsql_test 4.5.68.1 {
+ SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.68.2 {
+ SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 6 9 9 12 12 6 6 9 9 12 12 6 6 9 9 12 12}
+
+do_execsql_test 4.5.69.1 {
+ SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.69.2 {
+ SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {6 1 9 2 12 3 6 3 9 5 12 7 6 6 9 9 12 12}
+
+do_execsql_test 4.5.70.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.70.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
+
+do_execsql_test 4.5.71.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
+
+do_execsql_test 4.5.71.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 6 2 9 3 12 3 5 5 7 7 9 6 3 9 4 12 5}
+
+do_execsql_test 4.5.72.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.72.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 6 2 9 3 12 3 6 5 9 7 12 6 6 9 9 12 12}
+
+do_execsql_test 4.5.73.1 {
+ SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
+
+do_execsql_test 4.5.73.2 {
+ SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ FROM ttt ORDER BY a
+} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
+
+#==========================================================================
+
+do_execsql_test 7.0 {
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t1(x INTEGER, y INTEGER);
+ INSERT INTO t1 VALUES(1, 2);
+ INSERT INTO t1 VALUES(3, 4);
+ INSERT INTO t1 VALUES(5, 6);
+ INSERT INTO t1 VALUES(7, 8);
+ INSERT INTO t1 VALUES(9, 10);
+} {}
+
+do_execsql_test 7.1 {
+ SELECT lead(y) OVER win FROM t1
+ WINDOW win AS (ORDER BY x)
+} {4 6 8 10 {}}
+
+do_execsql_test 7.2 {
+ SELECT lead(y, 2) OVER win FROM t1
+ WINDOW win AS (ORDER BY x)
+} {6 8 10 {} {}}
+
+do_execsql_test 7.3 {
+ SELECT lead(y, 3, -1) OVER win FROM t1
+ WINDOW win AS (ORDER BY x)
+} {8 10 -1 -1 -1}
+
+do_execsql_test 7.4 {
+ SELECT
+ lead(y) OVER win, lead(y) OVER win
+ FROM t1
+ WINDOW win AS (ORDER BY x)
+} {4 4 6 6 8 8 10 10 {} {}}
+
+do_execsql_test 7.5 {
+ SELECT
+ lead(y) OVER win,
+ lead(y, 2) OVER win,
+ lead(y, 3, -1) OVER win
+ FROM t1
+ WINDOW win AS (ORDER BY x)
+} {4 6 8 6 8 10 8 10 -1 10 {} -1 {} {} -1}
+
+#==========================================================================
+
+do_execsql_test 8.0 {
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
+ INSERT INTO t1 VALUES(1, 2, 3, 4);
+ INSERT INTO t1 VALUES(5, 6, 7, 8);
+ INSERT INTO t1 VALUES(9, 10, 11, 12);
+} {}
+
+do_execsql_test 8.1 {
+ SELECT row_number() OVER win,
+ nth_value(d,2) OVER win,
+ lead(d) OVER win
+ FROM t1
+ WINDOW win AS (ORDER BY a)
+} {1 {} 8 2 8 12 3 8 {}}
+
+do_execsql_test 8.2 {
+ SELECT row_number() OVER win,
+ rank() OVER win,
+ dense_rank() OVER win,
+ ntile(2) OVER win,
+ first_value(d) OVER win,
+ last_value(d) OVER win,
+ nth_value(d,2) OVER win,
+ lead(d) OVER win,
+ lag(d) OVER win,
+ max(d) OVER win,
+ min(d) OVER win
+ FROM t1
+ WINDOW win AS (ORDER BY a)
+} {1 1 1 1 4 4 {} 8 {} 4 4 2 2 2 1 4 8 8 12 4 8 4 3 3 3 2 4 12 8 {} 8 12 4}
+
+#==========================================================================
+
+do_execsql_test 9.0 {
+ DROP TABLE IF EXISTS t2;
+ CREATE TABLE t2(x INTEGER);
+ INSERT INTO t2 VALUES(1), (1), (1), (4), (4), (6), (7);
+} {}
+
+do_execsql_test 9.1 {
+ SELECT rank() OVER () FROM t2
+} {1 1 1 1 1 1 1}
+
+do_execsql_test 9.2 {
+ SELECT dense_rank() OVER (PARTITION BY x) FROM t2
+} {1 1 1 1 1 1 1}
+
+
+do_test 9.3 {
+ set myres {}
+ foreach r [db eval {SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2}] {
+ lappend myres [format %.4f [set r]]
+ }
+ set res2 {1.0000 0.0000 1.0000 0.0000 1.0000 0.0000 4.0000 0.0000 4.0000 0.0000 6.0000 0.0000 7.0000 0.0000}
+ set i 0
+ foreach r [set myres] r2 [set res2] {
+ if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
+ error "list element [set i] does not match: got=[set r] expected=[set r2]"
+ }
+ incr i
+ }
+ set {} {}
+} {}
+
+do_execsql_test 9.4 {
+ SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
+} {1 1 1 1 1 1 4 4 4 4 6 6 7 7}
+
+do_execsql_test 9.5 {
+ SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
+} {1 1 4 4 6 6 7 7}
+
+
+do_test 9.6 {
+ set myres {}
+ foreach r [db eval {SELECT percent_rank() OVER () FROM t1}] {
+ lappend myres [format %.4f [set r]]
+ }
+ set res2 {0.0000 0.0000 0.0000}
+ set i 0
+ foreach r [set myres] r2 [set res2] {
+ if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
+ error "list element [set i] does not match: got=[set r] expected=[set r2]"
+ }
+ incr i
+ }
+ set {} {}
+} {}
+
+
+do_test 9.7 {
+ set myres {}
+ foreach r [db eval {SELECT cume_dist() OVER () FROM t1}] {
+ lappend myres [format %.4f [set r]]
+ }
+ set res2 {1.0000 1.0000 1.0000}
+ set i 0
+ foreach r [set myres] r2 [set res2] {
+ if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
+ error "list element [set i] does not match: got=[set r] expected=[set r2]"
+ }
+ incr i
+ }
+ set {} {}
+} {}
+
+do_execsql_test 10.0 {
+ DROP TABLE IF EXISTS t7;
+ CREATE TABLE t7(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
+ INSERT INTO t7(id, a, b) VALUES
+ (1, 1, 2), (2, 1, NULL), (3, 1, 4),
+ (4, 3, NULL), (5, 3, 8), (6, 3, 1);
+} {}
+
+do_execsql_test 10.1 {
+ SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
+} {1 2 2 2 3 2 4 {} 5 8 6 1}
+
+do_execsql_test 10.2 {
+ SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
+} {1 {} 2 2 3 {} 4 {} 5 {} 6 8}
+
+do_execsql_test 10.3 {
+ SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
+} {1 {} 2 4 3 {} 4 8 5 1 6 {}}
+
+do_execsql_test 11.0 {
+ DROP VIEW IF EXISTS v8;
+ DROP TABLE IF EXISTS t8;
+ CREATE TABLE t8(t INT, total INT);
+ INSERT INTO t8 VALUES(0,2);
+ INSERT INTO t8 VALUES(5,1);
+ INSERT INTO t8 VALUES(10,1);
+} {}
+
+do_execsql_test 11.1 {
+ SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
+} {0 1 2}
+
+do_execsql_test 11.2 {
+ CREATE VIEW v8 AS SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
+} {}
+
+do_execsql_test 11.3 {
+ SELECT * FROM v8;
+} {0 1 2}
+
+do_execsql_test 11.4 {
+ SELECT * FROM (
+ SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8
+ ) sub;
+} {0 1 2}
+
+do_execsql_test 11.5 {
+ SELECT sum( min(t) ) OVER () FROM t8 GROUP BY total;
+} {5 5}
+
+do_execsql_test 11.5 {
+ SELECT sum( max(t) ) OVER () FROM t8 GROUP BY total;
+} {10 10}
+
+do_execsql_test 11.7 {
+ SELECT sum( min(t) ) OVER () FROM t8;
+} {0}
+
+do_execsql_test 11.8 {
+ SELECT sum( max(t) ) OVER () FROM t8;
+} {10}
+
+do_execsql_test 12.0 {
+ DROP TABLE IF EXISTS t2;
+ CREATE TABLE t2(a INTEGER);
+ INSERT INTO t2 VALUES(1), (2), (3);
+} {}
+
+do_execsql_test 12.1 {
+ SELECT (SELECT min(a) OVER ()) FROM t2
+} {1 2 3}
+
+
+do_test 12.2 {
+ set myres {}
+ foreach r [db eval {SELECT (SELECT avg(a)) FROM t2 ORDER BY 1}] {
+ lappend myres [format %.4f [set r]]
+ }
+ set res2 {2.0000}
+ set i 0
+ foreach r [set myres] r2 [set res2] {
+ if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
+ error "list element [set i] does not match: got=[set r] expected=[set r2]"
+ }
+ incr i
+ }
+ set {} {}
+} {}
+
+
+do_test 12.3 {
+ set myres {}
+ foreach r [db eval {SELECT
+ (SELECT avg(a) UNION SELECT min(a) OVER ())
+ FROM t2 GROUP BY a
+ ORDER BY 1}] {
+ lappend myres [format %.4f [set r]]
+ }
+ set res2 {1.0000 2.0000 3.0000}
+ set i 0
+ foreach r [set myres] r2 [set res2] {
+ if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
+ error "list element [set i] does not match: got=[set r] expected=[set r2]"
+ }
+ incr i
+ }
+ set {} {}
+} {}
+
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-20 00:43:09 +0000