xref: /sqlite-3.40.0/test/window2.test (revision f9eae18b) 
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
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20set testprefix window2
21
22do_execsql_test 1.0 {
23  DROP TABLE IF EXISTS t1;
24  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
25  INSERT INTO t1 VALUES(1, 'odd',  'one',   1);
26  INSERT INTO t1 VALUES(2, 'even', 'two',   2);
27  INSERT INTO t1 VALUES(3, 'odd',  'three', 3);
28  INSERT INTO t1 VALUES(4, 'even', 'four',  4);
29  INSERT INTO t1 VALUES(5, 'odd',  'five',  5);
30  INSERT INTO t1 VALUES(6, 'even', 'six',   6);
31} {}
32
33do_execsql_test 1.1 {
34  SELECT c, sum(d) OVER (PARTITION BY b ORDER BY c) FROM t1;
35} {four 4   six 10   two 12   five 5   one 6   three 9}
36
37do_execsql_test 1.2 {
38  SELECT sum(d) OVER () FROM t1;
39} {21   21   21   21   21   21}
40
41do_execsql_test 1.3 {
42  SELECT sum(d) OVER (PARTITION BY b) FROM t1;
43} {12   12   12   9   9   9}
44
45finish_test
46#==========================================================================
47
48do_execsql_test 2.1 {
49  SELECT a, sum(d) OVER (
50    PARTITION BY b ORDER BY d
51    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
52  ) FROM t1
53} {2 12   4 10   6 6   1 9   3 8   5 5}
54
55do_execsql_test 2.2 {
56  SELECT a, sum(d) OVER (
57    ORDER BY b
58    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
59  ) FROM t1
60} {2 21   4 21   6 21   1 9   3 9   5 9}
61
62do_execsql_test 2.3 {
63  SELECT a, sum(d) OVER (
64    ORDER BY d
65    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
66  ) FROM t1
67} {1 21   2 21   3 21   4 21   5 21   6 21}
68
69finish_test
70