1 files changed, 424 insertions, 0 deletions
diff --git a/test/window4.tcl b/test/window4.tcl
new file mode 100644
index 0000000..0b91d76
--- /dev/null
+++ b/test/window4.tcl
@@ -0,0 +1,424 @@
+## 2018 May 19
+#
+# 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.
+#
+#***********************************************************************
+#
+
+source [file join [file dirname $argv0] pg_common.tcl]
+
+#=========================================================================
+
+start_test window4 "2018 June 04"
+ifcapable !windowfunc
+
+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');
+}
+
+for {set i 1} {$i < 20} {incr i} {
+  execsql_test 1.$i "SELECT a, ntile($i) OVER (ORDER BY a) FROM t3"
+}
+
+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);
+}
+
+execsql_test 2.1 {
+  SELECT a, nth_value(b, c) OVER (ORDER BY a) FROM t4
+}
+
+execsql_test 2.2.1 {
+  SELECT a, lead(b) OVER (ORDER BY a) FROM t4
+}
+execsql_test 2.2.2 {
+  SELECT a, lead(b, 2) OVER (ORDER BY a) FROM t4
+}
+execsql_test 2.2.3 {
+  SELECT a, lead(b, 3, 'abc') OVER (ORDER BY a) FROM t4
+}
+
+execsql_test 2.3.1 {
+  SELECT a, lag(b) OVER (ORDER BY a) FROM t4
+}
+execsql_test 2.3.2 {
+  SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
+}
+execsql_test 2.3.3 {
+  SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
+}
+
+execsql_test 2.4.1 {
+  SELECT string_agg(b, '.') OVER (
+    ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+  ) FROM t4
+}
+
+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);
+}
+
+execsql_test 3.1 {
+  SELECT a, nth_value(c, d) OVER (ORDER BY b) FROM t5
+}
+
+execsql_test 3.2 {
+  SELECT a, nth_value(c, d) OVER (PARTITION BY b ORDER BY a) FROM t5
+}
+
+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;
+}
+
+execsql_test 3.4 {
+  SELECT a, max(a) FILTER (WHERE (a%2)=0) OVER w FROM t5 
+  WINDOW w AS (ORDER BY a)
+}
+
+execsql_test 3.5.1 {
+  SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING)
+  FROM t5
+}
+execsql_test 3.5.2 {
+  SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
+  FROM t5
+}
+execsql_test 3.5.3 {
+  SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING)
+  FROM t5
+}
+
+execsql_test 3.6.1 {
+  SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING)
+  FROM t5
+}
+execsql_test 3.6.2 {
+  SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
+  FROM t5
+}
+execsql_test 3.6.3 {
+  SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)
+  FROM t5
+}
+
+==========
+
+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);
+}
+
+execsql_test 4.1 {
+  SELECT max(c), max(b) OVER (ORDER BY b) FROM ttt GROUP BY b;
+}
+
+execsql_test 4.2 {
+  SELECT max(b) OVER (ORDER BY max(c)) FROM ttt GROUP BY b;
+}
+
+execsql_test 4.3 {
+  SELECT abs(max(b) OVER (ORDER BY b)) FROM ttt GROUP BY b;
+}
+
+execsql_test 4.4 {
+  SELECT sum(b) OVER (
+    ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+  ) FROM ttt;
+}
+
+set lPart  [list "PARTITION BY b" "PARTITION BY b, a" "" "PARTITION BY a"]
+set lOrder [list "ORDER BY a" "ORDER BY a DESC" "" "ORDER BY b, a"]
+set lRange {
+    "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
+    "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
+    "RANGE BETWEEN CURRENT ROW AND CURRENT ROW"
+    "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING"
+}
+
+set lRows {
+    "ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING"
+    "ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING"
+    "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING"
+    "ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING"
+    "ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING"
+}
+
+set tn 1
+set SQL {
+  SELECT max(c) OVER ($p1 $o1 $r1), 
+  min(c) OVER ($p2 $o2 $r2)
+  FROM ttt ORDER BY a
+}
+set SQL2 {
+  SELECT sum(c) OVER ($p1 $o1 $r1), 
+         sum(c) OVER ($p2 $o2 $r2)
+  FROM ttt ORDER BY a
+}
+
+set o1 [lindex $lOrder 0]
+set o2 [lindex $lOrder 0]
+set r1 [lindex $lRange 0]
+set r2 [lindex $lRange 0]
+foreach p1 $lPart { foreach p2 $lPart { 
+  execsql_test 4.5.$tn.1 [subst $SQL]
+  execsql_test 4.5.$tn.2 [subst $SQL2]
+  incr tn
+}}
+
+set o1 [lindex $lOrder 0]
+set o2 [lindex $lOrder 0]
+set p1 [lindex $lPart 0]
+set p2 [lindex $lPart 0]
+foreach r1 $lRange { foreach r2 $lRange { 
+  execsql_test 4.5.$tn.1 [subst $SQL]
+  execsql_test 4.5.$tn.2 [subst $SQL2]
+  incr tn
+}}
+foreach r1 $lRows { foreach r2 $lRows { 
+  execsql_test 4.5.$tn.1 [subst $SQL]
+  execsql_test 4.5.$tn.2 [subst $SQL2]
+  incr tn
+}}
+
+set r1 [lindex $lRange 0]
+set r2 [lindex $lRange 0]
+set p1 [lindex $lPart 0]
+set p2 [lindex $lPart 0]
+foreach o1 $lOrder { foreach o2 $lOrder { 
+  execsql_test 4.5.$tn.1 [subst $SQL]
+  execsql_test 4.5.$tn.2 [subst $SQL2]
+  incr tn
+}}
+
+==========
+
+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);
+}
+
+execsql_test 7.1 {
+  SELECT lead(y) OVER win FROM t1
+  WINDOW win AS (ORDER BY x)
+}
+
+execsql_test 7.2 {
+  SELECT lead(y, 2) OVER win FROM t1
+  WINDOW win AS (ORDER BY x)
+}
+
+execsql_test 7.3 {
+  SELECT lead(y, 3, -1) OVER win FROM t1
+  WINDOW win AS (ORDER BY x)
+}
+
+execsql_test 7.4 {
+  SELECT 
+    lead(y) OVER win, lead(y) OVER win
+  FROM t1
+  WINDOW win AS (ORDER BY x)
+}
+
+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)
+}
+
+==========
+
+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);
+}
+
+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)
+}
+
+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)
+}
+
+==========
+
+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);
+}
+
+execsql_test 9.1 {
+  SELECT rank() OVER () FROM t2
+}
+execsql_test 9.2 {
+  SELECT dense_rank() OVER (PARTITION BY x) FROM t2
+}
+execsql_float_test 9.3 {
+  SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2
+}
+
+execsql_test 9.4 {
+  SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
+}
+
+execsql_test 9.5 {
+  SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
+}
+
+execsql_float_test 9.6 {
+  SELECT percent_rank() OVER () FROM t1
+}
+
+execsql_float_test 9.7 {
+  SELECT cume_dist() OVER () FROM t1
+}
+
+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);
+}
+execsql_test 10.1 {
+  SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
+}
+
+execsql_test 10.2 {
+  SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
+}
+execsql_test 10.3 {
+  SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
+}
+
+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);
+}
+
+execsql_test 11.1 {
+  SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
+}
+
+execsql_test 11.2 {
+  CREATE VIEW v8 AS SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
+}
+
+execsql_test 11.3 {
+  SELECT * FROM v8;
+}
+
+execsql_test 11.4 {
+  SELECT * FROM (
+    SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8
+  ) sub;
+}
+
+execsql_test 11.5 {
+  SELECT sum( min(t) ) OVER () FROM t8 GROUP BY total;
+}
+execsql_test 11.5 {
+  SELECT sum( max(t) ) OVER () FROM t8 GROUP BY total;
+}
+
+execsql_test 11.7 {
+  SELECT sum( min(t) ) OVER () FROM t8;
+}
+execsql_test 11.8 {
+  SELECT sum( max(t) ) OVER () FROM t8;
+}
+
+execsql_test 12.0 {
+  DROP TABLE IF EXISTS t2;
+  CREATE TABLE t2(a INTEGER);
+  INSERT INTO t2 VALUES(1), (2), (3);
+}
+
+execsql_test 12.1 {
+  SELECT (SELECT min(a) OVER ()) FROM t2
+}
+
+execsql_float_test 12.2 {
+  SELECT (SELECT avg(a)) FROM t2 ORDER BY 1
+}
+
+execsql_float_test 12.3 {
+  SELECT 
+    (SELECT avg(a) UNION SELECT min(a) OVER ()) 
+  FROM t2 GROUP BY a
+  ORDER BY 1
+}
+
+finish_test
+