xref: /sqlite-3.40.0/test/windowfault.test (revision 17074e3a) 
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 1.1 -faults oom-t* -prep {
50  faultsim_restore_and_reopen
51} -body {
52  execsql {
53    SELECT row_number() OVER win,
54           rank() OVER win,
55           dense_rank() OVER win
56    FROM t1
57    WINDOW win AS (PARTITION BY c<7 ORDER BY a)
58  }
59} -test {
60  faultsim_test_result {0 {1 1 1 2 2 2 1 1 1}}
61}
62
63do_faultsim_test 1.2 -faults oom-t* -prep {
64  faultsim_restore_and_reopen
65} -body {
66  execsql {
67    SELECT ntile(105)
68    OVER ( RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
69    FROM t1
70  }
71} -test {
72  faultsim_test_result {0 {1 2 3}}
73}
74
75do_faultsim_test 2 -start 1 -faults oom-* -prep {
76  faultsim_restore_and_reopen
77} -body {
78  execsql {
79    SELECT round(percent_rank() OVER win, 2),
80           round(cume_dist() OVER win, 2)
81    FROM t1
82    WINDOW win AS (ORDER BY a)
83  }
84} -test {
85  faultsim_test_result {0 {0.0 0.33 0.5 0.67 1.0 1.0}}
86}
87
88do_faultsim_test 3 -faults oom-* -prep {
89  faultsim_restore_and_reopen
90} -body {
91  execsql {
92    SELECT min(d) OVER win, max(d) OVER win
93    FROM t1
94    WINDOW win AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
95  }
96} -test {
97  faultsim_test_result {0 {4 12 8 12 12 12}}
98}
99
100do_faultsim_test 4 -faults oom-* -prep {
101  faultsim_restore_and_reopen
102} -body {
103  execsql {
104    CREATE VIEW aaa AS
105    SELECT min(d) OVER w, max(d) OVER w
106    FROM t1
107    WINDOW w AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
108    SELECT * FROM aaa;
109  }
110} -test {
111  faultsim_test_result {0 {4 12 8 12 12 12}}
112}
113
114do_faultsim_test 5 -start 1 -faults oom-* -prep {
115  faultsim_restore_and_reopen
116} -body {
117  execsql {
118    SELECT last_value(a) OVER win1,
119           last_value(a) OVER win2
120    FROM t1
121    WINDOW win1 AS (ORDER BY a ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),
122           win2 AS (ORDER BY a)
123  }
124} -test {
125  faultsim_test_result {0 {5 1 9 5 9 9}}
126}
127
128finish_test
129
130