3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. Specifically,
12 # it tests the sqlite3_create_window_function() API.
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix window6
19 ifcapable !windowfunc {
25 CREATE TABLE %t1(%x, %y %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');
48 set A(%typename) window
60 set A(%typename) integer
64 set setup_sql [string map $MAP $setup]
68 do_execsql_test 1.$tn.1 [string map $MAP {
69 SELECT group_concat(%x, '.') OVER (ORDER BY %y) FROM %t1
70 }] {1 1.2 1.2.3 1.2.3.4 1.2.3.4.5}
72 do_execsql_test 1.$tn.2 [string map $MAP {
73 SELECT sum(%x) OVER %w FROM %t1 WINDOW %w AS (ORDER BY %y)
76 do_execsql_test 1.$tn.3 [string map $MAP {
77 SELECT sum(%alias.%x) OVER %w FROM %t1 %alias WINDOW %w AS (ORDER BY %y)
80 do_execsql_test 1.$tn.4 [string map $MAP {
81 SELECT sum(%x) %alias FROM %t1
86 proc winproc {args} { return "window: $args" }
87 db func window winproc
89 SELECT window('hello world');
90 } {{window: {hello world}}}
92 proc wincmp {a b} { string compare $b $a }
93 db collate window wincmp
95 CREATE TABLE window(x COLLATE window);
96 INSERT INTO window VALUES('bob'), ('alice'), ('cate');
97 SELECT * FROM window ORDER BY x COLLATE window;
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;
108 do_execsql_test 4.0 { CREATE TABLE t4(x, y); }
110 # do_execsql_test 4.1 { PRAGMA parser_trace = 1 }
111 do_execsql_test 4.1 {
112 SELECT * FROM t4 window, t4;
115 #-------------------------------------------------------------------------
118 do_execsql_test 5.0 {
119 CREATE TABLE over(x, over);
120 CREATE TABLE window(x, window);
121 INSERT INTO over VALUES(1, 2), (3, 4), (5, 6);
122 INSERT INTO window VALUES(1, 2), (3, 4), (5, 6);
123 SELECT sum(x) over FROM over
126 do_execsql_test 5.1 {
127 SELECT sum(x) over over FROM over WINDOW over AS ()
130 do_execsql_test 5.2 {
131 SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over)
134 do_execsql_test 5.3 {
135 SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over);
138 do_execsql_test 5.4 {
139 SELECT sum(window) OVER window window FROM window window window window AS (ORDER BY window);
142 do_execsql_test 5.5 {
143 SELECT count(*) OVER win FROM over
144 WINDOW win AS (ORDER BY x ROWS BETWEEN +2 FOLLOWING AND +3 FOLLOWING)
147 #-------------------------------------------------------------------------
150 do_execsql_test 6.0 {
151 SELECT LIKE('!', '', '!') x WHERE x;
154 do_execsql_test 6.1 {
155 SELECT LIKE("!","","!")""WHeRE"";
158 do_catchsql_test 6.2 {
159 SELECT LIKE("!","","!")""window"";
160 } {1 {near "window": syntax error}}
163 do_execsql_test 7.0 {
164 CREATE TABLE t1(x TEXT);
165 CREATE INDEX i1 ON t1(x COLLATE nocase);
166 INSERT INTO t1 VALUES('');
169 do_execsql_test 7.1 {
170 SELECT count(*) FROM t1 WHERE x LIKE '!' ESCAPE '!';