Add support for RANGE window frames. Some cases still do not work.

FossilOrigin-Name: ffc32b246d92d53c66094afe11950b53ffab6a1c230c602eebbfedafb2eb57f4

6 files changed, 85 insertions, 26 deletions

diff --git a/test/pg_common.tcl b/test/pg_common.tcl
index 4f3899ea4..902f8a36c 100644
--- a/test/pg_common.tcl
+++ b/test/pg_common.tcl
@@ -72,7 +72,8 @@ proc errorsql_test {tn sql} {
   if {$rc==0} {
     error "errorsql_test SQL did not cause an error!"
   }
-  puts $::fd "# PG says \"[string trim $msg]\""
+  set msg [lindex [split [string trim $msg] "\n"] 0]
+  puts $::fd "# PG says $msg"
   set sql [string map {string_agg group_concat} $sql]
   puts $::fd "do_test $tn { catch { execsql {"
   puts $::fd "  [string trim $sql]"
diff --git a/test/window6.test b/test/window6.test
index 06cebcf1a..434a032f7 100644
--- a/test/window6.test
+++ b/test/window6.test
@@ -219,17 +219,17 @@ do_execsql_test 9.0 {
 } {
   1 1  2 1,2  3 1,2,3  4 2,3,4  5 3,4,5
 }
-do_catchsql_test 9.1 {
-  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
-  SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
-  FROM c;
-} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}
-
-do_catchsql_test 9.2 {
-  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
-  SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
-  FROM c;
-} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}
+#do_catchsql_test 9.1 {
+#  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
+#  SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
+#  FROM c;
+#} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}
+#
+#do_catchsql_test 9.2 {
+#  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
+#  SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
+#  FROM c;
+#} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}
 
 do_catchsql_test 9.3 {
   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
@@ -262,7 +262,7 @@ foreach {tn frame} {
     SELECT count() OVER (
         ORDER BY x ROWS $frame 
     ) FROM c;
-  " {1 {unsupported frame delimiter for ROWS}}
+  " {1 {unsupported frame specification}}
 }
 
 do_catchsql_test 9.8.1 {
diff --git a/test/window8.tcl b/test/window8.tcl
index 362070ecb..f117dd533 100644
--- a/test/window8.tcl
+++ b/test/window8.tcl
@@ -89,6 +89,24 @@ foreach {tn frame} {
   "
 }
 
+==========
+
+execsql_test 2.0 {
+  DROP TABLE IF EXISTS t1;
+  CREATE TABLE t1(a INTEGER, b INTEGER);
+  INSERT INTO t1 VALUES
+      (13, 26), (15, 30);
+}
+
+foreach {tn frame} {
+  1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
+  2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
+  3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
+} {
+  execsql_test 2.$tn "SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ($frame)"
+}
+
+
 finish_test
 
 
diff --git a/test/window8.test b/test/window8.test
index e861f8b4a..b74b7d992 100644
--- a/test/window8.test
+++ b/test/window8.test
@@ -433,4 +433,25 @@ do_execsql_test 1.19.5 {
   SELECT a, b, min(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
 } {AA aa 102   AA aa 102   AA aa 102   AA aa 102   AA bb 102   AA bb 102   AA bb 102   AA bb 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   CC aa 102   CC aa 102   CC aa 102   CC aa 102   CC bb 102   CC bb 102   DD aa 102   DD aa 102   DD aa 102   DD bb 102   DD bb 102   DD bb 102   DD bb 102   EE aa 102   EE aa 102   EE bb 102   EE bb 102   EE bb 102   FF aa 102   FF aa 102   FF aa 102   FF aa 102   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   GG aa 113   GG aa 113   GG aa 113   GG aa 113   GG bb 113   GG bb 113   GG bb 113   GG bb 113   HH aa 113   HH aa 113   HH aa 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   II aa 113   II aa 113   II bb 113   II bb 113   II bb 113   II bb 113   II bb 113   JJ aa 257   JJ aa 257   JJ aa 257   JJ aa 257   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}
 
+#==========================================================================
+
+do_execsql_test 2.0 {
+  DROP TABLE IF EXISTS t1;
+  CREATE TABLE t1(a INTEGER, b INTEGER);
+  INSERT INTO t1 VALUES
+      (13, 26), (15, 30);
+} {}
+
+do_execsql_test 2.1 {
+  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
+} {13 56   15 56}
+
+do_execsql_test 2.2 {
+  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
+} {13 {}   15 {}}
+
+do_execsql_test 2.3 {
+  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
+} {13 30   15 {}}
+
 finish_test
diff --git a/test/windowerr.tcl b/test/windowerr.tcl
index f1e3b49d7..38ef54edc 100644
--- a/test/windowerr.tcl
+++ b/test/windowerr.tcl
@@ -20,6 +20,11 @@ ifcapable !windowfunc
 execsql_test 1.0 {
   DROP TABLE IF EXISTS t1;
   CREATE TABLE t1(a INTEGER, b INTEGER);
+  INSERT INTO t1 VALUES(1, 1);
+  INSERT INTO t1 VALUES(2, 2);
+  INSERT INTO t1 VALUES(3, 3);
+  INSERT INTO t1 VALUES(4, 4);
+  INSERT INTO t1 VALUES(5, 5);
 }
 
 foreach {tn frame} {
@@ -31,11 +36,13 @@ foreach {tn frame} {
 
   5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
   6 "ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING"
+
+  7 "ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"
 } {
   errorsql_test 1.$tn "
   SELECT a, sum(b) OVER (
     $frame
-  ) FROM t3 ORDER BY 1
+  ) FROM t1 ORDER BY 1
   "
 }
 
diff --git a/test/windowerr.test b/test/windowerr.test
index 2dcc0a82d..d517665a9 100644
--- a/test/windowerr.test
+++ b/test/windowerr.test
@@ -23,48 +23,60 @@ ifcapable !windowfunc { finish_test ; return }
 do_execsql_test 1.0 {
   DROP TABLE IF EXISTS t1;
   CREATE TABLE t1(a INTEGER, b INTEGER);
+  INSERT INTO t1 VALUES(1, 1);
+  INSERT INTO t1 VALUES(2, 2);
+  INSERT INTO t1 VALUES(3, 3);
+  INSERT INTO t1 VALUES(4, 4);
+  INSERT INTO t1 VALUES(5, 5);
 } {}
 
-# PG says "ERROR:  frame starting offset must not be negative"
+# PG says ERROR:  frame starting offset must not be negative
 do_test 1.1 { catch { execsql {
   SELECT a, sum(b) OVER (
     ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING
-  ) FROM t3 ORDER BY 1
+  ) FROM t1 ORDER BY 1
 } } } 1
 
-# PG says "ERROR:  frame ending offset must not be negative"
+# PG says ERROR:  frame ending offset must not be negative
 do_test 1.2 { catch { execsql {
   SELECT a, sum(b) OVER (
     ORDER BY a ROWS BETWEEN  1 PRECEDING AND -1 FOLLOWING
-  ) FROM t3 ORDER BY 1
+  ) FROM t1 ORDER BY 1
 } } } 1
 
-# PG says "ERROR:  invalid preceding or following size in window function"
+# PG says ERROR:  invalid preceding or following size in window function
 do_test 1.3 { catch { execsql {
   SELECT a, sum(b) OVER (
     ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING
-  ) FROM t3 ORDER BY 1
+  ) FROM t1 ORDER BY 1
 } } } 1
 
-# PG says "ERROR:  invalid preceding or following size in window function"
+# PG says ERROR:  invalid preceding or following size in window function
 do_test 1.4 { catch { execsql {
   SELECT a, sum(b) OVER (
     ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING
-  ) FROM t3 ORDER BY 1
+  ) FROM t1 ORDER BY 1
 } } } 1
 
-# PG says "ERROR:  frame starting offset must not be negative"
+# PG says ERROR:  frame starting offset must not be negative
 do_test 1.5 { catch { execsql {
   SELECT a, sum(b) OVER (
     ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING
-  ) FROM t3 ORDER BY 1
+  ) FROM t1 ORDER BY 1
 } } } 1
 
-# PG says "ERROR:  frame ending offset must not be negative"
+# PG says ERROR:  frame ending offset must not be negative
 do_test 1.6 { catch { execsql {
   SELECT a, sum(b) OVER (
     ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING
-  ) FROM t3 ORDER BY 1
+  ) FROM t1 ORDER BY 1
+} } } 1
+
+# PG says ERROR:  RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
+do_test 1.7 { catch { execsql {
+  SELECT a, sum(b) OVER (
+    ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING
+  ) FROM t1 ORDER BY 1
 } } } 1
 
 finish_test