xref: /sqlite-3.40.0/test/window6.test (revision 59ff4251) 
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 window6
18
19ifcapable !windowfunc {
20  finish_test
21  return
22}
23
24set setup {
25  CREATE TABLE %t1(%a, %b %typename);
26  INSERT INTO %t1 VALUES(1, 'a');
27  INSERT INTO %t1 VALUES(2, 'b');
28  INSERT INTO %t1 VALUES(3, 'c');
29  INSERT INTO %t1 VALUES(4, 'd');
30  INSERT INTO %t1 VALUES(5, 'e');
31}
32
33foreach {tn vars} {
34  1 {}
35  2 { set A(%t1) over }
36  3 { set A(%a)  over }
37  4 {
38    set A(%alias)   over
39    set A(%a)       following
40    set A(%b)       over
41  }
42  5 {
43    set A(%t1)      over
44    set A(%a)       following
45    set A(%b)       preceding
46    set A(%w)       current
47    set A(%alias)   filter
48    set A(%typename)  window
49  }
50
51  6 {
52    set A(%a)       window
53  }
54} {
55  set A(%t1)    t1
56  set A(%a)     a
57  set A(%b)     b
58  set A(%w)     w
59  set A(%alias) alias
60  set A(%typename) integer
61  eval $vars
62
63  set MAP [array get A]
64  set setup_sql [string map $MAP $setup]
65  reset_db
66  execsql $setup_sql
67
68  do_execsql_test 1.$tn.1 [string map $MAP {
69    SELECT group_concat(%a, '.') OVER (ORDER BY %b) FROM %t1
70  }] {1 1.2 1.2.3 1.2.3.4 1.2.3.4.5}
71
72  do_execsql_test 1.$tn.2 [string map $MAP {
73    SELECT sum(%a) OVER %w FROM %t1 WINDOW %w AS (ORDER BY %b)
74  }] {1 3 6 10 15}
75
76  do_execsql_test 1.$tn.3 [string map $MAP {
77    SELECT sum(%alias.%a) OVER %w FROM %t1 %alias WINDOW %w AS (ORDER BY %b)
78  }] {1 3 6 10 15}
79
80  do_execsql_test 1.$tn.4 [string map $MAP {
81    SELECT sum(%a) %alias FROM %t1
82  }] {15}
83}
84
85
86proc winproc {args} { return "window: $args" }
87db func window winproc
88do_execsql_test 2.0 {
89  SELECT window('hello world');
90} {{window: {hello world}}}
91
92proc wincmp {a b} { string compare $b $a }
93db collate window wincmp
94do_execsql_test 3.0 {
95  CREATE TABLE window(x COLLATE window);
96  INSERT INTO window VALUES('bob'), ('alice'), ('cate');
97  SELECT * FROM window ORDER BY x COLLATE window;
98} {cate bob alice}
99do_execsql_test 3.1 {
100  DROP TABLE window;
101  CREATE TABLE x1(x);
102  INSERT INTO x1 VALUES('bob'), ('alice'), ('cate');
103  CREATE INDEX window ON x1(x COLLATE window);
104  SELECT * FROM x1 ORDER BY x COLLATE window;
105} {cate bob alice}
106
107
108do_execsql_test 4.0 { CREATE TABLE t4(x, y); }
109
110# do_execsql_test 4.1 { PRAGMA parser_trace = 1 }
111do_execsql_test 4.1 {
112  SELECT * FROM t4 window, t4;
113}
114
115
116finish_test
117
118