| # 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 |