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 9 540} 45 46do_execsql_test 1.2 { 47 SELECT a, sum(b) OVER ( 48 ORDER BY a GROUPS BETWEEN CURRENT ROW AND CURRENT ROW 49 ) FROM t3 ORDER BY 1; 50} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} 51 52do_execsql_test 1.3 { 53 SELECT a, sum(b) OVER ( 54 ORDER BY a GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING 55 ) FROM t3 ORDER BY 1; 56} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} 57 58do_execsql_test 1.4 { 59 SELECT a, sum(b) OVER ( 60 ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING 61 ) FROM t3 ORDER BY 1; 62} {0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590} 63 64do_execsql_test 1.5 { 65 SELECT a, sum(b) OVER ( 66 ORDER BY a RANGE 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 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} 69 70do_execsql_test 1.6 { 71 SELECT a, sum(b) OVER ( 72 ORDER BY a RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING 73 ) FROM t3 ORDER BY 1; 74} {0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590} 75 76do_execsql_test 1.7 { 77 SELECT a, sum(b) OVER ( 78 ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING 79 ) FROM t3 ORDER BY 1; 80} {0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590} 81 82do_execsql_test 1.8.1 { 83 SELECT a, sum(b) OVER ( 84 ORDER BY a RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING 85 ) FROM t3 ORDER BY 1; 86} {0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 1 930 1 930 1 930 1 930 1 930 1 930 1 930 1 930 1 930 1 930 2 950 2 950 2 950 2 950 2 950 2 950 2 950 2 950 2 950 2 950 3 970 3 970 3 970 3 970 3 970 3 970 3 970 3 970 3 970 3 970 4 990 4 990 4 990 4 990 4 990 4 990 4 990 4 990 4 990 4 990 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} 87 88do_execsql_test 1.8.2 { 89 SELECT a, sum(b) OVER ( 90 ORDER BY a DESC RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING 91 ) FROM t3 ORDER BY 1; 92} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 2 930 2 930 2 930 2 930 2 930 2 930 2 930 2 930 2 930 2 930 3 950 3 950 3 950 3 950 3 950 3 950 3 950 3 950 3 950 3 950 4 970 4 970 4 970 4 970 4 970 4 970 4 970 4 970 4 970 4 970 5 990 5 990 5 990 5 990 5 990 5 990 5 990 5 990 5 990 5 990 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070} 93 94finish_test 95