3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing VIEW statements.
14 # $Id: view.test,v 1.25 2005/06/06 15:32:08 drh Exp $
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
18 # Omit this entire file if the library is not configured with views enabled.
26 CREATE TABLE t1(a,b,c);
27 INSERT INTO t1 VALUES(1,2,3);
28 INSERT INTO t1 VALUES(4,5,6);
29 INSERT INTO t1 VALUES(7,8,9);
37 CREATE VIEW v1 AS SELECT a,b FROM t1;
38 SELECT * FROM v1 ORDER BY a;
44 SELECT * FROM v1 ORDER BY a;
46 } {1 {no such table: v1}}
49 CREATE VIEW v1 AS SELECT a,b FROM t1;
50 SELECT * FROM v1 ORDER BY a;
57 SELECT * FROM v1 ORDER BY a;
63 SELECT * FROM v1 ORDER BY a;
65 } {1 {no such table: v1}}
68 CREATE VIEW v1 AS SELECT a,b FROM t1;
69 SELECT * FROM v1 ORDER BY a;
75 SELECT * FROM v1 ORDER BY a;
77 } {1 {no such table: main.t1}}
80 CREATE TABLE t1(x,a,b,c);
81 INSERT INTO t1 VALUES(1,2,3,4);
82 INSERT INTO t1 VALUES(4,5,6,7);
83 INSERT INTO t1 VALUES(7,8,9,10);
84 SELECT * FROM v1 ORDER BY a;
91 SELECT * FROM v1 ORDER BY a;
97 CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5
105 INSERT INTO v2 VALUES(1,2,3,4);
107 } {1 {cannot modify v2 because it is a view}}
110 UPDATE v2 SET a=10 WHERE a=5;
112 } {1 {cannot modify v2 because it is a view}}
117 } {1 {cannot modify v2 because it is a view}}
120 INSERT INTO t1 VALUES(11,12,13,14);
121 SELECT * FROM v2 ORDER BY x;
123 } {7 8 9 10 11 12 13 14}
126 SELECT x FROM v2 WHERE a>10
130 # Test that column name of views are generated correctly.
134 SELECT * FROM v1 LIMIT 1
139 SELECT * FROM v2 LIMIT 1
145 CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1;
146 SELECT * FROM v1 LIMIT 1
148 } {xyz 2 pqr 7 c-b 1}
153 CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b;
154 SELECT * FROM v3 LIMIT 4;
162 SELECT b AS 'x', a AS 'y' FROM t1
164 SELECT y FROM v4 ORDER BY y LIMIT 4;
167 } ;# ifcapable compound
174 } {1 {use DROP TABLE to delete table t1}}
177 SELECT 1 FROM t1 LIMIT 1;
184 } {1 {use DROP VIEW to delete view v1}}
187 SELECT 1 FROM v1 LIMIT 1;
192 CREATE INDEX i1v1 ON v1(xyz);
194 } {1 {views may not be indexed}}
198 CREATE TABLE t2(y,a);
199 INSERT INTO t2 VALUES(22,2);
200 INSERT INTO t2 VALUES(33,3);
201 INSERT INTO t2 VALUES(44,4);
202 INSERT INTO t2 VALUES(55,5);
205 } {22 2 33 3 44 4 55 5}
209 SELECT t1.x AS v, t2.y AS w FROM t1 JOIN t2 USING(a);
214 # Verify that the view v5 gets flattened. see sqliteFlattenSubquery().
215 # This will only work if EXPLAIN is enabled.
218 ifcapable {explain} {
221 EXPLAIN SELECT * FROM v5;
226 SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
228 } {1 22 22 2 4 55 55 5}
231 EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
236 SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
238 } {22 2 1 22 55 5 4 55}
241 EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
246 SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
248 } {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5}
251 EXPLAIN SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
258 SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2;
263 SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2;
269 CREATE TABLE test1(id integer primary key, a);
270 CREATE TABLE test2(id integer, b);
271 INSERT INTO test1 VALUES(1,2);
272 INSERT INTO test2 VALUES(1,3);
274 SELECT test1.id, a, b
275 FROM test1 JOIN test2 ON test2.id=test1.id;
290 SELECT test1.id, a, b
291 FROM test1 JOIN test2 USING(id);
306 SELECT test1.id, a, b
307 FROM test1 NATURAL JOIN test2;
321 CREATE VIEW v6 AS SELECT pqr, xyz FROM v1;
322 SELECT * FROM v6 ORDER BY xyz;
324 } {7 2 13 5 19 8 27 12}
329 SELECT * FROM v6 ORDER BY xyz;
331 } {7 2 13 5 19 8 27 12}
334 CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6;
335 SELECT * FROM v7 ORDER BY a;
342 CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM
343 (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo);
349 SELECT mx+10, mx*2 FROM v8;
354 SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2;
359 SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2;
361 } {13 13 13 19 13 27}
362 } ;# ifcapable subquery
364 # Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW.
368 INSERT INTO t2 SELECT * FROM t2 WHERE a<5;
369 INSERT INTO t2 SELECT * FROM t2 WHERE a<4;
370 INSERT INTO t2 SELECT * FROM t2 WHERE a<3;
371 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1;
376 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
382 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
388 SELECT * FROM v9 ORDER BY 1 DESC;
394 SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3;
400 SELECT * FROM v10 ORDER BY 1;
404 # Tables with columns having peculiar quoted names used in views
409 CREATE TABLE t3("9" integer, [4] text);
410 INSERT INTO t3 VALUES(1,2);
411 CREATE VIEW v_t3_a AS SELECT a.[9] FROM t3 AS a;
412 CREATE VIEW v_t3_b AS SELECT "4" FROM t3;
413 SELECT * FROM v_t3_a;
418 SELECT * FROM v_t3_b;
424 CREATE TABLE t4(a COLLATE NOCASE);
425 INSERT INTO t4 VALUES('This');
426 INSERT INTO t4 VALUES('this');
427 INSERT INTO t4 VALUES('THIS');
428 SELECT * FROM t4 WHERE a = 'THIS';
433 SELECT * FROM (SELECT * FROM t4) WHERE a = 'THIS';
438 CREATE VIEW v11 AS SELECT * FROM t4;
439 SELECT * FROM v11 WHERE a = 'THIS';
443 # Ticket #1270: Do not allow parameters in view definitions.
447 CREATE VIEW v12 AS SELECT a FROM t1 WHERE b=?
449 } {1 {parameters are not allowed in views}}