1 2#pragma ident "%Z%%M% %I% %E% SMI" 3 4# 2002 February 26 5# 6# The author disclaims copyright to this source code. In place of 7# a legal notice, here is a blessing: 8# 9# May you do good and not evil. 10# May you find forgiveness for yourself and forgive others. 11# May you share freely, never taking more than you give. 12# 13#*********************************************************************** 14# This file implements regression tests for SQLite library. The 15# focus of this file is testing VIEW statements. 16# 17# $Id: view.test,v 1.16.2.1 2004/07/20 00:20:47 drh Exp $ 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21do_test view-1.0 { 22 execsql { 23 CREATE TABLE t1(a,b,c); 24 INSERT INTO t1 VALUES(1,2,3); 25 INSERT INTO t1 VALUES(4,5,6); 26 INSERT INTO t1 VALUES(7,8,9); 27 SELECT * FROM t1; 28 } 29} {1 2 3 4 5 6 7 8 9} 30 31do_test view-1.1 { 32 execsql { 33 BEGIN; 34 CREATE VIEW v1 AS SELECT a,b FROM t1; 35 SELECT * FROM v1 ORDER BY a; 36 } 37} {1 2 4 5 7 8} 38do_test view-1.2 { 39 catchsql { 40 ROLLBACK; 41 SELECT * FROM v1 ORDER BY a; 42 } 43} {1 {no such table: v1}} 44do_test view-1.3 { 45 execsql { 46 CREATE VIEW v1 AS SELECT a,b FROM t1; 47 SELECT * FROM v1 ORDER BY a; 48 } 49} {1 2 4 5 7 8} 50do_test view-1.3.1 { 51 db close 52 sqlite db test.db 53 execsql { 54 SELECT * FROM v1 ORDER BY a; 55 } 56} {1 2 4 5 7 8} 57do_test view-1.4 { 58 catchsql { 59 DROP VIEW v1; 60 SELECT * FROM v1 ORDER BY a; 61 } 62} {1 {no such table: v1}} 63do_test view-1.5 { 64 execsql { 65 CREATE VIEW v1 AS SELECT a,b FROM t1; 66 SELECT * FROM v1 ORDER BY a; 67 } 68} {1 2 4 5 7 8} 69do_test view-1.6 { 70 catchsql { 71 DROP TABLE t1; 72 SELECT * FROM v1 ORDER BY a; 73 } 74} {1 {no such table: main.t1}} 75do_test view-1.7 { 76 execsql { 77 CREATE TABLE t1(x,a,b,c); 78 INSERT INTO t1 VALUES(1,2,3,4); 79 INSERT INTO t1 VALUES(4,5,6,7); 80 INSERT INTO t1 VALUES(7,8,9,10); 81 SELECT * FROM v1 ORDER BY a; 82 } 83} {2 3 5 6 8 9} 84do_test view-1.8 { 85 db close 86 sqlite db test.db 87 execsql { 88 SELECT * FROM v1 ORDER BY a; 89 } 90} {2 3 5 6 8 9} 91 92do_test view-2.1 { 93 execsql { 94 CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5 95 }; # No semicolon 96 execsql2 { 97 SELECT * FROM v2; 98 } 99} {x 7 a 8 b 9 c 10} 100do_test view-2.2 { 101 catchsql { 102 INSERT INTO v2 VALUES(1,2,3,4); 103 } 104} {1 {cannot modify v2 because it is a view}} 105do_test view-2.3 { 106 catchsql { 107 UPDATE v2 SET a=10 WHERE a=5; 108 } 109} {1 {cannot modify v2 because it is a view}} 110do_test view-2.4 { 111 catchsql { 112 DELETE FROM v2; 113 } 114} {1 {cannot modify v2 because it is a view}} 115do_test view-2.5 { 116 execsql { 117 INSERT INTO t1 VALUES(11,12,13,14); 118 SELECT * FROM v2 ORDER BY x; 119 } 120} {7 8 9 10 11 12 13 14} 121do_test view-2.6 { 122 execsql { 123 SELECT x FROM v2 WHERE a>10 124 } 125} {11} 126 127# Test that column name of views are generated correctly. 128# 129do_test view-3.1 { 130 execsql2 { 131 SELECT * FROM v1 LIMIT 1 132 } 133} {a 2 b 3} 134do_test view-3.2 { 135 execsql2 { 136 SELECT * FROM v2 LIMIT 1 137 } 138} {x 7 a 8 b 9 c 10} 139do_test view-3.3 { 140 execsql2 { 141 DROP VIEW v1; 142 CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1; 143 SELECT * FROM v1 LIMIT 1 144 } 145} {xyz 2 pqr 7 c-b 1} 146do_test view-3.4 { 147 execsql2 { 148 CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b; 149 SELECT * FROM v3 LIMIT 4; 150 } 151} {b 2 b 3 b 5 b 6} 152do_test view-3.5 { 153 execsql2 { 154 CREATE VIEW v4 AS 155 SELECT a, b FROM t1 156 UNION 157 SELECT b AS 'x', a AS 'y' FROM t1 158 ORDER BY x, y; 159 SELECT y FROM v4 ORDER BY y LIMIT 4; 160 } 161} {y 2 y 3 y 5 y 6} 162 163 164do_test view-4.1 { 165 catchsql { 166 DROP VIEW t1; 167 } 168} {1 {use DROP TABLE to delete table t1}} 169do_test view-4.2 { 170 execsql { 171 SELECT 1 FROM t1 LIMIT 1; 172 } 173} 1 174do_test view-4.3 { 175 catchsql { 176 DROP TABLE v1; 177 } 178} {1 {use DROP VIEW to delete view v1}} 179do_test view-4.4 { 180 execsql { 181 SELECT 1 FROM v1 LIMIT 1; 182 } 183} {1} 184do_test view-4.5 { 185 catchsql { 186 CREATE INDEX i1v1 ON v1(xyz); 187 } 188} {1 {views may not be indexed}} 189 190do_test view-5.1 { 191 execsql { 192 CREATE TABLE t2(y,a); 193 INSERT INTO t2 VALUES(22,2); 194 INSERT INTO t2 VALUES(33,3); 195 INSERT INTO t2 VALUES(44,4); 196 INSERT INTO t2 VALUES(55,5); 197 SELECT * FROM t2; 198 } 199} {22 2 33 3 44 4 55 5} 200do_test view-5.2 { 201 execsql { 202 CREATE VIEW v5 AS 203 SELECT t1.x AS v, t2.y AS w FROM t1 JOIN t2 USING(a); 204 SELECT * FROM v5; 205 } 206} {1 22 4 55} 207 208# Verify that the view v5 gets flattened. see sqliteFlattenSubquery(). 209# Ticket #272 210do_test view-5.3 { 211 lsearch [execsql { 212 EXPLAIN SELECT * FROM v5; 213 }] OpenTemp 214} {-1} 215do_test view-5.4 { 216 execsql { 217 SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y; 218 } 219} {1 22 22 2 4 55 55 5} 220do_test view-5.5 { 221 lsearch [execsql { 222 EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y; 223 }] OpenTemp 224} {-1} 225do_test view-5.6 { 226 execsql { 227 SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y; 228 } 229} {22 2 1 22 55 5 4 55} 230do_test view-5.7 { 231 lsearch [execsql { 232 EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y; 233 }] OpenTemp 234} {-1} 235do_test view-5.8 { 236 execsql { 237 SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y; 238 } 239} {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5} 240do_test view-5.9 { 241 lsearch [execsql { 242 EXPLAIN SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y; 243 }] OpenTemp 244} {-1} 245 246do_test view-6.1 { 247 execsql { 248 SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2; 249 } 250} {7 8 9 10 27} 251do_test view-6.2 { 252 execsql { 253 SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2; 254 } 255} {11 12 13 14 39} 256 257do_test view-7.1 { 258 execsql { 259 CREATE TABLE test1(id integer primary key, a); 260 CREATE TABLE test2(id integer, b); 261 INSERT INTO test1 VALUES(1,2); 262 INSERT INTO test2 VALUES(1,3); 263 CREATE VIEW test AS 264 SELECT test1.id, a, b 265 FROM test1 JOIN test2 ON test2.id=test1.id; 266 SELECT * FROM test; 267 } 268} {1 2 3} 269do_test view-7.2 { 270 db close 271 sqlite db test.db 272 execsql { 273 SELECT * FROM test; 274 } 275} {1 2 3} 276do_test view-7.3 { 277 execsql { 278 DROP VIEW test; 279 CREATE VIEW test AS 280 SELECT test1.id, a, b 281 FROM test1 JOIN test2 USING(id); 282 SELECT * FROM test; 283 } 284} {1 2 3} 285do_test view-7.4 { 286 db close 287 sqlite db test.db 288 execsql { 289 SELECT * FROM test; 290 } 291} {1 2 3} 292do_test view-7.5 { 293 execsql { 294 DROP VIEW test; 295 CREATE VIEW test AS 296 SELECT test1.id, a, b 297 FROM test1 NATURAL JOIN test2; 298 SELECT * FROM test; 299 } 300} {1 2 3} 301do_test view-7.6 { 302 db close 303 sqlite db test.db 304 execsql { 305 SELECT * FROM test; 306 } 307} {1 2 3} 308 309do_test view-8.1 { 310 execsql { 311 CREATE VIEW v6 AS SELECT pqr, xyz FROM v1; 312 SELECT * FROM v6 ORDER BY xyz; 313 } 314} {7 2 13 5 19 8 27 12} 315do_test view-8.2 { 316 db close 317 sqlite db test.db 318 execsql { 319 SELECT * FROM v6 ORDER BY xyz; 320 } 321} {7 2 13 5 19 8 27 12} 322do_test view-8.3 { 323 execsql { 324 CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6; 325 SELECT * FROM v7 ORDER BY a; 326 } 327} {9 18 27 39} 328do_test view-8.4 { 329 execsql { 330 CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM 331 (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo); 332 SELECT * FROM v8; 333 } 334} 3 335do_test view-8.5 { 336 execsql { 337 SELECT mx+10, mx*2 FROM v8; 338 } 339} {13 6} 340do_test view-8.6 { 341 execsql { 342 SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2; 343 } 344} {13 7} 345do_test view-8.7 { 346 execsql { 347 SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2; 348 } 349} {13 13 13 19 13 27} 350 351# Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW. 352# 353do_test view-9.1 { 354 execsql { 355 INSERT INTO t2 SELECT * FROM t2 WHERE a<5; 356 INSERT INTO t2 SELECT * FROM t2 WHERE a<4; 357 INSERT INTO t2 SELECT * FROM t2 WHERE a<3; 358 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1; 359 } 360} {1 2 4 8} 361do_test view-9.2 { 362 execsql { 363 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3; 364 } 365} {1 2 4} 366do_test view-9.3 { 367 execsql { 368 CREATE VIEW v9 AS 369 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3; 370 SELECT * FROM v9; 371 } 372} {1 2 4} 373do_test view-9.4 { 374 execsql { 375 SELECT * FROM v9 ORDER BY 1 DESC; 376 } 377} {4 2 1} 378do_test view-9.5 { 379 execsql { 380 CREATE VIEW v10 AS 381 SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3; 382 SELECT * FROM v10; 383 } 384} {5 1 4 2 3 4} 385do_test view-9.6 { 386 execsql { 387 SELECT * FROM v10 ORDER BY 1; 388 } 389} {3 4 4 2 5 1} 390 391# Tables with columns having peculiar quoted names used in views 392# Ticket #756. 393# 394do_test view-10.1 { 395 execsql { 396 CREATE TABLE t3("9" integer, [4] text); 397 INSERT INTO t3 VALUES(1,2); 398 CREATE VIEW v_t3_a AS SELECT a.[9] FROM t3 AS a; 399 CREATE VIEW v_t3_b AS SELECT "4" FROM t3; 400 SELECT * FROM v_t3_a; 401 } 402} {1} 403do_test view-10.2 { 404 execsql { 405 SELECT * FROM v_t3_b; 406 } 407} {2} 408 409 410finish_test 411