xref: /sqlite-3.40.0/test/windowfault.test (revision 13b08bb6) 
1# 2018 May 8
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
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16set testprefix windowfault
17
18
19do_execsql_test 1.0 {
20  CREATE TABLE t1(a, b, c, d);
21  INSERT INTO t1 VALUES(1, 2, 3, 4);
22  INSERT INTO t1 VALUES(5, 6, 7, 8);
23  INSERT INTO t1 VALUES(9, 10, 11, 12);
24}
25faultsim_save_and_close
26
27do_faultsim_test 1 -start 1 -faults oom-* -prep {
28  faultsim_restore_and_reopen
29} -body {
30  execsql {
31    SELECT row_number() OVER win,
32           rank() OVER win,
33           dense_rank() OVER win,
34           ntile(2) OVER win,
35           first_value(d) OVER win,
36           last_value(d) OVER win,
37           nth_value(d,2) OVER win,
38           lead(d) OVER win,
39           lag(d) OVER win,
40           max(d) OVER win,
41           min(d) OVER win
42    FROM t1
43    WINDOW win AS (ORDER BY a)
44  }
45} -test {
46  faultsim_test_result {0 {1 1 1 1 4 4 {} 8 {} 4 4 2 2 2 1 4 8 8 12 4 8 4 3 3 3 2 4 12 8 {} 8 12 4}}
47}
48
49do_faultsim_test 2 -faults oom-* -prep {
50  faultsim_restore_and_reopen
51} -body {
52  execsql {
53    SELECT min(d) OVER win, max(d) OVER win
54    FROM t1
55    WINDOW win AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
56  }
57} -test {
58  faultsim_test_result {0 {4 12 8 12 12 12}}
59}
60
61
62finish_test
63
64