xref: /sqlite-3.40.0/test/windowfault.test (revision e89feee5) 
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
18ifcapable !windowfunc {
19  finish_test
20  return
21}
22
23do_execsql_test 1.0 {
24  CREATE TABLE t1(a, b, c, d);
25  INSERT INTO t1 VALUES(1, 2, 3, 4);
26  INSERT INTO t1 VALUES(5, 6, 7, 8);
27  INSERT INTO t1 VALUES(9, 10, 11, 12);
28}
29faultsim_save_and_close
30
31do_faultsim_test 1 -start 1 -faults oom-* -prep {
32  faultsim_restore_and_reopen
33} -body {
34  execsql {
35    SELECT row_number() OVER win,
36           rank() OVER win,
37           dense_rank() OVER win,
38           ntile(2) OVER win,
39           first_value(d) OVER win,
40           last_value(d) OVER win,
41           nth_value(d,2) OVER win,
42           lead(d) OVER win,
43           lag(d) OVER win,
44           max(d) OVER win,
45           min(d) OVER win
46    FROM t1
47    WINDOW win AS (ORDER BY a)
48  }
49} -test {
50  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}}
51}
52
53do_faultsim_test 1.1 -faults oom-t* -prep {
54  faultsim_restore_and_reopen
55} -body {
56  execsql {
57    SELECT row_number() OVER win,
58           rank() OVER win,
59           dense_rank() OVER win
60    FROM t1
61    WINDOW win AS (PARTITION BY c<7 ORDER BY a)
62  }
63} -test {
64  faultsim_test_result {0 {1 1 1 2 2 2 1 1 1}}
65}
66
67do_faultsim_test 1.2 -faults oom-t* -prep {
68  faultsim_restore_and_reopen
69} -body {
70  execsql {
71    SELECT ntile(105)
72    OVER ( RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
73    FROM t1
74  }
75} -test {
76  faultsim_test_result {0 {1 2 3}}
77}
78
79do_faultsim_test 2 -start 1 -faults oom-* -prep {
80  faultsim_restore_and_reopen
81} -body {
82  execsql {
83    SELECT round(percent_rank() OVER win, 2),
84           round(cume_dist() OVER win, 2)
85    FROM t1
86    WINDOW win AS (ORDER BY a)
87  }
88} -test {
89  faultsim_test_result {0 {0.0 0.33 0.5 0.67 1.0 1.0}}
90}
91
92do_faultsim_test 3 -faults oom-* -prep {
93  faultsim_restore_and_reopen
94} -body {
95  execsql {
96    SELECT min(d) OVER win, max(d) OVER win
97    FROM t1
98    WINDOW win AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
99  }
100} -test {
101  faultsim_test_result {0 {4 12 8 12 12 12}}
102}
103
104do_faultsim_test 4 -faults oom-* -prep {
105  faultsim_restore_and_reopen
106} -body {
107  execsql {
108    CREATE VIEW aaa AS
109    SELECT min(d) OVER w, max(d) OVER w
110    FROM t1
111    WINDOW w AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
112    SELECT * FROM aaa;
113  }
114} -test {
115  faultsim_test_result {0 {4 12 8 12 12 12}}
116}
117
118do_faultsim_test 5 -start 1 -faults oom-* -prep {
119  faultsim_restore_and_reopen
120} -body {
121  execsql {
122    SELECT last_value(a) OVER win1,
123           last_value(a) OVER win2
124    FROM t1
125    WINDOW win1 AS (ORDER BY a ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),
126           win2 AS (ORDER BY a)
127  }
128} -test {
129  faultsim_test_result {0 {5 1 9 5 9 9}}
130}
131
132do_faultsim_test 6 -faults oom-* -prep {
133  faultsim_restore_and_reopen
134} -body {
135  execsql {
136    SELECT percent_rank() OVER (), cume_dist() OVER () FROM t1
137  }
138} -test {
139  faultsim_test_result {0 {0.0 1.0 0.0 1.0 0.0 1.0}}
140}
141
142do_faultsim_test 7 -faults oom-* -prep {
143  faultsim_restore_and_reopen
144} -body {
145  execsql {
146    SELECT percent_rank() OVER (), cume_dist() OVER () FROM t1
147  }
148} -test {
149  faultsim_test_result {0 {0.0 1.0 0.0 1.0 0.0 1.0}}
150}
151
152do_faultsim_test 8 -faults oom-t* -prep {
153  faultsim_restore_and_reopen
154} -body {
155  execsql {
156    SELECT a, sum(b) OVER win1 FROM t1
157      WINDOW win1 AS (PARTITION BY a ),
158             win2 AS (PARTITION BY b )
159    ORDER BY a;
160  }
161} -test {
162  faultsim_test_result {0 {1 2 5 6 9 10}}
163}
164
165finish_test
166