xref: /sqlite-3.40.0/test/window5.test (revision f87e10c7) 
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. Specifically,
12# it tests the sqlite3_create_window_function() API.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17set testprefix window1
18
19proc m_step {ctx val} {
20  lappend ctx $val
21  return $ctx
22}
23proc m_value {ctx} {
24  set lSort [lsort $ctx]
25
26  set nVal [llength $lSort]
27  set n [expr $nVal/2]
28
29  if {($nVal % 2)==0 && $nVal>0} {
30    set a [lindex $lSort $n]
31    set b [lindex $lSort $n-1]
32    if {($a+$b) % 2} {
33      set ret [expr ($a+$b)/2.0]
34    } else {
35      set ret [expr ($a+$b)/2]
36    }
37  } else {
38    set ret [lindex $lSort $n]
39  }
40  return $ret
41}
42proc m_inverse {ctx val} {
43  set ctx [lrange $ctx 1 end]
44  return $ctx
45}
46proc w_value {ctx} {
47  lsort $ctx
48}
49
50sqlite3_create_window_function db median m_step m_value m_value m_inverse
51sqlite3_create_window_function db win m_step w_value w_value m_inverse
52
53do_test 0.0 {
54  test_create_window_function_misuse db
55} {}
56
57do_execsql_test 1.0 {
58  CREATE TABLE t1(a, b);
59  INSERT INTO t1 VALUES(4, 'a');
60  INSERT INTO t1 VALUES(6, 'b');
61  INSERT INTO t1 VALUES(1, 'c');
62  INSERT INTO t1 VALUES(5, 'd');
63  INSERT INTO t1 VALUES(2, 'e');
64  INSERT INTO t1 VALUES(3, 'f');
65}
66
67do_execsql_test 1.1 {
68  SELECT win(a) OVER (ORDER BY b), median(a) OVER (ORDER BY b) FROM t1;
69} {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}
70
71finish_test
72
73