1# 2019 March 01 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 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20set testprefix window7 21 22ifcapable !windowfunc { finish_test ; return } 23do_execsql_test 1.0 { 24 DROP TABLE IF EXISTS t3; 25 CREATE TABLE t3(a INTEGER, b INTEGER); 26 INSERT INTO t3 VALUES 27 (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), 28 (9, 9), (0, 10), (1, 11), (2, 12), (3, 13), (4, 14), (5, 15), (6, 16), 29 (7, 17), (8, 18), (9, 19), (0, 20), (1, 21), (2, 22), (3, 23), (4, 24), 30 (5, 25), (6, 26), (7, 27), (8, 28), (9, 29), (0, 30), (1, 31), (2, 32), 31 (3, 33), (4, 34), (5, 35), (6, 36), (7, 37), (8, 38), (9, 39), (0, 40), 32 (1, 41), (2, 42), (3, 43), (4, 44), (5, 45), (6, 46), (7, 47), (8, 48), 33 (9, 49), (0, 50), (1, 51), (2, 52), (3, 53), (4, 54), (5, 55), (6, 56), 34 (7, 57), (8, 58), (9, 59), (0, 60), (1, 61), (2, 62), (3, 63), (4, 64), 35 (5, 65), (6, 66), (7, 67), (8, 68), (9, 69), (0, 70), (1, 71), (2, 72), 36 (3, 73), (4, 74), (5, 75), (6, 76), (7, 77), (8, 78), (9, 79), (0, 80), 37 (1, 81), (2, 82), (3, 83), (4, 84), (5, 85), (6, 86), (7, 87), (8, 88), 38 (9, 89), (0, 90), (1, 91), (2, 92), (3, 93), (4, 94), (5, 95), (6, 96), 39 (7, 97), (8, 98), (9, 99), (0, 100); 40} {} 41 42do_execsql_test 1.1 { 43 SELECT a, sum(b) FROM t3 GROUP BY a ORDER BY 1; 44} {0 550 1 460 2 470 3 480 4 490 5 500 6 510 7 520 8 530 45 9 540} 46 47do_execsql_test 1.2 { 48 SELECT a, sum(b) OVER ( 49 ORDER BY a GROUPS BETWEEN CURRENT ROW AND CURRENT ROW 50 ) FROM t3 ORDER BY 1; 51} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 52 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 53 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 54 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 55 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 56 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 57 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 58 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 59 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 60 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 61 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 62 9 540} 63 64do_execsql_test 1.3 { 65 SELECT a, sum(b) OVER ( 66 ORDER BY a GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING 67 ) FROM t3 ORDER BY 1; 68} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 69 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 70 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 71 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 72 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 73 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 74 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 75 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 76 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 77 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 78 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 79 9 540} 80 81do_execsql_test 1.4 { 82 SELECT a, sum(b) OVER ( 83 ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING 84 ) FROM t3 ORDER BY 1; 85} {0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 86 0 1480 0 1480 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 87 1 1960 1 1960 1 1960 1 1960 2 2450 2 2450 2 2450 2 2450 88 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 3 2400 3 2400 89 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 90 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 91 4 2450 4 2450 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 92 5 2500 5 2500 5 2500 5 2500 6 2550 6 2550 6 2550 6 2550 93 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 7 2600 7 2600 94 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 95 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 96 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 97 9 1590 9 1590 9 1590 9 1590} 98 99do_execsql_test 1.5 { 100 SELECT a, sum(b) OVER ( 101 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING 102 ) FROM t3 ORDER BY 1; 103} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 104 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 105 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 106 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 107 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 108 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 109 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 110 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 111 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 112 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 113 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 114 9 540} 115 116do_execsql_test 1.6 { 117 SELECT a, sum(b) OVER ( 118 ORDER BY a RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING 119 ) FROM t3 ORDER BY 1; 120} {0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 121 0 1480 0 1480 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 122 1 1960 1 1960 1 1960 1 1960 2 2450 2 2450 2 2450 2 2450 123 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 3 2400 3 2400 124 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 125 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 126 4 2450 4 2450 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 127 5 2500 5 2500 5 2500 5 2500 6 2550 6 2550 6 2550 6 2550 128 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 7 2600 7 2600 129 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 130 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 131 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 132 9 1590 9 1590 9 1590 9 1590} 133 134do_execsql_test 1.7 { 135 SELECT a, sum(b) OVER ( 136 ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING 137 ) FROM t3 ORDER BY 1; 138} {0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 139 0 1010 0 1010 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 140 1 1480 1 1480 1 1480 1 1480 2 1960 2 1960 2 1960 2 1960 141 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 3 1900 3 1900 142 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 143 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 144 4 1940 4 1940 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 145 5 1980 5 1980 5 1980 5 1980 6 2020 6 2020 6 2020 6 2020 146 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 7 2060 7 2060 147 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 148 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 149 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 150 9 1590 9 1590 9 1590 9 1590} 151 152do_execsql_test 1.8.1 { 153 SELECT a, sum(b) OVER ( 154 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING 155 ) FROM t3 ORDER BY 1; 156} {0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 157 0 1010 0 1010 1 930 1 930 1 930 1 930 1 930 1 930 1 930 158 1 930 1 930 1 930 2 950 2 950 2 950 2 950 2 950 2 950 159 2 950 2 950 2 950 2 950 3 970 3 970 3 970 3 970 3 970 160 3 970 3 970 3 970 3 970 3 970 4 990 4 990 4 990 4 990 161 4 990 4 990 4 990 4 990 4 990 4 990 5 1010 5 1010 5 1010 162 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 6 1030 163 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 164 6 1030 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 165 7 1050 7 1050 7 1050 8 1070 8 1070 8 1070 8 1070 8 1070 166 8 1070 8 1070 8 1070 8 1070 8 1070 9 540 9 540 9 540 9 540 167 9 540 9 540 9 540 9 540 9 540 9 540} 168 169do_execsql_test 1.8.2 { 170 SELECT a, sum(b) OVER ( 171 ORDER BY a DESC RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING 172 ) FROM t3 ORDER BY 1; 173} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 174 0 550 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 175 1 1010 1 1010 1 1010 2 930 2 930 2 930 2 930 2 930 2 930 176 2 930 2 930 2 930 2 930 3 950 3 950 3 950 3 950 3 950 177 3 950 3 950 3 950 3 950 3 950 4 970 4 970 4 970 4 970 178 4 970 4 970 4 970 4 970 4 970 4 970 5 990 5 990 5 990 179 5 990 5 990 5 990 5 990 5 990 5 990 5 990 6 1010 6 1010 180 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 181 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 182 7 1030 7 1030 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 183 8 1050 8 1050 8 1050 8 1050 9 1070 9 1070 9 1070 9 1070 184 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070} 185 186finish_test 187