gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/filter1.test (about) 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. 12 # 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix filter1 17 18 ifcapable !windowfunc { 19 finish_test 20 return 21 } 22 23 do_execsql_test 1.0 { 24 CREATE TABLE t1(a); 25 CREATE INDEX i1 ON t1(a); 26 INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9); 27 } 28 29 do_execsql_test 1.1 { SELECT sum(a) FROM t1; } 45 30 do_execsql_test 1.2 { SELECT sum(a) FILTER( WHERE a<5 ) FROM t1; } 10 31 32 do_execsql_test 1.3 { 33 SELECT sum(a) FILTER( WHERE a>9 ), 34 sum(a) FILTER( WHERE a>8 ), 35 sum(a) FILTER( WHERE a>7 ), 36 sum(a) FILTER( WHERE a>6 ), 37 sum(a) FILTER( WHERE a>5 ), 38 sum(a) FILTER( WHERE a>4 ), 39 sum(a) FILTER( WHERE a>3 ), 40 sum(a) FILTER( WHERE a>2 ), 41 sum(a) FILTER( WHERE a>1 ), 42 sum(a) FILTER( WHERE a>0 ) 43 FROM t1; 44 } {{} 9 17 24 30 35 39 42 44 45} 45 46 do_execsql_test 1.4 { 47 SELECT max(a) FILTER (WHERE (a % 2)==0) FROM t1 48 } {8} 49 50 do_execsql_test 1.5 { 51 SELECT min(a) FILTER (WHERE a>4) FROM t1 52 } {5} 53 54 do_execsql_test 1.6 { 55 SELECT count(*) FILTER (WHERE a!=5) FROM t1 56 } {8} 57 58 do_execsql_test 1.7 { 59 SELECT min(a) FILTER (WHERE a>3) FROM t1 GROUP BY (a%2) ORDER BY 1; 60 } {4 5} 61 62 do_execsql_test 1.8 { 63 CREATE VIEW vv AS 64 SELECT sum(a) FILTER( WHERE a>9 ), 65 sum(a) FILTER( WHERE a>8 ), 66 sum(a) FILTER( WHERE a>7 ), 67 sum(a) FILTER( WHERE a>6 ), 68 sum(a) FILTER( WHERE a>5 ), 69 sum(a) FILTER( WHERE a>4 ), 70 sum(a) FILTER( WHERE a>3 ), 71 sum(a) FILTER( WHERE a>2 ), 72 sum(a) FILTER( WHERE a>1 ), 73 sum(a) FILTER( WHERE a>0 ) 74 FROM t1; 75 SELECT * FROM vv; 76 } {{} 9 17 24 30 35 39 42 44 45} 77 78 79 #------------------------------------------------------------------------- 80 # Test some errors: 81 # 82 # .1 FILTER on a non-aggregate function, 83 # .2 Window function in FILTER clause, 84 # .3 Aggregate function in FILTER clause, 85 # 86 reset_db 87 do_execsql_test 2.0 { 88 CREATE TABLE t1(a); 89 INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9); 90 } 91 92 do_catchsql_test 2.1 { 93 SELECT upper(a) FILTER (WHERE a=1) FROM t1 94 } {1 {FILTER may not be used with non-aggregate upper()}} 95 96 do_catchsql_test 2.2 { 97 SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1 98 } {1 {misuse of window function max()}} 99 100 do_catchsql_test 2.3 { 101 SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1 102 } {1 {misuse of aggregate function count()}} 103 104 #------------------------------------------------------------------------- 105 reset_db 106 do_execsql_test 3.0 { 107 CREATE TABLE t1(a,b); 108 INSERT INTO t1 VALUES(1, 1); 109 } 110 do_execsql_test 3.1 { 111 SELECT b, max(a) FILTER (WHERE b='x') FROM t1; 112 } {1 {}} 113 114 do_execsql_test 3.2 { 115 CREATE TABLE t2(a, b, c); 116 INSERT INTO t2 VALUES(1, 2, 3); 117 INSERT INTO t2 VALUES(1, 3, 4); 118 INSERT INTO t2 VALUES(2, 5, 6); 119 INSERT INTO t2 VALUES(2, 7, 8); 120 } 121 do_execsql_test 3.3 { 122 SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a; 123 } {1 3 {} 2 6 {}} 124 125 do_execsql_test 3.4 { 126 DELETE FROM t2; 127 INSERT INTO t2 VALUES(1, 5, 'x'); 128 INSERT INTO t2 VALUES(1, 2, 3); 129 INSERT INTO t2 VALUES(1, 4, 'x'); 130 INSERT INTO t2 VALUES(2, 5, 6); 131 INSERT INTO t2 VALUES(2, 7, 8); 132 } 133 do_execsql_test 3.5 { 134 SELECT a, c, max(b) FILTER (WHERE c='x') FROM t2 GROUP BY a; 135 } {1 x 5 2 6 {}} 136 137 #------------------------------------------------------------------------- 138 reset_db 139 do_execsql_test 4.0 { 140 CREATE TABLE t1(a, b, c); 141 INSERT INTO t1 VALUES('a', 0, 5); 142 INSERT INTO t1 VALUES('a', 1, 10); 143 INSERT INTO t1 VALUES('a', 0, 15); 144 145 INSERT INTO t1 VALUES('b', 0, 5); 146 INSERT INTO t1 VALUES('b', 1, 1000); 147 INSERT INTO t1 VALUES('b', 0, 5); 148 149 INSERT INTO t1 VALUES('c', 0, 1); 150 INSERT INTO t1 VALUES('c', 1, 2); 151 INSERT INTO t1 VALUES('c', 0, 3); 152 } 153 154 do_execsql_test 4.1 { 155 SELECT avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY h; 156 } {2.0 5.0 10.0} 157 do_execsql_test 4.2 { 158 SELECT avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY (h+1.0); 159 } {2.0 5.0 10.0} 160 do_execsql_test 4.3 { 161 SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c); 162 } {c 2.0 a 10.0 b 5.0} 163 do_execsql_test 4.4 { 164 SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2 165 } {c 2.0 b 5.0 a 10.0} 166 167 #------------------------------------------------------------------------- 168 reset_db 169 do_execsql_test 5.0 { 170 CREATE TABLE t1(a, b); 171 INSERT INTO t1 VALUES(1, 2); 172 INSERT INTO t1 VALUES(1, 3); 173 } 174 175 do_execsql_test 5.1 { 176 SELECT count(*) FILTER (WHERE b>2) FROM (SELECT * FROM t1) 177 } {1} 178 179 do_execsql_test 5.2 { 180 SELECT count(*) FILTER (WHERE b>2) OVER () FROM (SELECT * FROM t1) 181 } {1 1} 182 183 do_execsql_test 5.3 { 184 SELECT count(*) FILTER (WHERE b>2) OVER (ORDER BY b) FROM (SELECT * FROM t1) 185 } {0 1} 186 187 #------------------------------------------------------------------------- 188 reset_db 189 do_execsql_test 6.0 { 190 CREATE TABLE t1(a,b); 191 INSERT INTO t1 VALUES(1,1); 192 INSERT INTO t1 VALUES(2,2); 193 CREATE TABLE t2(x,y); 194 INSERT INTO t2 VALUES(1,1); 195 } 196 197 do_execsql_test 6.1 { 198 SELECT (SELECT COUNT(a) FILTER(WHERE x) FROM t2) FROM t1; 199 } {1 1} 200 do_execsql_test 6.2 { 201 SELECT (SELECT COUNT(a+x) FROM t2) FROM t1; 202 } {1 1} 203 do_execsql_test 6.3 { 204 SELECT (SELECT COUNT(a) FROM t2) FROM t1; 205 } {2} 206 207 #------------------------------------------------------------------------- 208 reset_db 209 do_execsql_test 7.0 { 210 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 211 INSERT INTO t1 VALUES(321, 100000); 212 INSERT INTO t1 VALUES(111, 110000); 213 INSERT INTO t1 VALUES(444, 120000); 214 INSERT INTO t1 VALUES(222, 130000); 215 } 216 217 do_execsql_test 7.1 { 218 SELECT max(a), max(a) FILTER (WHERE b<12345), b FROM t1; 219 } { 220 444 {} 120000 221 } 222 223 224 225 finish_test