From 63847496f14c813a5d80efd5b7de0f1294ffe1e3 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 16:07:11 +0200 Subject: Adding upstream version 3.45.1. Signed-off-by: Daniel Baumann --- test/window5.test | 96 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 96 insertions(+) create mode 100644 test/window5.test (limited to 'test/window5.test') diff --git a/test/window5.test b/test/window5.test new file mode 100644 index 0000000..890d670 --- /dev/null +++ b/test/window5.test @@ -0,0 +1,96 @@ +# 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. Specifically, +# it tests the sqlite3_create_window_function() API. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix window5 + +ifcapable !windowfunc { + finish_test + return +} + +proc m_step {ctx val} { + lappend ctx $val + return $ctx +} +proc m_value {ctx} { + set lSort [lsort $ctx] + + set nVal [llength $lSort] + set n [expr $nVal/2] + + if {($nVal % 2)==0 && $nVal>0} { + set a [lindex $lSort $n] + set b [lindex $lSort $n-1] + if {($a+$b) % 2} { + set ret [expr ($a+$b)/2.0] + } else { + set ret [expr ($a+$b)/2] + } + } else { + set ret [lindex $lSort $n] + } + return $ret +} +proc m_inverse {ctx val} { + set ctx [lrange $ctx 1 end] + return $ctx +} +proc w_value {ctx} { + lsort $ctx +} + +sqlite3_create_window_function db median m_step m_value m_value m_inverse +sqlite3_create_window_function db win m_step w_value w_value m_inverse + +do_test 0.0 { + test_create_window_function_misuse db +} {} + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(4, 'a'); + INSERT INTO t1 VALUES(6, 'b'); + INSERT INTO t1 VALUES(1, 'c'); + INSERT INTO t1 VALUES(5, 'd'); + INSERT INTO t1 VALUES(2, 'e'); + INSERT INTO t1 VALUES(3, 'f'); +} + +do_execsql_test 1.1 { + SELECT win(a) OVER (ORDER BY b), median(a) OVER (ORDER BY b) FROM t1; +} {4 4 {4 6} 5 {1 4 6} 4 {1 4 5 6} 4.5 {1 2 4 5 6} 4 {1 2 3 4 5 6} 3.5} + +test_create_sumint db +do_execsql_test 2.0 { + SELECT sumint(a) OVER (ORDER BY rowid) FROM t1 ORDER BY rowid; +} {4 10 11 16 18 21} + +do_execsql_test 2.1 { + SELECT sumint(a) OVER (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t1 ORDER BY rowid; +} {10 11 12 8 10 5} + +test_override_sum db +do_catchsql_test 3.0 { + SELECT sum(a) OVER + (ORDER BY b ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) + FROM t1; +} {1 {sum() may not be used as a window function}} +do_execsql_test 3.1 { + SELECT sum(a) FROM t1; +} {21} + + +finish_test -- cgit v1.2.3