niceSOFT/sqlite: Official Git mirror of the SQLite source tree

test/window2.test
976 строк47 KB
dan
Do not mistake constant integers in a PARTITION BY expression for references to ORDER BY style references to values returned by the SELECT statement.
27 дек 2019, 18:31
27 дек 2019, 18:31d8d2fb9
# 2018 May 19
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#

####################################################
# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED!
####################################################

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix window2

ifcapable !windowfunc { finish_test ; return }
do_execsql_test 1.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
  INSERT INTO t1 VALUES(1, 'odd',  'one',   1);
  INSERT INTO t1 VALUES(2, 'even', 'two',   2);
  INSERT INTO t1 VALUES(3, 'odd',  'three', 3);
  INSERT INTO t1 VALUES(4, 'even', 'four',  4);
  INSERT INTO t1 VALUES(5, 'odd',  'five',  5);
  INSERT INTO t1 VALUES(6, 'even', 'six',   6);
} {}

do_execsql_test 1.1 {
  SELECT c, sum(d) OVER (PARTITION BY b ORDER BY c) FROM t1;
} {four 4   six 10   two 12   five 5   one 6   three 9}

do_execsql_test 1.2 {
  SELECT sum(d) OVER () FROM t1;
} {21   21   21   21   21   21}

do_execsql_test 1.3 {
  SELECT sum(d) OVER (PARTITION BY b) FROM t1;
} {12   12   12   9   9   9}

#==========================================================================

do_execsql_test 2.1 {
  SELECT a, sum(d) OVER (
    ORDER BY d
    ROWS BETWEEN 1000 PRECEDING AND 1 FOLLOWING
  ) FROM t1
} {1 3   2 6   3 10   4 15   5 21   6 21}

do_execsql_test 2.2 {
  SELECT a, sum(d) OVER (
    ORDER BY d
    ROWS BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  ) FROM t1
} {1 21   2 21   3 21   4 21   5 21   6 21}

do_execsql_test 2.3 {
  SELECT a, sum(d) OVER (
    ORDER BY d
    ROWS BETWEEN 1 PRECEDING AND 1000 FOLLOWING
  ) FROM t1
} {1 21   2 21   3 20   4 18   5 15   6 11}

do_execsql_test 2.4 {
  SELECT a, sum(d) OVER (
    ORDER BY d
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1
} {1 3   2 6   3 9   4 12   5 15   6 11}

do_execsql_test 2.5 {
  SELECT a, sum(d) OVER (
    ORDER BY d
    ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING
  ) FROM t1
} {1 1   2 3   3 5   4 7   5 9   6 11}

do_execsql_test 2.6 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1
} {2 6   4 12   6 10   1 4   3 9   5 8}

do_execsql_test 2.7 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING
  ) FROM t1
} {2 2   4 4   6 6   1 1   3 3   5 5}

do_execsql_test 2.8 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
  ) FROM t1
} {1 6   2 9   3 12   4 15   5 11   6 6}

do_execsql_test 2.9 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING
  ) FROM t1
} {1 6   2 10   3 15   4 21   5 21   6 21}

do_execsql_test 2.10 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
  ) FROM t1
} {1 6   2 9   3 12   4 15   5 11   6 6}

do_execsql_test 2.11 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) FROM t1
} {1 1   2 3   3 6   4 9   5 12   6 15}

do_execsql_test 2.13 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING
  ) FROM t1
} {1 21   2 21   3 21   4 20   5 18   6 15}

do_execsql_test 2.14 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
  ) FROM t1
} {1 {}   2 1   3 3   4 6   5 9   6 12}

do_execsql_test 2.15 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING
  ) FROM t1
} {2 2   4 6   6 10   1 1   3 4   5 8}

do_execsql_test 2.16 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
  ) FROM t1
} {2 {}   4 2   6 4   1 {}   3 1   5 3}

do_execsql_test 2.17 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING
  ) FROM t1
} {2 {}   4 {}   6 {}   1 {}   3 {}   5 {}}

do_execsql_test 2.18 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) FROM t1
} {2 {}   4 {}   6 2   1 {}   3 {}   5 1}

do_execsql_test 2.19 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING
  ) FROM t1
} {2 10   4 6   6 {}   1 8   3 5   5 {}}

do_execsql_test 2.20 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
  ) FROM t1
} {1 5   2 7   3 9   4 11   5 6   6 {}}

do_execsql_test 2.21 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  ) FROM t1
} {1 20   2 18   3 15   4 11   5 6   6 {}}

do_execsql_test 2.22 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  ) FROM t1
} {2 10   4 6   6 {}   1 8   3 5   5 {}}

do_execsql_test 2.23 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t1
} {1 21   2 20   3 18   4 15   5 11   6 6}

do_execsql_test 2.24 {
  SELECT a, sum(d) OVER (
    PARTITION BY a%2
    ORDER BY d 
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t1
} {2 12   4 10   6 6   1 9   3 8   5 5}

do_execsql_test 2.25 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) FROM t1
} {1 21   2 21   3 21   4 21   5 21   6 21}

do_execsql_test 2.26 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) FROM t1
} {2 12   4 12   6 12   1 9   3 9   5 9}

do_execsql_test 2.27 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    ROWS BETWEEN CURRENT ROW AND CURRENT ROW
  ) FROM t1
} {1 1   2 2   3 3   4 4   5 5   6 6}

do_execsql_test 2.28 {
  SELECT a, sum(d) OVER (
    PARTITION BY b
    ORDER BY d 
    ROWS BETWEEN CURRENT ROW AND CURRENT ROW
  ) FROM t1
} {2 2   4 4   6 6   1 1   3 3   5 5}

