3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix windowfault
18 ifcapable !windowfunc {
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);
29 faultsim_save_and_close
31 do_faultsim_test 1 -start 1 -faults oom-t* -prep {
32 faultsim_restore_and_reopen
35 SELECT row_number() OVER win,
37 dense_rank() OVER win,
39 first_value(d) OVER win,
40 last_value(d) OVER win,
41 nth_value(d,2) OVER win,
47 WINDOW win AS (ORDER BY a)
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}}
53 do_faultsim_test 1.1 -faults oom-t* -prep {
54 faultsim_restore_and_reopen
57 SELECT row_number() OVER win,
61 WINDOW win AS (PARTITION BY c<7 ORDER BY a)
64 faultsim_test_result {0 {1 1 1 2 2 2 1 1 1}}
67 do_faultsim_test 1.2 -faults oom-t* -prep {
68 faultsim_restore_and_reopen
72 OVER ( RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
76 faultsim_test_result {0 {1 2 3}}
79 do_faultsim_test 2 -start 1 -faults oom-* -prep {
80 faultsim_restore_and_reopen
83 SELECT round(percent_rank() OVER win, 2),
84 round(cume_dist() OVER win, 2)
86 WINDOW win AS (ORDER BY a)
89 faultsim_test_result {0 {0.0 0.33 0.5 0.67 1.0 1.0}}
92 do_faultsim_test 3 -faults oom-* -prep {
93 faultsim_restore_and_reopen
96 SELECT min(d) OVER win, max(d) OVER win
98 WINDOW win AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
101 faultsim_test_result {0 {4 12 8 12 12 12}}
104 do_faultsim_test 4 -faults oom-* -prep {
105 faultsim_restore_and_reopen
109 SELECT min(d) OVER w, max(d) OVER w
111 WINDOW w AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
115 faultsim_test_result {0 {4 12 8 12 12 12}}
118 do_faultsim_test 5 -start 1 -faults oom-* -prep {
119 faultsim_restore_and_reopen
122 SELECT last_value(a) OVER win1,
123 last_value(a) OVER win2
125 WINDOW win1 AS (ORDER BY a ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),
129 faultsim_test_result {0 {5 1 9 5 9 9}}
132 do_faultsim_test 6 -faults oom-* -prep {
133 faultsim_restore_and_reopen
136 SELECT percent_rank() OVER (), cume_dist() OVER () FROM t1
139 faultsim_test_result {0 {0.0 1.0 0.0 1.0 0.0 1.0}}
142 do_faultsim_test 7 -faults oom-* -prep {
143 faultsim_restore_and_reopen
146 SELECT percent_rank() OVER (), cume_dist() OVER () FROM t1
149 faultsim_test_result {0 {0.0 1.0 0.0 1.0 0.0 1.0}}
152 do_faultsim_test 8 -faults oom-t* -prep {
153 faultsim_restore_and_reopen
156 SELECT a, sum(b) OVER win1 FROM t1
157 WINDOW win1 AS (PARTITION BY a ),
158 win2 AS (PARTITION BY b )
162 faultsim_test_result {0 {1 2 5 6 9 10}}
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.
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);
177 VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<1900
179 INSERT INTO t(y) SELECT $big FROM s;
183 testvfs tvfs -default 1
184 tvfs script vfs_callback
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"
198 set FAULTSIM(tmpread) [list \
199 -injectstart tmpread_injectstart \
200 -injectstop tmpread_injectstop \
201 -injecterrlist {{1 {disk I/O error}}} \
203 proc tmpread_injectstart {iFail} {
204 set ::tmp_read_fail $iFail
206 proc tmpread_injectstop {} {
207 set ret [expr $::tmp_read_fail<=0]
208 unset -nocomplain ::tmp_read_fail
212 set L [db eval {SELECT 0.0 FROM t}]
213 do_faultsim_test 9 -end 25 -faults tmpread -body {
215 SELECT sum(y) OVER win FROM t
217 ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 1800 FOLLOWING
221 faultsim_test_result [list 0 $::L]
228 do_execsql_test 10.0 {
229 CREATE TABLE t1(a, b, c, d);
230 CREATE TABLE t2(a, b, c, d);
233 do_faultsim_test 10 -faults oom* -prep {
236 SELECT row_number() OVER win
240 SELECT percent_rank() OVER win2 FROM t2
241 WINDOW win2 AS (ORDER BY a)
246 faultsim_test_result {0 {}}
249 #-------------------------------------------------------------------------
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);
257 do_faultsim_test 11.1 -faults oom* -prep {
260 SELECT * FROM t0 WHERE
261 (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0);
264 faultsim_test_result {0 {}}
267 do_faultsim_test 11.2 -faults oom* -prep {
270 VALUES(false),(current_date collate binary)
272 values(count() not like group_concat(cast(cast(0e00 as text) as integer) <= NULL || 0.4e-0 || 0x8 & true ) over () collate rtrim);
275 faultsim_test_result {0 {}}
278 #-------------------------------------------------------------------------
280 do_execsql_test 12.0 {
281 CREATE TABLE t1(a, b, c);
283 do_faultsim_test 12 -faults oom* -prep {
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
289 SELECT * FROM v WHERE a=2
292 faultsim_test_result {0 {}}
295 #-------------------------------------------------------------------------
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');
308 set queryres [list {*}{
314 55555f666666gggggggggg7777777
315 666666gggggggggg7777777
317 do_execsql_test 13.1 {
318 SELECT group_concat(a, b) OVER (
319 ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
323 do_faultsim_test 13 -faults oom* -prep {
326 SELECT group_concat(a, b) OVER (
327 ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
331 faultsim_test_result [list 0 $::queryres]