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(%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'); 31} 32 33foreach {tn vars} { 34 1 {} 35 2 { set A(%t1) over } 36 3 { set A(%x) over } 37 4 { 38 set A(%alias) over 39 set A(%x) following 40 set A(%y) over 41 } 42 5 { 43 set A(%t1) over 44 set A(%x) following 45 set A(%y) preceding 46 set A(%w) current 47 set A(%alias) filter 48 set A(%typename) window 49 } 50 51 6 { 52 set A(%x) window 53 } 54} { 55 set A(%t1) t1 56 set A(%x) x 57 set A(%y) y 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(%x, '.') OVER (ORDER BY %y) 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(%x) OVER %w FROM %t1 WINDOW %w AS (ORDER BY %y) 74 }] {1 3 6 10 15} 75 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) 78 }] {1 3 6 10 15} 79 80 do_execsql_test 1.$tn.4 [string map $MAP { 81 SELECT sum(%x) %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#------------------------------------------------------------------------- 116reset_db 117 118do_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 124} {9} 125 126do_execsql_test 5.1 { 127 SELECT sum(x) over over FROM over WINDOW over AS () 128} {9 9 9} 129 130do_execsql_test 5.2 { 131 SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over) 132} {2 6 12} 133 134do_execsql_test 5.3 { 135 SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over); 136} {2 6 12} 137 138do_execsql_test 5.4 { 139 SELECT sum(window) OVER window window FROM window window window window AS (ORDER BY window); 140} {2 6 12} 141 142do_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) 145} {1 0 0} 146 147#------------------------------------------------------------------------- 148# 149do_execsql_test 6.0 { 150 SELECT LIKE("!","","!")""WHeRE""; 151} {1} 152do_catchsql_test 6.1 { 153 SELECT LIKE("!","","!")""window""; 154} {1 {near "window": syntax error}} 155 156finish_test 157 158