do_execsql_test 2.29 {
  SELECT a, sum(d) OVER (
    ORDER BY d 
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t1
} {1 21   2 20   3 18   4 15   5 11   6 6}

do_execsql_test 2.30 {
  SELECT a, sum(d) OVER (
    ORDER BY b 
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t1
} {2 21   4 21   6 21   1 9   3 9   5 9}

do_execsql_test 3.1 {
  SELECT a, sum(d) OVER (
    PARTITION BY b ORDER BY d
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t1
} {2 12   4 10   6 6   1 9   3 8   5 5}

do_execsql_test 3.2 {
  SELECT a, sum(d) OVER (
    ORDER BY b
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t1
} {2 21   4 21   6 21   1 9   3 9   5 9}

do_execsql_test 3.3 {
  SELECT a, sum(d) OVER (
    ORDER BY d
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) FROM t1
} {1 21   2 21   3 21   4 21   5 21   6 21}

do_execsql_test 3.4 {
  SELECT a, sum(d) OVER (
    ORDER BY d/2
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) FROM t1
} {1 1   2 3   3 6   4 10   5 15   6 21}

#==========================================================================

do_execsql_test 4.0 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
  INSERT INTO t2(a, b) VALUES
  (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2),
  (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62),
  (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78),
  (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77),
  (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7),
  (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43),
  (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90),
  (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56),
  (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98),
  (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33),
  (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84),
  (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13),
  (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35),
  (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8),
  (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73), 
  (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34), 
  (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77),
  (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70),
  (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80),
  (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66),
  (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37),
  (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91),
  (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69),
  (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84),
  (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38),
  (195,34), (196,83), (197,27), (198,82), (199,17), (200,7);
} {}

do_execsql_test 4.1 {
  SELECT a, sum(b) OVER (
    PARTITION BY (b%10)
    ORDER BY b
  ) FROM t2 ORDER BY a;
} {1 0   2 754   3 251   4 754   5 101   6 1247   7 132   8 266   9 6   10 950
  11 667   12 1052   13 535   14 128   15 428   16 250   17 336   18 1122
  19 368   20 6   21 1247   22 1000   23 92   24 368   25 584   26 320
  27 1000   28 24   29 478   30 133   31 1049   32 1090   33 632   34 101
  35 54   36 54   37 1049   38 450   39 145   40 354   41 21   42 764
  43 754   44 424   45 1122   46 930   47 42   48 930   49 352   50 535
  51 42   52 118   53 536   54 6   55 1122   56 86   57 770   58 255   59 50
  60 52   61 950   62 75   63 354   64 2   65 536   66 160   67 352   68 536
  69 54   70 675   71 276   72 950   73 868   74 678   75 667   76 4
  77 1184   78 160   79 120   80 584   81 266   82 133   83 405   84 468
  85 6   86 806   87 166   88 500   89 1090   90 552   91 251   92 27
  93 424   94 687   95 1215   96 450   97 32   98 360   99 1052   100 868
  101 2   102 66   103 754   104 450   105 145   106 5   107 687   108 24
  109 302   110 806   111 251   112 42   113 24   114 30   115 128   116 128
  117 50   118 1215   119 86   120 687   121 683   122 672   123 178   124 24
  125 24   126 299   127 178   128 770   129 535   130 1052   131 270
  132 255   133 675   134 632   135 266   136 6   137 21   138 930   139 411
  140 754   141 133   142 340   143 535   144 46   145 250   146 132
  147 132   148 354   149 500   150 770   151 276   152 360   153 354
  154 27   155 552   156 552   157 602   158 266   159 1049   160 675
  161 384   162 667   163 27   164 101   165 166   166 32   167 42   168 18
  169 336   170 1122   171 276   172 1122   173 266   174 50   175 178
  176 276   177 1247   178 6   179 1215   180 604   181 360   182 212
  183 120   184 210   185 1090   186 10   187 1090   188 266   189 66
  190 250   191 266   192 360   193 120   194 128   195 178   196 770
  197 92   198 634   199 38   200 21}

do_execsql_test 4.2 {
  SELECT a, sum(b) OVER (
    PARTITION BY (b%10)
    ORDER BY b
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) FROM t2 ORDER BY a;
} {1 0   2 754   3 251   4 754   5 101   6 1247   7 132   8 266   9 6   10 950
  11 667   12 1052   13 535   14 128   15 428   16 250   17 336   18 1122
  19 368   20 6   21 1247   22 1000   23 92   24 368   25 584   26 320
  27 1000   28 24   29 478   30 133   31 1049   32 1090   33 632   34 101
  35 54   36 54   37 1049   38 450   39 145   40 354   41 21   42 764
  43 754   44 424   45 1122   46 930   47 42   48 930   49 352   50 535
  51 42   52 118   53 536   54 6   55 1122   56 86   57 770   58 255   59 50
  60 52   61 950   62 75   63 354   64 2   65 536   66 160   67 352   68 536
  69 54   70 675   71 276   72 950   73 868   74 678   75 667   76 4
  77 1184   78 160   79 120   80 584   81 266   82 133   83 405   84 468
  85 6   86 806   87 166   88 500   89 1090   90 552   91 251   92 27
  93 424   94 687   95 1215   96 450   97 32   98 360   99 1052   100 868
  101 2   102 66   103 754   104 450   105 145   106 5   107 687   108 24
  109 302   110 806   111 251   112 42   113 24   114 30   115 128   116 128
  117 50   118 1215   119 86   120 687   121 683   122 672   123 178   124 24
  125 24   126 299   127 178   128 770   129 535   130 1052   131 270
  132 255   133 675   134 632   135 266   136 6   137 21   138 930   139 411
  140 754   141 133   142 340   143 535   144 46   145 250   146 132
  147 132   148 354   149 500   150 770   151 276   152 360   153 354
  154 27   155 552   156 552   157 602   158 266   159 1049   160 675
  161 384   162 667   163 27   164 101   165 166   166 32   167 42   168 18
  169 336   170 1122   171 276   172 1122   173 266   174 50   175 178
  176 276   177 1247   178 6   179 1215   180 604   181 360   182 212
  183 120   184 210   185 1090   186 10   187 1090   188 266   189 66
  190 250   191 266   192 360   193 120   194 128   195 178   196 770
  197 92   198 634   199 38   200 21}

do_execsql_test 4.3 {
  SELECT b, sum(b) OVER (
    ORDER BY b
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) FROM t2 ORDER BY b;
} {0 0   1 1   1 2   2 4   2 6   2 8   3 11   3 14   4 18   5 23   6 29   7 36
  7 43   7 50   8 58   8 66   8 74   9 83   9 92   9 101   10 111   11 122
  11 133   12 145   12 157   12 169   13 182   13 195   14 209   15 224
  15 239   15 254   16 270   16 286   16 302   17 319   19 338   20 358
  21 379   21 400   22 422   22 444   23 467   23 490   23 513   24 537
  25 562   26 588   26 614   26 640   27 667   27 694   28 722   29 751
  29 780   29 809   30 839   30 869   30 899   31 930   31 961   32 993
  33 1026   33 1059   33 1092   33 1125   33 1158   34 1192   34 1226
  34 1260   34 1294   35 1329   35 1364   36 1400   36 1436   36 1472
  36 1508   37 1545   37 1582   38 1620   38 1658   39 1697   39 1736
  39 1775   40 1815   41 1856   41 1897   41 1938   42 1980   43 2023
  43 2066   44 2110   44 2154   46 2200   46 2246   47 2293   47 2340
  47 2387   47 2434   49 2483   50 2533   51 2584   52 2636   53 2689
  54 2743   55 2798   55 2853   56 2909   56 2965   56 3021   57 3078
  58 3136   58 3194   58 3252   58 3310   59 3369   59 3428   59 3487
  59 3546   60 3606   61 3667   61 3728   62 3790   62 3852   63 3915
  64 3979   65 4044   65 4109   65 4174   66 4240   67 4307   68 4375
  69 4444   70 4514   72 4586   72 4658   72 4730   73 4803   73 4876
  73 4949   74 5023   74 5097   74 5171   74 5245   74 5319   75 5394
  75 5469   75 5544   76 5620   77 5697   77 5774   78 5852   78 5930
  79 6009   80 6089   80 6169   81 6250   81 6331   81 6412   82 6494
  83 6577   84 6661   84 6745   84 6829   84 6913   85 6998   85 7083
  85 7168   86 7254   87 7341   87 7428   88 7516   89 7605   89 7694
  89 7783   90 7873   90 7963   90 8053   91 8144   91 8235   91 8326
  91 8417   91 8508   93 8601   93 8694   93 8787   94 8881   95 8976
  95 9071   95 9166   96 9262   96 9358   96 9454   97 9551   97 9648
  98 9746   98 9844   99 9943   99 10042   99 10141}

do_execsql_test 4.4 {
  SELECT b, sum(b) OVER (
    ORDER BY b
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) FROM t2 ORDER BY b;
} {0 10141   1 10141   1 10141   2 10141   2 10141   2 10141   3 10141
  3 10141   4 10141   5 10141   6 10141   7 10141   7 10141   7 10141
  8 10141   8 10141   8 10141   9 10141   9 10141   9 10141   10 10141
  11 10141   11 10141   12 10141   12 10141   12 10141   13 10141   13 10141
  14 10141   15 10141   15 10141   15 10141   16 10141   16 10141   16 10141
  17 10141   19 10141   20 10141   21 10141   21 10141   22 10141   22 10141
  23 10141   23 10141   23 10141   24 10141   25 10141   26 10141   26 10141
  26 10141   27 10141   27 10141   28 10141   29 10141   29 10141   29 10141
  30 10141   30 10141   30 10141   31 10141   31 10141   32 10141   33 10141
  33 10141   33 10141   33 10141   33 10141   34 10141   34 10141   34 10141
  34 10141   35 10141   35 10141   36 10141   36 10141   36 10141   36 10141
  37 10141   37 10141   38 10141   38 10141   39 10141   39 10141   39 10141
  40 10141   41 10141   41 10141   41 10141   42 10141   43 10141   43 10141
  44 10141   44 10141   46 10141   46 10141   47 10141   47 10141   47 10141
  47 10141   49 10141   50 10141   51 10141   52 10141   53 10141   54 10141
  55 10141   55 10141   56 10141   56 10141   56 10141   57 10141   58 10141
  58 10141   58 10141   58 10141   59 10141   59 10141   59 10141   59 10141
  60 10141   61 10141   61 10141   62 10141   62 10141   63 10141   64 10141
  65 10141   65 10141   65 10141   66 10141   67 10141   68 10141   69 10141
  70 10141   72 10141   72 10141   72 10141   73 10141   73 10141   73 10141
  74 10141   74 10141   74 10141   74 10141   74 10141   75 10141   75 10141
  75 10141   76 10141   77 10141   77 10141   78 10141   78 10141   79 10141
  80 10141   80 10141   81 10141   81 10141   81 10141   82 10141   83 10141
  84 10141   84 10141   84 10141   84 10141   85 10141   85 10141   85 10141
  86 10141   87 10141   87 10141   88 10141   89 10141   89 10141   89 10141
  90 10141   90 10141   90 10141   91 10141   91 10141   91 10141   91 10141
  91 10141   93 10141   93 10141   93 10141   94 10141   95 10141   95 10141
  95 10141   96 10141   96 10141   96 10141   97 10141   97 10141   98 10141
  98 10141   99 10141   99 10141   99 10141}

do_execsql_test 4.5 {
  SELECT b, sum(b) OVER (
    ORDER BY b
    RANGE BETWEEN CURRENT ROW AND CURRENT ROW
  ) FROM t2 ORDER BY b;
} {0 0   1 2   1 2   2 6   2 6   2 6   3 6   3 6   4 4   5 5   6 6   7 21
  7 21   7 21   8 24   8 24   8 24   9 27   9 27   9 27   10 10   11 22
  11 22   12 36   12 36   12 36   13 26   13 26   14 14   15 45   15 45
  15 45   16 48   16 48   16 48   17 17   19 19   20 20   21 42   21 42
  22 44   22 44   23 69   23 69   23 69   24 24   25 25   26 78   26 78
  26 78   27 54   27 54   28 28   29 87   29 87   29 87   30 90   30 90
  30 90   31 62   31 62   32 32   33 165   33 165   33 165   33 165   33 165
  34 136   34 136   34 136   34 136   35 70   35 70   36 144   36 144
  36 144   36 144   37 74   37 74   38 76   38 76   39 117   39 117   39 117
  40 40   41 123   41 123   41 123   42 42   43 86   43 86   44 88   44 88
  46 92   46 92   47 188   47 188   47 188   47 188   49 49   50 50   51 51
  52 52   53 53   54 54   55 110   55 110   56 168   56 168   56 168   57 57
  58 232   58 232   58 232   58 232   59 236   59 236   59 236   59 236
  60 60   61 122   61 122   62 124   62 124   63 63   64 64   65 195   65 195
  65 195   66 66   67 67   68 68   69 69   70 70   72 216   72 216   72 216
  73 219   73 219   73 219   74 370   74 370   74 370   74 370   74 370
  75 225   75 225   75 225   76 76   77 154   77 154   78 156   78 156
  79 79   80 160   80 160   81 243   81 243   81 243   82 82   83 83   84 336
  84 336   84 336   84 336   85 255   85 255   85 255   86 86   87 174
  87 174   88 88   89 267   89 267   89 267   90 270   90 270   90 270
  91 455   91 455   91 455   91 455   91 455   93 279   93 279   93 279
  94 94   95 285   95 285   95 285   96 288   96 288   96 288   97 194
  97 194   98 196   98 196   99 297   99 297   99 297}

do_execsql_test 4.6.1 {
  SELECT b, sum(b) OVER (
    RANGE BETWEEN CURRENT ROW AND CURRENT ROW
  ) FROM t2 ORDER BY b;
} {0 10141   1 10141   1 10141   2 10141   2 10141   2 10141   3 10141
  3 10141   4 10141   5 10141   6 10141   7 10141   7 10141   7 10141
  8 10141   8 10141   8 10141   9 10141   9 10141   9 10141   10 10141
  11 10141   11 10141   12 10141   12 10141   12 10141   13 10141   13 10141
  14 10141   15 10141   15 10141   15 10141   16 10141   16 10141   16 10141
  17 10141   19 10141   20 10141   21 10141   21 10141   22 10141   22 10141
  23 10141   23 10141   23 10141   24 10141   25 10141   26 10141   26 10141
  26 10141   27 10141   27 10141   28 10141   29 10141   29 10141   29 10141
  30 10141   30 10141   30 10141   31 10141   31 10141   32 10141   33 10141
  33 10141   33 10141   33 10141   33 10141   34 10141   34 10141   34 10141
  34 10141   35 10141   35 10141   36 10141   36 10141   36 10141   36 10141
  37 10141   37 10141   38 10141   38 10141   39 10141   39 10141   39 10141
  40 10141   41 10141   41 10141   41 10141   42 10141   43 10141   43 10141
  44 10141   44 10141   46 10141   46 10141   47 10141   47 10141   47 10141
  47 10141   49 10141   50 10141   51 10141   52 10141   53 10141   54 10141
  55 10141   55 10141   56 10141   56 10141   56 10141   57 10141   58 10141
  58 10141   58 10141   58 10141   59 10141   59 10141   59 10141   59 10141
  60 10141   61 10141   61 10141   62 10141   62 10141   63 10141   64 10141
  65 10141   65 10141   65 10141   66 10141   67 10141   68 10141   69 10141
  70 10141   72 10141   72 10141   72 10141   73 10141   73 10141   73 10141
  74 10141   74 10141   74 10141   74 10141   74 10141   75 10141   75 10141
  75 10141   76 10141   77 10141   77 10141   78 10141   78 10141   79 10141
  80 10141   80 10141   81 10141   81 10141   81 10141   82 10141   83 10141
  84 10141   84 10141   84 10141   84 10141   85 10141   85 10141   85 10141
  86 10141   87 10141   87 10141   88 10141   89 10141   89 10141   89 10141
  90 10141   90 10141   90 10141   91 10141   91 10141   91 10141   91 10141
  91 10141   93 10141   93 10141   93 10141   94 10141   95 10141   95 10141
  95 10141   96 10141   96 10141   96 10141   97 10141   97 10141   98 10141
  98 10141   99 10141   99 10141   99 10141}

do_execsql_test 4.6.2 {
  SELECT b, sum(b) OVER () FROM t2 ORDER BY b;
} {0 10141   1 10141   1 10141   2 10141   2 10141   2 10141   3 10141
  3 10141   4 10141   5 10141   6 10141   7 10141   7 10141   7 10141
  8 10141   8 10141   8 10141   9 10141   9 10141   9 10141   10 10141
  11 10141   11 10141   12 10141   12 10141   12 10141   13 10141   13 10141
  14 10141   15 10141   15 10141   15 10141   16 10141   16 10141   16 10141
  17 10141   19 10141   20 10141   21 10141   21 10141   22 10141   22 10141
  23 10141   23 10141   23 10141   24 10141   25 10141   26 10141   26 10141
  26 10141   27 10141   27 10141   28 10141   29 10141   29 10141   29 10141
  30 10141   30 10141   30 10141   31 10141   31 10141   32 10141   33 10141
  33 10141   33 10141   33 10141   33 10141   34 10141   34 10141   34 10141
  34 10141   35 10141   35 10141   36 10141   36 10141   36 10141   36 10141
  37 10141   37 10141   38 10141   38 10141   39 10141   39 10141   39 10141
  40 10141   41 10141   41 10141   41 10141   42 10141   43 10141   43 10141
  44 10141   44 10141   46 10141   46 10141   47 10141   47 10141   47 10141
  47 10141   49 10141   50 10141   51 10141   52 10141   53 10141   54 10141
  55 10141   55 10141   56 10141   56 10141   56 10141   57 10141   58 10141
  58 10141   58 10141   58 10141   59 10141   59 10141   59 10141   59 10141
  60 10141   61 10141   61 10141   62 10141   62 10141   63 10141   64 10141
  65 10141   65 10141   65 10141   66 10141   67 10141   68 10141   69 10141
  70 10141   72 10141   72 10141   72 10141   73 10141   73 10141   73 10141
  74 10141   74 10141   74 10141   74 10141   74 10141   75 10141   75 10141
  75 10141   76 10141   77 10141   77 10141   78 10141   78 10141   79 10141
  80 10141   80 10141   81 10141   81 10141   81 10141   82 10141   83 10141
  84 10141   84 10141   84 10141   84 10141   85 10141   85 10141   85 10141
  86 10141   87 10141   87 10141   88 10141   89 10141   89 10141   89 10141
  90 10141   90 10141   90 10141   91 10141   91 10141   91 10141   91 10141
  91 10141   93 10141   93 10141   93 10141   94 10141   95 10141   95 10141
  95 10141   96 10141   96 10141   96 10141   97 10141   97 10141   98 10141
  98 10141   99 10141   99 10141   99 10141}

do_execsql_test 4.6.3 {
  SELECT b, sum(b) OVER (
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) FROM t2 ORDER BY b;
} {0 10141   1 10141   1 10141   2 10141   2 10141   2 10141   3 10141
  3 10141   4 10141   5 10141   6 10141   7 10141   7 10141   7 10141
  8 10141   8 10141   8 10141   9 10141   9 10141   9 10141   10 10141
  11 10141   11 10141   12 10141   12 10141   12 10141   13 10141   13 10141
  14 10141   15 10141   15 10141   15 10141   16 10141   16 10141   16 10141
  17 10141   19 10141   20 10141   21 10141   21 10141   22 10141   22 10141
  23 10141   23 10141   23 10141   24 10141   25 10141   26 10141   26 10141
  26 10141   27 10141   27 10141   28 10141   29 10141   29 10141   29 10141
  30 10141   30 10141   30 10141   31 10141   31 10141   32 10141   33 10141
  33 10141   33 10141   33 10141   33 10141   34 10141   34 10141   34 10141
  34 10141   35 10141   35 10141   36 10141   36 10141   36 10141   36 10141
  37 10141   37 10141   38 10141   38 10141   39 10141   39 10141   39 10141
  40 10141   41 10141   41 10141   41 10141   42 10141   43 10141   43 10141
  44 10141   44 10141   46 10141   46 10141   47 10141   47 10141   47 10141
  47 10141   49 10141   50 10141   51 10141   52 10141   53 10141   54 10141
  55 10141   55 10141   56 10141   56 10141   56 10141   57 10141   58 10141
  58 10141   58 10141   58 10141   59 10141   59 10141   59 10141   59 10141
  60 10141   61 10141   61 10141   62 10141   62 10141   63 10141   64 10141
  65 10141   65 10141   65 10141   66 10141   67 10141   68 10141   69 10141
  70 10141   72 10141   72 10141   72 10141   73 10141   73 10141   73 10141
  74 10141   74 10141   74 10141   74 10141   74 10141   75 10141   75 10141
  75 10141   76 10141   77 10141   77 10141   78 10141   78 10141   79 10141
  80 10141   80 10141   81 10141   81 10141   81 10141   82 10141   83 10141
  84 10141   84 10141   84 10141   84 10141   85 10141   85 10141   85 10141
  86 10141   87 10141   87 10141   88 10141   89 10141   89 10141   89 10141
  90 10141   90 10141   90 10141   91 10141   91 10141   91 10141   91 10141
  91 10141   93 10141   93 10141   93 10141   94 10141   95 10141   95 10141
  95 10141   96 10141   96 10141   96 10141   97 10141   97 10141   98 10141
  98 10141   99 10141   99 10141   99 10141}

do_execsql_test 4.6.4 {
  SELECT b, sum(b) OVER (
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t2 ORDER BY b;
} {0 10141   1 10141   1 10141   2 10141   2 10141   2 10141   3 10141
  3 10141   4 10141   5 10141   6 10141   7 10141   7 10141   7 10141
  8 10141   8 10141   8 10141   9 10141   9 10141   9 10141   10 10141
  11 10141   11 10141   12 10141   12 10141   12 10141   13 10141   13 10141
  14 10141   15 10141   15 10141   15 10141   16 10141   16 10141   16 10141
  17 10141   19 10141   20 10141   21 10141   21 10141   22 10141   22 10141
  23 10141   23 10141   23 10141   24 10141   25 10141   26 10141   26 10141
  26 10141   27 10141   27 10141   28 10141   29 10141   29 10141   29 10141
  30 10141   30 10141   30 10141   31 10141   31 10141   32 10141   33 10141
  33 10141   33 10141   33 10141   33 10141   34 10141   34 10141   34 10141
  34 10141   35 10141   35 10141   36 10141   36 10141   36 10141   36 10141
  37 10141   37 10141   38 10141   38 10141   39 10141   39 10141   39 10141
  40 10141   41 10141   41 10141   41 10141   42 10141   43 10141   43 10141
  44 10141   44 10141   46 10141   46 10141   47 10141   47 10141   47 10141
  47 10141   49 10141   50 10141   51 10141   52 10141   53 10141   54 10141
  55 10141   55 10141   56 10141   56 10141   56 10141   57 10141   58 10141
  58 10141   58 10141   58 10141   59 10141   59 10141   59 10141   59 10141
  60 10141   61 10141   61 10141   62 10141   62 10141   63 10141   64 10141
  65 10141   65 10141   65 10141   66 10141   67 10141   68 10141   69 10141
  70 10141   72 10141   72 10141   72 10141   73 10141   73 10141   73 10141
  74 10141   74 10141   74 10141   74 10141   74 10141   75 10141   75 10141
  75 10141   76 10141   77 10141   77 10141   78 10141   78 10141   79 10141
  80 10141   80 10141   81 10141   81 10141   81 10141   82 10141   83 10141
  84 10141   84 10141   84 10141   84 10141   85 10141   85 10141   85 10141
  86 10141   87 10141   87 10141   88 10141   89 10141   89 10141   89 10141
  90 10141   90 10141   90 10141   91 10141   91 10141   91 10141   91 10141
  91 10141   93 10141   93 10141   93 10141   94 10141   95 10141   95 10141
  95 10141   96 10141   96 10141   96 10141   97 10141   97 10141   98 10141
  98 10141   99 10141   99 10141   99 10141}

do_execsql_test 4.7.1 {
  SELECT b, sum(b) OVER (
    ROWS BETWEEN CURRENT ROW AND CURRENT ROW
  ) FROM t2 ORDER BY 1, 2;
} {0 0   1 1   1 1   2 2   2 2   2 2   3 3   3 3   4 4   5 5   6 6   7 7   7 7
  7 7   8 8   8 8   8 8   9 9   9 9   9 9   10 10   11 11   11 11   12 12
  12 12   12 12   13 13   13 13   14 14   15 15   15 15   15 15   16 16
  16 16   16 16   17 17   19 19   20 20   21 21   21 21   22 22   22 22
  23 23   23 23   23 23   24 24   25 25   26 26   26 26   26 26   27 27
  27 27   28 28   29 29   29 29   29 29   30 30   30 30   30 30   31 31
  31 31   32 32   33 33   33 33   33 33   33 33   33 33   34 34   34 34
  34 34   34 34   35 35   35 35   36 36   36 36   36 36   36 36   37 37
  37 37   38 38   38 38   39 39   39 39   39 39   40 40   41 41   41 41
  41 41   42 42   43 43   43 43   44 44   44 44   46 46   46 46   47 47
  47 47   47 47   47 47   49 49   50 50   51 51   52 52   53 53   54 54
  55 55   55 55   56 56   56 56   56 56   57 57   58 58   58 58   58 58
  58 58   59 59   59 59   59 59   59 59   60 60   61 61   61 61   62 62
  62 62   63 63   64 64   65 65   65 65   65 65   66 66   67 67   68 68
  69 69   70 70   72 72   72 72   72 72   73 73   73 73   73 73   74 74
  74 74   74 74   74 74   74 74   75 75   75 75   75 75   76 76   77 77
  77 77   78 78   78 78   79 79   80 80   80 80   81 81   81 81   81 81
  82 82   83 83   84 84   84 84   84 84   84 84   85 85   85 85   85 85
  86 86   87 87   87 87   88 88   89 89   89 89   89 89   90 90   90 90
  90 90   91 91   91 91   91 91   91 91   91 91   93 93   93 93   93 93
  94 94   95 95   95 95   95 95   96 96   96 96   96 96   97 97   97 97
  98 98   98 98   99 99   99 99   99 99}

do_execsql_test 4.7.2 {
  SELECT b, sum(b) OVER (
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) FROM t2 ORDER BY 1, 2;
} {0 0   1 3379   1 5443   2 372   2 4473   2 7074   3 2916   3 9096   4 4049
  5 5643   6 1047   7 2205   7 7081   7 10141   8 1553   8 5926   8 6422
  9 4883   9 7932   9 8497   10 9544   11 5727   11 6433   12 2825   12 5918
  12 8582   13 5190   13 8570   14 8596   15 3189   15 6023   15 8924
  16 1942   16 1958   16 3590   17 10134   19 7474   20 5946   21 5464
  21 9682   22 3029   22 6140   23 212   23 1926   23 8520   24 2626
  25 3331   26 337   26 7539   26 7565   27 1270   27 10035   28 3217
  29 1649   29 4355   29 7326   30 4215   30 9400   30 9853   31 5977
  31 6008   32 2857   33 370   33 4326   33 8175   33 8909   33 9661
  34 6414   34 6516   34 8958   34 9925   35 2151   35 5638   36 3701
  36 7818   36 8785   36 8994   37 4597   37 8557   38 735   38 9891   39 842
  39 7513   39 9721   40 3475   41 115   41 4874   41 5906   42 4185
  43 2754   43 3518   44 7072   44 9765   46 1041   46 1316   47 2198
  47 3378   47 7612   47 7923   49 6482   50 9450   51 5778   52 9370
  53 4408   54 1448   55 3174   55 6876   56 2913   56 3435   56 3574
  57 7223   58 5248   58 7876   58 9318   58 9823   59 697   59 2813
  59 6665   59 7455   60 6821   61 2426   61 4944   62 904   62 8658
  63 4471   64 8407   65 2116   65 5177   65 5603   66 8142   67 1620
  68 803   69 9260   70 7396   72 4833   72 8004   72 8076   73 5017
  73 5716   73 6213   74 74   74 189   74 2365   74 5538   74 7297   75 3665
  75 6951   75 8343   76 3964   77 1903   77 7028   78 1394   78 4293
  79 6292   80 4677   80 7692   81 542   81 4045   81 8488   82 10117
  83 10008   84 1826   84 4761   84 9534   84 9628   85 2602   85 2711
  85 7166   86 2291   87 4560   87 5865   88 6380   89 461   89 3306
  89 3790   90 3119   90 6606   90 7782   91 995   91 2517   91 3007
  91 8749   91 8876   93 1742   93 2051   93 8268   94 4143   95 5112
  95 6118   95 9191   96 638   96 5344   96 6761   97 1243   97 1545
  98 3888   98 5442   99 311   99 1146   99 9093}

do_execsql_test 4.7.3 {
  SELECT b, sum(b) OVER (
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) FROM t2 ORDER BY 1, 2;
} {0 10141   1 10141   1 10141   2 10141   2 10141   2 10141   3 10141
  3 10141   4 10141   5 10141   6 10141   7 10141   7 10141   7 10141
  8 10141   8 10141   8 10141   9 10141   9 10141   9 10141   10 10141
  11 10141   11 10141   12 10141   12 10141   12 10141   13 10141   13 10141
  14 10141   15 10141   15 10141   15 10141   16 10141   16 10141   16 10141
  17 10141   19 10141   20 10141   21 10141   21 10141   22 10141   22 10141
  23 10141   23 10141   23 10141   24 10141   25 10141   26 10141   26 10141
  26 10141   27 10141   27 10141   28 10141   29 10141   29 10141   29 10141
  30 10141   30 10141   30 10141   31 10141   31 10141   32 10141   33 10141
  33 10141   33 10141   33 10141   33 10141   34 10141   34 10141   34 10141
  34 10141   35 10141   35 10141   36 10141   36 10141   36 10141   36 10141
  37 10141   37 10141   38 10141   38 10141   39 10141   39 10141   39 10141
  40 10141   41 10141   41 10141   41 10141   42 10141   43 10141   43 10141
  44 10141   44 10141   46 10141   46 10141   47 10141   47 10141   47 10141
  47 10141   49 10141   50 10141   51 10141   52 10141   53 10141   54 10141
  55 10141   55 10141   56 10141   56 10141   56 10141   57 10141   58 10141
  58 10141   58 10141   58 10141   59 10141   59 10141   59 10141   59 10141
  60 10141   61 10141   61 10141   62 10141   62 10141   63 10141   64 10141
  65 10141   65 10141   65 10141   66 10141   67 10141   68 10141   69 10141
  70 10141   72 10141   72 10141   72 10141   73 10141   73 10141   73 10141
  74 10141   74 10141   74 10141   74 10141   74 10141   75 10141   75 10141
  75 10141   76 10141   77 10141   77 10141   78 10141   78 10141   79 10141
  80 10141   80 10141   81 10141   81 10141   81 10141   82 10141   83 10141
  84 10141   84 10141   84 10141   84 10141   85 10141   85 10141   85 10141
  86 10141   87 10141   87 10141   88 10141   89 10141   89 10141   89 10141
  90 10141   90 10141   90 10141   91 10141   91 10141   91 10141   91 10141
  91 10141   93 10141   93 10141   93 10141   94 10141   95 10141   95 10141
  95 10141   96 10141   96 10141   96 10141   97 10141   97 10141   98 10141
  98 10141   99 10141   99 10141   99 10141}

do_execsql_test 4.7.4 {
  SELECT b, sum(b) OVER (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t2 ORDER BY 1, 2;
} {0 10141   1 4699   1 6763   2 3069   2 5670   2 9771   3 1048   3 7228
  4 6096   5 4503   6 9100   7 7   7 3067   7 7943   8 3727   8 4223   8 8596
  9 1653   9 2218   9 5267   10 607   11 3719   11 4425   12 1571   12 4235
  12 7328   13 1584   13 4964   14 1559   15 1232   15 4133   15 6967
  16 6567   16 8199   16 8215   17 24   19 2686   20 4215   21 480   21 4698
  22 4023   22 7134   23 1644   23 8238   23 9952   24 7539   25 6835
  26 2602   26 2628   26 9830   27 133   27 8898   28 6952   29 2844
  29 5815   29 8521   30 318   30 771   30 5956   31 4164   31 4195   32 7316
  33 513   33 1265   33 1999   33 5848   33 9804   34 250   34 1217   34 3659
  34 3761   35 4538   35 8025   36 1183   36 1392   36 2359   36 6476
  37 1621   37 5581   38 288   38 9444   39 459   39 2667   39 9338   40 6706
  41 4276   41 5308   41 10067   42 5998   43 6666   43 7430   44 420
  44 3113   46 8871   46 9146   47 2265   47 2576   47 6810   47 7990
  49 3708   50 741   51 4414   52 823   53 5786   54 8747   55 3320   55 7022
  56 6623   56 6762   56 7284   57 2975   58 376   58 881   58 2323   58 4951
  59 2745   59 3535   59 7387   59 9503   60 3380   61 5258   61 7776
  62 1545   62 9299   63 5733   64 1798   65 4603   65 5029   65 8090
  66 2065   67 8588   68 9406   69 950   70 2815   72 2137   72 2209
  72 5380   73 4001   73 4498   73 5197   74 2918   74 4677   74 7850
  74 10026   74 10141   75 1873   75 3265   75 6551   76 6253   77 3190
  77 8315   78 5926   78 8825   79 3928   80 2529   80 5544   81 1734
  81 6177   81 9680   82 106   83 216   84 597   84 691   84 5464   84 8399
  85 3060   85 7515   85 7624   86 7936   87 4363   87 5668   88 3849
  89 6440   89 6924   89 9769   90 2449   90 3625   90 7112   91 1356
  91 1483   91 7225   91 7715   91 9237   93 1966   93 8183   93 8492
  94 6092   95 1045   95 4118   95 5124   96 3476   96 4893   96 9599
  97 8693   97 8995   98 4797   98 6351   99 1147   99 9094   99 9929}

do_execsql_test 4.8.1 {
  SELECT b, sum(b) OVER (
    ORDER BY a
    ROWS BETWEEN CURRENT ROW AND CURRENT ROW
  ) FROM t2 ORDER BY 1, 2;
} {0 0   1 1   1 1   2 2   2 2   2 2   3 3   3 3   4 4   5 5   6 6   7 7   7 7
  7 7   8 8   8 8   8 8   9 9   9 9   9 9   10 10   11 11   11 11   12 12
  12 12   12 12   13 13   13 13   14 14   15 15   15 15   15 15   16 16
  16 16   16 16   17 17   19 19   20 20   21 21   21 21   22 22   22 22
  23 23   23 23   23 23   24 24   25 25   26 26   26 26   26 26   27 27
  27 27   28 28   29 29   29 29   29 29   30 30   30 30   30 30   31 31
  31 31   32 32   33 33   33 33   33 33   33 33   33 33   34 34   34 34
  34 34   34 34   35 35   35 35   36 36   36 36   36 36   36 36   37 37
  37 37   38 38   38 38   39 39   39 39   39 39   40 40   41 41   41 41
  41 41   42 42   43 43   43 43   44 44   44 44   46 46   46 46   47 47
  47 47   47 47   47 47   49 49   50 50   51 51   52 52   53 53   54 54
  55 55   55 55   56 56   56 56   56 56   57 57   58 58   58 58   58 58
  58 58   59 59   59 59   59 59   59 59   60 60   61 61   61 61   62 62
  62 62   63 63   64 64   65 65   65 65   65 65   66 66   67 67   68 68
  69 69   70 70   72 72   72 72   72 72   73 73   73 73   73 73   74 74
  74 74   74 74   74 74   74 74   75 75   75 75   75 75   76 76   77 77
  77 77   78 78   78 78   79 79   80 80   80 80   81 81   81 81   81 81
  82 82   83 83   84 84   84 84   84 84   84 84   85 85   85 85   85 85
  86 86   87 87   87 87   88 88   89 89   89 89   89 89   90 90   90 90
  90 90   91 91   91 91   91 91   91 91   91 91   93 93   93 93   93 93
  94 94   95 95   95 95   95 95   96 96   96 96   96 96   97 97   97 97
  98 98   98 98   99 99   99 99   99 99}

do_execsql_test 4.8.2 {
  SELECT b, sum(b) OVER (
    ORDER BY a
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) FROM t2 ORDER BY 1, 2;
} {0 0   1 3379   1 5443   2 372   2 4473   2 7074   3 2916   3 9096   4 4049
  5 5643   6 1047   7 2205   7 7081   7 10141   8 1553   8 5926   8 6422
  9 4883   9 7932   9 8497   10 9544   11 5727   11 6433   12 2825   12 5918
  12 8582   13 5190   13 8570   14 8596   15 3189   15 6023   15 8924
  16 1942   16 1958   16 3590   17 10134   19 7474   20 5946   21 5464
  21 9682   22 3029   22 6140   23 212   23 1926   23 8520   24 2626
  25 3331   26 337   26 7539   26 7565   27 1270   27 10035   28 3217
  29 1649   29 4355   29 7326   30 4215   30 9400   30 9853   31 5977
  31 6008   32 2857   33 370   33 4326   33 8175   33 8909   33 9661
  34 6414   34 6516   34 8958   34 9925   35 2151   35 5638   36 3701
  36 7818   36 8785   36 8994   37 4597   37 8557   38 735   38 9891   39 842
  39 7513   39 9721   40 3475   41 115   41 4874   41 5906   42 4185
  43 2754   43 3518   44 7072   44 9765   46 1041   46 1316   47 2198
  47 3378   47 7612   47 7923   49 6482   50 9450   51 5778   52 9370
  53 4408   54 1448   55 3174   55 6876   56 2913   56 3435   56 3574
  57 7223   58 5248   58 7876   58 9318   58 9823   59 697   59 2813
  59 6665   59 7455   60 6821   61 2426   61 4944   62 904   62 8658
  63 4471   64 8407   65 2116   65 5177   65 5603   66 8142   67 1620
  68 803   69 9260   70 7396   72 4833   72 8004   72 8076   73 5017
  73 5716   73 6213   74 74   74 189   74 2365   74 5538   74 7297   75 3665
  75 6951   75 8343   76 3964   77 1903   77 7028   78 1394   78 4293
  79 6292   80 4677   80 7692   81 542   81 4045   81 8488   82 10117
  83 10008   84 1826   84 4761   84 9534   84 9628   85 2602   85 2711
  85 7166   86 2291   87 4560   87 5865   88 6380   89 461   89 3306
  89 3790   90 3119   90 6606   90 7782   91 995   91 2517   91 3007
  91 8749   91 8876   93 1742   93 2051   93 8268   94 4143   95 5112
  95 6118   95 9191   96 638   96 5344   96 6761   97 1243   97 1545
  98 3888   98 5442   99 311   99 1146   99 9093}

do_execsql_test 4.8.3 {
  SELECT b, sum(b) OVER (
    ORDER BY a
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) FROM t2 ORDER BY 1, 2;
} {0 10141   1 10141   1 10141   2 10141   2 10141   2 10141   3 10141
  3 10141   4 10141   5 10141   6 10141   7 10141   7 10141   7 10141
  8 10141   8 10141   8 10141   9 10141   9 10141   9 10141   10 10141
  11 10141   11 10141   12 10141   12 10141   12 10141   13 10141   13 10141
  14 10141   15 10141   15 10141   15 10141   16 10141   16 10141   16 10141
  17 10141   19 10141   20 10141   21 10141   21 10141   22 10141   22 10141
  23 10141   23 10141   23 10141   24 10141   25 10141   26 10141   26 10141
  26 10141   27 10141   27 10141   28 10141   29 10141   29 10141   29 10141
  30 10141   30 10141   30 10141   31 10141   31 10141   32 10141   33 10141
  33 10141   33 10141   33 10141   33 10141   34 10141   34 10141   34 10141
  34 10141   35 10141   35 10141   36 10141   36 10141   36 10141   36 10141
  37 10141   37 10141   38 10141   38 10141   39 10141   39 10141   39 10141
  40 10141   41 10141   41 10141   41 10141   42 10141   43 10141   43 10141
  44 10141   44 10141   46 10141   46 10141   47 10141   47 10141   47 10141
  47 10141   49 10141   50 10141   51 10141   52 10141   53 10141   54 10141
  55 10141   55 10141   56 10141   56 10141   56 10141   57 10141   58 10141
  58 10141   58 10141   58 10141   59 10141   59 10141   59 10141   59 10141
  60 10141   61 10141   61 10141   62 10141   62 10141   63 10141   64 10141
  65 10141   65 10141   65 10141   66 10141   67 10141   68 10141   69 10141
  70 10141   72 10141   72 10141   72 10141   73 10141   73 10141   73 10141
  74 10141   74 10141   74 10141   74 10141   74 10141   75 10141   75 10141
  75 10141   76 10141   77 10141   77 10141   78 10141   78 10141   79 10141
  80 10141   80 10141   81 10141   81 10141   81 10141   82 10141   83 10141
  84 10141   84 10141   84 10141   84 10141   85 10141   85 10141   85 10141
  86 10141   87 10141   87 10141   88 10141   89 10141   89 10141   89 10141
  90 10141   90 10141   90 10141   91 10141   91 10141   91 10141   91 10141
  91 10141   93 10141   93 10141   93 10141   94 10141   95 10141   95 10141
  95 10141   96 10141   96 10141   96 10141   97 10141   97 10141   98 10141
  98 10141   99 10141   99 10141   99 10141}

do_execsql_test 4.8.4 {
  SELECT b, sum(b) OVER (
    ORDER BY a
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) FROM t2 ORDER BY 1, 2;
} {0 10141   1 4699   1 6763   2 3069   2 5670   2 9771   3 1048   3 7228
  4 6096   5 4503   6 9100   7 7   7 3067   7 7943   8 3727   8 4223   8 8596
  9 1653   9 2218   9 5267   10 607   11 3719   11 4425   12 1571   12 4235
  12 7328   13 1584   13 4964   14 1559   15 1232   15 4133   15 6967
  16 6567   16 8199   16 8215   17 24   19 2686   20 4215   21 480   21 4698
  22 4023   22 7134   23 1644   23 8238   23 9952   24 7539   25 6835
  26 2602   26 2628   26 9830   27 133   27 8898   28 6952   29 2844
  29 5815   29 8521   30 318   30 771   30 5956   31 4164   31 4195   32 7316
  33 513   33 1265   33 1999   33 5848   33 9804   34 250   34 1217   34 3659
  34 3761   35 4538   35 8025   36 1183   36 1392   36 2359   36 6476
  37 1621   37 5581   38 288   38 9444   39 459   39 2667   39 9338   40 6706
  41 4276   41 5308   41 10067   42 5998   43 6666   43 7430   44 420
  44 3113   46 8871   46 9146   47 2265   47 2576   47 6810   47 7990
  49 3708   50 741   51 4414   52 823   53 5786   54 8747   55 3320   55 7022
  56 6623   56 6762   56 7284   57 2975   58 376   58 881   58 2323   58 4951
  59 2745   59 3535   59 7387   59 9503   60 3380   61 5258   61 7776
  62 1545   62 9299   63 5733   64 1798   65 4603   65 5029   65 8090
  66 2065   67 8588   68 9406   69 950   70 2815   72 2137   72 2209
  72 5380   73 4001   73 4498   73 5197   74 2918   74 4677   74 7850
  74 10026   74 10141   75 1873   75 3265   75 6551   76 6253   77 3190
  77 8315   78 5926   78 8825   79 3928   80 2529   80 5544   81 1734
  81 6177   81 9680   82 106   83 216   84 597   84 691   84 5464   84 8399
  85 3060   85 7515   85 7624   86 7936   87 4363   87 5668   88 3849
  89 6440   89 6924   89 9769   90 2449   90 3625   90 7112   91 1356
  91 1483   91 7225   91 7715   91 9237   93 1966   93 8183   93 8492
  94 6092   95 1045   95 4118   95 5124   96 3476   96 4893   96 9599
  97 8693   97 8995   98 4797   98 6351   99 1147   99 9094   99 9929}


do_test 4.9 {
  set myres {}
  foreach r [db eval {SELECT 
    rank() OVER win AS rank,
    cume_dist() OVER win AS cume_dist FROM t1
  WINDOW win AS (ORDER BY 1);}] {
    lappend myres [format %.4f [set r]]
  }
  set res2 {1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000}
  set i 0
  foreach r [set myres] r2 [set res2] {
    if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
      error "list element [set i] does not match: got=[set r] expected=[set r2]"
    }
    incr i
  }
  set {} {}
} {}

do_execsql_test 4.10 {
  SELECT count(*) OVER (ORDER BY b) FROM t1
} {3   3   3   6   6   6}

do_execsql_test 4.11 {
  SELECT count(distinct a) FILTER (WHERE b='odd') FROM t1
} {3}

#==========================================================================

do_execsql_test 5.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x INTEGER, y INTEGER);
  INSERT INTO t1 VALUES(10, 1);
  INSERT INTO t1 VALUES(20, 2);
  INSERT INTO t1 VALUES(3, 3);
  INSERT INTO t1 VALUES(2, 4);
  INSERT INTO t1 VALUES(1, 5);
} {}


do_test 5.1 {
  set myres {}
  foreach r [db eval {SELECT avg(x) OVER (ORDER BY y) AS z FROM t1 ORDER BY z;}] {
    lappend myres [format %.4f [set r]]
  }
  set res2 {7.2000 8.7500 10.0000 11.0000 15.0000}
  set i 0
  foreach r [set myres] r2 [set res2] {
    if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
      error "list element [set i] does not match: got=[set r] expected=[set r2]"
    }
    incr i
  }
  set {} {}
} {}

#==========================================================================

do_execsql_test 6.0 {
  DROP TABLE IF EXISTS t0;
  CREATE TABLE t0(c0 INTEGER UNIQUE);
  INSERT INTO t0 VALUES(0);
} {}

do_execsql_test 6.1 {
  SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0;
} {1 {}}

do_execsql_test 6.2 {
  SELECT * FROM t0 WHERE 
      (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0);
} {}

#==========================================================================

do_execsql_test 7.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
  INSERT INTO t1 VALUES(1, 1, 1);
  INSERT INTO t1 VALUES(1, 2, 2);
  INSERT INTO t1 VALUES(3, 3, 3);
  INSERT INTO t1 VALUES(3, 4, 4);
} {}

do_execsql_test 7.1 {
  SELECT c, sum(c) OVER win1 FROM t1 
  WINDOW win1 AS (ORDER BY b)
} {1 1   2 3   3 6   4 10}

do_execsql_test 7.2 {
  SELECT c, sum(c) OVER win1 FROM t1 
  WINDOW win1 AS (PARTITION BY 1 ORDER BY b)
} {1 1   2 3   3 6   4 10}

do_execsql_test 7.3 {
  SELECT c, sum(c) OVER win1 FROM t1 
  WINDOW win1 AS (ORDER BY 1)
} {1 10   2 10   3 10   4 10}

finish_test