diff options
Diffstat (limited to 'test/windowfault.test')
| -rw-r--r-- | test/windowfault.test | 334 |
1 files changed, 334 insertions, 0 deletions
diff --git a/test/windowfault.test b/test/windowfault.test new file mode 100644 index 0000000..5340ce0 --- /dev/null +++ b/test/windowfault.test @@ -0,0 +1,334 @@ +# 2018 May 8 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix windowfault + +ifcapable !windowfunc { + finish_test + return +} + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b, c, d); + INSERT INTO t1 VALUES(1, 2, 3, 4); + INSERT INTO t1 VALUES(5, 6, 7, 8); + INSERT INTO t1 VALUES(9, 10, 11, 12); +} +faultsim_save_and_close + +do_faultsim_test 1 -start 1 -faults oom-t* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + SELECT row_number() OVER win, + rank() OVER win, + dense_rank() OVER win, + ntile(2) OVER win, + first_value(d) OVER win, + last_value(d) OVER win, + nth_value(d,2) OVER win, + lead(d) OVER win, + lag(d) OVER win, + max(d) OVER win, + min(d) OVER win + FROM t1 + WINDOW win AS (ORDER BY a) + } +} -test { + 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}} +} + +do_faultsim_test 1.1 -faults oom-t* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + SELECT row_number() OVER win, + rank() OVER win, + dense_rank() OVER win + FROM t1 + WINDOW win AS (PARTITION BY c<7 ORDER BY a) + } +} -test { + faultsim_test_result {0 {1 1 1 2 2 2 1 1 1}} +} + +do_faultsim_test 1.2 -faults oom-t* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + SELECT ntile(105) + OVER ( RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) + FROM t1 + } +} -test { + faultsim_test_result {0 {1 2 3}} +} + +do_faultsim_test 2 -start 1 -faults oom-* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + SELECT round(percent_rank() OVER win, 2), + round(cume_dist() OVER win, 2) + FROM t1 + WINDOW win AS (ORDER BY a) + } +} -test { + faultsim_test_result {0 {0.0 0.33 0.5 0.67 1.0 1.0}} +} + +do_faultsim_test 3 -faults oom-* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + SELECT min(d) OVER win, max(d) OVER win + FROM t1 + WINDOW win AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + } +} -test { + faultsim_test_result {0 {4 12 8 12 12 12}} +} + +do_faultsim_test 4 -faults oom-* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + CREATE VIEW aaa AS + SELECT min(d) OVER w, max(d) OVER w + FROM t1 + WINDOW w AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); + SELECT * FROM aaa; + } +} -test { + faultsim_test_result {0 {4 12 8 12 12 12}} +} + +do_faultsim_test 5 -start 1 -faults oom-* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + SELECT last_value(a) OVER win1, + last_value(a) OVER win2 + FROM t1 + WINDOW win1 AS (ORDER BY a ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), + win2 AS (ORDER BY a) + } +} -test { + faultsim_test_result {0 {5 1 9 5 9 9}} +} + +do_faultsim_test 6 -faults oom-* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + SELECT percent_rank() OVER (), cume_dist() OVER () FROM t1 + } +} -test { + faultsim_test_result {0 {0.0 1.0 0.0 1.0 0.0 1.0}} +} + +do_faultsim_test 7 -faults oom-* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + SELECT percent_rank() OVER (), cume_dist() OVER () FROM t1 + } +} -test { + faultsim_test_result {0 {0.0 1.0 0.0 1.0 0.0 1.0}} +} + +do_faultsim_test 8 -faults oom-t* -prep { + faultsim_restore_and_reopen +} -body { + execsql { + SELECT a, sum(b) OVER win1 FROM t1 + WINDOW win1 AS (PARTITION BY a ), + win2 AS (PARTITION BY b ) + ORDER BY a; + } +} -test { + faultsim_test_result {0 {1 2 5 6 9 10}} +} + +#------------------------------------------------------------------------- +# The following test causes a cursor in REQURESEEK state to be passed +# to sqlite3BtreeDelete(). An error is simulated within the seek operation +# to restore the cursors position. +# +reset_db +set big [string repeat x 900] +do_execsql_test 9.0 { + PRAGMA page_size = 512; + PRAGMA cache_size = 2; + CREATE TABLE t(x INTEGER PRIMARY KEY, y TEXT); + WITH s(i) AS ( + VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<1900 + ) + INSERT INTO t(y) SELECT $big FROM s; +} +db close + +testvfs tvfs -default 1 +tvfs script vfs_callback +tvfs filter xRead + +sqlite3 db test.db +proc vfs_callback {method file args} { + if {$file=="" && [info exists ::tmp_read_fail]} { + incr ::tmp_read_fail -1 + if {$::tmp_read_fail<=0} { + return "SQLITE_IOERR" + } + } + return "SQLITE_OK" +} + +set FAULTSIM(tmpread) [list \ + -injectstart tmpread_injectstart \ + -injectstop tmpread_injectstop \ + -injecterrlist {{1 {disk I/O error}}} \ +] +proc tmpread_injectstart {iFail} { + set ::tmp_read_fail $iFail +} +proc tmpread_injectstop {} { + set ret [expr $::tmp_read_fail<=0] + unset -nocomplain ::tmp_read_fail + return $ret +} + +set L [db eval {SELECT 0.0 FROM t}] +do_faultsim_test 9 -end 25 -faults tmpread -body { + execsql { + SELECT sum(y) OVER win FROM t + WINDOW win AS ( + ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 1800 FOLLOWING + ) + } +} -test { + faultsim_test_result [list 0 $::L] +} + +catch {db close} +tvfs delete + +reset_db +do_execsql_test 10.0 { + CREATE TABLE t1(a, b, c, d); + CREATE TABLE t2(a, b, c, d); +} + +do_faultsim_test 10 -faults oom* -prep { +} -body { + execsql { + SELECT row_number() OVER win + FROM t1 + WINDOW win AS ( + ORDER BY ( + SELECT percent_rank() OVER win2 FROM t2 + WINDOW win2 AS (ORDER BY a) + ) + ) + } +} -test { + faultsim_test_result {0 {}} +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 11.0 { + DROP TABLE IF EXISTS t0; + CREATE TABLE t0(c0 INTEGER UNIQUE); + INSERT INTO t0 VALUES(0); +} {} + +do_faultsim_test 11.1 -faults oom* -prep { +} -body { + execsql { + SELECT * FROM t0 WHERE + (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0); + } +} -test { + faultsim_test_result {0 {}} +} + +do_faultsim_test 11.2 -faults oom* -prep { +} -body { + execsql { + VALUES(false),(current_date collate binary) + intersect + values(count() not like group_concat(cast(cast(0e00 as text) as integer) <= NULL || 0.4e-0 || 0x8 & true ) over () collate rtrim); + } +} -test { + faultsim_test_result {0 {}} +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 12.0 { + CREATE TABLE t1(a, b, c); +} {} +do_faultsim_test 12 -faults oom* -prep { +} -body { + execsql { + WITH v(a, b, row_number) AS ( + SELECT a, b, row_number() OVER (PARTITION BY a COLLATE nocase ORDER BY b) FROM t1 + ) + SELECT * FROM v WHERE a=2 + } +} -test { + faultsim_test_result {0 {}} +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 13.0 { + CREATE TABLE t1(id INTEGER PRIMARY KEY, a, b); + INSERT INTO t1 VALUES(1, '1', 'a'); + INSERT INTO t1 VALUES(2, '22', 'b'); + INSERT INTO t1 VALUES(3, '333', 'c'); + INSERT INTO t1 VALUES(4, '4444', 'dddd'); + INSERT INTO t1 VALUES(5, '55555', 'e'); + INSERT INTO t1 VALUES(6, '666666', 'f'); + INSERT INTO t1 VALUES(7, '7777777', 'gggggggggg'); +} {} + +set queryres [list {*}{ + 1b22 + 1b22c333 + 22c333dddd4444 + 333dddd4444e55555 + 4444e55555f666666 + 55555f666666gggggggggg7777777 + 666666gggggggggg7777777 +}] +do_execsql_test 13.1 { + SELECT group_concat(a, b) OVER ( + ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING + ) FROM t1 +} $queryres + +do_faultsim_test 13 -faults oom* -prep { +} -body { + execsql { + SELECT group_concat(a, b) OVER ( + ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING + ) FROM t1 + } +} -test { + faultsim_test_result [list 0 $::queryres] +} + +finish_test |