gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/window9.test (about) 1 # 2019 June 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 window9 17 18 ifcapable !windowfunc { 19 finish_test 20 return 21 } 22 23 do_execsql_test 1.0 { 24 CREATE TABLE fruits( 25 name TEXT COLLATE NOCASE, 26 color TEXT COLLATE NOCASE 27 ); 28 } 29 30 do_execsql_test 1.1 { 31 INSERT INTO fruits (name, color) VALUES ('apple', 'RED'); 32 INSERT INTO fruits (name, color) VALUES ('APPLE', 'yellow'); 33 INSERT INTO fruits (name, color) VALUES ('pear', 'YELLOW'); 34 INSERT INTO fruits (name, color) VALUES ('PEAR', 'green'); 35 } 36 37 do_execsql_test 1.2 { 38 SELECT name, color, dense_rank() OVER (ORDER BY name) FROM fruits; 39 } { 40 apple RED 1 41 APPLE yellow 1 42 pear YELLOW 2 43 PEAR green 2 44 } 45 46 do_execsql_test 1.3 { 47 SELECT name, color, 48 dense_rank() OVER (PARTITION BY name ORDER BY color) 49 FROM fruits; 50 } { 51 apple RED 1 52 APPLE yellow 2 53 PEAR green 1 54 pear YELLOW 2 55 } 56 57 do_execsql_test 1.4 { 58 SELECT name, color, 59 dense_rank() OVER (ORDER BY name), 60 dense_rank() OVER (PARTITION BY name ORDER BY color) 61 FROM fruits; 62 } { 63 apple RED 1 1 64 APPLE yellow 1 2 65 PEAR green 2 1 66 pear YELLOW 2 2 67 } 68 69 do_execsql_test 1.5 { 70 SELECT name, color, 71 dense_rank() OVER (ORDER BY name), 72 dense_rank() OVER (PARTITION BY name ORDER BY color) 73 FROM fruits ORDER BY color; 74 } { 75 PEAR green 2 1 76 apple RED 1 1 77 APPLE yellow 1 2 78 pear YELLOW 2 2 79 } 80 81 do_execsql_test 2.0 { 82 CREATE TABLE t1(a BLOB, b INTEGER, c COLLATE nocase); 83 INSERT INTO t1 VALUES(1, 2, 'abc'); 84 INSERT INTO t1 VALUES(3, 4, 'ABC'); 85 } 86 87 do_execsql_test 2.1.1 { 88 SELECT c=='Abc' FROM t1 89 } {1 1} 90 do_execsql_test 2.1.2 { 91 SELECT c=='Abc', rank() OVER (ORDER BY b) FROM t1 92 } {1 1 1 2} 93 94 do_execsql_test 2.2.1 { 95 SELECT b=='2' FROM t1 96 } {1 0} 97 do_execsql_test 2.2.2 { 98 SELECT b=='2', rank() OVER (ORDER BY a) FROM t1 99 } {1 1 0 2} 100 101 #------------------------------------------------------------------------- 102 reset_db 103 do_execsql_test 3.0 { 104 CREATE TABLE t1(a); 105 CREATE TABLE t2(a,b,c); 106 } 107 108 do_execsql_test 3.1 { 109 SELECT EXISTS(SELECT 1 FROM t1 ORDER BY sum(a) OVER ()) FROM t1; 110 } 111 112 do_execsql_test 3.2 { 113 SELECT sum(a) OVER () FROM t2 114 ORDER BY EXISTS(SELECT 1 FROM t2 ORDER BY sum(a) OVER ()); 115 } 116 117 do_catchsql_test 3.3 { 118 SELECT a, sum(a) OVER (ORDER BY a DESC) FROM t2 119 ORDER BY EXISTS( 120 SELECT 1 FROM t2 ORDER BY sum(a) OVER (ORDER BY a) 121 ) OVER (ORDER BY a); 122 } {1 {near "OVER": syntax error}} 123 124 do_catchsql_test 3.4 { 125 SELECT y, y+1, y+2 FROM ( 126 SELECT c IN ( 127 SELECT min(a) OVER (), 128 (abs(row_number() OVER())+22)/19, 129 max(a) OVER () FROM t1 130 ) AS y FROM t2 131 ); 132 } {1 {sub-select returns 3 columns - expected 1}} 133 134 #------------------------------------------------------------------------- 135 reset_db 136 do_execsql_test 4.0 { 137 CREATE TABLE t1(a, b TEXT); 138 INSERT INTO t1 VALUES('A', 1), ('A', 2), ('2', 1), ('2', 2); 139 } 140 141 do_execsql_test 4.1.1 { 142 SELECT b, b=count(*), '1,2' FROM t1 GROUP BY b; 143 } {1 0 1,2 2 1 1,2} 144 do_execsql_test 4.1.2 { 145 SELECT b, b=count(*), group_concat(b) OVER () FROM t1 GROUP BY b; 146 } {1 0 1,2 2 1 1,2} 147 148 #-------------------------------------------------------------------------- 149 reset_db 150 do_execsql_test 5.0 { 151 CREATE TABLE t1(a, b, c, d, e); 152 CREATE INDEX i1 ON t1(a, b, c, d, e); 153 } 154 155 foreach {tn sql} { 156 1 { 157 SELECT 158 sum(e) OVER (), 159 sum(e) OVER (ORDER BY a), 160 sum(e) OVER (PARTITION BY a ORDER BY b), 161 sum(e) OVER (PARTITION BY a, b ORDER BY c), 162 sum(e) OVER (PARTITION BY a, b, c ORDER BY d) 163 FROM t1; 164 } 165 2 { 166 SELECT sum(e) OVER (PARTITION BY a ORDER BY b) FROM t1 ORDER BY a; 167 } 168 } { 169 do_test 5.1.$tn { 170 execsql "EXPLAIN QUERY PLAN $sql" 171 } {~/ORDER/} 172 } 173 174 #------------------------------------------------------------------------- 175 reset_db 176 do_execsql_test 6.0 { 177 CREATE TABLE t0(c0); 178 INSERT INTO t0(c0) VALUES (0); 179 } 180 181 do_execsql_test 6.1 { 182 SELECT * FROM t0 WHERE 183 EXISTS ( 184 SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0 185 ) >=1 AND 186 EXISTS ( 187 SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0 188 ) <=1; 189 } {0} 190 191 do_execsql_test 6.2 { 192 SELECT * FROM t0 WHERE EXISTS ( 193 SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0 194 ) 195 BETWEEN 1 AND 1; 196 } {0} 197 198 #------------------------------------------------------------------------- 199 reset_db 200 do_execsql_test 7.0 { 201 DROP TABLE IF EXISTS t1; 202 CREATE TABLE t1(x, y); 203 INSERT INTO t1 VALUES(10, 1); 204 INSERT INTO t1 VALUES(20, 2); 205 INSERT INTO t1 VALUES(3, 3); 206 INSERT INTO t1 VALUES(2, 4); 207 INSERT INTO t1 VALUES(1, 5); 208 } {} 209 210 211 do_execsql_test 7.1 { 212 SELECT avg(x) OVER (ORDER BY y) AS z FROM t1 ORDER BY z 213 } { 214 7.2 8.75 10.0 11.0 15.0 215 } 216 217 do_execsql_test 7.2 { 218 SELECT avg(x) OVER (ORDER BY y) z FROM t1 ORDER BY (z IS y); 219 } { 220 10.0 15.0 11.0 8.75 7.2 221 } 222 223 do_execsql_test 7.3 { 224 SELECT avg(x) OVER (ORDER BY y) z FROM t1 ORDER BY (y IS z); 225 } { 226 10.0 15.0 11.0 8.75 7.2 227 } 228 229 do_execsql_test 7.4 { 230 SELECT avg(x) OVER (ORDER BY y) z FROM t1 ORDER BY z + 0.0; 231 } { 232 7.2 8.75 10.0 11.0 15.0 233 } 234 235 #------------------------------------------------------------------------- 236 reset_db 237 do_execsql_test 8.1.1 { 238 CREATE TABLE t1(a, b); 239 INSERT INTO t1 VALUES(1, 2), (3, 4); 240 SELECT min( sum(a) ) OVER () FROM t1; 241 } {4} 242 243 do_execsql_test 8.1.2 { 244 SELECT min( sum(a) ) OVER () FROM t1 GROUP BY a; 245 } {1 1} 246 247 do_execsql_test 8.2 { 248 CREATE VIEW v1 AS 249 SELECT 0 AS x 250 UNION 251 SELECT count() OVER() FROM (SELECT 0) 252 ORDER BY 1 253 ; 254 } 255 256 do_catchsql_test 8.3 { 257 SELECT min( max((SELECT x FROM v1)) ) OVER() 258 } {0 0} 259 260 do_execsql_test 8.4 { 261 SELECT( 262 SELECT x UNION 263 SELECT sum( avg((SELECT x FROM v1)) ) OVER() 264 ) 265 FROM v1; 266 } {0.0 0.0} 267 268 #-------------------------------------------------------------------------- 269 reset_db 270 do_execsql_test 9.0 { 271 CREATE TABLE t1(a, b, c); 272 INSERT INTO t1 VALUES(NULL,'bb',356); 273 INSERT INTO t1 VALUES('CB','aa',158); 274 INSERT INTO t1 VALUES('BB','aa',399); 275 INSERT INTO t1 VALUES('FF','bb',938); 276 } 277 278 do_catchsql_test 9.1 { 279 SELECT sum(c) OVER ( 280 ORDER BY c RANGE BETWEEN 0 PRECEDING AND '-700' PRECEDING 281 ) 282 FROM t1 283 } {1 {frame ending offset must be a non-negative number}} 284 285 finish_test