diff options
Diffstat (limited to 'test/window7.test')
| -rw-r--r-- | test/window7.test | 186 |
1 files changed, 186 insertions, 0 deletions
diff --git a/test/window7.test b/test/window7.test new file mode 100644 index 0000000..441e2b1 --- /dev/null +++ b/test/window7.test @@ -0,0 +1,186 @@ +# 2019 March 01 +# +# 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 window7 + +ifcapable !windowfunc { finish_test ; return } +do_execsql_test 1.0 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER, b INTEGER); + INSERT INTO t3 VALUES + (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), + (9, 9), (0, 10), (1, 11), (2, 12), (3, 13), (4, 14), (5, 15), (6, 16), + (7, 17), (8, 18), (9, 19), (0, 20), (1, 21), (2, 22), (3, 23), (4, 24), + (5, 25), (6, 26), (7, 27), (8, 28), (9, 29), (0, 30), (1, 31), (2, 32), + (3, 33), (4, 34), (5, 35), (6, 36), (7, 37), (8, 38), (9, 39), (0, 40), + (1, 41), (2, 42), (3, 43), (4, 44), (5, 45), (6, 46), (7, 47), (8, 48), + (9, 49), (0, 50), (1, 51), (2, 52), (3, 53), (4, 54), (5, 55), (6, 56), + (7, 57), (8, 58), (9, 59), (0, 60), (1, 61), (2, 62), (3, 63), (4, 64), + (5, 65), (6, 66), (7, 67), (8, 68), (9, 69), (0, 70), (1, 71), (2, 72), + (3, 73), (4, 74), (5, 75), (6, 76), (7, 77), (8, 78), (9, 79), (0, 80), + (1, 81), (2, 82), (3, 83), (4, 84), (5, 85), (6, 86), (7, 87), (8, 88), + (9, 89), (0, 90), (1, 91), (2, 92), (3, 93), (4, 94), (5, 95), (6, 96), + (7, 97), (8, 98), (9, 99), (0, 100); +} {} + +do_execsql_test 1.1 { + SELECT a, sum(b) FROM t3 GROUP BY a ORDER BY 1; +} {0 550 1 460 2 470 3 480 4 490 5 500 6 510 7 520 8 530 + 9 540} + +do_execsql_test 1.2 { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN CURRENT ROW AND CURRENT ROW + ) FROM t3 ORDER BY 1; +} {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} + +do_execsql_test 1.3 { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING + ) FROM t3 ORDER BY 1; +} {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} + +do_execsql_test 1.4 { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING + ) FROM t3 ORDER BY 1; +} {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} + +do_execsql_test 1.5 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING + ) FROM t3 ORDER BY 1; +} {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} + +do_execsql_test 1.6 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING + ) FROM t3 ORDER BY 1; +} {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} + +do_execsql_test 1.7 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1; +} {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} + +do_execsql_test 1.8.1 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1; +} {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} + +do_execsql_test 1.8.2 { + SELECT a, sum(b) OVER ( + ORDER BY a DESC RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1; +} {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} + +finish_test |