# 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 window6 ifcapable !windowfunc { finish_test return } set setup { CREATE TABLE %t1(%x, %y %typename); INSERT INTO %t1 VALUES(1, 'a'); INSERT INTO %t1 VALUES(2, 'b'); INSERT INTO %t1 VALUES(3, 'c'); INSERT INTO %t1 VALUES(4, 'd'); INSERT INTO %t1 VALUES(5, 'e'); } foreach {tn vars} { 1 {} 2 { set A(%t1) over } 3 { set A(%x) over } 4 { set A(%alias) over set A(%x) following set A(%y) over } 5 { set A(%t1) over set A(%x) following set A(%y) preceding set A(%w) current set A(%alias) filter set A(%typename) window } 6 { set A(%x) window } } { set A(%t1) t1 set A(%x) x set A(%y) y set A(%w) w set A(%alias) alias set A(%typename) integer eval $vars set MAP [array get A] set setup_sql [string map $MAP $setup] reset_db execsql $setup_sql do_execsql_test 1.$tn.1 [string map $MAP { SELECT group_concat(%x, '.') OVER (ORDER BY %y) FROM %t1 }] {1 1.2 1.2.3 1.2.3.4 1.2.3.4.5} do_execsql_test 1.$tn.2 [string map $MAP { SELECT sum(%x) OVER %w FROM %t1 WINDOW %w AS (ORDER BY %y) }] {1 3 6 10 15} do_execsql_test 1.$tn.3 [string map $MAP { SELECT sum(%alias.%x) OVER %w FROM %t1 %alias WINDOW %w AS (ORDER BY %y) }] {1 3 6 10 15} do_execsql_test 1.$tn.4 [string map $MAP { SELECT sum(%x) %alias FROM %t1 }] {15} } proc winproc {args} { return "window: $args" } db func window winproc do_execsql_test 2.0 { SELECT window('hello world'); } {{window: {hello world}}} proc wincmp {a b} { string compare $b $a } db collate window wincmp do_execsql_test 3.0 { CREATE TABLE window(x COLLATE window); INSERT INTO window VALUES('bob'), ('alice'), ('cate'); SELECT * FROM window ORDER BY x COLLATE window; } {cate bob alice} do_execsql_test 3.1 { DROP TABLE window; CREATE TABLE x1(x); INSERT INTO x1 VALUES('bob'), ('alice'), ('cate'); CREATE INDEX window ON x1(x COLLATE window); SELECT * FROM x1 ORDER BY x COLLATE window; } {cate bob alice} do_execsql_test 4.0 { CREATE TABLE t4(x, y); } # do_execsql_test 4.1 { PRAGMA parser_trace = 1 } do_execsql_test 4.1 { SELECT * FROM t4 window, t4; } #------------------------------------------------------------------------- reset_db do_execsql_test 5.0 { CREATE TABLE over(x, over); CREATE TABLE window(x, window); INSERT INTO over VALUES(1, 2), (3, 4), (5, 6); INSERT INTO window VALUES(1, 2), (3, 4), (5, 6); SELECT sum(x) over FROM over } {9} do_execsql_test 5.1 { SELECT sum(x) over over FROM over WINDOW over AS () } {9 9 9} do_execsql_test 5.2 { SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over) } {2 6 12} do_execsql_test 5.3 { SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over); } {2 6 12} do_execsql_test 5.4 { SELECT sum(window) OVER window window FROM window window window window AS (ORDER BY window); } {2 6 12} do_execsql_test 5.5 { SELECT count(*) OVER win FROM over WINDOW win AS (ORDER BY x ROWS BETWEEN +2 FOLLOWING AND +3 FOLLOWING) } {1 0 0} #------------------------------------------------------------------------- # do_execsql_test 6.0 { SELECT LIKE('!', '', '!') x WHERE x; } {} do_execsql_test 6.1 { SELECT LIKE("!","","!")""WHeRE""; } {} do_catchsql_test 6.2 { SELECT LIKE("!","","!")""window""; } {1 {near "window": syntax error}} reset_db do_execsql_test 7.0 { CREATE TABLE t1(x TEXT); CREATE INDEX i1 ON t1(x COLLATE nocase); INSERT INTO t1 VALUES(''); } do_execsql_test 7.1 { SELECT count(*) FROM t1 WHERE x LIKE '!' ESCAPE '!'; } {0} #------------------------------------------------------------------------- # do_execsql_test 8.0 { CREATE TABLE IF NOT EXISTS "sample" ( "id" INTEGER NOT NULL PRIMARY KEY, "counter" INTEGER NOT NULL, "value" REAL NOT NULL ); INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.), (2, 3.), (3, 100.); } do_execsql_test 8.1 { SELECT "counter", "value", RANK() OVER w AS "rank" FROM "sample" WINDOW w AS (PARTITION BY "counter" ORDER BY "value" DESC) ORDER BY "counter", RANK() OVER w } { 1 20.0 1 1 10.0 2 2 3.0 1 2 1.0 2 3 100.0 1 } do_execsql_test 8.2 { SELECT "counter", "value", SUM("value") OVER (ORDER BY "id" ROWS 2 PRECEDING) FROM "sample" ORDER BY "id" } { 1 10.0 10.0 1 20.0 30.0 2 1.0 31.0 2 3.0 24.0 3 100.0 104.0 } do_execsql_test 8.3 { SELECT SUM("value") OVER (ORDER BY "id" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM "sample" ORDER BY "id" } { 10.0 30.0 31.0 24.0 104.0 } do_execsql_test 9.0 { WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5) SELECT x, group_concat(x) OVER (ORDER BY x ROWS 2 PRECEDING) FROM c; } { 1 1 2 1,2 3 1,2,3 4 2,3,4 5 3,4,5 } do_catchsql_test 9.1 { WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5) SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING) FROM c; } {1 {RANGE PRECEDING is only supported with UNBOUNDED}} do_catchsql_test 9.2 { WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5) SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING) FROM c; } {1 {RANGE FOLLOWING is only supported with UNBOUNDED}} do_catchsql_test 9.3 { WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5) SELECT count(DISTINCT x) OVER (ORDER BY x) FROM c; } {1 {DISTINCT is not supported for window functions}} finish_test