2N/A#pragma ident "%Z%%M% %I% %E% SMI"
2N/A# The author disclaims copyright to this source code. In place of
2N/A# a legal notice, here is a blessing:
2N/A# May you do good and not evil.
2N/A# May you find forgiveness for yourself and forgive others.
2N/A# May you share freely, never taking more than you give.
2N/A#***********************************************************************
2N/A# This file implements regression tests for SQLite library. The
2N/A# focus of this file is testing VIEW statements.
2N/Aset testdir [file dirname $argv0]
2N/A CREATE TABLE t1(a,b,c);
2N/A INSERT INTO t1 VALUES(1,2,3);
2N/A INSERT INTO t1 VALUES(4,5,6);
2N/A INSERT INTO t1 VALUES(7,8,9);
2N/A} {1 2 3 4 5 6 7 8 9}
2N/A CREATE VIEW v1 AS SELECT a,b FROM t1;
2N/A SELECT * FROM v1 ORDER BY a;
2N/A SELECT * FROM v1 ORDER BY a;
2N/A} {1 {no such table: v1}}
2N/A CREATE VIEW v1 AS SELECT a,b FROM t1;
2N/A SELECT * FROM v1 ORDER BY a;
2N/A SELECT * FROM v1 ORDER BY a;
2N/A SELECT * FROM v1 ORDER BY a;
2N/A} {1 {no such table: v1}}
2N/A CREATE VIEW v1 AS SELECT a,b FROM t1;
2N/A SELECT * FROM v1 ORDER BY a;
2N/A SELECT * FROM v1 ORDER BY a;
2N/A CREATE TABLE t1(x,a,b,c);
2N/A INSERT INTO t1 VALUES(1,2,3,4);
2N/A INSERT INTO t1 VALUES(4,5,6,7);
2N/A INSERT INTO t1 VALUES(7,8,9,10);
2N/A SELECT * FROM v1 ORDER BY a;
2N/A SELECT * FROM v1 ORDER BY a;
2N/A CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5
2N/A INSERT INTO v2 VALUES(1,2,3,4);
2N/A} {1 {cannot modify v2 because it is a view}}
2N/A UPDATE v2 SET a=10 WHERE a=5;
2N/A} {1 {cannot modify v2 because it is a view}}
2N/A} {1 {cannot modify v2 because it is a view}}
2N/A INSERT INTO t1 VALUES(11,12,13,14);
2N/A SELECT * FROM v2 ORDER BY x;
2N/A} {7 8 9 10 11 12 13 14}
2N/A SELECT x FROM v2 WHERE a>10
2N/A# Test that column name of views are generated correctly.
2N/A SELECT * FROM v1 LIMIT 1
2N/A SELECT * FROM v2 LIMIT 1
2N/A CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1;
2N/A SELECT * FROM v1 LIMIT 1
2N/A} {xyz 2 pqr 7 c-b 1}
2N/A CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b;
2N/A SELECT * FROM v3 LIMIT 4;
2N/A SELECT b AS 'x', a AS 'y' FROM t1
2N/A SELECT y FROM v4 ORDER BY y LIMIT 4;
2N/A} {1 {use DROP TABLE to delete table t1}}
2N/A SELECT 1 FROM t1 LIMIT 1;
2N/A} {1 {use DROP VIEW to delete view v1}}
2N/A SELECT 1 FROM v1 LIMIT 1;
2N/A CREATE INDEX i1v1 ON v1(xyz);
2N/A} {1 {views may not be indexed}}
2N/A CREATE TABLE t2(y,a);
2N/A INSERT INTO t2 VALUES(22,2);
2N/A INSERT INTO t2 VALUES(33,3);
2N/A INSERT INTO t2 VALUES(44,4);
2N/A INSERT INTO t2 VALUES(55,5);
2N/A} {22 2 33 3 44 4 55 5}
2N/A# Verify that the view v5 gets flattened. see sqliteFlattenSubquery().
2N/A EXPLAIN SELECT * FROM v5;
2N/A SELECT * FROM v5 AS a, t2 AS b WHERE
a.w=
b.y;
2N/A} {1 22 22 2 4 55 55 5}
2N/A EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE
a.w=
b.y;
2N/A SELECT * FROM t2 AS b, v5 AS a WHERE
a.w=
b.y;
2N/A} {22 2 1 22 55 5 4 55}
2N/A EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE
a.w=
b.y;
2N/A} {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5}
2N/A SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2;
2N/A SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2;
2N/A CREATE TABLE test1(id integer primary key, a);
2N/A CREATE TABLE test2(id integer, b);
2N/A INSERT INTO test1 VALUES(1,2);
2N/A INSERT INTO test2 VALUES(1,3);
2N/A FROM test1 JOIN test2 USING(id);
2N/A FROM test1 NATURAL JOIN test2;
2N/A CREATE VIEW v6 AS SELECT pqr, xyz FROM v1;
2N/A SELECT * FROM v6 ORDER BY xyz;
2N/A} {7 2 13 5 19 8 27 12}
2N/A SELECT * FROM v6 ORDER BY xyz;
2N/A} {7 2 13 5 19 8 27 12}
2N/A CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6;
2N/A SELECT * FROM v7 ORDER BY a;
2N/A CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM
2N/A (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo);
2N/A SELECT mx+10, mx*2 FROM v8;
2N/A SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2;
2N/A SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2;
2N/A} {13 13 13 19 13 27}
2N/A# Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW.
2N/A INSERT INTO t2 SELECT * FROM t2 WHERE a<5;
2N/A INSERT INTO t2 SELECT * FROM t2 WHERE a<4;
2N/A INSERT INTO t2 SELECT * FROM t2 WHERE a<3;
2N/A SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1;
2N/A SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
2N/A SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
2N/A SELECT * FROM v9 ORDER BY 1 DESC;
2N/A SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3;
2N/A SELECT * FROM v10 ORDER BY 1;
2N/A# Tables with columns having peculiar quoted names used in views
2N/A CREATE TABLE t3("9" integer, [4] text);
2N/A INSERT INTO t3 VALUES(1,2);
2N/A CREATE VIEW v_t3_a AS SELECT a.[9] FROM t3 AS a;
2N/A CREATE VIEW v_t3_b AS SELECT "4" FROM t3;
2N/A SELECT * FROM v_t3_a;
2N/A SELECT * FROM v_t3_b;