| 1 |
# 2018 May 19 |
| 2 |
# |
| 3 |
# The author disclaims copyright to this source code. In place of |
| 4 |
# a legal notice, here is a blessing: |
| 5 |
# |
| 6 |
# May you do good and not evil. |
| 7 |
# May you find forgiveness for yourself and forgive others. |
| 8 |
# May you share freely, never taking more than you give. |
| 9 |
# |
| 10 |
#*********************************************************************** |
| 11 |
# This file implements regression tests for SQLite library. |
| 12 |
# |
| 13 |
|
| 14 |
#################################################### |
| 15 |
# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! |
| 16 |
#################################################### |
| 17 |
|
| 18 |
set testdir [file dirname $argv0] |
| 19 |
source $testdir/tester.tcl |
| 20 |
set testprefix window2 |
| 21 |
|
| 22 |
ifcapable !windowfunc { finish_test ; return } |
| 23 |
do_execsql_test 1.0 { |
| 24 |
DROP TABLE IF EXISTS t1; |
| 25 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER); |
| 26 |
INSERT INTO t1 VALUES(1, 'odd', 'one', 1); |
| 27 |
INSERT INTO t1 VALUES(2, 'even', 'two', 2); |
| 28 |
INSERT INTO t1 VALUES(3, 'odd', 'three', 3); |
| 29 |
INSERT INTO t1 VALUES(4, 'even', 'four', 4); |
| 30 |
INSERT INTO t1 VALUES(5, 'odd', 'five', 5); |
| 31 |
INSERT INTO t1 VALUES(6, 'even', 'six', 6); |
| 32 |
} {} |
| 33 |
|
| 34 |
do_execsql_test 1.1 { |
| 35 |
SELECT c, sum(d) OVER (PARTITION BY b ORDER BY c) FROM t1; |
| 36 |
} {four 4 six 10 two 12 five 5 one 6 three 9} |
| 37 |
|
| 38 |
do_execsql_test 1.2 { |
| 39 |
SELECT sum(d) OVER () FROM t1; |
| 40 |
} {21 21 21 21 21 21} |
| 41 |
|
| 42 |
do_execsql_test 1.3 { |
| 43 |
SELECT sum(d) OVER (PARTITION BY b) FROM t1; |
| 44 |
} {12 12 12 9 9 9} |
| 45 |
|
| 46 |
#========================================================================== |
| 47 |
|
| 48 |
do_execsql_test 2.1 { |
| 49 |
SELECT a, sum(d) OVER ( |
| 50 |
ORDER BY d |
| 51 |
ROWS BETWEEN 1000 PRECEDING AND 1 FOLLOWING |
| 52 |
) FROM t1 |
| 53 |
} {1 3 2 6 3 10 4 15 5 21 6 21} |
| 54 |
|
| 55 |
do_execsql_test 2.2 { |
| 56 |
SELECT a, sum(d) OVER ( |
| 57 |
ORDER BY d |
| 58 |
ROWS BETWEEN 1000 PRECEDING AND 1000 FOLLOWING |
| 59 |
) FROM t1 |
| 60 |
} {1 21 2 21 3 21 4 21 5 21 6 21} |
| 61 |
|
| 62 |
do_execsql_test 2.3 { |
| 63 |
SELECT a, sum(d) OVER ( |
| 64 |
ORDER BY d |
| 65 |
ROWS BETWEEN 1 PRECEDING AND 1000 FOLLOWING |
| 66 |
) FROM t1 |
| 67 |
} {1 21 2 21 3 20 4 18 5 15 6 11} |
| 68 |
|
| 69 |
do_execsql_test 2.4 { |
| 70 |
SELECT a, sum(d) OVER ( |
| 71 |
ORDER BY d |
| 72 |
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING |
| 73 |
) FROM t1 |
| 74 |
} {1 3 2 6 3 9 4 12 5 15 6 11} |
| 75 |
|
| 76 |
do_execsql_test 2.5 { |
| 77 |
SELECT a, sum(d) OVER ( |
| 78 |
ORDER BY d |
| 79 |
ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING |
| 80 |
) FROM t1 |
| 81 |
} {1 1 2 3 3 5 4 7 5 9 6 11} |
| 82 |
|
| 83 |
do_execsql_test 2.6 { |
| 84 |
SELECT a, sum(d) OVER ( |
| 85 |
PARTITION BY b |
| 86 |
ORDER BY d |
| 87 |
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING |
| 88 |
) FROM t1 |
| 89 |
} {2 6 4 12 6 10 1 4 3 9 5 8} |
| 90 |
|
| 91 |
do_execsql_test 2.7 { |
| 92 |
SELECT a, sum(d) OVER ( |
| 93 |
PARTITION BY b |
| 94 |
ORDER BY d |
| 95 |
ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING |
| 96 |
) FROM t1 |
| 97 |
} {2 2 4 4 6 6 1 1 3 3 5 5} |
| 98 |
|
| 99 |
do_execsql_test 2.8 { |
| 100 |
SELECT a, sum(d) OVER ( |
| 101 |
ORDER BY d |
| 102 |
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING |
| 103 |
) FROM t1 |
| 104 |
} {1 6 2 9 3 12 4 15 5 11 6 6} |
| 105 |
|
| 106 |
do_execsql_test 2.9 { |
| 107 |
SELECT a, sum(d) OVER ( |
| 108 |
ORDER BY d |
| 109 |
ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING |
| 110 |
) FROM t1 |
| 111 |
} {1 6 2 10 3 15 4 21 5 21 6 21} |
| 112 |
|
| 113 |
do_execsql_test 2.10 { |
| 114 |
SELECT a, sum(d) OVER ( |
| 115 |
ORDER BY d |
| 116 |
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING |
| 117 |
) FROM t1 |
| 118 |
} {1 6 2 9 3 12 4 15 5 11 6 6} |
| 119 |
|
| 120 |
do_execsql_test 2.11 { |
| 121 |
SELECT a, sum(d) OVER ( |
| 122 |
ORDER BY d |
| 123 |
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW |
| 124 |
) FROM t1 |
| 125 |
} {1 1 2 3 3 6 4 9 5 12 6 15} |
| 126 |
|
| 127 |
do_execsql_test 2.13 { |
| 128 |
SELECT a, sum(d) OVER ( |
| 129 |
ORDER BY d |
| 130 |
ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING |
| 131 |
) FROM t1 |
| 132 |
} {1 21 2 21 3 21 4 20 5 18 6 15} |
| 133 |
|
| 134 |
do_execsql_test 2.14 { |
| 135 |
SELECT a, sum(d) OVER ( |
| 136 |
ORDER BY d |
| 137 |
ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING |
| 138 |
) FROM t1 |
| 139 |
} {1 {} 2 1 3 3 4 6 5 9 6 12} |
| 140 |
|
| 141 |
do_execsql_test 2.15 { |
| 142 |
SELECT a, sum(d) OVER ( |
| 143 |
PARTITION BY b |
| 144 |
ORDER BY d |
| 145 |
ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING |
| 146 |
) FROM t1 |
| 147 |
} {2 2 4 6 6 10 1 1 3 4 5 8} |
| 148 |
|
| 149 |
do_execsql_test 2.16 { |
| 150 |
SELECT a, sum(d) OVER ( |
| 151 |
PARTITION BY b |
| 152 |
ORDER BY d |
| 153 |
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING |
| 154 |
) FROM t1 |
| 155 |
} {2 {} 4 2 6 4 1 {} 3 1 5 3} |
| 156 |
|
| 157 |
do_execsql_test 2.17 { |
| 158 |
SELECT a, sum(d) OVER ( |
| 159 |
PARTITION BY b |
| 160 |
ORDER BY d |
| 161 |
ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING |
| 162 |
) FROM t1 |
| 163 |
} {2 {} 4 {} 6 {} 1 {} 3 {} 5 {}} |
| 164 |
|
| 165 |
do_execsql_test 2.18 { |
| 166 |
SELECT a, sum(d) OVER ( |
| 167 |
PARTITION BY b |
| 168 |
ORDER BY d |
| 169 |
ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING |
| 170 |
) FROM t1 |
| 171 |
} {2 {} 4 {} 6 2 1 {} 3 {} 5 1} |
| 172 |
|
| 173 |
do_execsql_test 2.19 { |
| 174 |
SELECT a, sum(d) OVER ( |
| 175 |
PARTITION BY b |
| 176 |
ORDER BY d |
| 177 |
ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING |
| 178 |
) FROM t1 |
| 179 |
} {2 10 4 6 6 {} 1 8 3 5 5 {}} |
| 180 |
|
| 181 |
do_execsql_test 2.20 { |
| 182 |
SELECT a, sum(d) OVER ( |
| 183 |
ORDER BY d |
| 184 |
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING |
| 185 |
) FROM t1 |
| 186 |
} {1 5 2 7 3 9 4 11 5 6 6 {}} |
| 187 |
|
| 188 |
do_execsql_test 2.21 { |
| 189 |
SELECT a, sum(d) OVER ( |
| 190 |
ORDER BY d |
| 191 |
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING |
| 192 |
) FROM t1 |
| 193 |
} {1 20 2 18 3 15 4 11 5 6 6 {}} |
| 194 |
|
| 195 |
do_execsql_test 2.22 { |
| 196 |
SELECT a, sum(d) OVER ( |
| 197 |
PARTITION BY b |
| 198 |
ORDER BY d |
| 199 |
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING |
| 200 |
) FROM t1 |
| 201 |
} {2 10 4 6 6 {} 1 8 3 5 5 {}} |
| 202 |
|
| 203 |
do_execsql_test 2.23 { |
| 204 |
SELECT a, sum(d) OVER ( |
| 205 |
ORDER BY d |
| 206 |
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 207 |
) FROM t1 |
| 208 |
} {1 21 2 20 3 18 4 15 5 11 6 6} |
| 209 |
|
| 210 |
do_execsql_test 2.24 { |
| 211 |
SELECT a, sum(d) OVER ( |
| 212 |
PARTITION BY a%2 |
| 213 |
ORDER BY d |
| 214 |
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 215 |
) FROM t1 |
| 216 |
} {2 12 4 10 6 6 1 9 3 8 5 5} |
| 217 |
|
| 218 |
do_execsql_test 2.25 { |
| 219 |
SELECT a, sum(d) OVER ( |
| 220 |
ORDER BY d |
| 221 |
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| 222 |
) FROM t1 |
| 223 |
} {1 21 2 21 3 21 4 21 5 21 6 21} |
| 224 |
|
| 225 |
do_execsql_test 2.26 { |
| 226 |
SELECT a, sum(d) OVER ( |
| 227 |
PARTITION BY b |
| 228 |
ORDER BY d |
| 229 |
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| 230 |
) FROM t1 |
| 231 |
} {2 12 4 12 6 12 1 9 3 9 5 9} |
| 232 |
|
| 233 |
do_execsql_test 2.27 { |
| 234 |
SELECT a, sum(d) OVER ( |
| 235 |
ORDER BY d |
| 236 |
ROWS BETWEEN CURRENT ROW AND CURRENT ROW |
| 237 |
) FROM t1 |
| 238 |
} {1 1 2 2 3 3 4 4 5 5 6 6} |
| 239 |
|
| 240 |
do_execsql_test 2.28 { |
| 241 |
SELECT a, sum(d) OVER ( |
| 242 |
PARTITION BY b |
| 243 |
ORDER BY d |
| 244 |
ROWS BETWEEN CURRENT ROW AND CURRENT ROW |
| 245 |
) FROM t1 |
| 246 |
} {2 2 4 4 6 6 1 1 3 3 5 5} |
| 247 |
|
| 248 |
do_execsql_test 2.29 { |
| 249 |
SELECT a, sum(d) OVER ( |
| 250 |
ORDER BY d |
| 251 |
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 252 |
) FROM t1 |
| 253 |
} {1 21 2 20 3 18 4 15 5 11 6 6} |
| 254 |
|
| 255 |
do_execsql_test 2.30 { |
| 256 |
SELECT a, sum(d) OVER ( |
| 257 |
ORDER BY b |
| 258 |
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 259 |
) FROM t1 |
| 260 |
} {2 21 4 21 6 21 1 9 3 9 5 9} |
| 261 |
|
| 262 |
do_execsql_test 3.1 { |
| 263 |
SELECT a, sum(d) OVER ( |
| 264 |
PARTITION BY b ORDER BY d |
| 265 |
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 266 |
) FROM t1 |
| 267 |
} {2 12 4 10 6 6 1 9 3 8 5 5} |
| 268 |
|
| 269 |
do_execsql_test 3.2 { |
| 270 |
SELECT a, sum(d) OVER ( |
| 271 |
ORDER BY b |
| 272 |
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 273 |
) FROM t1 |
| 274 |
} {2 21 4 21 6 21 1 9 3 9 5 9} |
| 275 |
|
| 276 |
do_execsql_test 3.3 { |
| 277 |
SELECT a, sum(d) OVER ( |
| 278 |
ORDER BY d |
| 279 |
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| 280 |
) FROM t1 |
| 281 |
} {1 21 2 21 3 21 4 21 5 21 6 21} |
| 282 |
|
| 283 |
do_execsql_test 3.4 { |
| 284 |
SELECT a, sum(d) OVER ( |
| 285 |
ORDER BY d/2 |
| 286 |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| 287 |
) FROM t1 |
| 288 |
} {1 1 2 3 3 6 4 10 5 15 6 21} |
| 289 |
|
| 290 |
#========================================================================== |
| 291 |
|
| 292 |
do_execsql_test 4.0 { |
| 293 |
DROP TABLE IF EXISTS t2; |
| 294 |
CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER); |
| 295 |
INSERT INTO t2(a, b) VALUES |
| 296 |
(1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2), |
| 297 |
(10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62), |
| 298 |
(18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78), |
| 299 |
(26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77), |
| 300 |
(34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7), |
| 301 |
(42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43), |
| 302 |
(50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90), |
| 303 |
(58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56), |
| 304 |
(66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98), |
| 305 |
(74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33), |
| 306 |
(82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84), |
| 307 |
(90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13), |
| 308 |
(98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35), |
| 309 |
(106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8), |
| 310 |
(114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73), |
| 311 |
(121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34), |
| 312 |
(128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77), |
| 313 |
(135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70), |
| 314 |
(143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80), |
| 315 |
(150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66), |
| 316 |
(158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37), |
| 317 |
(166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91), |
| 318 |
(173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69), |
| 319 |
(181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84), |
| 320 |
(188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38), |
| 321 |
(195,34), (196,83), (197,27), (198,82), (199,17), (200,7); |
| 322 |
} {} |
| 323 |
|
| 324 |
do_execsql_test 4.1 { |
| 325 |
SELECT a, sum(b) OVER ( |
| 326 |
PARTITION BY (b%10) |
| 327 |
ORDER BY b |
| 328 |
) FROM t2 ORDER BY a; |
| 329 |
} {1 0 2 754 3 251 4 754 5 101 6 1247 7 132 8 266 9 6 10 950 |
| 330 |
11 667 12 1052 13 535 14 128 15 428 16 250 17 336 18 1122 |
| 331 |
19 368 20 6 21 1247 22 1000 23 92 24 368 25 584 26 320 |
| 332 |
27 1000 28 24 29 478 30 133 31 1049 32 1090 33 632 34 101 |
| 333 |
35 54 36 54 37 1049 38 450 39 145 40 354 41 21 42 764 |
| 334 |
43 754 44 424 45 1122 46 930 47 42 48 930 49 352 50 535 |
| 335 |
51 42 52 118 53 536 54 6 55 1122 56 86 57 770 58 255 59 50 |
| 336 |
60 52 61 950 62 75 63 354 64 2 65 536 66 160 67 352 68 536 |
| 337 |
69 54 70 675 71 276 72 950 73 868 74 678 75 667 76 4 |
| 338 |
77 1184 78 160 79 120 80 584 81 266 82 133 83 405 84 468 |
| 339 |
85 6 86 806 87 166 88 500 89 1090 90 552 91 251 92 27 |
| 340 |
93 424 94 687 95 1215 96 450 97 32 98 360 99 1052 100 868 |
| 341 |
101 2 102 66 103 754 104 450 105 145 106 5 107 687 108 24 |
| 342 |
109 302 110 806 111 251 112 42 113 24 114 30 115 128 116 128 |
| 343 |
117 50 118 1215 119 86 120 687 121 683 122 672 123 178 124 24 |
| 344 |
125 24 126 299 127 178 128 770 129 535 130 1052 131 270 |
| 345 |
132 255 133 675 134 632 135 266 136 6 137 21 138 930 139 411 |
| 346 |
140 754 141 133 142 340 143 535 144 46 145 250 146 132 |
| 347 |
147 132 148 354 149 500 150 770 151 276 152 360 153 354 |
| 348 |
154 27 155 552 156 552 157 602 158 266 159 1049 160 675 |
| 349 |
161 384 162 667 163 27 164 101 165 166 166 32 167 42 168 18 |
| 350 |
169 336 170 1122 171 276 172 1122 173 266 174 50 175 178 |
| 351 |
176 276 177 1247 178 6 179 1215 180 604 181 360 182 212 |
| 352 |
183 120 184 210 185 1090 186 10 187 1090 188 266 189 66 |
| 353 |
190 250 191 266 192 360 193 120 194 128 195 178 196 770 |
| 354 |
197 92 198 634 199 38 200 21} |
| 355 |
|
| 356 |
do_execsql_test 4.2 { |
| 357 |
SELECT a, sum(b) OVER ( |
| 358 |
PARTITION BY (b%10) |
| 359 |
ORDER BY b |
| 360 |
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| 361 |
) FROM t2 ORDER BY a; |
| 362 |
} {1 0 2 754 3 251 4 754 5 101 6 1247 7 132 8 266 9 6 10 950 |
| 363 |
11 667 12 1052 13 535 14 128 15 428 16 250 17 336 18 1122 |
| 364 |
19 368 20 6 21 1247 22 1000 23 92 24 368 25 584 26 320 |
| 365 |
27 1000 28 24 29 478 30 133 31 1049 32 1090 33 632 34 101 |
| 366 |
35 54 36 54 37 1049 38 450 39 145 40 354 41 21 42 764 |
| 367 |
43 754 44 424 45 1122 46 930 47 42 48 930 49 352 50 535 |
| 368 |
51 42 52 118 53 536 54 6 55 1122 56 86 57 770 58 255 59 50 |
| 369 |
60 52 61 950 62 75 63 354 64 2 65 536 66 160 67 352 68 536 |
| 370 |
69 54 70 675 71 276 72 950 73 868 74 678 75 667 76 4 |
| 371 |
77 1184 78 160 79 120 80 584 81 266 82 133 83 405 84 468 |
| 372 |
85 6 86 806 87 166 88 500 89 1090 90 552 91 251 92 27 |
| 373 |
93 424 94 687 95 1215 96 450 97 32 98 360 99 1052 100 868 |
| 374 |
101 2 102 66 103 754 104 450 105 145 106 5 107 687 108 24 |
| 375 |
109 302 110 806 111 251 112 42 113 24 114 30 115 128 116 128 |
| 376 |
117 50 118 1215 119 86 120 687 121 683 122 672 123 178 124 24 |
| 377 |
125 24 126 299 127 178 128 770 129 535 130 1052 131 270 |
| 378 |
132 255 133 675 134 632 135 266 136 6 137 21 138 930 139 411 |
| 379 |
140 754 141 133 142 340 143 535 144 46 145 250 146 132 |
| 380 |
147 132 148 354 149 500 150 770 151 276 152 360 153 354 |
| 381 |
154 27 155 552 156 552 157 602 158 266 159 1049 160 675 |
| 382 |
161 384 162 667 163 27 164 101 165 166 166 32 167 42 168 18 |
| 383 |
169 336 170 1122 171 276 172 1122 173 266 174 50 175 178 |
| 384 |
176 276 177 1247 178 6 179 1215 180 604 181 360 182 212 |
| 385 |
183 120 184 210 185 1090 186 10 187 1090 188 266 189 66 |
| 386 |
190 250 191 266 192 360 193 120 194 128 195 178 196 770 |
| 387 |
197 92 198 634 199 38 200 21} |
| 388 |
|
| 389 |
do_execsql_test 4.3 { |
| 390 |
SELECT b, sum(b) OVER ( |
| 391 |
ORDER BY b |
| 392 |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| 393 |
) FROM t2 ORDER BY b; |
| 394 |
} {0 0 1 1 1 2 2 4 2 6 2 8 3 11 3 14 4 18 5 23 6 29 7 36 |
| 395 |
7 43 7 50 8 58 8 66 8 74 9 83 9 92 9 101 10 111 11 122 |
| 396 |
11 133 12 145 12 157 12 169 13 182 13 195 14 209 15 224 |
| 397 |
15 239 15 254 16 270 16 286 16 302 17 319 19 338 20 358 |
| 398 |
21 379 21 400 22 422 22 444 23 467 23 490 23 513 24 537 |
| 399 |
25 562 26 588 26 614 26 640 27 667 27 694 28 722 29 751 |
| 400 |
29 780 29 809 30 839 30 869 30 899 31 930 31 961 32 993 |
| 401 |
33 1026 33 1059 33 1092 33 1125 33 1158 34 1192 34 1226 |
| 402 |
34 1260 34 1294 35 1329 35 1364 36 1400 36 1436 36 1472 |
| 403 |
36 1508 37 1545 37 1582 38 1620 38 1658 39 1697 39 1736 |
| 404 |
39 1775 40 1815 41 1856 41 1897 41 1938 42 1980 43 2023 |
| 405 |
43 2066 44 2110 44 2154 46 2200 46 2246 47 2293 47 2340 |
| 406 |
47 2387 47 2434 49 2483 50 2533 51 2584 52 2636 53 2689 |
| 407 |
54 2743 55 2798 55 2853 56 2909 56 2965 56 3021 57 3078 |
| 408 |
58 3136 58 3194 58 3252 58 3310 59 3369 59 3428 59 3487 |
| 409 |
59 3546 60 3606 61 3667 61 3728 62 3790 62 3852 63 3915 |
| 410 |
64 3979 65 4044 65 4109 65 4174 66 4240 67 4307 68 4375 |
| 411 |
69 4444 70 4514 72 4586 72 4658 72 4730 73 4803 73 4876 |
| 412 |
73 4949 74 5023 74 5097 74 5171 74 5245 74 5319 75 5394 |
| 413 |
75 5469 75 5544 76 5620 77 5697 77 5774 78 5852 78 5930 |
| 414 |
79 6009 80 6089 80 6169 81 6250 81 6331 81 6412 82 6494 |
| 415 |
83 6577 84 6661 84 6745 84 6829 84 6913 85 6998 85 7083 |
| 416 |
85 7168 86 7254 87 7341 87 7428 88 7516 89 7605 89 7694 |
| 417 |
89 7783 90 7873 90 7963 90 8053 91 8144 91 8235 91 8326 |
| 418 |
91 8417 91 8508 93 8601 93 8694 93 8787 94 8881 95 8976 |
| 419 |
95 9071 95 9166 96 9262 96 9358 96 9454 97 9551 97 9648 |
| 420 |
98 9746 98 9844 99 9943 99 10042 99 10141} |
| 421 |
|
| 422 |
do_execsql_test 4.4 { |
| 423 |
SELECT b, sum(b) OVER ( |
| 424 |
ORDER BY b |
| 425 |
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| 426 |
) FROM t2 ORDER BY b; |
| 427 |
} {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 |
| 428 |
3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 |
| 429 |
8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 |
| 430 |
11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 |
| 431 |
14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 |
| 432 |
17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 |
| 433 |
23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 |
| 434 |
26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 |
| 435 |
30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 |
| 436 |
33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 |
| 437 |
34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 |
| 438 |
37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 |
| 439 |
40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 |
| 440 |
44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 |
| 441 |
47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 |
| 442 |
55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 |
| 443 |
58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 |
| 444 |
60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 |
| 445 |
65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 |
| 446 |
70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 |
| 447 |
74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 |
| 448 |
75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 |
| 449 |
80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 |
| 450 |
84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 |
| 451 |
86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 |
| 452 |
90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 |
| 453 |
91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 |
| 454 |
95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 |
| 455 |
98 10141 99 10141 99 10141 99 10141} |
| 456 |
|
| 457 |
do_execsql_test 4.5 { |
| 458 |
SELECT b, sum(b) OVER ( |
| 459 |
ORDER BY b |
| 460 |
RANGE BETWEEN CURRENT ROW AND CURRENT ROW |
| 461 |
) FROM t2 ORDER BY b; |
| 462 |
} {0 0 1 2 1 2 2 6 2 6 2 6 3 6 3 6 4 4 5 5 6 6 7 21 |
| 463 |
7 21 7 21 8 24 8 24 8 24 9 27 9 27 9 27 10 10 11 22 |
| 464 |
11 22 12 36 12 36 12 36 13 26 13 26 14 14 15 45 15 45 |
| 465 |
15 45 16 48 16 48 16 48 17 17 19 19 20 20 21 42 21 42 |
| 466 |
22 44 22 44 23 69 23 69 23 69 24 24 25 25 26 78 26 78 |
| 467 |
26 78 27 54 27 54 28 28 29 87 29 87 29 87 30 90 30 90 |
| 468 |
30 90 31 62 31 62 32 32 33 165 33 165 33 165 33 165 33 165 |
| 469 |
34 136 34 136 34 136 34 136 35 70 35 70 36 144 36 144 |
| 470 |
36 144 36 144 37 74 37 74 38 76 38 76 39 117 39 117 39 117 |
| 471 |
40 40 41 123 41 123 41 123 42 42 43 86 43 86 44 88 44 88 |
| 472 |
46 92 46 92 47 188 47 188 47 188 47 188 49 49 50 50 51 51 |
| 473 |
52 52 53 53 54 54 55 110 55 110 56 168 56 168 56 168 57 57 |
| 474 |
58 232 58 232 58 232 58 232 59 236 59 236 59 236 59 236 |
| 475 |
60 60 61 122 61 122 62 124 62 124 63 63 64 64 65 195 65 195 |
| 476 |
65 195 66 66 67 67 68 68 69 69 70 70 72 216 72 216 72 216 |
| 477 |
73 219 73 219 73 219 74 370 74 370 74 370 74 370 74 370 |
| 478 |
75 225 75 225 75 225 76 76 77 154 77 154 78 156 78 156 |
| 479 |
79 79 80 160 80 160 81 243 81 243 81 243 82 82 83 83 84 336 |
| 480 |
84 336 84 336 84 336 85 255 85 255 85 255 86 86 87 174 |
| 481 |
87 174 88 88 89 267 89 267 89 267 90 270 90 270 90 270 |
| 482 |
91 455 91 455 91 455 91 455 91 455 93 279 93 279 93 279 |
| 483 |
94 94 95 285 95 285 95 285 96 288 96 288 96 288 97 194 |
| 484 |
97 194 98 196 98 196 99 297 99 297 99 297} |
| 485 |
|
| 486 |
do_execsql_test 4.6.1 { |
| 487 |
SELECT b, sum(b) OVER ( |
| 488 |
RANGE BETWEEN CURRENT ROW AND CURRENT ROW |
| 489 |
) FROM t2 ORDER BY b; |
| 490 |
} {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 |
| 491 |
3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 |
| 492 |
8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 |
| 493 |
11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 |
| 494 |
14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 |
| 495 |
17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 |
| 496 |
23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 |
| 497 |
26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 |
| 498 |
30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 |
| 499 |
33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 |
| 500 |
34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 |
| 501 |
37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 |
| 502 |
40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 |
| 503 |
44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 |
| 504 |
47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 |
| 505 |
55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 |
| 506 |
58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 |
| 507 |
60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 |
| 508 |
65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 |
| 509 |
70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 |
| 510 |
74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 |
| 511 |
75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 |
| 512 |
80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 |
| 513 |
84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 |
| 514 |
86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 |
| 515 |
90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 |
| 516 |
91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 |
| 517 |
95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 |
| 518 |
98 10141 99 10141 99 10141 99 10141} |
| 519 |
|
| 520 |
do_execsql_test 4.6.2 { |
| 521 |
SELECT b, sum(b) OVER () FROM t2 ORDER BY b; |
| 522 |
} {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 |
| 523 |
3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 |
| 524 |
8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 |
| 525 |
11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 |
| 526 |
14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 |
| 527 |
17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 |
| 528 |
23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 |
| 529 |
26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 |
| 530 |
30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 |
| 531 |
33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 |
| 532 |
34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 |
| 533 |
37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 |
| 534 |
40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 |
| 535 |
44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 |
| 536 |
47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 |
| 537 |
55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 |
| 538 |
58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 |
| 539 |
60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 |
| 540 |
65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 |
| 541 |
70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 |
| 542 |
74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 |
| 543 |
75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 |
| 544 |
80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 |
| 545 |
84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 |
| 546 |
86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 |
| 547 |
90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 |
| 548 |
91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 |
| 549 |
95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 |
| 550 |
98 10141 99 10141 99 10141 99 10141} |
| 551 |
|
| 552 |
do_execsql_test 4.6.3 { |
| 553 |
SELECT b, sum(b) OVER ( |
| 554 |
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| 555 |
) FROM t2 ORDER BY b; |
| 556 |
} {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 |
| 557 |
3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 |
| 558 |
8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 |
| 559 |
11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 |
| 560 |
14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 |
| 561 |
17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 |
| 562 |
23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 |
| 563 |
26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 |
| 564 |
30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 |
| 565 |
33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 |
| 566 |
34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 |
| 567 |
37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 |
| 568 |
40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 |
| 569 |
44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 |
| 570 |
47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 |
| 571 |
55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 |
| 572 |
58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 |
| 573 |
60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 |
| 574 |
65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 |
| 575 |
70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 |
| 576 |
74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 |
| 577 |
75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 |
| 578 |
80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 |
| 579 |
84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 |
| 580 |
86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 |
| 581 |
90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 |
| 582 |
91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 |
| 583 |
95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 |
| 584 |
98 10141 99 10141 99 10141 99 10141} |
| 585 |
|
| 586 |
do_execsql_test 4.6.4 { |
| 587 |
SELECT b, sum(b) OVER ( |
| 588 |
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 589 |
) FROM t2 ORDER BY b; |
| 590 |
} {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 |
| 591 |
3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 |
| 592 |
8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 |
| 593 |
11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 |
| 594 |
14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 |
| 595 |
17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 |
| 596 |
23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 |
| 597 |
26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 |
| 598 |
30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 |
| 599 |
33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 |
| 600 |
34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 |
| 601 |
37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 |
| 602 |
40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 |
| 603 |
44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 |
| 604 |
47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 |
| 605 |
55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 |
| 606 |
58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 |
| 607 |
60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 |
| 608 |
65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 |
| 609 |
70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 |
| 610 |
74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 |
| 611 |
75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 |
| 612 |
80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 |
| 613 |
84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 |
| 614 |
86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 |
| 615 |
90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 |
| 616 |
91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 |
| 617 |
95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 |
| 618 |
98 10141 99 10141 99 10141 99 10141} |
| 619 |
|
| 620 |
do_execsql_test 4.7.1 { |
| 621 |
SELECT b, sum(b) OVER ( |
| 622 |
ROWS BETWEEN CURRENT ROW AND CURRENT ROW |
| 623 |
) FROM t2 ORDER BY 1, 2; |
| 624 |
} {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 |
| 625 |
7 7 8 8 8 8 8 8 9 9 9 9 9 9 10 10 11 11 11 11 12 12 |
| 626 |
12 12 12 12 13 13 13 13 14 14 15 15 15 15 15 15 16 16 |
| 627 |
16 16 16 16 17 17 19 19 20 20 21 21 21 21 22 22 22 22 |
| 628 |
23 23 23 23 23 23 24 24 25 25 26 26 26 26 26 26 27 27 |
| 629 |
27 27 28 28 29 29 29 29 29 29 30 30 30 30 30 30 31 31 |
| 630 |
31 31 32 32 33 33 33 33 33 33 33 33 33 33 34 34 34 34 |
| 631 |
34 34 34 34 35 35 35 35 36 36 36 36 36 36 36 36 37 37 |
| 632 |
37 37 38 38 38 38 39 39 39 39 39 39 40 40 41 41 41 41 |
| 633 |
41 41 42 42 43 43 43 43 44 44 44 44 46 46 46 46 47 47 |
| 634 |
47 47 47 47 47 47 49 49 50 50 51 51 52 52 53 53 54 54 |
| 635 |
55 55 55 55 56 56 56 56 56 56 57 57 58 58 58 58 58 58 |
| 636 |
58 58 59 59 59 59 59 59 59 59 60 60 61 61 61 61 62 62 |
| 637 |
62 62 63 63 64 64 65 65 65 65 65 65 66 66 67 67 68 68 |
| 638 |
69 69 70 70 72 72 72 72 72 72 73 73 73 73 73 73 74 74 |
| 639 |
74 74 74 74 74 74 74 74 75 75 75 75 75 75 76 76 77 77 |
| 640 |
77 77 78 78 78 78 79 79 80 80 80 80 81 81 81 81 81 81 |
| 641 |
82 82 83 83 84 84 84 84 84 84 84 84 85 85 85 85 85 85 |
| 642 |
86 86 87 87 87 87 88 88 89 89 89 89 89 89 90 90 90 90 |
| 643 |
90 90 91 91 91 91 91 91 91 91 91 91 93 93 93 93 93 93 |
| 644 |
94 94 95 95 95 95 95 95 96 96 96 96 96 96 97 97 97 97 |
| 645 |
98 98 98 98 99 99 99 99 99 99} |
| 646 |
|
| 647 |
do_execsql_test 4.7.2 { |
| 648 |
SELECT b, sum(b) OVER ( |
| 649 |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| 650 |
) FROM t2 ORDER BY 1, 2; |
| 651 |
} {0 0 1 3379 1 5443 2 372 2 4473 2 7074 3 2916 3 9096 4 4049 |
| 652 |
5 5643 6 1047 7 2205 7 7081 7 10141 8 1553 8 5926 8 6422 |
| 653 |
9 4883 9 7932 9 8497 10 9544 11 5727 11 6433 12 2825 12 5918 |
| 654 |
12 8582 13 5190 13 8570 14 8596 15 3189 15 6023 15 8924 |
| 655 |
16 1942 16 1958 16 3590 17 10134 19 7474 20 5946 21 5464 |
| 656 |
21 9682 22 3029 22 6140 23 212 23 1926 23 8520 24 2626 |
| 657 |
25 3331 26 337 26 7539 26 7565 27 1270 27 10035 28 3217 |
| 658 |
29 1649 29 4355 29 7326 30 4215 30 9400 30 9853 31 5977 |
| 659 |
31 6008 32 2857 33 370 33 4326 33 8175 33 8909 33 9661 |
| 660 |
34 6414 34 6516 34 8958 34 9925 35 2151 35 5638 36 3701 |
| 661 |
36 7818 36 8785 36 8994 37 4597 37 8557 38 735 38 9891 39 842 |
| 662 |
39 7513 39 9721 40 3475 41 115 41 4874 41 5906 42 4185 |
| 663 |
43 2754 43 3518 44 7072 44 9765 46 1041 46 1316 47 2198 |
| 664 |
47 3378 47 7612 47 7923 49 6482 50 9450 51 5778 52 9370 |
| 665 |
53 4408 54 1448 55 3174 55 6876 56 2913 56 3435 56 3574 |
| 666 |
57 7223 58 5248 58 7876 58 9318 58 9823 59 697 59 2813 |
| 667 |
59 6665 59 7455 60 6821 61 2426 61 4944 62 904 62 8658 |
| 668 |
63 4471 64 8407 65 2116 65 5177 65 5603 66 8142 67 1620 |
| 669 |
68 803 69 9260 70 7396 72 4833 72 8004 72 8076 73 5017 |
| 670 |
73 5716 73 6213 74 74 74 189 74 2365 74 5538 74 7297 75 3665 |
| 671 |
75 6951 75 8343 76 3964 77 1903 77 7028 78 1394 78 4293 |
| 672 |
79 6292 80 4677 80 7692 81 542 81 4045 81 8488 82 10117 |
| 673 |
83 10008 84 1826 84 4761 84 9534 84 9628 85 2602 85 2711 |
| 674 |
85 7166 86 2291 87 4560 87 5865 88 6380 89 461 89 3306 |
| 675 |
89 3790 90 3119 90 6606 90 7782 91 995 91 2517 91 3007 |
| 676 |
91 8749 91 8876 93 1742 93 2051 93 8268 94 4143 95 5112 |
| 677 |
95 6118 95 9191 96 638 96 5344 96 6761 97 1243 97 1545 |
| 678 |
98 3888 98 5442 99 311 99 1146 99 9093} |
| 679 |
|
| 680 |
do_execsql_test 4.7.3 { |
| 681 |
SELECT b, sum(b) OVER ( |
| 682 |
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| 683 |
) FROM t2 ORDER BY 1, 2; |
| 684 |
} {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 |
| 685 |
3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 |
| 686 |
8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 |
| 687 |
11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 |
| 688 |
14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 |
| 689 |
17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 |
| 690 |
23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 |
| 691 |
26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 |
| 692 |
30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 |
| 693 |
33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 |
| 694 |
34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 |
| 695 |
37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 |
| 696 |
40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 |
| 697 |
44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 |
| 698 |
47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 |
| 699 |
55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 |
| 700 |
58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 |
| 701 |
60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 |
| 702 |
65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 |
| 703 |
70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 |
| 704 |
74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 |
| 705 |
75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 |
| 706 |
80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 |
| 707 |
84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 |
| 708 |
86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 |
| 709 |
90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 |
| 710 |
91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 |
| 711 |
95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 |
| 712 |
98 10141 99 10141 99 10141 99 10141} |
| 713 |
|
| 714 |
do_execsql_test 4.7.4 { |
| 715 |
SELECT b, sum(b) OVER ( |
| 716 |
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 717 |
) FROM t2 ORDER BY 1, 2; |
| 718 |
} {0 10141 1 4699 1 6763 2 3069 2 5670 2 9771 3 1048 3 7228 |
| 719 |
4 6096 5 4503 6 9100 7 7 7 3067 7 7943 8 3727 8 4223 8 8596 |
| 720 |
9 1653 9 2218 9 5267 10 607 11 3719 11 4425 12 1571 12 4235 |
| 721 |
12 7328 13 1584 13 4964 14 1559 15 1232 15 4133 15 6967 |
| 722 |
16 6567 16 8199 16 8215 17 24 19 2686 20 4215 21 480 21 4698 |
| 723 |
22 4023 22 7134 23 1644 23 8238 23 9952 24 7539 25 6835 |
| 724 |
26 2602 26 2628 26 9830 27 133 27 8898 28 6952 29 2844 |
| 725 |
29 5815 29 8521 30 318 30 771 30 5956 31 4164 31 4195 32 7316 |
| 726 |
33 513 33 1265 33 1999 33 5848 33 9804 34 250 34 1217 34 3659 |
| 727 |
34 3761 35 4538 35 8025 36 1183 36 1392 36 2359 36 6476 |
| 728 |
37 1621 37 5581 38 288 38 9444 39 459 39 2667 39 9338 40 6706 |
| 729 |
41 4276 41 5308 41 10067 42 5998 43 6666 43 7430 44 420 |
| 730 |
44 3113 46 8871 46 9146 47 2265 47 2576 47 6810 47 7990 |
| 731 |
49 3708 50 741 51 4414 52 823 53 5786 54 8747 55 3320 55 7022 |
| 732 |
56 6623 56 6762 56 7284 57 2975 58 376 58 881 58 2323 58 4951 |
| 733 |
59 2745 59 3535 59 7387 59 9503 60 3380 61 5258 61 7776 |
| 734 |
62 1545 62 9299 63 5733 64 1798 65 4603 65 5029 65 8090 |
| 735 |
66 2065 67 8588 68 9406 69 950 70 2815 72 2137 72 2209 |
| 736 |
72 5380 73 4001 73 4498 73 5197 74 2918 74 4677 74 7850 |
| 737 |
74 10026 74 10141 75 1873 75 3265 75 6551 76 6253 77 3190 |
| 738 |
77 8315 78 5926 78 8825 79 3928 80 2529 80 5544 81 1734 |
| 739 |
81 6177 81 9680 82 106 83 216 84 597 84 691 84 5464 84 8399 |
| 740 |
85 3060 85 7515 85 7624 86 7936 87 4363 87 5668 88 3849 |
| 741 |
89 6440 89 6924 89 9769 90 2449 90 3625 90 7112 91 1356 |
| 742 |
91 1483 91 7225 91 7715 91 9237 93 1966 93 8183 93 8492 |
| 743 |
94 6092 95 1045 95 4118 95 5124 96 3476 96 4893 96 9599 |
| 744 |
97 8693 97 8995 98 4797 98 6351 99 1147 99 9094 99 9929} |
| 745 |
|
| 746 |
do_execsql_test 4.8.1 { |
| 747 |
SELECT b, sum(b) OVER ( |
| 748 |
ORDER BY a |
| 749 |
ROWS BETWEEN CURRENT ROW AND CURRENT ROW |
| 750 |
) FROM t2 ORDER BY 1, 2; |
| 751 |
} {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 |
| 752 |
7 7 8 8 8 8 8 8 9 9 9 9 9 9 10 10 11 11 11 11 12 12 |
| 753 |
12 12 12 12 13 13 13 13 14 14 15 15 15 15 15 15 16 16 |
| 754 |
16 16 16 16 17 17 19 19 20 20 21 21 21 21 22 22 22 22 |
| 755 |
23 23 23 23 23 23 24 24 25 25 26 26 26 26 26 26 27 27 |
| 756 |
27 27 28 28 29 29 29 29 29 29 30 30 30 30 30 30 31 31 |
| 757 |
31 31 32 32 33 33 33 33 33 33 33 33 33 33 34 34 34 34 |
| 758 |
34 34 34 34 35 35 35 35 36 36 36 36 36 36 36 36 37 37 |
| 759 |
37 37 38 38 38 38 39 39 39 39 39 39 40 40 41 41 41 41 |
| 760 |
41 41 42 42 43 43 43 43 44 44 44 44 46 46 46 46 47 47 |
| 761 |
47 47 47 47 47 47 49 49 50 50 51 51 52 52 53 53 54 54 |
| 762 |
55 55 55 55 56 56 56 56 56 56 57 57 58 58 58 58 58 58 |
| 763 |
58 58 59 59 59 59 59 59 59 59 60 60 61 61 61 61 62 62 |
| 764 |
62 62 63 63 64 64 65 65 65 65 65 65 66 66 67 67 68 68 |
| 765 |
69 69 70 70 72 72 72 72 72 72 73 73 73 73 73 73 74 74 |
| 766 |
74 74 74 74 74 74 74 74 75 75 75 75 75 75 76 76 77 77 |
| 767 |
77 77 78 78 78 78 79 79 80 80 80 80 81 81 81 81 81 81 |
| 768 |
82 82 83 83 84 84 84 84 84 84 84 84 85 85 85 85 85 85 |
| 769 |
86 86 87 87 87 87 88 88 89 89 89 89 89 89 90 90 90 90 |
| 770 |
90 90 91 91 91 91 91 91 91 91 91 91 93 93 93 93 93 93 |
| 771 |
94 94 95 95 95 95 95 95 96 96 96 96 96 96 97 97 97 97 |
| 772 |
98 98 98 98 99 99 99 99 99 99} |
| 773 |
|
| 774 |
do_execsql_test 4.8.2 { |
| 775 |
SELECT b, sum(b) OVER ( |
| 776 |
ORDER BY a |
| 777 |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| 778 |
) FROM t2 ORDER BY 1, 2; |
| 779 |
} {0 0 1 3379 1 5443 2 372 2 4473 2 7074 3 2916 3 9096 4 4049 |
| 780 |
5 5643 6 1047 7 2205 7 7081 7 10141 8 1553 8 5926 8 6422 |
| 781 |
9 4883 9 7932 9 8497 10 9544 11 5727 11 6433 12 2825 12 5918 |
| 782 |
12 8582 13 5190 13 8570 14 8596 15 3189 15 6023 15 8924 |
| 783 |
16 1942 16 1958 16 3590 17 10134 19 7474 20 5946 21 5464 |
| 784 |
21 9682 22 3029 22 6140 23 212 23 1926 23 8520 24 2626 |
| 785 |
25 3331 26 337 26 7539 26 7565 27 1270 27 10035 28 3217 |
| 786 |
29 1649 29 4355 29 7326 30 4215 30 9400 30 9853 31 5977 |
| 787 |
31 6008 32 2857 33 370 33 4326 33 8175 33 8909 33 9661 |
| 788 |
34 6414 34 6516 34 8958 34 9925 35 2151 35 5638 36 3701 |
| 789 |
36 7818 36 8785 36 8994 37 4597 37 8557 38 735 38 9891 39 842 |
| 790 |
39 7513 39 9721 40 3475 41 115 41 4874 41 5906 42 4185 |
| 791 |
43 2754 43 3518 44 7072 44 9765 46 1041 46 1316 47 2198 |
| 792 |
47 3378 47 7612 47 7923 49 6482 50 9450 51 5778 52 9370 |
| 793 |
53 4408 54 1448 55 3174 55 6876 56 2913 56 3435 56 3574 |
| 794 |
57 7223 58 5248 58 7876 58 9318 58 9823 59 697 59 2813 |
| 795 |
59 6665 59 7455 60 6821 61 2426 61 4944 62 904 62 8658 |
| 796 |
63 4471 64 8407 65 2116 65 5177 65 5603 66 8142 67 1620 |
| 797 |
68 803 69 9260 70 7396 72 4833 72 8004 72 8076 73 5017 |
| 798 |
73 5716 73 6213 74 74 74 189 74 2365 74 5538 74 7297 75 3665 |
| 799 |
75 6951 75 8343 76 3964 77 1903 77 7028 78 1394 78 4293 |
| 800 |
79 6292 80 4677 80 7692 81 542 81 4045 81 8488 82 10117 |
| 801 |
83 10008 84 1826 84 4761 84 9534 84 9628 85 2602 85 2711 |
| 802 |
85 7166 86 2291 87 4560 87 5865 88 6380 89 461 89 3306 |
| 803 |
89 3790 90 3119 90 6606 90 7782 91 995 91 2517 91 3007 |
| 804 |
91 8749 91 8876 93 1742 93 2051 93 8268 94 4143 95 5112 |
| 805 |
95 6118 95 9191 96 638 96 5344 96 6761 97 1243 97 1545 |
| 806 |
98 3888 98 5442 99 311 99 1146 99 9093} |
| 807 |
|
| 808 |
do_execsql_test 4.8.3 { |
| 809 |
SELECT b, sum(b) OVER ( |
| 810 |
ORDER BY a |
| 811 |
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| 812 |
) FROM t2 ORDER BY 1, 2; |
| 813 |
} {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 |
| 814 |
3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 |
| 815 |
8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 |
| 816 |
11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 |
| 817 |
14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 |
| 818 |
17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 |
| 819 |
23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 |
| 820 |
26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 |
| 821 |
30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 |
| 822 |
33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 |
| 823 |
34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 |
| 824 |
37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 |
| 825 |
40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 |
| 826 |
44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 |
| 827 |
47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 |
| 828 |
55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 |
| 829 |
58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 |
| 830 |
60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 |
| 831 |
65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 |
| 832 |
70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 |
| 833 |
74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 |
| 834 |
75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 |
| 835 |
80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 |
| 836 |
84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 |
| 837 |
86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 |
| 838 |
90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 |
| 839 |
91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 |
| 840 |
95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 |
| 841 |
98 10141 99 10141 99 10141 99 10141} |
| 842 |
|
| 843 |
do_execsql_test 4.8.4 { |
| 844 |
SELECT b, sum(b) OVER ( |
| 845 |
ORDER BY a |
| 846 |
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
| 847 |
) FROM t2 ORDER BY 1, 2; |
| 848 |
} {0 10141 1 4699 1 6763 2 3069 2 5670 2 9771 3 1048 3 7228 |
| 849 |
4 6096 5 4503 6 9100 7 7 7 3067 7 7943 8 3727 8 4223 8 8596 |
| 850 |
9 1653 9 2218 9 5267 10 607 11 3719 11 4425 12 1571 12 4235 |
| 851 |
12 7328 13 1584 13 4964 14 1559 15 1232 15 4133 15 6967 |
| 852 |
16 6567 16 8199 16 8215 17 24 19 2686 20 4215 21 480 21 4698 |
| 853 |
22 4023 22 7134 23 1644 23 8238 23 9952 24 7539 25 6835 |
| 854 |
26 2602 26 2628 26 9830 27 133 27 8898 28 6952 29 2844 |
| 855 |
29 5815 29 8521 30 318 30 771 30 5956 31 4164 31 4195 32 7316 |
| 856 |
33 513 33 1265 33 1999 33 5848 33 9804 34 250 34 1217 34 3659 |
| 857 |
34 3761 35 4538 35 8025 36 1183 36 1392 36 2359 36 6476 |
| 858 |
37 1621 37 5581 38 288 38 9444 39 459 39 2667 39 9338 40 6706 |
| 859 |
41 4276 41 5308 41 10067 42 5998 43 6666 43 7430 44 420 |
| 860 |
44 3113 46 8871 46 9146 47 2265 47 2576 47 6810 47 7990 |
| 861 |
49 3708 50 741 51 4414 52 823 53 5786 54 8747 55 3320 55 7022 |
| 862 |
56 6623 56 6762 56 7284 57 2975 58 376 58 881 58 2323 58 4951 |
| 863 |
59 2745 59 3535 59 7387 59 9503 60 3380 61 5258 61 7776 |
| 864 |
62 1545 62 9299 63 5733 64 1798 65 4603 65 5029 65 8090 |
| 865 |
66 2065 67 8588 68 9406 69 950 70 2815 72 2137 72 2209 |
| 866 |
72 5380 73 4001 73 4498 73 5197 74 2918 74 4677 74 7850 |
| 867 |
74 10026 74 10141 75 1873 75 3265 75 6551 76 6253 77 3190 |
| 868 |
77 8315 78 5926 78 8825 79 3928 80 2529 80 5544 81 1734 |
| 869 |
81 6177 81 9680 82 106 83 216 84 597 84 691 84 5464 84 8399 |
| 870 |
85 3060 85 7515 85 7624 86 7936 87 4363 87 5668 88 3849 |
| 871 |
89 6440 89 6924 89 9769 90 2449 90 3625 90 7112 91 1356 |
| 872 |
91 1483 91 7225 91 7715 91 9237 93 1966 93 8183 93 8492 |
| 873 |
94 6092 95 1045 95 4118 95 5124 96 3476 96 4893 96 9599 |
| 874 |
97 8693 97 8995 98 4797 98 6351 99 1147 99 9094 99 9929} |
| 875 |
|
| 876 |
|
| 877 |
do_test 4.9 { |
| 878 |
set myres {} |
| 879 |
foreach r [db eval {SELECT |
| 880 |
rank() OVER win AS rank, |
| 881 |
cume_dist() OVER win AS cume_dist FROM t1 |
| 882 |
WINDOW win AS (ORDER BY 1);}] { |
| 883 |
lappend myres [format %.4f [set r]] |
| 884 |
} |
| 885 |
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} |
| 886 |
set i 0 |
| 887 |
foreach r [set myres] r2 [set res2] { |
| 888 |
if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} { |
| 889 |
error "list element [set i] does not match: got=[set r] expected=[set r2]" |
| 890 |
} |
| 891 |
incr i |
| 892 |
} |
| 893 |
set {} {} |
| 894 |
} {} |
| 895 |
|
| 896 |
do_execsql_test 4.10 { |
| 897 |
SELECT count(*) OVER (ORDER BY b) FROM t1 |
| 898 |
} {3 3 3 6 6 6} |
| 899 |
|
| 900 |
do_execsql_test 4.11 { |
| 901 |
SELECT count(distinct a) FILTER (WHERE b='odd') FROM t1 |
| 902 |
} {3} |
| 903 |
|
| 904 |
#========================================================================== |
| 905 |
|
| 906 |
do_execsql_test 5.0 { |
| 907 |
DROP TABLE IF EXISTS t1; |
| 908 |
CREATE TABLE t1(x INTEGER, y INTEGER); |
| 909 |
INSERT INTO t1 VALUES(10, 1); |
| 910 |
INSERT INTO t1 VALUES(20, 2); |
| 911 |
INSERT INTO t1 VALUES(3, 3); |
| 912 |
INSERT INTO t1 VALUES(2, 4); |
| 913 |
INSERT INTO t1 VALUES(1, 5); |
| 914 |
} {} |
| 915 |
|
| 916 |
|
| 917 |
do_test 5.1 { |
| 918 |
set myres {} |
| 919 |
foreach r [db eval {SELECT avg(x) OVER (ORDER BY y) AS z FROM t1 ORDER BY z;}] { |
| 920 |
lappend myres [format %.4f [set r]] |
| 921 |
} |
| 922 |
set res2 {7.2000 8.7500 10.0000 11.0000 15.0000} |
| 923 |
set i 0 |
| 924 |
foreach r [set myres] r2 [set res2] { |
| 925 |
if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} { |
| 926 |
error "list element [set i] does not match: got=[set r] expected=[set r2]" |
| 927 |
} |
| 928 |
incr i |
| 929 |
} |
| 930 |
set {} {} |
| 931 |
} {} |
| 932 |
|
| 933 |
#========================================================================== |
| 934 |
|
| 935 |
do_execsql_test 6.0 { |
| 936 |
DROP TABLE IF EXISTS t0; |
| 937 |
CREATE TABLE t0(c0 INTEGER UNIQUE); |
| 938 |
INSERT INTO t0 VALUES(0); |
| 939 |
} {} |
| 940 |
|
| 941 |
do_execsql_test 6.1 { |
| 942 |
SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0; |
| 943 |
} {1 {}} |
| 944 |
|
| 945 |
do_execsql_test 6.2 { |
| 946 |
SELECT * FROM t0 WHERE |
| 947 |
(0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0); |
| 948 |
} {} |
| 949 |
|
| 950 |
#========================================================================== |
| 951 |
|
| 952 |
do_execsql_test 7.0 { |
| 953 |
DROP TABLE IF EXISTS t1; |
| 954 |
CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER); |
| 955 |
INSERT INTO t1 VALUES(1, 1, 1); |
| 956 |
INSERT INTO t1 VALUES(1, 2, 2); |
| 957 |
INSERT INTO t1 VALUES(3, 3, 3); |
| 958 |
INSERT INTO t1 VALUES(3, 4, 4); |
| 959 |
} {} |
| 960 |
|
| 961 |
do_execsql_test 7.1 { |
| 962 |
SELECT c, sum(c) OVER win1 FROM t1 |
| 963 |
WINDOW win1 AS (ORDER BY b) |
| 964 |
} {1 1 2 3 3 6 4 10} |
| 965 |
|
| 966 |
do_execsql_test 7.2 { |
| 967 |
SELECT c, sum(c) OVER win1 FROM t1 |
| 968 |
WINDOW win1 AS (PARTITION BY 1 ORDER BY b) |
| 969 |
} {1 1 2 3 3 6 4 10} |
| 970 |
|
| 971 |
do_execsql_test 7.3 { |
| 972 |
SELECT c, sum(c) OVER win1 FROM t1 |
| 973 |
WINDOW win1 AS (ORDER BY 1) |
| 974 |
} {1 10 2 10 3 10 4 10} |
| 975 |
|
| 976 |
finish_test |