# 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
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
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}
finish_test