Debian Codegraph @

test/windowfault.test

raw
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
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix windowfault
17
18 ifcapable !windowfunc {
19 finish_test
20 return
21 }
22
23 do_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 }
29 faultsim_save_and_close
30
31 do_faultsim_test 1 -start 1 -faults oom-t* -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
53 do_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
67 do_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
79 do_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
92 do_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
104 do_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
118 do_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
132 do_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
142 do_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
152 do_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
165 #-------------------------------------------------------------------------
166 # The following test causes a cursor in REQURESEEK state to be passed
167 # to sqlite3BtreeDelete(). An error is simulated within the seek operation
168 # to restore the cursors position.
169 #
170 reset_db
171 set big [string repeat x 900]
172 do_execsql_test 9.0 {
173 PRAGMA page_size = 512;
174 PRAGMA cache_size = 2;
175 CREATE TABLE t(x INTEGER PRIMARY KEY, y TEXT);
176 WITH s(i) AS (
177 VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<1900
178 )
179 INSERT INTO t(y) SELECT $big FROM s;
180 }
181 db close
182
183 testvfs tvfs -default 1
184 tvfs script vfs_callback
185 tvfs filter xRead
186
187 sqlite3 db test.db
188 proc vfs_callback {method file args} {
189 if {$file=="" && [info exists ::tmp_read_fail]} {
190 incr ::tmp_read_fail -1
191 if {$::tmp_read_fail<=0} {
192 return "SQLITE_IOERR"
193 }
194 }
195 return "SQLITE_OK"
196 }
197
198 set FAULTSIM(tmpread) [list \
199 -injectstart tmpread_injectstart \
200 -injectstop tmpread_injectstop \
201 -injecterrlist {{1 {disk I/O error}}} \
202 ]
203 proc tmpread_injectstart {iFail} {
204 set ::tmp_read_fail $iFail
205 }
206 proc tmpread_injectstop {} {
207 set ret [expr $::tmp_read_fail<=0]
208 unset -nocomplain ::tmp_read_fail
209 return $ret
210 }
211
212 set L [db eval {SELECT 0.0 FROM t}]
213 do_faultsim_test 9 -end 25 -faults tmpread -body {
214 execsql {
215 SELECT sum(y) OVER win FROM t
216 WINDOW win AS (
217 ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 1800 FOLLOWING
218 )
219 }
220 } -test {
221 faultsim_test_result [list 0 $::L]
222 }
223
224 catch {db close}
225 tvfs delete
226
227 reset_db
228 do_execsql_test 10.0 {
229 CREATE TABLE t1(a, b, c, d);
230 CREATE TABLE t2(a, b, c, d);
231 }
232
233 do_faultsim_test 10 -faults oom* -prep {
234 } -body {
235 execsql {
236 SELECT row_number() OVER win
237 FROM t1
238 WINDOW win AS (
239 ORDER BY (
240 SELECT percent_rank() OVER win2 FROM t2
241 WINDOW win2 AS (ORDER BY a)
242 )
243 )
244 }
245 } -test {
246 faultsim_test_result {0 {}}
247 }
248
249 #-------------------------------------------------------------------------
250 reset_db
251 do_execsql_test 11.0 {
252 DROP TABLE IF EXISTS t0;
253 CREATE TABLE t0(c0 INTEGER UNIQUE);
254 INSERT INTO t0 VALUES(0);
255 } {}
256
257 do_faultsim_test 11.1 -faults oom* -prep {
258 } -body {
259 execsql {
260 SELECT * FROM t0 WHERE
261 (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0);
262 }
263 } -test {
264 faultsim_test_result {0 {}}
265 }
266
267 do_faultsim_test 11.2 -faults oom* -prep {
268 } -body {
269 execsql {
270 VALUES(false),(current_date collate binary)
271 intersect
272 values(count() not like group_concat(cast(cast(0e00 as text) as integer) <= NULL || 0.4e-0 || 0x8 & true ) over () collate rtrim);
273 }
274 } -test {
275 faultsim_test_result {0 {}}
276 }
277
278 #-------------------------------------------------------------------------
279 reset_db
280 do_execsql_test 12.0 {
281 CREATE TABLE t1(a, b, c);
282 } {}
283 do_faultsim_test 12 -faults oom* -prep {
284 } -body {
285 execsql {
286 WITH v(a, b, row_number) AS (
287 SELECT a, b, row_number() OVER (PARTITION BY a COLLATE nocase ORDER BY b) FROM t1
288 )
289 SELECT * FROM v WHERE a=2
290 }
291 } -test {
292 faultsim_test_result {0 {}}
293 }
294
295 #-------------------------------------------------------------------------
296 reset_db
297 do_execsql_test 13.0 {
298 CREATE TABLE t1(id INTEGER PRIMARY KEY, a, b);
299 INSERT INTO t1 VALUES(1, '1', 'a');
300 INSERT INTO t1 VALUES(2, '22', 'b');
301 INSERT INTO t1 VALUES(3, '333', 'c');
302 INSERT INTO t1 VALUES(4, '4444', 'dddd');
303 INSERT INTO t1 VALUES(5, '55555', 'e');
304 INSERT INTO t1 VALUES(6, '666666', 'f');
305 INSERT INTO t1 VALUES(7, '7777777', 'gggggggggg');
306 } {}
307
308 set queryres [list {*}{
309 1b22
310 1b22c333
311 22c333dddd4444
312 333dddd4444e55555
313 4444e55555f666666
314 55555f666666gggggggggg7777777
315 666666gggggggggg7777777
316 }]
317 do_execsql_test 13.1 {
318 SELECT group_concat(a, b) OVER (
319 ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
320 ) FROM t1
321 } $queryres
322
323 do_faultsim_test 13 -faults oom* -prep {
324 } -body {
325 execsql {
326 SELECT group_concat(a, b) OVER (
327 ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
328 ) FROM t1
329 }
330 } -test {
331 faultsim_test_result [list 0 $::queryres]
332 }
333
334 finish_test