diff options
| author | dan <dan@noemail.net> | 2019-03-19 19:39:42 +0000 |
|---|---|---|
| committer | dan <dan@noemail.net> | 2019-03-19 19:39:42 +0000 |
| commit | 78694ea3071ff71f99a22f575d0ae9feef3bf7f7 (patch) | |
| tree | db9330b6babf8c96e1f81b4b961a4bcf6089158b | |
| parent | 6603342f12fd09f1f5623c166f0d168ab636f9b6 (diff) | |
| download | sqlite-78694ea3071ff71f99a22f575d0ae9feef3bf7f7.tar.gz sqlite-78694ea3071ff71f99a22f575d0ae9feef3bf7f7.zip | |
Add further tests to window8.test.
FossilOrigin-Name: ec7e224f50271a69a28074270b01328ec0ee38751fcb93b2c598d8be2b77a95d
| -rw-r--r-- | manifest | 14 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | test/window8.tcl | 13 | ||||
| -rw-r--r-- | test/window8.test | 1638 |
4 files changed, 1091 insertions, 576 deletions
@@ -1,5 +1,5 @@ -C Fix\sa\sproblem\swith\sEXCLUDE\sclauses\son\swindow\sframes\swith\sno\sORDER\sBY. -D 2019-03-19T19:19:53.610 +C Add\sfurther\stests\sto\swindow8.test. +D 2019-03-19T19:39:42.451 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -1688,8 +1688,8 @@ F test/window5.test d328dd18221217c49c144181975eea17339eaeaf0e9aa558cee3afb84652 F test/window6.test 604890f2b0f976339e6a1774cd90d48840e57a54b4f21a8b4b3047aa2c9787d1 F test/window7.tcl 6a1210f05d40ec89c22960213a22cd3f98d4e2f2eb20646c83c8c30d4d76108f F test/window7.test ce7f865241fdd1c5c4db869cd7bb2986c3be836bc2e73649a6846dd920f63e0f -F test/window8.tcl 38eca1d36735d785251e26ab4e6a53ab32b9f892dab25f3adc730288ebdf405a -F test/window8.test 8d191c46d0d05f4dfd0cbacf2a1ba7f44a14ca8d0c4e8758cdab50ad54cd1293 +F test/window8.tcl 97de3829e0e1aae2c3aaae41a55c54bc1b0751bbc80dfdd93020431b7a889dad +F test/window8.test 0dd7e2d32605bf59dc6163b924faeff5951419c1c17a9e506e5a38606e97ab7e F test/windowerr.tcl 727d77959ac512b473dd2d659937f58688ce5460482a442c2d569fc8656491e5 F test/windowerr.test 7962cf9b39f27b11ea5c62105ab2322a61ceac098dce536e2b8569c3053f62e7 F test/windowfault.test cb27a4d8c85982733b123af667921df3a96e23d3739b15d414de75054b334cbe @@ -1813,7 +1813,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P e195948a6876efe01b5cf2ed67bc9015a781fda39dca668099cb7edc1d331818 -R 7302062dc5c56ecf785fcf50d86c1879 +P e0255063799a2a8531f0eaa8790334509591a5ababc5b915035b46d2faa80363 +R 35c7ba821ef75c9b3e6a15f72b75c115 U dan -Z 9d652fe80863fdaec099639a4ce1b008 +Z a54d8b32ea4fb15a6f66e7fe3ce1d38d diff --git a/manifest.uuid b/manifest.uuid index 518d3f092..16db4c6ba 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -e0255063799a2a8531f0eaa8790334509591a5ababc5b915035b46d2faa80363
\ No newline at end of file +ec7e224f50271a69a28074270b01328ec0ee38751fcb93b2c598d8be2b77a95d
\ No newline at end of file diff --git a/test/window8.tcl b/test/window8.tcl index e4eb523af..234f0f003 100644 --- a/test/window8.tcl +++ b/test/window8.tcl @@ -245,21 +245,26 @@ foreach {tn ex} { RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING } 4 { ORDER BY a NULLS FIRST GROUPS 6 PRECEDING } 5 { ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING } + 6 { ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING } + 7 { ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST + ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING } } { execsql_test 5.$tn.$tn2.1 " SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( $frame $ex ) - ORDER BY 1 NULLS FIRST, 2 NULLS FIRST + ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST " execsql_test 5.$tn.$tn2.2 " SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( $frame $ex ) - ORDER BY 1 NULLS FIRST, 2 NULLS FIRST + ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST " } } diff --git a/test/window8.test b/test/window8.test index 0545bd77c..d204d5b77 100644 --- a/test/window8.test +++ b/test/window8.test @@ -3560,782 +3560,1292 @@ do_execsql_test 5.0 { do_execsql_test 5.1.1.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS ) - ORDER BY 1 , 2 -} {979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102} + ORDER BY 1 , 2 , 3 +} {979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83} do_execsql_test 5.1.1.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS ) - ORDER BY 1 , 2 -} {23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1} + ORDER BY 1 , 2 , 3 +} {23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1} do_execsql_test 5.1.2.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS ) - ORDER BY 1 , 2 -} {899 113 899 113 899 113 899 113 899 113 899 113 899 113 - 899 113 899 113 899 113 899 113 899 113 899 113 899 113 - 899 113 899 113 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 113 979 113 979 113 979 113 979 113 - 979 113 979 113 979 113 979 113 979 113 979 113 979 113 - 979 113 979 113 979 113 979 113 979 113} + ORDER BY 1 , 2 , 3 +} {899 113 9 899 113 9 899 113 9 899 113 9 899 113 9 899 113 9 + 899 113 9 899 113 9 899 113 9 899 113 16 899 113 16 899 113 16 + 899 113 16 899 113 16 899 113 16 899 113 16 979 102 44 979 102 44 + 979 102 44 979 102 44 979 102 44 979 102 44 979 102 44 979 102 44 + 979 102 44 979 102 44 979 102 44 979 102 49 979 102 49 979 102 49 + 979 102 49 979 102 49 979 102 56 979 102 56 979 102 56 979 102 56 + 979 102 56 979 102 56 979 102 56 979 102 62 979 102 62 979 102 62 + 979 102 62 979 102 62 979 102 62 979 102 75 979 102 75 979 102 75 + 979 102 75 979 102 75 979 102 75 979 102 75 979 102 75 979 102 75 + 979 102 75 979 102 75 979 102 75 979 102 75 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 113 25 979 113 25 979 113 25 979 113 25 979 113 25 979 113 25 + 979 113 25 979 113 25 979 113 25 979 113 33 979 113 33 979 113 33 + 979 113 33 979 113 33 979 113 33 979 113 33 979 113 33} do_execsql_test 5.1.2.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS ) - ORDER BY 1 , 2 -} {2947 81 2947 81 2947 81 2947 81 2947 81 2947 81 2947 81 - 2947 81 2947 81 5287 74 5287 74 5287 74 5287 74 5287 74 - 5287 74 5287 74 8400 65 8400 65 8400 65 8400 65 8400 65 - 8400 65 8400 65 8400 65 8400 65 9664 57 9664 57 9664 57 - 9664 57 9664 57 9664 57 9664 57 9664 57 10626 46 10626 46 - 10626 46 10626 46 10626 46 10626 46 10626 46 10626 46 10626 46 - 10626 46 10626 46 12145 41 12145 41 12145 41 12145 41 12145 41 - 13949 34 13949 34 13949 34 13949 34 13949 34 13949 34 13949 34 - 15315 28 15315 28 15315 28 15315 28 15315 28 15315 28 18796 15 - 18796 15 18796 15 18796 15 18796 15 18796 15 18796 15 18796 15 - 18796 15 18796 15 18796 15 18796 15 18796 15 21105 7 21105 7 - 21105 7 21105 7 21105 7 21105 7 21105 7 21105 7 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1} + ORDER BY 1 , 2 , 3 +} {2947 81 11 2947 81 11 2947 81 11 2947 81 11 2947 81 11 2947 81 11 + 2947 81 11 2947 81 11 2947 81 11 5287 74 10 5287 74 10 5287 74 10 + 5287 74 10 5287 74 10 5287 74 10 5287 74 10 8400 65 9 8400 65 9 + 8400 65 9 8400 65 9 8400 65 9 8400 65 9 8400 65 9 8400 65 9 + 8400 65 9 9664 57 8 9664 57 8 9664 57 8 9664 57 8 9664 57 8 + 9664 57 8 9664 57 8 9664 57 8 10626 46 7 10626 46 7 10626 46 7 + 10626 46 7 10626 46 7 10626 46 7 10626 46 7 10626 46 7 10626 46 7 + 10626 46 7 10626 46 7 12145 41 6 12145 41 6 12145 41 6 12145 41 6 + 12145 41 6 13949 34 5 13949 34 5 13949 34 5 13949 34 5 13949 34 5 + 13949 34 5 13949 34 5 15315 28 4 15315 28 4 15315 28 4 15315 28 4 + 15315 28 4 15315 28 4 18796 15 3 18796 15 3 18796 15 3 18796 15 3 + 18796 15 3 18796 15 3 18796 15 3 18796 15 3 18796 15 3 18796 15 3 + 18796 15 3 18796 15 3 18796 15 3 21105 7 2 21105 7 2 21105 7 2 + 21105 7 2 21105 7 2 21105 7 2 21105 7 2 21105 7 2 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1} do_execsql_test 5.1.3.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( PARTITION BY coalesce(a, '') RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS ) - ORDER BY 1 , 2 -} {777 113 777 113 777 113 777 113 777 113 805 250 805 250 - 805 250 805 250 805 250 805 250 805 250 822 158 822 158 - 822 158 822 158 822 158 822 158 840 247 840 247 840 247 - 840 247 840 247 840 247 840 247 840 247 840 247 840 247 - 840 247 840 247 840 247 870 158 870 158 870 158 870 158 - 870 158 870 158 899 113 899 113 899 113 899 113 899 113 - 899 113 899 113 899 113 899 113 934 223 934 223 934 223 - 934 223 934 223 934 223 934 223 934 223 938 102 938 102 - 938 102 938 102 938 102 938 102 938 102 938 102 938 102 - 938 102 938 102 938 148 938 148 938 148 938 148 938 148 - 938 148 938 148 938 148 959 224 959 224 959 224 959 224 - 959 224 959 224 959 224 979 133 979 133 979 133 979 133 - 979 133 979 133 979 133 979 133 979 133} + ORDER BY 1 , 2 , 3 +} {777 113 5 777 113 5 777 113 5 777 113 5 777 113 5 805 250 7 + 805 250 7 805 250 7 805 250 7 805 250 7 805 250 7 805 250 7 + 822 158 6 822 158 6 822 158 6 822 158 6 822 158 6 822 158 6 + 840 247 13 840 247 13 840 247 13 840 247 13 840 247 13 840 247 13 + 840 247 13 840 247 13 840 247 13 840 247 13 840 247 13 840 247 13 + 840 247 13 870 158 0 870 158 0 870 158 0 870 158 0 870 158 0 + 870 158 0 899 113 9 899 113 9 899 113 9 899 113 9 899 113 9 + 899 113 9 899 113 9 899 113 9 899 113 9 934 223 8 934 223 8 + 934 223 8 934 223 8 934 223 8 934 223 8 934 223 8 934 223 8 + 938 102 11 938 102 11 938 102 11 938 102 11 938 102 11 938 102 11 + 938 102 11 938 102 11 938 102 11 938 102 11 938 102 11 938 148 8 + 938 148 8 938 148 8 938 148 8 938 148 8 938 148 8 938 148 8 + 938 148 8 959 224 7 959 224 7 959 224 7 959 224 7 959 224 7 + 959 224 7 959 224 7 979 133 9 979 133 9 979 133 9 979 133 9 + 979 133 9 979 133 9 979 133 9 979 133 9 979 133 9} do_execsql_test 5.1.3.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( PARTITION BY coalesce(a, '') RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS ) - ORDER BY 1 , 2 -} {962 1 962 1 962 1 962 1 962 1 962 1 962 1 962 1 962 1 - 962 1 962 1 1264 1 1264 1 1264 1 1264 1 1264 1 1264 1 - 1264 1 1264 1 1366 1 1366 1 1366 1 1366 1 1366 1 1366 1 - 1519 1 1519 1 1519 1 1519 1 1519 1 1804 1 1804 1 1804 1 - 1804 1 1804 1 1804 1 1804 1 2050 1 2050 1 2050 1 2050 1 - 2050 1 2050 1 2309 1 2309 1 2309 1 2309 1 2309 1 2309 1 - 2309 1 2309 1 2340 1 2340 1 2340 1 2340 1 2340 1 2340 1 - 2340 1 2947 1 2947 1 2947 1 2947 1 2947 1 2947 1 2947 1 - 2947 1 2947 1 3113 1 3113 1 3113 1 3113 1 3113 1 3113 1 - 3113 1 3113 1 3113 1 3481 1 3481 1 3481 1 3481 1 3481 1 - 3481 1 3481 1 3481 1 3481 1 3481 1 3481 1 3481 1 3481 1} + ORDER BY 1 , 2 , 3 +} {962 1 1 962 1 1 962 1 1 962 1 1 962 1 1 962 1 1 962 1 1 + 962 1 1 962 1 1 962 1 1 962 1 1 1264 1 1 1264 1 1 1264 1 1 + 1264 1 1 1264 1 1 1264 1 1 1264 1 1 1264 1 1 1366 1 1 1366 1 1 + 1366 1 1 1366 1 1 1366 1 1 1366 1 1 1519 1 1 1519 1 1 1519 1 1 + 1519 1 1 1519 1 1 1804 1 1 1804 1 1 1804 1 1 1804 1 1 1804 1 1 + 1804 1 1 1804 1 1 2050 1 1 2050 1 1 2050 1 1 2050 1 1 2050 1 1 + 2050 1 1 2309 1 1 2309 1 1 2309 1 1 2309 1 1 2309 1 1 2309 1 1 + 2309 1 1 2309 1 1 2340 1 1 2340 1 1 2340 1 1 2340 1 1 2340 1 1 + 2340 1 1 2340 1 1 2947 1 1 2947 1 1 2947 1 1 2947 1 1 2947 1 1 + 2947 1 1 2947 1 1 2947 1 1 2947 1 1 3113 1 1 3113 1 1 3113 1 1 + 3113 1 1 3113 1 1 3113 1 1 3113 1 1 3113 1 1 3113 1 1 3481 1 1 + 3481 1 1 3481 1 1 3481 1 1 3481 1 1 3481 1 1 3481 1 1 3481 1 1 + 3481 1 1 3481 1 1 3481 1 1 3481 1 1 3481 1 1} do_execsql_test 5.1.4.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY a GROUPS 6 PRECEDING EXCLUDE NO OTHERS ) - ORDER BY 1 , 2 -} {870 158 870 158 870 158 870 158 870 158 870 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 959 102 959 102 - 959 102 959 102 959 102 959 102 959 102 959 102 959 102 - 959 102 959 102 959 102 959 102 959 102 959 102 959 102 - 959 102 959 102 959 102 959 113 959 113 959 113 959 113 - 959 113 959 158 959 158 959 158 959 158 959 158 959 158 - 959 158 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102} + ORDER BY 1 , 2 , 3 +} {870 158 0 870 158 0 870 158 0 870 158 0 870 158 0 870 158 0 + 934 158 8 934 158 8 934 158 8 934 158 8 934 158 8 934 158 8 + 934 158 8 934 158 8 934 158 21 934 158 21 934 158 21 934 158 21 + 934 158 21 934 158 21 934 158 21 934 158 21 934 158 21 934 158 21 + 934 158 21 934 158 21 934 158 21 934 158 27 934 158 27 934 158 27 + 934 158 27 934 158 27 934 158 27 959 102 50 959 102 50 959 102 50 + 959 102 50 959 102 50 959 102 50 959 102 50 959 102 50 959 102 50 + 959 102 50 959 102 50 959 102 58 959 102 58 959 102 58 959 102 58 + 959 102 58 959 102 58 959 102 58 959 102 58 959 113 39 959 113 39 + 959 113 39 959 113 39 959 113 39 959 158 34 959 158 34 959 158 34 + 959 158 34 959 158 34 959 158 34 959 158 34 979 102 53 979 102 53 + 979 102 53 979 102 53 979 102 53 979 102 53 979 102 53 979 102 56 + 979 102 56 979 102 56 979 102 56 979 102 56 979 102 56 979 102 56 + 979 102 56 979 102 56 979 102 59 979 102 59 979 102 59 979 102 59 + 979 102 59 979 102 59 979 102 59 979 102 59 979 102 59} do_execsql_test 5.1.4.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY a GROUPS 6 PRECEDING EXCLUDE NO OTHERS ) - ORDER BY 1 , 2 -} {2050 1 2050 1 2050 1 2050 1 2050 1 2050 1 4359 7 4359 7 - 4359 7 4359 7 4359 7 4359 7 4359 7 4359 7 7840 15 7840 15 - 7840 15 7840 15 7840 15 7840 15 7840 15 7840 15 7840 15 - 7840 15 7840 15 7840 15 7840 15 9206 28 9206 28 9206 28 - 9206 28 9206 28 9206 28 11010 34 11010 34 11010 34 11010 34 - 11010 34 11010 34 11010 34 12368 74 12368 74 12368 74 12368 74 - 12368 74 12368 74 12368 74 12529 41 12529 41 12529 41 12529 41 - 12529 41 12705 57 12705 57 12705 57 12705 57 12705 57 12705 57 - 12705 57 12705 57 13491 46 13491 46 13491 46 13491 46 13491 46 - 13491 46 13491 46 13491 46 13491 46 13491 46 13491 46 13509 65 - 13509 65 13509 65 13509 65 13509 65 13509 65 13509 65 13509 65 - 13509 65 13949 81 13949 81 13949 81 13949 81 13949 81 13949 81 - 13949 81 13949 81 13949 81} + ORDER BY 1 , 2 , 3 +} {2050 1 1 2050 1 1 2050 1 1 2050 1 1 2050 1 1 2050 1 1 4359 7 2 + 4359 7 2 4359 7 2 4359 7 2 4359 7 2 4359 7 2 4359 7 2 4359 7 2 + 7840 15 3 7840 15 3 7840 15 3 7840 15 3 7840 15 3 7840 15 3 + 7840 15 3 7840 15 3 7840 15 3 7840 15 3 7840 15 3 7840 15 3 + 7840 15 3 9206 28 4 9206 28 4 9206 28 4 9206 28 4 9206 28 4 + 9206 28 4 11010 34 5 11010 34 5 11010 34 5 11010 34 5 11010 34 5 + 11010 34 5 11010 34 5 12368 74 10 12368 74 10 12368 74 10 + 12368 74 10 12368 74 10 12368 74 10 12368 74 10 12529 41 6 + 12529 41 6 12529 41 6 12529 41 6 12529 41 6 12705 57 8 12705 57 8 + 12705 57 8 12705 57 8 12705 57 8 12705 57 8 12705 57 8 12705 57 8 + 13491 46 7 13491 46 7 13491 46 7 13491 46 7 13491 46 7 13491 46 7 + 13491 46 7 13491 46 7 13491 46 7 13491 46 7 13491 46 7 13509 65 9 + 13509 65 9 13509 65 9 13509 65 9 13509 65 9 13509 65 9 13509 65 9 + 13509 65 9 13509 65 9 13949 81 11 13949 81 11 13949 81 11 + 13949 81 11 13949 81 11 13949 81 11 13949 81 11 13949 81 11 + 13949 81 11} do_execsql_test 5.1.5.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY c RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE NO OTHERS ) - ORDER BY 1 , 2 -} {102 102 113 113 113 113 133 133 148 148 160 158 160 158 - 160 158 208 208 224 223 224 223 239 234 239 234 239 234 - 252 247 257 247 257 247 257 250 257 252 295 295 309 309 - 336 330 336 330 336 330 346 346 355 354 355 354 355 354 - 399 393 399 393 399 393 399 393 399 393 412 412 421 421 - 430 430 443 443 480 480 480 480 574 572 574 572 607 607 - 618 618 618 618 634 627 634 627 634 627 634 627 634 629 - 652 652 667 660 671 667 671 667 671 667 671 667 683 683 - 711 705 716 705 716 711 730 726 730 726 762 759 768 759 - 768 762 768 762 777 777 792 786 794 786 794 786 794 790 - 805 805 822 822 845 839 845 839 845 839 845 839 845 839 - 870 870 870 870 870 870 899 899 911 911 934 929 938 929 - 938 934 938 934 963 959 963 959 979 979} + ORDER BY 1 , 2 , 3 +} {102 102 1 113 113 2 113 113 2 133 133 1 148 148 1 160 158 2 + 160 158 2 160 158 2 208 208 1 224 223 2 224 223 2 239 234 3 + 239 234 3 239 234 3 252 247 3 257 247 5 257 247 5 257 250 4 + 257 252 3 295 295 1 309 309 1 336 330 3 336 330 3 336 330 3 + 346 346 1 355 354 2 355 354 2 355 354 2 399 393 4 399 393 4 + 399 393 4 399 393 4 399 393 4 412 412 1 421 421 1 430 430 1 + 443 443 1 480 480 2 480 480 2 574 572 2 574 572 2 607 607 1 + 618 618 2 618 618 2 634 627 4 634 627 4 634 627 4 634 627 4 + 634 629 3 652 652 1 667 660 2 671 667 3 671 667 3 671 667 3 + 671 667 3 683 683 1 711 705 2 716 705 3 716 711 2 730 726 2 + 730 726 2 762 759 2 768 759 4 768 762 3 768 762 3 777 777 1 + 792 786 3 794 786 4 794 786 4 794 790 3 805 805 1 822 822 1 + 845 839 5 845 839 5 845 839 5 845 839 5 845 839 5 870 870 2 + 870 870 2 870 870 2 899 899 1 911 911 1 934 929 2 938 929 4 + 938 934 3 938 934 3 963 959 2 963 959 2 979 979 1} do_execsql_test 5.1.5.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY c RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE NO OTHERS ) - ORDER BY 1 , 2 -} {{} 1 {} 5 {} 6 {} 6 {} 8 {} 9 {} 25 {} 34 {} 36 {} 38 - {} 38 {} 40 {} 41 {} 43 {} 43 {} 50 {} 60 {} 61 {} 64 - {} 64 {} 67 {} 68 {} 69 {} 70 {} 72 {} 78 {} 78 {} 78 - {} 85 {} 85 133 4 223 10 223 11 226 2 226 2 239 12 239 13 - 239 14 247 15 257 18 257 19 295 20 309 21 335 22 335 23 - 335 24 421 35 443 37 504 16 504 17 607 42 683 56 710 26 - 710 27 710 27 711 59 759 62 759 63 777 66 805 71 899 81 - 911 82 929 83 929 84 979 89 1334 51 1416 57 1416 58 1584 29 - 1584 29 1584 31 1584 32 1584 32 1891 49 1922 87 1922 88 - 2005 52 2005 52 2005 54 2005 55 2518 45 2518 46 2518 46 - 2518 48 2523 73 2523 73 2523 75 2523 76 2523 77} + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 5 4 {} 6 5 {} 6 5 {} 8 6 {} 9 7 {} 25 23 {} 34 29 + {} 36 31 {} 38 33 {} 38 33 {} 40 34 {} 41 35 {} 43 37 {} 43 37 + {} 50 42 {} 60 51 {} 61 52 {} 64 55 {} 64 55 {} 67 57 {} 68 58 + {} 69 59 {} 70 60 {} 72 62 {} 78 67 {} 78 67 {} 78 67 {} 85 72 + {} 85 72 133 4 3 223 10 8 223 11 9 226 2 2 226 2 2 239 12 10 + 239 13 11 239 14 12 247 15 13 257 18 16 257 19 17 295 20 18 + 309 21 19 335 22 20 335 23 21 335 24 22 421 35 30 443 37 32 + 504 16 14 504 17 15 607 42 36 683 56 47 710 26 24 710 27 25 + 710 27 25 711 59 50 759 62 53 759 63 54 777 66 56 805 71 61 + 899 81 68 911 82 69 929 83 70 929 84 71 979 89 75 1334 51 43 + 1416 57 48 1416 58 49 1584 29 26 1584 29 26 1584 31 27 1584 32 28 + 1584 32 28 1891 49 41 1922 87 73 1922 88 74 2005 52 44 2005 52 44 + 2005 54 45 2005 55 46 2518 45 38 2518 46 39 2518 46 39 2518 48 40 + 2523 73 63 2523 73 63 2523 75 64 2523 76 65 2523 77 66} + +do_execsql_test 5.1.6.1 { + SELECT max(c) OVER win, + min(c) OVER win, + count(a) OVER win + FROM t3 + WINDOW win AS ( ORDER BY c RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE NO OTHERS ) + ORDER BY 1 , 2 , 3 +} {102 102 1 113 113 2 113 113 2 133 133 1 148 148 1 158 158 1 + 158 158 1 160 160 1 208 208 1 223 223 1 224 224 1 234 234 1 + 238 238 1 239 239 1 247 247 1 250 250 1 252 252 1 256 256 1 + 257 257 1 295 295 1 309 309 1 330 330 1 335 335 1 336 336 1 + 346 346 1 354 354 1 355 355 1 355 355 1 393 393 2 393 393 2 + 398 398 1 399 399 1 399 399 1 412 412 1 421 421 1 430 430 1 + 443 443 1 480 480 2 480 480 2 572 572 1 574 574 1 607 607 1 + 618 618 2 618 618 2 627 627 1 629 629 1 629 629 1 633 633 1 + 634 634 1 652 652 1 660 660 1 667 667 1 667 667 1 670 670 1 + 671 671 1 683 683 1 705 705 1 711 711 1 716 716 1 726 726 1 + 730 730 1 759 759 1 762 762 1 768 768 2 768 768 2 777 777 1 + 786 786 1 790 790 1 792 792 1 794 794 1 805 805 1 822 822 1 + 839 839 2 839 839 2 840 840 1 844 844 1 845 845 1 870 870 2 + 870 870 2 870 870 2 899 899 1 911 911 1 929 929 1 934 934 1 + 938 938 2 938 938 2 959 959 1 963 963 1 979 979 1} + +do_execsql_test 5.1.6.2 { + SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, + rank() OVER win, + dense_rank() OVER win + FROM t3 + WINDOW win AS ( ORDER BY c RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE NO OTHERS ) + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 5 4 {} 6 5 {} 6 5 {} 8 6 {} 9 7 {} 11 9 {} 12 10 + {} 13 11 {} 16 14 {} 17 15 {} 18 16 {} 22 20 {} 24 22 {} 25 23 + {} 26 24 {} 31 27 {} 34 29 {} 36 31 {} 38 33 {} 38 33 {} 40 34 + {} 41 35 {} 43 37 {} 43 37 {} 49 41 {} 50 42 {} 51 43 {} 54 45 + {} 59 50 {} 60 51 {} 61 52 {} 63 54 {} 64 55 {} 64 55 {} 67 57 + {} 68 58 {} 69 59 {} 70 60 {} 72 62 {} 75 64 {} 76 65 {} 78 67 + {} 78 67 {} 78 67 {} 84 71 {} 85 72 {} 85 72 133 4 3 223 10 8 + 226 2 2 226 2 2 239 14 12 247 15 13 257 19 17 295 20 18 + 309 21 19 335 23 21 421 35 30 443 37 32 607 42 36 627 45 38 + 633 48 40 671 55 46 683 56 47 705 57 48 710 27 25 710 27 25 + 711 58 49 759 62 53 777 66 56 786 29 26 786 29 26 798 32 28 + 798 32 28 805 71 61 845 77 66 899 81 68 911 82 69 929 83 70 + 959 87 73 963 88 74 979 89 75 1258 46 39 1258 46 39 1334 52 44 + 1334 52 44 1678 73 63 1678 73 63} + +do_execsql_test 5.1.7.1 { + SELECT max(c) OVER win, + min(c) OVER win, + count(a) OVER win + FROM t3 + WINDOW win AS ( ORDER BY c , b , a + ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS ) + ORDER BY 1 , 2 , 3 +} {979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 113 81 979 113 82 979 133 80 979 148 79 979 158 77 + 979 158 78 979 160 77 979 208 76 979 223 75 979 224 74 979 234 73 + 979 238 72 979 239 71 979 247 70 979 250 69 979 252 68 979 256 67 + 979 257 66 979 295 65 979 309 64 979 330 63 979 335 62 979 336 61 + 979 346 60 979 354 59 979 355 58 979 355 58 979 393 56 979 393 57 + 979 398 55 979 399 54 979 399 54 979 412 53 979 421 52 979 430 51 + 979 443 50 979 480 48 979 480 49 979 572 47 979 574 46 979 607 45 + 979 618 43 979 618 44 979 627 42 979 629 41 979 629 41 979 633 40 + 979 634 39 979 652 38 979 660 37 979 667 36 979 667 36 979 670 35 + 979 671 34 979 683 33 979 705 32 979 711 31 979 716 30 979 726 29 + 979 730 28 979 759 27 979 762 26 979 768 24 979 768 25 979 777 23 + 979 786 22 979 790 21 979 792 20 979 794 19 979 805 18 979 822 17 + 979 839 15 979 839 16 979 840 14 979 844 13 979 845 12 979 870 10 + 979 870 11 979 870 11 979 899 9 979 911 8 979 929 7} + +do_execsql_test 5.1.7.2 { + SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, + rank() OVER win, + dense_rank() OVER win + FROM t3 + WINDOW win AS ( ORDER BY c , b , a + ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS ) + ORDER BY 1 , 2 , 3 +} {3830 89 89 4741 88 88 5640 84 84 5640 85 85 5640 86 86 5640 87 87 + 6485 81 81 6485 82 82 6485 83 83 7324 80 80 8163 78 78 8163 79 79 + 8968 73 73 8968 74 74 8968 75 75 8968 76 76 8968 77 77 9745 69 69 + 9745 70 70 9745 71 71 9745 72 72 10504 65 65 10504 66 66 + 10504 67 67 10504 68 68 11215 64 64 11920 63 63 12603 62 62 + 13274 60 60 13274 61 61 13941 59 59 14608 55 55 14608 56 56 + 14608 57 57 14608 58 58 15241 54 54 15870 53 53 16499 52 52 + 17126 49 49 17126 50 50 17126 51 51 17733 44 44 17733 45 45 + 17733 46 46 17733 47 47 17733 48 48 18176 42 42 18176 43 43 + 18597 40 40 18597 41 41 18996 39 39 19395 37 37 19395 38 38 + 19788 36 36 20181 35 35 20536 34 34 20891 30 30 20891 31 31 + 20891 32 32 20891 33 33 21226 28 28 21226 29 29 21535 27 27 + 21830 26 26 22087 22 22 22087 23 23 22087 24 24 22087 25 25 + 22334 21 21 22573 17 17 22573 18 18 22573 19 19 22573 20 20 + 22796 11 11 22796 12 12 22796 13 13 22796 14 14 22796 15 15 + 22796 16 16 22929 10 10 23042 9 9 23155 1 1 23155 2 2 23155 3 3 + 23155 4 4 23155 5 5 23155 6 6 23155 7 7 23155 8 8} do_execsql_test 5.2.1.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW ) - ORDER BY 1 , 2 -} {963 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 113} + ORDER BY 1 , 2 , 3 +} {963 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 113 82} do_execsql_test 5.2.1.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW ) - ORDER BY 1 , 2 -} {22176 1 22192 1 22196 1 22226 1 22244 1 22256 1 22310 1 - 22316 1 22316 1 22350 1 22378 1 22396 1 22444 1 22450 1 - 22472 1 22484 1 22488 1 22488 1 22522 1 22526 1 22526 1 - 22528 1 22548 1 22712 1 22734 1 22756 1 22756 1 22762 1 - 22762 1 22800 1 22800 1 22820 1 22846 1 22860 1 22898 1 - 22908 1 22916 1 22932 1 23022 1 23042 1 23042 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 23155 1 - 23155 1 23155 1 23155 1 23155 1 23155 1} + ORDER BY 1 , 2 , 3 +} {22176 1 1 22192 1 1 22196 1 1 22226 1 1 22244 1 1 22256 1 1 + 22310 1 1 22316 1 1 22316 1 1 22350 1 1 22378 1 1 22396 1 1 + 22444 1 1 22450 1 1 22472 1 1 22484 1 1 22488 1 1 22488 1 1 + 22522 1 1 22526 1 1 22526 1 1 22528 1 1 22548 1 1 22712 1 1 + 22734 1 1 22756 1 1 22756 1 1 22762 1 1 22762 1 1 22800 1 1 + 22800 1 1 22820 1 1 22846 1 1 22860 1 1 22898 1 1 22908 1 1 + 22916 1 1 22932 1 1 23022 1 1 23042 1 1 23042 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1 + 23155 1 1 23155 1 1 23155 1 1 23155 1 1 23155 1 1} do_execsql_test 5.2.2.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW ) - ORDER BY 1 , 2 -} {839 113 899 113 899 113 899 113 899 113 899 113 899 113 - 899 113 899 113 899 113 899 113 899 113 899 113 899 113 - 899 113 899 234 963 113 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 113 979 113 979 113 979 113 979 113 - 979 113 979 113 979 113 979 113 979 113 979 113 979 113 - 979 113 979 113 979 113 979 113 979 113} + ORDER BY 1 , 2 , 3 +} {839 113 8 899 113 8 899 113 8 899 113 8 899 113 8 899 113 8 + 899 113 8 899 113 8 899 113 15 899 113 15 899 113 15 899 113 15 + 899 113 15 899 113 15 899 113 15 899 234 8 963 113 24 979 102 43 + 979 102 43 979 102 43 979 102 43 979 102 43 979 102 43 979 102 43 + 979 102 43 979 102 43 979 102 43 979 102 48 979 102 48 979 102 48 + 979 102 48 979 102 48 979 102 55 979 102 55 979 102 55 979 102 55 + 979 102 55 979 102 55 979 102 55 979 102 61 979 102 61 979 102 61 + 979 102 61 979 102 61 979 102 61 979 102 74 979 102 74 979 102 74 + 979 102 74 979 102 74 979 102 74 979 102 74 979 102 74 979 102 74 + 979 102 74 979 102 74 979 102 74 979 102 74 979 102 82 979 102 82 + 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 113 24 979 113 24 979 113 24 979 113 24 979 113 24 979 113 24 + 979 113 24 979 113 24 979 113 32 979 113 32 979 113 32 979 113 32 + 979 113 32 979 113 32 979 113 32 979 113 32 979 113 43} do_execsql_test 5.2.2.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW ) - ORDER BY 1 , 2 -} {2048 81 2108 81 2108 81 2690 81 2834 81 2947 81 2947 81 - 2947 81 2947 81 4482 74 4616 74 4844 74 4866 74 5287 74 - 5287 74 5287 74 7421 65 7437 65 7717 65 8045 65 8267 65 - 8400 65 8400 65 8400 65 8400 65 8735 57 9329 57 9664 57 - 9664 57 9664 57 9664 57 9664 57 9664 57 9959 46 10331 46 - 10626 46 10626 46 10626 46 10626 46 10626 46 10626 46 10626 46 - 10626 46 10626 46 11368 41 11516 41 12032 41 12145 41 12145 41 - 12990 34 13104 34 13949 34 13949 34 13949 34 13949 34 13949 34 - 14556 28 14708 28 15315 28 15315 28 15315 28 15315 28 18085 15 - 18091 15 18163 15 18397 15 18403 15 18403 15 18549 15 18796 15 - 18796 15 18796 15 18796 15 18796 15 18796 15 20194 7 20478 7 - 20796 7 20866 7 20882 7 21105 7 21105 7 21105 7 22488 1 - 22526 1 22756 1 22800 1 23155 1 23155 1} + ORDER BY 1 , 2 , 3 +} {2048 81 11 2108 81 11 2108 81 11 2690 81 11 2834 81 11 2947 81 11 + 2947 81 11 2947 81 11 2947 81 11 4482 74 10 4616 74 10 4844 74 10 + 4866 74 10 5287 74 10 5287 74 10 5287 74 10 7421 65 9 7437 65 9 + 7717 65 9 8045 65 9 8267 65 9 8400 65 9 8400 65 9 8400 65 9 + 8400 65 9 8735 57 8 9329 57 8 9664 57 8 9664 57 8 9664 57 8 + 9664 57 8 9664 57 8 9664 57 8 9959 46 7 10331 46 7 10626 46 7 + 10626 46 7 10626 46 7 10626 46 7 10626 46 7 10626 46 7 10626 46 7 + 10626 46 7 10626 46 7 11368 41 6 11516 41 6 12032 41 6 12145 41 6 + 12145 41 6 12990 34 5 13104 34 5 13949 34 5 13949 34 5 13949 34 5 + 13949 34 5 13949 34 5 14556 28 4 14708 28 4 15315 28 4 15315 28 4 + 15315 28 4 15315 28 4 18085 15 3 18091 15 3 18163 15 3 18397 15 3 + 18403 15 3 18403 15 3 18549 15 3 18796 15 3 18796 15 3 18796 15 3 + 18796 15 3 18796 15 3 18796 15 3 20194 7 2 20478 7 2 20796 7 2 + 20866 7 2 20882 7 2 21105 7 2 21105 7 2 21105 7 2 22488 1 1 + 22526 1 1 22756 1 1 22800 1 1 23155 1 1 23155 1 1} do_execsql_test 5.2.3.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( PARTITION BY coalesce(a, '') RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW ) - ORDER BY 1 , 2 -} {667 158 671 250 759 158 768 113 777 113 777 113 777 113 - 777 252 792 247 805 250 805 250 805 250 805 250 805 250 - 805 398 822 158 822 158 822 158 822 158 822 346 839 113 - 840 247 840 247 840 247 840 247 840 247 840 247 840 247 - 840 247 840 247 840 247 840 247 840 393 845 224 870 102 - 870 158 870 158 870 158 870 158 870 355 899 113 899 113 - 899 113 899 113 899 113 899 113 899 113 899 234 911 223 - 929 148 934 223 934 223 934 223 934 223 934 223 934 223 - 934 239 938 102 938 102 938 102 938 102 938 102 938 102 - 938 102 938 102 938 102 938 148 938 148 938 148 938 148 - 938 148 938 148 938 160 938 208 959 224 959 224 959 224 - 959 224 959 224 959 238 963 133 979 133 979 133 979 133 - 979 133 979 133 979 133 979 133 979 330} + ORDER BY 1 , 2 , 3 +} {667 158 0 671 250 6 759 158 5 768 113 4 777 113 4 777 113 4 + 777 113 4 777 252 4 792 247 12 805 250 6 805 250 6 805 250 6 + 805 250 6 805 250 6 805 398 6 822 158 5 822 158 5 822 158 5 + 822 158 5 822 346 5 839 113 8 840 247 12 840 247 12 840 247 12 + 840 247 12 840 247 12 840 247 12 840 247 12 840 247 12 840 247 12 + 840 247 12 840 247 12 840 393 12 845 224 6 870 102 10 870 158 0 + 870 158 0 870 158 0 870 158 0 870 355 0 899 113 8 899 113 8 + 899 113 8 899 113 8 899 113 8 899 113 8 899 113 8 899 234 8 + 911 223 7 929 148 7 934 223 7 934 223 7 934 223 7 934 223 7 + 934 223 7 934 223 7 934 239 7 938 102 10 938 102 10 938 102 10 + 938 102 10 938 102 10 938 102 10 938 102 10 938 102 10 938 102 10 + 938 148 7 938 148 7 938 148 7 938 148 7 938 148 7 938 148 7 + 938 160 7 938 208 10 959 224 6 959 224 6 959 224 6 959 224 6 + 959 224 6 959 238 6 963 133 8 979 133 8 979 133 8 979 133 8 + 979 133 8 979 133 8 979 133 8 979 133 8 979 330 8} do_execsql_test 5.2.3.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( PARTITION BY coalesce(a, '') RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW ) - ORDER BY 1 , 2 -} {295 1 335 1 607 1 667 1 742 1 759 1 845 1 890 1 929 1 - 959 1 962 1 962 1 962 1 962 1 962 1 962 1 962 1 962 1 - 962 1 1264 1 1264 1 1264 1 1264 1 1264 1 1264 1 1366 1 - 1366 1 1366 1 1366 1 1383 1 1398 1 1406 1 1421 1 1519 1 - 1519 1 1535 1 1651 1 1669 1 1682 1 1695 1 1804 1 1804 1 - 1804 1 1804 1 1804 1 1897 1 1919 1 2000 1 2048 1 2050 1 - 2050 1 2070 1 2086 1 2108 1 2108 1 2134 1 2150 1 2309 1 - 2309 1 2309 1 2340 1 2340 1 2340 1 2430 1 2690 1 2758 1 - 2770 1 2776 1 2834 1 2848 1 2947 1 2947 1 2947 1 2947 1 - 2980 1 3082 1 3088 1 3088 1 3113 1 3113 1 3113 1 3113 1 - 3234 1 3481 1 3481 1 3481 1 3481 1 3481 1 3481 1} + ORDER BY 1 , 2 , 3 +} {295 1 1 335 1 1 607 1 1 667 1 1 742 1 1 759 1 1 845 1 1 + 890 1 1 929 1 1 959 1 1 962 1 1 962 1 1 962 1 1 962 1 1 + 962 1 1 962 1 1 962 1 1 962 1 1 962 1 1 1264 1 1 1264 1 1 + 1264 1 1 1264 1 1 1264 1 1 1264 1 1 1366 1 1 1366 1 1 1366 1 1 + 1366 1 1 1383 1 1 1398 1 1 1406 1 1 1421 1 1 1519 1 1 1519 1 1 + 1535 1 1 1651 1 1 1669 1 1 1682 1 1 1695 1 1 1804 1 1 1804 1 1 + 1804 1 1 1804 1 1 1804 1 1 1897 1 1 1919 1 1 2000 1 1 2048 1 1 + 2050 1 1 2050 1 1 2070 1 1 2086 1 1 2108 1 1 2108 1 1 2134 1 1 + 2150 1 1 2309 1 1 2309 1 1 2309 1 1 2340 1 1 2340 1 1 2340 1 1 + 2430 1 1 2690 1 1 2758 1 1 2770 1 1 2776 1 1 2834 1 1 2848 1 1 + 2947 1 1 2947 1 1 2947 1 1 2947 1 1 2980 1 1 3082 1 1 3088 1 1 + 3088 1 1 3113 1 1 3113 1 1 3113 1 1 3113 1 1 3234 1 1 3481 1 1 + 3481 1 1 3481 1 1 3481 1 1 3481 1 1 3481 1 1} do_execsql_test 5.2.4.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY a GROUPS 6 PRECEDING EXCLUDE CURRENT ROW ) - ORDER BY 1 , 2 -} {667 158 870 158 870 158 870 158 870 158 870 355 911 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 959 102 - 959 102 959 102 959 102 959 102 959 102 959 102 959 102 - 959 102 959 102 959 102 959 102 959 102 959 102 959 102 - 959 102 959 102 959 102 959 113 959 113 959 113 959 113 - 959 113 959 158 959 158 959 158 959 158 959 158 959 158 - 959 158 963 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102} + ORDER BY 1 , 2 , 3 +} {667 158 0 870 158 0 870 158 0 870 158 0 870 158 0 870 355 0 + 911 158 7 934 158 7 934 158 7 934 158 7 934 158 7 934 158 7 + 934 158 7 934 158 7 934 158 20 934 158 20 934 158 20 934 158 20 + 934 158 20 934 158 20 934 158 20 934 158 20 934 158 20 934 158 20 + 934 158 20 934 158 20 934 158 20 934 158 26 934 158 26 934 158 26 + 934 158 26 934 158 26 934 158 26 934 158 33 959 102 49 959 102 49 + 959 102 49 959 102 49 959 102 49 959 102 49 959 102 49 959 102 49 + 959 102 49 959 102 49 959 102 57 959 102 57 959 102 57 959 102 57 + 959 102 57 959 102 57 959 102 57 959 102 57 959 113 38 959 113 38 + 959 113 38 959 113 38 959 113 49 959 158 33 959 158 33 959 158 33 + 959 158 33 959 158 33 959 158 33 959 158 38 963 102 58 979 102 52 + 979 102 52 979 102 52 979 102 52 979 102 52 979 102 52 979 102 52 + 979 102 55 979 102 55 979 102 55 979 102 55 979 102 55 979 102 55 + 979 102 55 979 102 55 979 102 55 979 102 58 979 102 58 979 102 58 + 979 102 58 979 102 58 979 102 58 979 102 58 979 102 58} do_execsql_test 5.2.4.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY a GROUPS 6 PRECEDING EXCLUDE CURRENT ROW ) - ORDER BY 1 , 2 -} {1383 1 1421 1 1651 1 1695 1 2050 1 2050 1 3448 7 3732 7 - 4050 7 4120 7 4136 7 4359 7 4359 7 4359 7 7129 15 7135 15 - 7207 15 7441 15 7447 15 7447 15 7593 15 7840 15 7840 15 - 7840 15 7840 15 7840 15 7840 15 8447 28 8599 28 9206 28 - 9206 28 9206 28 9206 28 10051 34 10165 34 11010 34 11010 34 - 11010 34 11010 34 11010 34 11563 74 11697 74 11752 41 11776 57 - 11900 41 11925 74 11947 74 12368 74 12368 74 12368 74 12370 57 - 12416 41 12529 41 12529 41 12530 65 12546 65 12705 57 12705 57 - 12705 57 12705 57 12705 57 12705 57 12824 46 12826 65 13050 81 - 13110 81 13110 81 13154 65 13196 46 13376 65 13491 46 13491 46 - 13491 46 13491 46 13491 46 13491 46 13491 46 13491 46 13491 46 - 13509 65 13509 65 13509 65 13509 65 13692 81 13836 81 13949 81 - 13949 81 13949 81 13949 81} + ORDER BY 1 , 2 , 3 +} {1383 1 1 1421 1 1 1651 1 1 1695 1 1 2050 1 1 2050 1 1 3448 7 2 + 3732 7 2 4050 7 2 4120 7 2 4136 7 2 4359 7 2 4359 7 2 4359 7 2 + 7129 15 3 7135 15 3 7207 15 3 7441 15 3 7447 15 3 7447 15 3 + 7593 15 3 7840 15 3 7840 15 3 7840 15 3 7840 15 3 7840 15 3 + 7840 15 3 8447 28 4 8599 28 4 9206 28 4 9206 28 4 9206 28 4 + 9206 28 4 10051 34 5 10165 34 5 11010 34 5 11010 34 5 11010 34 5 + 11010 34 5 11010 34 5 11563 74 10 11697 74 10 11752 41 6 + 11776 57 8 11900 41 6 11925 74 10 11947 74 10 12368 74 10 + 12368 74 10 12368 74 10 12370 57 8 12416 41 6 12529 41 6 + 12529 41 6 12530 65 9 12546 65 9 12705 57 8 12705 57 8 12705 57 8 + 12705 57 8 12705 57 8 12705 57 8 12824 46 7 12826 65 9 + 13050 81 11 13110 81 11 13110 81 11 13154 65 9 13196 46 7 + 13376 65 9 13491 46 7 13491 46 7 13491 46 7 13491 46 7 13491 46 7 + 13491 46 7 13491 46 7 13491 46 7 13491 46 7 13509 65 9 13509 65 9 + 13509 65 9 13509 65 9 13692 81 11 13836 81 11 13949 81 11 + 13949 81 11 13949 81 11 13949 81 11} do_execsql_test 5.2.5.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY c RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE CURRENT ROW ) - ORDER BY 1 , 2 -} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} 113 113 113 113 158 158 160 158 160 158 223 223 - 224 224 238 234 239 234 239 238 252 250 256 252 257 247 - 257 247 257 250 335 330 336 330 336 335 355 354 355 354 - 355 355 399 393 399 393 399 393 399 393 399 393 480 480 - 480 480 572 572 574 574 618 618 618 618 633 629 634 627 - 634 627 634 627 634 629 667 667 670 667 671 667 671 667 - 671 667 711 711 711 711 716 705 726 726 730 730 762 762 - 768 759 768 762 768 762 792 790 792 790 794 786 794 786 - 844 839 845 839 845 839 845 839 845 839 870 870 870 870 - 870 870 934 934 938 929 938 934 938 934 959 959 963 963} + ORDER BY 1 , 2 , 3 +} {{} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 113 113 1 + 113 113 1 158 158 1 160 158 1 160 158 2 223 223 1 224 224 1 + 238 234 2 239 234 2 239 238 2 252 250 2 256 252 2 257 247 4 + 257 247 4 257 250 3 335 330 2 336 330 2 336 335 2 355 354 1 + 355 354 2 355 355 1 399 393 3 399 393 3 399 393 3 399 393 3 + 399 393 4 480 480 1 480 480 1 572 572 1 574 574 1 618 618 1 + 618 618 1 633 629 2 634 627 3 634 627 3 634 627 4 634 629 3 + 667 667 1 670 667 2 671 667 2 671 667 2 671 667 3 711 711 1 + 711 711 1 716 705 2 726 726 1 730 730 1 762 762 1 768 759 3 + 768 762 2 768 762 2 792 790 2 792 790 2 794 786 3 794 786 3 + 844 839 4 845 839 4 845 839 4 845 839 4 845 839 4 870 870 1 + 870 870 1 870 870 2 934 934 1 938 929 3 938 934 2 938 934 2 + 959 959 1 963 963 1} do_execsql_test 5.2.5.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY c RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE CURRENT ROW ) - ORDER BY 1 , 2 -} {{} 1 {} 4 {} 5 {} 6 {} 6 {} 8 {} 9 {} 10 {} 14 {} 15 - {} 19 {} 20 {} 21 {} 23 {} 25 {} 34 {} 35 {} 36 {} 37 - {} 38 {} 38 {} 40 {} 41 {} 42 {} 43 {} 43 {} 50 {} 56 - {} 60 {} 61 {} 62 {} 64 {} 64 {} 66 {} 67 {} 68 {} 69 - {} 70 {} 71 {} 72 {} 78 {} 78 {} 78 {} 81 {} 82 {} 83 - {} 85 {} 85 {} 89 113 2 113 2 223 11 239 12 239 13 257 18 - 335 22 335 24 355 27 355 27 504 16 504 17 705 58 710 26 - 711 57 711 59 759 63 929 84 959 88 963 87 1185 32 1185 32 - 1191 29 1191 29 1334 51 1334 55 1338 52 1338 52 1584 31 - 1678 77 1684 73 1684 73 1885 48 1889 46 1889 46 1891 45 - 1891 49 2005 54 2523 75 2523 76} + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 4 3 {} 5 4 {} 6 5 {} 6 5 {} 8 6 {} 9 7 {} 10 8 + {} 14 12 {} 15 13 {} 19 17 {} 20 18 {} 21 19 {} 23 21 {} 25 23 + {} 34 29 {} 35 30 {} 36 31 {} 37 32 {} 38 33 {} 38 33 {} 40 34 + {} 41 35 {} 42 36 {} 43 37 {} 43 37 {} 50 42 {} 56 47 {} 60 51 + {} 61 52 {} 62 53 {} 64 55 {} 64 55 {} 66 56 {} 67 57 {} 68 58 + {} 69 59 {} 70 60 {} 71 61 {} 72 62 {} 78 67 {} 78 67 {} 78 67 + {} 81 68 {} 82 69 {} 83 70 {} 85 72 {} 85 72 {} 89 75 113 2 2 + 113 2 2 223 11 9 239 12 10 239 13 11 257 18 16 335 22 20 + 335 24 22 355 27 25 355 27 25 504 16 14 504 17 15 705 58 49 + 710 26 24 711 57 48 711 59 50 759 63 54 929 84 71 959 88 74 + 963 87 73 1185 32 28 1185 32 28 1191 29 26 1191 29 26 1334 51 43 + 1334 55 46 1338 52 44 1338 52 44 1584 31 27 1678 77 66 1684 73 63 + 1684 73 63 1885 48 40 1889 46 39 1889 46 39 1891 45 38 1891 49 41 + 2005 54 45 2523 75 64 2523 76 65} + +do_execsql_test 5.2.6.1 { + SELECT max(c) OVER win, + min(c) OVER win, + count(a) OVER win + FROM t3 + WINDOW win AS ( ORDER BY c RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW ) + ORDER BY 1 , 2 , 3 +} {{} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 113 113 1 + 113 113 1 158 158 0 158 158 1 355 355 0 355 355 1 393 393 1 + 393 393 1 399 399 0 399 399 1 480 480 1 480 480 1 618 618 1 + 618 618 1 629 629 0 629 629 1 667 667 0 667 667 1 768 768 1 + 768 768 1 839 839 1 839 839 1 870 870 1 870 870 1 870 870 2 + 938 938 1 938 938 1} + +do_execsql_test 5.2.6.2 { + SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, + rank() OVER win, + dense_rank() OVER win + FROM t3 + WINDOW win AS ( ORDER BY c RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW ) + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 4 3 {} 5 4 {} 6 5 {} 6 5 {} 8 6 {} 9 7 {} 10 8 + {} 11 9 {} 12 10 {} 13 11 {} 14 12 {} 15 13 {} 16 14 {} 17 15 + {} 18 16 {} 19 17 {} 20 18 {} 21 19 {} 22 20 {} 23 21 {} 24 22 + {} 25 23 {} 26 24 {} 31 27 {} 34 29 {} 35 30 {} 36 31 {} 37 32 + {} 38 33 {} 38 33 {} 40 34 {} 41 35 {} 42 36 {} 43 37 {} 43 37 + {} 45 38 {} 48 40 {} 49 41 {} 50 42 {} 51 43 {} 54 45 {} 55 46 + {} 56 47 {} 57 48 {} 58 49 {} 59 50 {} 60 51 {} 61 52 {} 62 53 + {} 63 54 {} 64 55 {} 64 55 {} 66 56 {} 67 57 {} 68 58 {} 69 59 + {} 70 60 {} 71 61 {} 72 62 {} 75 64 {} 76 65 {} 77 66 {} 78 67 + {} 78 67 {} 78 67 {} 81 68 {} 82 69 {} 83 70 {} 84 71 {} 85 72 + {} 85 72 {} 87 73 {} 88 74 {} 89 75 113 2 2 113 2 2 355 27 25 + 355 27 25 393 29 26 393 29 26 399 32 28 399 32 28 629 46 39 + 629 46 39 667 52 44 667 52 44 839 73 63 839 73 63} + +do_execsql_test 5.2.7.1 { + SELECT max(c) OVER win, + min(c) OVER win, + count(a) OVER win + FROM t3 + WINDOW win AS ( ORDER BY c , b , a + ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW ) + ORDER BY 1 , 2 , 3 +} {963 929 6 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 83 979 113 80 979 113 81 979 113 82 979 133 79 979 148 78 + 979 158 76 979 158 77 979 160 76 979 208 75 979 223 74 979 224 73 + 979 234 72 979 238 71 979 239 70 979 247 69 979 250 68 979 252 67 + 979 256 66 979 257 65 979 295 64 979 309 64 979 330 62 979 335 61 + 979 336 60 979 346 59 979 354 59 979 355 57 979 355 57 979 393 55 + 979 393 56 979 398 54 979 399 53 979 399 53 979 412 52 979 421 51 + 979 430 50 979 443 49 979 480 47 979 480 48 979 572 47 979 574 45 + 979 607 44 979 618 42 979 618 43 979 627 41 979 629 40 979 629 41 + 979 633 39 979 634 38 979 652 37 979 660 36 979 667 35 979 667 35 + 979 670 34 979 671 33 979 683 32 979 705 31 979 711 30 979 716 29 + 979 726 28 979 730 27 979 759 26 979 762 25 979 768 23 979 768 24 + 979 777 22 979 786 21 979 790 20 979 792 19 979 794 18 979 805 17 + 979 822 17 979 839 14 979 839 15 979 840 13 979 844 12 979 845 11 + 979 870 9 979 870 10 979 870 10 979 899 8 979 911 7} + +do_execsql_test 5.2.7.2 { + SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, + rank() OVER win, + dense_rank() OVER win + FROM t3 + WINDOW win AS ( ORDER BY c , b , a + ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW ) + ORDER BY 1 , 2 , 3 +} {2851 89 89 3778 88 88 4681 87 87 5556 83 83 5574 82 82 5586 81 81 + 5640 84 84 5640 85 85 5640 86 86 7324 80 80 8123 77 77 8129 73 73 + 8129 74 74 8163 78 78 8163 79 79 8940 71 71 8968 75 75 8968 76 76 + 9727 66 66 9745 69 69 9745 70 70 9745 72 72 10504 65 65 + 10504 67 67 10504 68 68 11215 64 64 11844 62 62 11920 63 63 + 13274 60 60 13274 61 61 13897 58 58 13903 57 57 13925 56 56 + 13937 55 55 13941 59 59 15203 53 53 15241 54 54 15832 52 52 + 17100 48 48 17104 46 46 17104 47 47 17106 45 45 17126 49 49 + 17126 50 50 17126 51 51 17569 42 42 17733 44 44 18176 43 43 + 18597 40 40 18597 41 41 18952 37 37 18996 39 39 19395 38 38 + 19760 35 35 19788 36 36 20492 32 32 20492 33 33 20498 30 30 + 20536 34 34 20833 29 29 20871 28 28 20891 31 31 21180 27 27 + 21752 23 23 21830 26 26 22025 21 21 22087 22 22 22087 24 24 + 22087 25 25 22278 20 20 22316 19 19 22549 15 15 22557 14 14 + 22573 17 17 22573 18 18 22706 10 10 22796 11 11 22796 12 12 + 22796 13 13 22796 16 16 23022 4 4 23042 2 2 23042 3 3 23042 9 9 + 23155 1 1 23155 5 5 23155 6 6 23155 7 7 23155 8 8} do_execsql_test 5.3.1.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP ) - ORDER BY 1 , 2 -} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} + ORDER BY 1 , 2 , 3 +} {{} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0} do_execsql_test 5.3.1.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP ) - ORDER BY 1 , 2 -} {{} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1} + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1} do_execsql_test 5.3.2.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP ) - ORDER BY 1 , 2 -} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - 899 113 899 113 899 113 899 113 899 113 899 113 899 113 - 899 113 899 113 899 113 899 113 899 113 899 113 899 113 - 899 113 899 113 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 113 979 113 - 979 113 979 113 979 113 979 113 979 113 979 113 979 113 - 979 113 979 113 979 113 979 113 979 113 979 113 979 113 - 979 113 979 113 979 113} + ORDER BY 1 , 2 , 3 +} {{} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 899 113 9 899 113 9 899 113 9 899 113 9 + 899 113 9 899 113 9 899 113 9 899 113 16 899 113 16 899 113 16 + 899 113 16 899 113 16 899 113 16 899 113 16 899 113 16 899 113 16 + 979 102 44 979 102 44 979 102 44 979 102 44 979 102 44 979 102 49 + 979 102 49 979 102 49 979 102 49 979 102 49 979 102 49 979 102 49 + 979 102 56 979 102 56 979 102 56 979 102 56 979 102 56 979 102 56 + 979 102 62 979 102 62 979 102 62 979 102 62 979 102 62 979 102 62 + 979 102 62 979 102 62 979 102 62 979 102 62 979 102 62 979 102 62 + 979 102 62 979 102 75 979 102 75 979 102 75 979 102 75 979 102 75 + 979 102 75 979 102 75 979 102 75 979 102 83 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 113 25 979 113 25 979 113 25 + 979 113 25 979 113 25 979 113 25 979 113 25 979 113 25 979 113 33 + 979 113 33 979 113 33 979 113 33 979 113 33 979 113 33 979 113 33 + 979 113 33 979 113 33 979 113 33 979 113 33} do_execsql_test 5.3.2.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP ) - ORDER BY 1 , 2 -} {{} 81 {} 81 {} 81 {} 81 {} 81 {} 81 {} 81 {} 81 {} 81 - 2947 74 2947 74 2947 74 2947 74 2947 74 2947 74 2947 74 - 5287 65 5287 65 5287 65 5287 65 5287 65 5287 65 5287 65 - 5287 65 5287 65 8400 57 8400 57 8400 57 8400 57 8400 57 - 8400 57 8400 57 8400 57 9664 46 9664 46 9664 46 9664 46 - 9664 46 9664 46 9664 46 9664 46 9664 46 9664 46 9664 46 - 10626 41 10626 41 10626 41 10626 41 10626 41 12145 34 12145 34 - 12145 34 12145 34 12145 34 12145 34 12145 34 13949 28 13949 28 - 13949 28 13949 28 13949 28 13949 28 15315 15 15315 15 15315 15 - 15315 15 15315 15 15315 15 15315 15 15315 15 15315 15 15315 15 - 15315 15 15315 15 15315 15 18796 7 18796 7 18796 7 18796 7 - 18796 7 18796 7 18796 7 18796 7 21105 1 21105 1 21105 1 - 21105 1 21105 1 21105 1} + ORDER BY 1 , 2 , 3 +} {{} 81 11 {} 81 11 {} 81 11 {} 81 11 {} 81 11 {} 81 11 {} 81 11 + {} 81 11 {} 81 11 2947 74 10 2947 74 10 2947 74 10 2947 74 10 + 2947 74 10 2947 74 10 2947 74 10 5287 65 9 5287 65 9 5287 65 9 + 5287 65 9 5287 65 9 5287 65 9 5287 65 9 5287 65 9 5287 65 9 + 8400 57 8 8400 57 8 8400 57 8 8400 57 8 8400 57 8 8400 57 8 + 8400 57 8 8400 57 8 9664 46 7 9664 46 7 9664 46 7 9664 46 7 + 9664 46 7 9664 46 7 9664 46 7 9664 46 7 9664 46 7 9664 46 7 + 9664 46 7 10626 41 6 10626 41 6 10626 41 6 10626 41 6 10626 41 6 + 12145 34 5 12145 34 5 12145 34 5 12145 34 5 12145 34 5 12145 34 5 + 12145 34 5 13949 28 4 13949 28 4 13949 28 4 13949 28 4 13949 28 4 + 13949 28 4 15315 15 3 15315 15 3 15315 15 3 15315 15 3 15315 15 3 + 15315 15 3 15315 15 3 15315 15 3 15315 15 3 15315 15 3 15315 15 3 + 15315 15 3 15315 15 3 18796 7 2 18796 7 2 18796 7 2 18796 7 2 + 18796 7 2 18796 7 2 18796 7 2 18796 7 2 21105 1 1 21105 1 1 + 21105 1 1 21105 1 1 21105 1 1 21105 1 1} do_execsql_test 5.3.3.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( PARTITION BY coalesce(a, '') RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP ) - ORDER BY 1 , 2 -} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} + ORDER BY 1 , 2 , 3 +} {{} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0} do_execsql_test 5.3.3.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( PARTITION BY coalesce(a, '') RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP ) - ORDER BY 1 , 2 -} {{} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1} + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1} do_execsql_test 5.3.4.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY a GROUPS 6 PRECEDING EXCLUDE GROUP ) - ORDER BY 1 , 2 -} {{} {} {} {} {} {} {} {} {} {} {} {} 870 158 870 158 870 158 - 870 158 870 158 870 158 870 158 870 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 959 102 959 102 959 102 959 102 - 959 102 959 102 959 102 959 102 959 102 959 102 959 102 - 959 102 959 102 959 102 959 102 959 102 959 102 959 113 - 959 113 959 113 959 113 959 113 959 113 959 113 959 113 - 959 113 959 113 959 113 959 158 959 158 959 158 959 158 - 959 158 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102} + ORDER BY 1 , 2 , 3 +} {{} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 870 158 0 + 870 158 0 870 158 0 870 158 0 870 158 0 870 158 0 870 158 0 + 870 158 0 934 158 8 934 158 8 934 158 8 934 158 8 934 158 8 + 934 158 8 934 158 8 934 158 8 934 158 8 934 158 8 934 158 8 + 934 158 8 934 158 8 934 158 21 934 158 21 934 158 21 934 158 21 + 934 158 21 934 158 21 934 158 27 934 158 27 934 158 27 934 158 27 + 934 158 27 934 158 27 934 158 27 959 102 50 959 102 50 959 102 50 + 959 102 50 959 102 50 959 102 50 959 102 50 959 102 50 959 102 50 + 959 102 50 959 102 50 959 102 50 959 102 50 959 102 50 959 102 50 + 959 102 50 959 102 50 959 113 39 959 113 39 959 113 39 959 113 39 + 959 113 39 959 113 39 959 113 39 959 113 39 959 113 39 959 113 39 + 959 113 39 959 158 34 959 158 34 959 158 34 959 158 34 959 158 34 + 979 102 46 979 102 46 979 102 46 979 102 46 979 102 46 979 102 46 + 979 102 46 979 102 47 979 102 47 979 102 47 979 102 47 979 102 47 + 979 102 47 979 102 47 979 102 47 979 102 47} do_execsql_test 5.3.4.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY a GROUPS 6 PRECEDING EXCLUDE GROUP ) - ORDER BY 1 , 2 -} {{} 1 {} 1 {} 1 {} 1 {} 1 {} 1 2050 7 2050 7 2050 7 2050 7 - 2050 7 2050 7 2050 7 2050 7 4359 15 4359 15 4359 15 4359 15 - 4359 15 4359 15 4359 15 4359 15 4359 15 4359 15 4359 15 - 4359 15 4359 15 7840 28 7840 28 7840 28 7840 28 7840 28 - 7840 28 9206 34 9206 34 9206 34 9206 34 9206 34 9206 34 - 9206 34 10028 74 10028 74 10028 74 10028 74 10028 74 10028 74 - 10028 74 10396 65 10396 65 10396 65 10396 65 10396 65 10396 65 - 10396 65 10396 65 10396 65 11002 81 11002 81 11002 81 11002 81 - 11002 81 11002 81 11002 81 11002 81 11002 81 11010 41 11010 41 - 11010 41 11010 41 11010 41 11441 57 11441 57 11441 57 11441 57 - 11441 57 11441 57 11441 57 11441 57 12529 46 12529 46 12529 46 - 12529 46 12529 46 12529 46 12529 46 12529 46 12529 46 12529 46 - 12529 46} + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 2050 7 2 2050 7 2 + 2050 7 2 2050 7 2 2050 7 2 2050 7 2 2050 7 2 2050 7 2 4359 15 3 + 4359 15 3 4359 15 3 4359 15 3 4359 15 3 4359 15 3 4359 15 3 + 4359 15 3 4359 15 3 4359 15 3 4359 15 3 4359 15 3 4359 15 3 + 7840 28 4 7840 28 4 7840 28 4 7840 28 4 7840 28 4 7840 28 4 + 9206 34 5 9206 34 5 9206 34 5 9206 34 5 9206 34 5 9206 34 5 + 9206 34 5 10028 74 10 10028 74 10 10028 74 10 10028 74 10 + 10028 74 10 10028 74 10 10028 74 10 10396 65 9 10396 65 9 + 10396 65 9 10396 65 9 10396 65 9 10396 65 9 10396 65 9 10396 65 9 + 10396 65 9 11002 81 11 11002 81 11 11002 81 11 11002 81 11 + 11002 81 11 11002 81 11 11002 81 11 11002 81 11 11002 81 11 + 11010 41 6 11010 41 6 11010 41 6 11010 41 6 11010 41 6 11441 57 8 + 11441 57 8 11441 57 8 11441 57 8 11441 57 8 11441 57 8 11441 57 8 + 11441 57 8 12529 46 7 12529 46 7 12529 46 7 12529 46 7 12529 46 7 + 12529 46 7 12529 46 7 12529 46 7 12529 46 7 12529 46 7 12529 46 7} do_execsql_test 5.3.5.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY c RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE GROUP ) - ORDER BY 1 , 2 -} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} - {} {} {} {} 158 158 160 160 160 160 223 223 224 224 238 234 - 239 234 239 238 252 250 256 252 257 247 257 247 257 250 - 335 330 336 330 336 335 354 354 354 354 355 355 398 393 - 398 393 399 393 399 398 399 398 572 572 574 574 633 629 - 634 627 634 627 634 627 634 629 667 667 670 667 671 667 - 671 670 671 670 711 711 711 711 716 705 726 726 730 730 - 762 762 762 762 762 762 768 759 792 790 792 790 794 786 - 794 786 844 839 845 839 845 839 845 840 845 840 934 934 - 934 934 934 934 938 929 959 959 963 963} + ORDER BY 1 , 2 , 3 +} {{} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 158 158 1 160 160 1 160 160 1 223 223 1 224 224 1 + 238 234 2 239 234 2 239 238 2 252 250 2 256 252 2 257 247 4 + 257 247 4 257 250 3 335 330 2 336 330 2 336 335 2 354 354 1 + 354 354 1 355 355 1 398 393 3 398 393 3 399 393 3 399 398 2 + 399 398 2 572 572 1 574 574 1 633 629 2 634 627 3 634 627 3 + 634 627 3 634 629 3 667 667 1 670 667 2 671 667 2 671 670 2 + 671 670 2 711 711 1 711 711 1 716 705 2 726 726 1 730 730 1 + 762 762 1 762 762 1 762 762 1 768 759 3 792 790 2 792 790 2 + 794 786 3 794 786 3 844 839 4 845 839 4 845 839 4 845 840 3 + 845 840 3 934 934 1 934 934 1 934 934 1 938 929 3 959 959 1 + 963 963 1} do_execsql_test 5.3.5.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY c RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE GROUP ) - ORDER BY 1 , 2 -} {{} 1 {} 2 {} 2 {} 4 {} 5 {} 6 {} 6 {} 8 {} 9 {} 10 - {} 14 {} 15 {} 19 {} 20 {} 21 {} 23 {} 25 {} 27 {} 27 - {} 34 {} 35 {} 36 {} 37 {} 38 {} 38 {} 40 {} 41 {} 42 - {} 43 {} 43 {} 50 {} 56 {} 60 {} 61 {} 62 {} 64 {} 64 - {} 66 {} 67 {} 68 {} 69 {} 70 {} 71 {} 72 {} 78 {} 78 - {} 78 {} 81 {} 82 {} 83 {} 85 {} 85 {} 89 223 11 239 12 - 239 13 257 18 335 22 335 24 504 16 504 17 671 52 671 52 - 705 58 710 26 711 57 711 59 759 63 786 32 786 32 798 29 - 798 29 845 73 845 73 929 84 959 88 963 87 1260 46 1260 46 - 1334 51 1334 55 1584 31 1678 77 1885 48 1891 45 1891 49 - 2005 54 2523 75 2523 76} + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 2 2 {} 2 2 {} 4 3 {} 5 4 {} 6 5 {} 6 5 {} 8 6 + {} 9 7 {} 10 8 {} 14 12 {} 15 13 {} 19 17 {} 20 18 {} 21 19 + {} 23 21 {} 25 23 {} 27 25 {} 27 25 {} 34 29 {} 35 30 {} 36 31 + {} 37 32 {} 38 33 {} 38 33 {} 40 34 {} 41 35 {} 42 36 {} 43 37 + {} 43 37 {} 50 42 {} 56 47 {} 60 51 {} 61 52 {} 62 53 {} 64 55 + {} 64 55 {} 66 56 {} 67 57 {} 68 58 {} 69 59 {} 70 60 {} 71 61 + {} 72 62 {} 78 67 {} 78 67 {} 78 67 {} 81 68 {} 82 69 {} 83 70 + {} 85 72 {} 85 72 {} 89 75 223 11 9 239 12 10 239 13 11 + 257 18 16 335 22 20 335 24 22 504 16 14 504 17 15 671 52 44 + 671 52 44 705 58 49 710 26 24 711 57 48 711 59 50 759 63 54 + 786 32 28 786 32 28 798 29 26 798 29 26 845 73 63 845 73 63 + 929 84 71 959 88 74 963 87 73 1260 46 39 1260 46 39 1334 51 43 + 1334 55 46 1584 31 27 1678 77 66 1885 48 40 1891 45 38 1891 49 41 + 2005 54 45 2523 75 64 2523 76 65} + +do_execsql_test 5.3.6.1 { + SELECT max(c) OVER win, + min(c) OVER win, + count(a) OVER win + FROM t3 + WINDOW win AS ( ORDER BY c RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE GROUP ) + ORDER BY 1 , 2 , 3 +} {{} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0 + {} {} 0 {} {} 0 {} {} 0 {} {} 0 {} {} 0} + +do_execsql_test 5.3.6.2 { + SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, + rank() OVER win, + dense_rank() OVER win + FROM t3 + WINDOW win AS ( ORDER BY c RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE GROUP ) + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 2 2 {} 2 2 {} 4 3 {} 5 4 {} 6 5 {} 6 5 {} 8 6 + {} 9 7 {} 10 8 {} 11 9 {} 12 10 {} 13 11 {} 14 12 {} 15 13 + {} 16 14 {} 17 15 {} 18 16 {} 19 17 {} 20 18 {} 21 19 {} 22 20 + {} 23 21 {} 24 22 {} 25 23 {} 26 24 {} 27 25 {} 27 25 {} 29 26 + {} 29 26 {} 31 27 {} 32 28 {} 32 28 {} 34 29 {} 35 30 {} 36 31 + {} 37 32 {} 38 33 {} 38 33 {} 40 34 {} 41 35 {} 42 36 {} 43 37 + {} 43 37 {} 45 38 {} 46 39 {} 46 39 {} 48 40 {} 49 41 {} 50 42 + {} 51 43 {} 52 44 {} 52 44 {} 54 45 {} 55 46 {} 56 47 {} 57 48 + {} 58 49 {} 59 50 {} 60 51 {} 61 52 {} 62 53 {} 63 54 {} 64 55 + {} 64 55 {} 66 56 {} 67 57 {} 68 58 {} 69 59 {} 70 60 {} 71 61 + {} 72 62 {} 73 63 {} 73 63 {} 75 64 {} 76 65 {} 77 66 {} 78 67 + {} 78 67 {} 78 67 {} 81 68 {} 82 69 {} 83 70 {} 84 71 {} 85 72 + {} 85 72 {} 87 73 {} 88 74 {} 89 75} + +do_execsql_test 5.3.7.1 { + SELECT max(c) OVER win, + min(c) OVER win, + count(a) OVER win + FROM t3 + WINDOW win AS ( ORDER BY c , b , a + ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP ) + ORDER BY 1 , 2 , 3 +} {963 929 6 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82 + 979 102 83 979 113 80 979 113 81 979 113 82 979 133 79 979 148 78 + 979 158 76 979 158 77 979 160 76 979 208 75 979 223 74 979 224 73 + 979 234 72 979 238 71 979 239 70 979 247 69 979 250 68 979 252 67 + 979 256 66 979 257 65 979 295 64 979 309 64 979 330 62 979 335 61 + 979 336 60 979 346 59 979 354 59 979 355 57 979 355 57 979 393 55 + 979 393 56 979 398 54 979 399 53 979 399 53 979 412 52 979 421 51 + 979 430 50 979 443 49 979 480 47 979 480 48 979 572 47 979 574 45 + 979 607 44 979 618 42 979 618 43 979 627 41 979 629 40 979 629 41 + 979 633 39 979 634 38 979 652 37 979 660 36 979 667 35 979 667 35 + 979 670 34 979 671 33 979 683 32 979 705 31 979 711 30 979 716 29 + 979 726 28 979 730 27 979 759 26 979 762 25 979 768 23 979 768 24 + 979 777 22 979 786 21 979 790 20 979 792 19 979 794 18 979 805 17 + 979 822 17 979 839 14 979 839 15 979 840 13 979 844 12 979 845 11 + 979 870 9 979 870 10 979 870 10 979 899 8 979 911 7} + +do_execsql_test 5.3.7.2 { + SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, + rank() OVER win, + dense_rank() OVER win + FROM t3 + WINDOW win AS ( ORDER BY c , b , a + ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP ) + ORDER BY 1 , 2 , 3 +} {2851 89 89 3778 88 88 4681 87 87 5556 83 83 5574 82 82 5586 81 81 + 5640 84 84 5640 85 85 5640 86 86 7324 80 80 8123 77 77 8129 73 73 + 8129 74 74 8163 78 78 8163 79 79 8940 71 71 8968 75 75 8968 76 76 + 9727 66 66 9745 69 69 9745 70 70 9745 72 72 10504 65 65 + 10504 67 67 10504 68 68 11215 64 64 11844 62 62 11920 63 63 + 13274 60 60 13274 61 61 13897 58 58 13903 57 57 13925 56 56 + 13937 55 55 13941 59 59 15203 53 53 15241 54 54 15832 52 52 + 17100 48 48 17104 46 46 17104 47 47 17106 45 45 17126 49 49 + 17126 50 50 17126 51 51 17569 42 42 17733 44 44 18176 43 43 + 18597 40 40 18597 41 41 18952 37 37 18996 39 39 19395 38 38 + 19760 35 35 19788 36 36 20492 32 32 20492 33 33 20498 30 30 + 20536 34 34 20833 29 29 20871 28 28 20891 31 31 21180 27 27 + 21752 23 23 21830 26 26 22025 21 21 22087 22 22 22087 24 24 + 22087 25 25 22278 20 20 22316 19 19 22549 15 15 22557 14 14 + 22573 17 17 22573 18 18 22706 10 10 22796 11 11 22796 12 12 + 22796 13 13 22796 16 16 23022 4 4 23042 2 2 23042 3 3 23042 9 9 + 23155 1 1 23155 5 5 23155 6 6 23155 7 7 23155 8 8} do_execsql_test 5.4.1.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES ) - ORDER BY 1 , 2 -} {102 102 113 113 113 113 133 133 148 148 158 158 158 158 - 160 160 208 208 223 223 224 224 234 234 238 238 239 239 - 247 247 250 250 252 252 256 256 257 257 295 295 309 309 - 330 330 335 335 336 336 346 346 354 354 355 355 355 355 - 393 393 393 393 398 398 399 399 399 399 412 412 421 421 - 430 430 443 443 480 480 480 480 572 572 574 574 607 607 - 618 618 618 618 627 627 629 629 629 629 633 633 634 634 - 652 652 660 660 667 667 667 667 670 670 671 671 683 683 - 705 705 711 711 716 716 726 726 730 730 759 759 762 762 - 768 768 768 768 777 777 786 786 790 790 792 792 794 794 - 805 805 822 822 839 839 839 839 840 840 844 844 845 845 - 870 870 870 870 870 870 899 899 911 911 929 929 934 934 - 938 938 938 938 959 959 963 963 979 979} + ORDER BY 1 , 2 , 3 +} {102 102 1 113 113 1 113 113 1 133 133 1 148 148 1 158 158 0 + 158 158 1 160 160 1 208 208 1 223 223 1 224 224 1 234 234 1 + 238 238 1 239 239 1 247 247 1 250 250 1 252 252 1 256 256 1 + 257 257 1 295 295 1 309 309 1 330 330 1 335 335 1 336 336 1 + 346 346 1 354 354 1 355 355 0 355 355 1 393 393 1 393 393 1 + 398 398 1 399 399 0 399 399 1 412 412 1 421 421 1 430 430 1 + 443 443 1 480 480 1 480 480 1 572 572 1 574 574 1 607 607 1 + 618 618 1 618 618 1 627 627 1 629 629 0 629 629 1 633 633 1 + 634 634 1 652 652 1 660 660 1 667 667 0 667 667 1 670 670 1 + 671 671 1 683 683 1 705 705 1 711 711 1 716 716 1 726 726 1 + 730 730 1 759 759 1 762 762 1 768 768 1 768 768 1 777 777 1 + 786 786 1 790 790 1 792 792 1 794 794 1 805 805 1 822 822 1 + 839 839 1 839 839 1 840 840 1 844 844 1 845 845 1 870 870 0 + 870 870 1 870 870 1 899 899 1 911 911 1 929 929 1 934 934 1 + 938 938 1 938 938 1 959 959 1 963 963 1 979 979 1} do_execsql_test 5.4.1.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES ) - ORDER BY 1 , 2 -} {{} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 113 1 113 1 133 1 223 1 239 1 247 1 - 257 1 295 1 309 1 335 1 355 1 355 1 393 1 393 1 399 1 - 399 1 421 1 443 1 607 1 627 1 629 1 629 1 633 1 667 1 - 667 1 671 1 683 1 705 1 711 1 759 1 777 1 805 1 839 1 - 839 1 845 1 899 1 911 1 929 1 959 1 963 1 979 1} + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + 113 1 1 113 1 1 133 1 1 223 1 1 239 1 1 247 1 1 257 1 1 + 295 1 1 309 1 1 335 1 1 355 1 1 355 1 1 393 1 1 393 1 1 + 399 1 1 399 1 1 421 1 1 443 1 1 607 1 1 627 1 1 629 1 1 + 629 1 1 633 1 1 667 1 1 667 1 1 671 1 1 683 1 1 705 1 1 + 711 1 1 759 1 1 777 1 1 805 1 1 839 1 1 839 1 1 845 1 1 + 899 1 1 911 1 1 929 1 1 959 1 1 963 1 1 979 1 1} do_execsql_test 5.4.2.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES ) - ORDER BY 1 , 2 -} {113 113 234 234 257 257 336 336 354 354 768 768 839 839 - 839 839 899 113 899 113 899 113 899 113 899 113 899 113 - 899 113 899 113 899 113 899 113 899 113 899 113 899 113 - 899 113 899 899 963 113 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 113 979 113 979 113 979 113 979 113 979 113 979 113 - 979 113 979 113 979 113 979 113 979 113 979 113 979 113 - 979 113 979 113 979 113 979 113 979 113} + ORDER BY 1 , 2 , 3 +} {113 113 1 234 234 1 257 257 1 336 336 1 354 354 1 768 768 1 + 839 839 1 839 839 1 899 113 10 899 113 10 899 113 10 899 113 10 + 899 113 10 899 113 10 899 113 10 899 113 17 899 113 17 899 113 17 + 899 113 17 899 113 17 899 113 17 899 113 17 899 899 1 963 113 17 + 979 102 34 979 102 45 979 102 45 979 102 45 979 102 45 979 102 45 + 979 102 50 979 102 50 979 102 50 979 102 50 979 102 50 979 102 50 + 979 102 50 979 102 57 979 102 57 979 102 57 979 102 57 979 102 57 + 979 102 57 979 102 63 979 102 63 979 102 63 979 102 63 979 102 63 + 979 102 63 979 102 63 979 102 63 979 102 63 979 102 63 979 102 63 + 979 102 63 979 102 63 979 102 76 979 102 76 979 102 76 979 102 76 + 979 102 76 979 102 76 979 102 76 979 102 76 979 102 83 979 102 83 + 979 102 83 979 102 83 979 102 83 979 102 83 979 113 17 979 113 26 + 979 113 26 979 113 26 979 113 26 979 113 26 979 113 26 979 113 26 + 979 113 26 979 113 34 979 113 34 979 113 34 979 113 34 979 113 34 + 979 113 34 979 113 34 979 113 34 979 113 34 979 113 34} do_execsql_test 5.4.2.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES ) - ORDER BY 1 , 2 -} {{} 81 {} 81 {} 81 {} 81 113 81 257 81 839 81 839 81 899 81 - 2947 74 2947 74 2947 74 3368 74 3390 74 3618 74 3752 74 - 5287 65 5287 65 5287 65 5287 65 5420 65 5642 65 5970 65 - 6250 65 6266 65 8400 57 8400 57 8400 57 8400 57 8400 57 - 8400 57 8735 57 9329 57 9664 46 9664 46 9664 46 9664 46 - 9664 46 9664 46 9664 46 9664 46 9664 46 9959 46 10331 46 - 10626 41 10626 41 10739 41 11255 41 11403 41 12145 34 12145 34 - 12145 34 12145 34 12145 34 12990 34 13104 34 13949 28 13949 28 - 13949 28 13949 28 14556 28 14708 28 15315 15 15315 15 15315 15 - 15315 15 15315 15 15315 15 15562 15 15708 15 15708 15 15714 15 - 15948 15 16020 15 16026 15 18796 7 18796 7 18796 7 19019 7 - 19035 7 19105 7 19423 7 19707 7 21105 1 21105 1 21460 1 - 21504 1 21734 1 21772 1} + ORDER BY 1 , 2 , 3 +} {{} 81 11 {} 81 11 {} 81 11 {} 81 11 113 81 11 257 81 11 + 839 81 11 839 81 11 899 81 11 2947 74 10 2947 74 10 2947 74 10 + 3368 74 10 3390 74 10 3618 74 10 3752 74 10 5287 65 9 5287 65 9 + 5287 65 9 5287 65 9 5420 65 9 5642 65 9 5970 65 9 6250 65 9 + 6266 65 9 8400 57 8 8400 57 8 8400 57 8 8400 57 8 8400 57 8 + 8400 57 8 8735 57 8 9329 57 8 9664 46 7 9664 46 7 9664 46 7 + 9664 46 7 9664 46 7 9664 46 7 9664 46 7 9664 46 7 9664 46 7 + 9959 46 7 10331 46 7 10626 41 6 10626 41 6 10739 41 6 11255 41 6 + 11403 41 6 12145 34 5 12145 34 5 12145 34 5 12145 34 5 12145 34 5 + 12990 34 5 13104 34 5 13949 28 4 13949 28 4 13949 28 4 13949 28 4 + 14556 28 4 14708 28 4 15315 15 3 15315 15 3 15315 15 3 15315 15 3 + 15315 15 3 15315 15 3 15562 15 3 15708 15 3 15708 15 3 15714 15 3 + 15948 15 3 16020 15 3 16026 15 3 18796 7 2 18796 7 2 18796 7 2 + 19019 7 2 19035 7 2 19105 7 2 19423 7 2 19707 7 2 21105 1 1 + 21105 1 1 21460 1 1 21504 1 1 21734 1 1 21772 1 1} do_execsql_test 5.4.3.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( PARTITION BY coalesce(a, '') RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES ) - ORDER BY 1 , 2 -} {102 102 113 113 113 113 133 133 148 148 158 158 158 158 - 160 160 208 208 223 223 224 224 234 234 238 238 239 239 - 247 247 250 250 252 252 256 256 257 257 295 295 309 309 - 330 330 335 335 336 336 346 346 354 354 355 355 355 355 - 393 393 393 393 398 398 399 399 399 399 412 412 421 421 - 430 430 443 443 480 480 480 480 572 572 574 574 607 607 - 618 618 618 618 627 627 629 629 629 629 633 633 634 634 - 652 652 660 660 667 667 667 667 670 670 671 671 683 683 - 705 705 711 711 716 716 726 726 730 730 759 759 762 762 - 768 768 768 768 777 777 786 786 790 790 792 792 794 794 - 805 805 822 822 839 839 839 839 840 840 844 844 845 845 - 870 870 870 870 870 870 899 899 911 911 929 929 934 934 - 938 938 938 938 959 959 963 963 979 979} + ORDER BY 1 , 2 , 3 +} {102 102 1 113 113 1 113 113 1 133 133 1 148 148 1 158 158 0 + 158 158 1 160 160 1 208 208 1 223 223 1 224 224 1 234 234 1 + 238 238 1 239 239 1 247 247 1 250 250 1 252 252 1 256 256 1 + 257 257 1 295 295 1 309 309 1 330 330 1 335 335 1 336 336 1 + 346 346 1 354 354 1 355 355 0 355 355 1 393 393 1 393 393 1 + 398 398 1 399 399 0 399 399 1 412 412 1 421 421 1 430 430 1 + 443 443 1 480 480 1 480 480 1 572 572 1 574 574 1 607 607 1 + 618 618 1 618 618 1 627 627 1 629 629 0 629 629 1 633 633 1 + 634 634 1 652 652 1 660 660 1 667 667 0 667 667 1 670 670 1 + 671 671 1 683 683 1 705 705 1 711 711 1 716 716 1 726 726 1 + 730 730 1 759 759 1 762 762 1 768 768 1 768 768 1 777 777 1 + 786 786 1 790 790 1 792 792 1 794 794 1 805 805 1 822 822 1 + 839 839 1 839 839 1 840 840 1 844 844 1 845 845 1 870 870 0 + 870 870 1 870 870 1 899 899 1 911 911 1 929 929 1 934 934 1 + 938 938 1 938 938 1 959 959 1 963 963 1 979 979 1} do_execsql_test 5.4.3.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( PARTITION BY coalesce(a, '') RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES ) - ORDER BY 1 , 2 -} {{} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 {} 1 - {} 1 {} 1 {} 1 {} 1 113 1 113 1 133 1 223 1 239 1 247 1 - 257 1 295 1 309 1 335 1 355 1 355 1 393 1 393 1 399 1 - 399 1 421 1 443 1 607 1 627 1 629 1 629 1 633 1 667 1 - 667 1 671 1 683 1 705 1 711 1 759 1 777 1 805 1 839 1 - 839 1 845 1 899 1 911 1 929 1 959 1 963 1 979 1} + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 {} 1 1 + 113 1 1 113 1 1 133 1 1 223 1 1 239 1 1 247 1 1 257 1 1 + 295 1 1 309 1 1 335 1 1 355 1 1 355 1 1 393 1 1 393 1 1 + 399 1 1 399 1 1 421 1 1 443 1 1 607 1 1 627 1 1 629 1 1 + 629 1 1 633 1 1 667 1 1 667 1 1 671 1 1 683 1 1 705 1 1 + 711 1 1 759 1 1 777 1 1 805 1 1 839 1 1 839 1 1 845 1 1 + 899 1 1 911 1 1 929 1 1 959 1 1 963 1 1 979 1 1} do_execsql_test 5.4.4.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY a GROUPS 6 PRECEDING EXCLUDE TIES ) - ORDER BY 1 , 2 -} {158 158 355 355 399 399 629 629 667 667 870 158 870 158 - 870 158 870 158 870 158 870 158 870 870 911 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 934 158 934 158 934 158 - 934 158 934 158 934 158 934 158 959 102 959 102 959 102 - 959 102 959 102 959 102 959 102 959 102 959 102 959 102 - 959 102 959 102 959 102 959 102 959 102 959 102 959 113 - 959 113 959 113 959 113 959 113 959 113 959 113 959 113 - 959 113 959 113 959 113 959 158 959 158 959 158 959 158 - 959 158 963 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102 979 102 979 102 - 979 102 979 102 979 102 979 102 979 102} + ORDER BY 1 , 2 , 3 +} {158 158 0 355 355 0 399 399 0 629 629 0 667 667 0 870 158 1 + 870 158 1 870 158 1 870 158 1 870 158 1 870 158 1 870 870 0 + 911 158 1 934 158 1 934 158 9 934 158 9 934 158 9 934 158 9 + 934 158 9 934 158 9 934 158 9 934 158 9 934 158 9 934 158 9 + 934 158 9 934 158 9 934 158 9 934 158 22 934 158 22 934 158 22 + 934 158 22 934 158 22 934 158 22 934 158 28 934 158 28 934 158 28 + 934 158 28 934 158 28 934 158 28 959 102 40 959 102 51 959 102 51 + 959 102 51 959 102 51 959 102 51 959 102 51 959 102 51 959 102 51 + 959 102 51 959 102 51 959 102 51 959 102 51 959 102 51 959 102 51 + 959 102 51 959 113 35 959 113 40 959 113 40 959 113 40 959 113 40 + 959 113 40 959 113 40 959 113 40 959 113 40 959 113 40 959 113 40 + 959 158 28 959 158 35 959 158 35 959 158 35 959 158 35 963 102 51 + 979 102 47 979 102 47 979 102 47 979 102 47 979 102 47 979 102 47 + 979 102 47 979 102 48 979 102 48 979 102 48 979 102 48 979 102 48 + 979 102 48 979 102 48 979 102 48 979 102 48 979 102 51} do_execsql_test 5.4.4.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY a GROUPS 6 PRECEDING EXCLUDE TIES ) - ORDER BY 1 , 2 -} {{} 1 {} 1 355 1 399 1 629 1 667 1 2050 7 2050 7 2050 7 - 2273 7 2289 7 2359 7 2677 7 2961 7 4359 15 4359 15 4359 15 - 4359 15 4359 15 4359 15 4606 15 4752 15 4752 15 4758 15 - 4992 15 5064 15 5070 15 7840 28 7840 28 7840 28 7840 28 - 8447 28 8599 28 9206 34 9206 34 9206 34 9206 34 9206 34 - 10028 74 10028 74 10028 74 10051 34 10165 34 10396 65 10396 65 - 10396 65 10396 65 10449 74 10471 74 10529 65 10699 74 10751 65 - 10833 74 11002 81 11002 81 11002 81 11002 81 11010 41 11010 41 - 11079 65 11115 81 11123 41 11259 81 11359 65 11375 65 11441 57 - 11441 57 11441 57 11441 57 11441 57 11441 57 11639 41 11776 57 - 11787 41 11841 81 11841 81 11901 81 12370 57 12529 46 12529 46 - 12529 46 12529 46 12529 46 12529 46 12529 46 12529 46 12529 46 - 12824 46 13196 46} + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 1 1 355 1 1 399 1 1 629 1 1 667 1 1 2050 7 2 + 2050 7 2 2050 7 2 2273 7 2 2289 7 2 2359 7 2 2677 7 2 2961 7 2 + 4359 15 3 4359 15 3 4359 15 3 4359 15 3 4359 15 3 4359 15 3 + 4606 15 3 4752 15 3 4752 15 3 4758 15 3 4992 15 3 5064 15 3 + 5070 15 3 7840 28 4 7840 28 4 7840 28 4 7840 28 4 8447 28 4 + 8599 28 4 9206 34 5 9206 34 5 9206 34 5 9206 34 5 9206 34 5 + 10028 74 10 10028 74 10 10028 74 10 10051 34 5 10165 34 5 + 10396 65 9 10396 65 9 10396 65 9 10396 65 9 10449 74 10 + 10471 74 10 10529 65 9 10699 74 10 10751 65 9 10833 74 10 + 11002 81 11 11002 81 11 11002 81 11 11002 81 11 11010 41 6 + 11010 41 6 11079 65 9 11115 81 11 11123 41 6 11259 81 11 + 11359 65 9 11375 65 9 11441 57 8 11441 57 8 11441 57 8 11441 57 8 + 11441 57 8 11441 57 8 11639 41 6 11776 57 8 11787 41 6 + 11841 81 11 11841 81 11 11901 81 11 12370 57 8 12529 46 7 + 12529 46 7 12529 46 7 12529 46 7 12529 46 7 12529 46 7 12529 46 7 + 12529 46 7 12529 46 7 12824 46 7 13196 46 7} do_execsql_test 5.4.5.1 { SELECT max(c) OVER win, - min(c) OVER win + min(c) OVER win, + count(a) OVER win FROM t3 WINDOW win AS ( ORDER BY c RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE TIES ) - ORDER BY 1 , 2 -} {102 102 113 113 113 113 133 133 148 148 160 158 160 158 - 160 158 208 208 224 223 224 223 239 234 239 234 239 234 - 252 247 257 247 257 247 257 250 257 252 295 295 309 309 - 336 330 336 330 336 330 346 346 355 354 355 354 355 354 - 399 393 399 393 399 393 399 393 399 393 412 412 421 421 - 430 430 443 443 480 480 480 480 574 572 574 572 607 607 - 618 618 618 618 634 627 634 627 634 627 634 627 634 629 - 652 652 667 660 671 667 671 667 671 667 671 667 683 683 - 711 705 716 705 716 711 730 726 730 726 762 759 768 759 - 768 762 768 762 777 777 792 786 794 786 794 786 794 790 - 805 805 822 822 845 839 845 839 845 839 845 839 845 839 - 870 870 870 870 870 870 899 899 911 911 934 929 938 929 - 938 934 938 934 963 959 963 959 979 979} + ORDER BY 1 , 2 , 3 +} {102 102 1 113 113 1 113 113 1 133 133 1 148 148 1 160 158 1 + 160 158 2 160 158 2 208 208 1 224 223 2 224 223 2 239 234 3 + 239 234 3 239 234 3 252 247 3 257 247 5 257 247 5 257 250 4 + 257 252 3 295 295 1 309 309 1 336 330 3 336 330 3 336 330 3 + 346 346 1 355 354 1 355 354 2 355 354 2 399 393 3 399 393 3 + 399 393 3 399 393 4 399 393 4 412 412 1 421 421 1 430 430 1 + 443 443 1 480 480 1 480 480 1 574 572 2 574 572 2 607 607 1 + 618 618 1 618 618 1 634 627 3 634 627 4 634 627 4 634 627 4 + 634 629 3 652 652 1 667 660 2 671 667 2 671 667 3 671 667 3 + 671 667 3 683 683 1 711 705 2 716 705 3 716 711 2 730 726 2 + 730 726 2 762 759 2 768 759 4 768 762 2 768 762 2 777 777 1 + 792 786 3 794 786 4 794 786 4 794 790 3 805 805 1 822 822 1 + 845 839 4 845 839 4 845 839 5 845 839 5 845 839 5 870 870 0 + 870 870 1 870 870 1 899 899 1 911 911 1 934 929 2 938 929 4 + 938 934 2 938 934 2 963 959 2 963 959 2 979 979 1} do_execsql_test 5.4.5.2 { SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, - rank() OVER win + rank() OVER win, + dense_rank() OVER win FROM t3 WINDOW win AS ( ORDER BY c RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE TIES ) - ORDER BY 1 , 2 -} {{} 1 {} 5 {} 6 {} 6 {} 8 {} 9 {} 25 {} 34 {} 36 {} 38 - {} 38 {} 40 {} 41 {} 43 {} 43 {} 50 {} 60 {} 61 {} 64 - {} 64 {} 67 {} 68 {} 69 {} 70 {} 72 {} 78 {} 78 {} 78 - {} 85 {} 85 113 2 113 2 133 4 223 10 223 11 239 12 239 13 - 239 14 247 15 257 18 257 19 295 20 309 21 335 22 335 23 - 335 24 355 27 355 27 421 35 443 37 504 16 504 17 607 42 - 683 56 710 26 711 59 759 62 759 63 777 66 805 71 899 81 - 911 82 929 83 929 84 979 89 1185 32 1185 32 1191 29 1191 29 - 1334 51 1338 52 1338 52 1416 57 1416 58 1584 31 1684 73 - 1684 73 1889 46 1889 46 1891 49 1922 87 1922 88 2005 54 - 2005 55 2518 45 2518 48 2523 75 2523 76 2523 77} + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 5 4 {} 6 5 {} 6 5 {} 8 6 {} 9 7 {} 25 23 {} 34 29 + {} 36 31 {} 38 33 {} 38 33 {} 40 34 {} 41 35 {} 43 37 {} 43 37 + {} 50 42 {} 60 51 {} 61 52 {} 64 55 {} 64 55 {} 67 57 {} 68 58 + {} 69 59 {} 70 60 {} 72 62 {} 78 67 {} 78 67 {} 78 67 {} 85 72 + {} 85 72 113 2 2 113 2 2 133 4 3 223 10 8 223 11 9 239 12 10 + 239 13 11 239 14 12 247 15 13 257 18 16 257 19 17 295 20 18 + 309 21 19 335 22 20 335 23 21 335 24 22 355 27 25 355 27 25 + 421 35 30 443 37 32 504 16 14 504 17 15 607 42 36 683 56 47 + 710 26 24 711 59 50 759 62 53 759 63 54 777 66 56 805 71 61 + 899 81 68 911 82 69 929 83 70 929 84 71 979 89 75 1185 32 28 + 1185 32 28 1191 29 26 1191 29 26 1334 51 43 1338 52 44 1338 52 44 + 1416 57 48 1416 58 49 1584 31 27 1684 73 63 1684 73 63 1889 46 39 + 1889 46 39 1891 49 41 1922 87 73 1922 88 74 2005 54 45 2005 55 46 + 2518 45 38 2518 48 40 2523 75 64 2523 76 65 2523 77 66} + +do_execsql_test 5.4.6.1 { + SELECT max(c) OVER win, + min(c) OVER win, + count(a) OVER win + FROM t3 + WINDOW win AS ( ORDER BY c RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE TIES ) + ORDER BY 1 , 2 , 3 +} {102 102 1 113 113 1 113 113 1 133 133 1 148 148 1 158 158 0 + 158 158 1 160 160 1 208 208 1 223 223 1 224 224 1 234 234 1 + 238 238 1 239 239 1 247 247 1 250 250 1 252 252 1 256 256 1 + 257 257 1 295 295 1 309 309 1 330 330 1 335 335 1 336 336 1 + 346 346 1 354 354 1 355 355 0 355 355 1 393 393 1 393 393 1 + 398 398 1 399 399 0 399 399 1 412 412 1 421 421 1 430 430 1 + 443 443 1 480 480 1 480 480 1 572 572 1 574 574 1 607 607 1 + 618 618 1 618 618 1 627 627 1 629 629 0 629 629 1 633 633 1 + 634 634 1 652 652 1 660 660 1 667 667 0 667 667 1 670 670 1 + 671 671 1 683 683 1 705 705 1 711 711 1 716 716 1 726 726 1 + 730 730 1 759 759 1 762 762 1 768 768 1 768 768 1 777 777 1 + 786 786 1 790 790 1 792 792 1 794 794 1 805 805 1 822 822 1 + 839 839 1 839 839 1 840 840 1 844 844 1 845 845 1 870 870 0 + 870 870 1 870 870 1 899 899 1 911 911 1 929 929 1 934 934 1 + 938 938 1 938 938 1 959 959 1 963 963 1 979 979 1} + +do_execsql_test 5.4.6.2 { + SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, + rank() OVER win, + dense_rank() OVER win + FROM t3 + WINDOW win AS ( ORDER BY c RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE TIES ) + ORDER BY 1 , 2 , 3 +} {{} 1 1 {} 5 4 {} 6 5 {} 6 5 {} 8 6 {} 9 7 {} 11 9 {} 12 10 + {} 13 11 {} 16 14 {} 17 15 {} 18 16 {} 22 20 {} 24 22 {} 25 23 + {} 26 24 {} 31 27 {} 34 29 {} 36 31 {} 38 33 {} 38 33 {} 40 34 + {} 41 35 {} 43 37 {} 43 37 {} 49 41 {} 50 42 {} 51 43 {} 54 45 + {} 59 50 {} 60 51 {} 61 52 {} 63 54 {} 64 55 {} 64 55 {} 67 57 + {} 68 58 {} 69 59 {} 70 60 {} 72 62 {} 75 64 {} 76 65 {} 78 67 + {} 78 67 {} 78 67 {} 84 71 {} 85 72 {} 85 72 113 2 2 113 2 2 + 133 4 3 223 10 8 239 14 12 247 15 13 257 19 17 295 20 18 + 309 21 19 335 23 21 355 27 25 355 27 25 393 29 26 393 29 26 + 399 32 28 399 32 28 421 35 30 443 37 32 607 42 36 627 45 38 + 629 46 39 629 46 39 633 48 40 667 52 44 667 52 44 671 55 46 + 683 56 47 705 57 48 711 58 49 759 62 53 777 66 56 805 71 61 + 839 73 63 839 73 63 845 77 66 899 81 68 911 82 69 929 83 70 + 959 87 73 963 88 74 979 89 75} + +do_execsql_test 5.4.7.1 { + SELECT max(c) OVER win, + min(c) OVER win, + count(a) OVER win + FROM t3 + WINDOW win AS ( ORDER BY c , b , a + ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES ) + ORDER BY 1 , 2 , 3 +} {979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 + 979 102 83 979 113 81 979 113 82 979 133 80 979 148 79 979 158 77 + 979 158 78 979 160 77 979 208 76 979 223 75 979 224 74 979 234 73 + 979 238 72 979 239 71 979 247 70 979 250 69 979 252 68 979 256 67 + 979 257 66 979 295 65 979 309 64 979 330 63 979 335 62 979 336 61 + 979 346 60 979 354 59 979 355 58 979 355 58 979 393 56 979 393 57 + 979 398 55 979 399 54 979 399 54 979 412 53 979 421 52 979 430 51 + 979 443 50 979 480 48 979 480 49 979 572 47 979 574 46 979 607 45 + 979 618 43 979 618 44 979 627 42 979 629 41 979 629 41 979 633 40 + 979 634 39 979 652 38 979 660 37 979 667 36 979 667 36 979 670 35 + 979 671 34 979 683 33 979 705 32 979 711 31 979 716 30 979 726 29 + 979 730 28 979 759 27 979 762 26 979 768 24 979 768 25 979 777 23 + 979 786 22 979 790 21 979 792 20 979 794 19 979 805 18 979 822 17 + 979 839 15 979 839 16 979 840 14 979 844 13 979 845 12 979 870 10 + 979 870 11 979 870 11 979 899 9 979 911 8 979 929 7} + +do_execsql_test 5.4.7.2 { + SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win, + rank() OVER win, + dense_rank() OVER win + FROM t3 + WINDOW win AS ( ORDER BY c , b , a + ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES ) + ORDER BY 1 , 2 , 3 +} {3830 89 89 4741 88 88 5640 84 84 5640 85 85 5640 86 86 5640 87 87 + 6485 81 81 6485 82 82 6485 83 83 7324 80 80 8163 78 78 8163 79 79 + 8968 73 73 8968 74 74 8968 75 75 8968 76 76 8968 77 77 9745 69 69 + 9745 70 70 9745 71 71 9745 72 72 10504 65 65 10504 66 66 + 10504 67 67 10504 68 68 11215 64 64 11920 63 63 12603 62 62 + 13274 60 60 13274 61 61 13941 59 59 14608 55 55 14608 56 56 + 14608 57 57 14608 58 58 15241 54 54 15870 53 53 16499 52 52 + 17126 49 49 17126 50 50 17126 51 51 17733 44 44 17733 45 45 + 17733 46 46 17733 47 47 17733 48 48 18176 42 42 18176 43 43 + 18597 40 40 18597 41 41 18996 39 39 19395 37 37 19395 38 38 + 19788 36 36 20181 35 35 20536 34 34 20891 30 30 20891 31 31 + 20891 32 32 20891 33 33 21226 28 28 21226 29 29 21535 27 27 + 21830 26 26 22087 22 22 22087 23 23 22087 24 24 22087 25 25 + 22334 21 21 22573 17 17 22573 18 18 22573 19 19 22573 20 20 + 22796 11 11 22796 12 12 22796 13 13 22796 14 14 22796 15 15 + 22796 16 16 22929 10 10 23042 9 9 23155 1 1 23155 2 2 23155 3 3 + 23155 4 4 23155 5 5 23155 6 6 23155 7 7 23155 8 8} finish_test |