diff options
Diffstat (limited to 'test/filter1.test')
| -rw-r--r-- | test/filter1.test | 225 |
1 files changed, 225 insertions, 0 deletions
diff --git a/test/filter1.test b/test/filter1.test new file mode 100644 index 0000000..7b2cf9c --- /dev/null +++ b/test/filter1.test @@ -0,0 +1,225 @@ +# 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. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix filter1 + +ifcapable !windowfunc { + finish_test + return +} + +do_execsql_test 1.0 { + CREATE TABLE t1(a); + CREATE INDEX i1 ON t1(a); + INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9); +} + +do_execsql_test 1.1 { SELECT sum(a) FROM t1; } 45 +do_execsql_test 1.2 { SELECT sum(a) FILTER( WHERE a<5 ) FROM t1; } 10 + +do_execsql_test 1.3 { + SELECT sum(a) FILTER( WHERE a>9 ), + sum(a) FILTER( WHERE a>8 ), + sum(a) FILTER( WHERE a>7 ), + sum(a) FILTER( WHERE a>6 ), + sum(a) FILTER( WHERE a>5 ), + sum(a) FILTER( WHERE a>4 ), + sum(a) FILTER( WHERE a>3 ), + sum(a) FILTER( WHERE a>2 ), + sum(a) FILTER( WHERE a>1 ), + sum(a) FILTER( WHERE a>0 ) + FROM t1; +} {{} 9 17 24 30 35 39 42 44 45} + +do_execsql_test 1.4 { + SELECT max(a) FILTER (WHERE (a % 2)==0) FROM t1 +} {8} + +do_execsql_test 1.5 { + SELECT min(a) FILTER (WHERE a>4) FROM t1 +} {5} + +do_execsql_test 1.6 { + SELECT count(*) FILTER (WHERE a!=5) FROM t1 +} {8} + +do_execsql_test 1.7 { + SELECT min(a) FILTER (WHERE a>3) FROM t1 GROUP BY (a%2) ORDER BY 1; +} {4 5} + +do_execsql_test 1.8 { + CREATE VIEW vv AS + SELECT sum(a) FILTER( WHERE a>9 ), + sum(a) FILTER( WHERE a>8 ), + sum(a) FILTER( WHERE a>7 ), + sum(a) FILTER( WHERE a>6 ), + sum(a) FILTER( WHERE a>5 ), + sum(a) FILTER( WHERE a>4 ), + sum(a) FILTER( WHERE a>3 ), + sum(a) FILTER( WHERE a>2 ), + sum(a) FILTER( WHERE a>1 ), + sum(a) FILTER( WHERE a>0 ) + FROM t1; + SELECT * FROM vv; +} {{} 9 17 24 30 35 39 42 44 45} + + +#------------------------------------------------------------------------- +# Test some errors: +# +# .1 FILTER on a non-aggregate function, +# .2 Window function in FILTER clause, +# .3 Aggregate function in FILTER clause, +# +reset_db +do_execsql_test 2.0 { + CREATE TABLE t1(a); + INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9); +} + +do_catchsql_test 2.1 { + SELECT upper(a) FILTER (WHERE a=1) FROM t1 +} {1 {FILTER may not be used with non-aggregate upper()}} + +do_catchsql_test 2.2 { + SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1 +} {1 {misuse of window function max()}} + +do_catchsql_test 2.3 { + SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1 +} {1 {misuse of aggregate function count()}} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 3.0 { + CREATE TABLE t1(a,b); + INSERT INTO t1 VALUES(1, 1); +} +do_execsql_test 3.1 { + SELECT b, max(a) FILTER (WHERE b='x') FROM t1; +} {1 {}} + +do_execsql_test 3.2 { + CREATE TABLE t2(a, b, c); + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(1, 3, 4); + INSERT INTO t2 VALUES(2, 5, 6); + INSERT INTO t2 VALUES(2, 7, 8); +} +do_execsql_test 3.3 { + SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a; +} {1 3 {} 2 6 {}} + +do_execsql_test 3.4 { + DELETE FROM t2; + INSERT INTO t2 VALUES(1, 5, 'x'); + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(1, 4, 'x'); + INSERT INTO t2 VALUES(2, 5, 6); + INSERT INTO t2 VALUES(2, 7, 8); +} +do_execsql_test 3.5 { + SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a; +} {1 x 5 2 6 {}} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 4.0 { + CREATE TABLE t1(a, b, c); + INSERT INTO t1 VALUES('a', 0, 5); + INSERT INTO t1 VALUES('a', 1, 10); + INSERT INTO t1 VALUES('a', 0, 15); + + INSERT INTO t1 VALUES('b', 0, 5); + INSERT INTO t1 VALUES('b', 1, 1000); + INSERT INTO t1 VALUES('b', 0, 5); + + INSERT INTO t1 VALUES('c', 0, 1); + INSERT INTO t1 VALUES('c', 1, 2); + INSERT INTO t1 VALUES('c', 0, 3); +} + +do_execsql_test 4.1 { + SELECT avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY h; +} {2.0 5.0 10.0} +do_execsql_test 4.2 { + SELECT avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY (h+1.0); +} {2.0 5.0 10.0} +do_execsql_test 4.3 { + SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c); +} {c 2.0 a 10.0 b 5.0} +do_execsql_test 4.4 { + SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2 +} {c 2.0 b 5.0 a 10.0} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 5.0 { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 2); + INSERT INTO t1 VALUES(1, 3); +} + +do_execsql_test 5.1 { + SELECT count(*) FILTER (WHERE b>2) FROM (SELECT * FROM t1) +} {1} + +do_execsql_test 5.2 { + SELECT count(*) FILTER (WHERE b>2) OVER () FROM (SELECT * FROM t1) +} {1 1} + +do_execsql_test 5.3 { + SELECT count(*) FILTER (WHERE b>2) OVER (ORDER BY b) FROM (SELECT * FROM t1) +} {0 1} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 6.0 { + CREATE TABLE t1(a,b); + INSERT INTO t1 VALUES(1,1); + INSERT INTO t1 VALUES(2,2); + CREATE TABLE t2(x,y); + INSERT INTO t2 VALUES(1,1); +} + +do_execsql_test 6.1 { + SELECT (SELECT COUNT(a) FILTER(WHERE x) FROM t2) FROM t1; +} {1 1} +do_execsql_test 6.2 { + SELECT (SELECT COUNT(a+x) FROM t2) FROM t1; +} {1 1} +do_execsql_test 6.3 { + SELECT (SELECT COUNT(a) FROM t2) FROM t1; +} {2} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 7.0 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + INSERT INTO t1 VALUES(321, 100000); + INSERT INTO t1 VALUES(111, 110000); + INSERT INTO t1 VALUES(444, 120000); + INSERT INTO t1 VALUES(222, 130000); +} + +do_execsql_test 7.1 { + SELECT max(a), max(a) FILTER (WHERE b<12345), b FROM t1; +} { + 444 {} 120000 +} + + + +finish_test |