1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/view.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,569 @@
1.4 +# 2002 February 26
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing VIEW statements.
1.16 +#
1.17 +# $Id: view.test,v 1.38 2008/08/21 14:54:29 drh Exp $
1.18 +set testdir [file dirname $argv0]
1.19 +source $testdir/tester.tcl
1.20 +
1.21 +# Omit this entire file if the library is not configured with views enabled.
1.22 +ifcapable !view {
1.23 + finish_test
1.24 + return
1.25 +}
1.26 +
1.27 +do_test view-1.0 {
1.28 + execsql {
1.29 + CREATE TABLE t1(a,b,c);
1.30 + INSERT INTO t1 VALUES(1,2,3);
1.31 + INSERT INTO t1 VALUES(4,5,6);
1.32 + INSERT INTO t1 VALUES(7,8,9);
1.33 + SELECT * FROM t1;
1.34 + }
1.35 +} {1 2 3 4 5 6 7 8 9}
1.36 +
1.37 +do_test view-1.1 {
1.38 + execsql {
1.39 + BEGIN;
1.40 + CREATE VIEW IF NOT EXISTS v1 AS SELECT a,b FROM t1;
1.41 + SELECT * FROM v1 ORDER BY a;
1.42 + }
1.43 +} {1 2 4 5 7 8}
1.44 +do_test view-1.2 {
1.45 + catchsql {
1.46 + ROLLBACK;
1.47 + SELECT * FROM v1 ORDER BY a;
1.48 + }
1.49 +} {1 {no such table: v1}}
1.50 +do_test view-1.3 {
1.51 + execsql {
1.52 + CREATE VIEW v1 AS SELECT a,b FROM t1;
1.53 + SELECT * FROM v1 ORDER BY a;
1.54 + }
1.55 +} {1 2 4 5 7 8}
1.56 +do_test view-1.3.1 {
1.57 + db close
1.58 + sqlite3 db test.db
1.59 + execsql {
1.60 + SELECT * FROM v1 ORDER BY a;
1.61 + }
1.62 +} {1 2 4 5 7 8}
1.63 +do_test view-1.4 {
1.64 + catchsql {
1.65 + DROP VIEW IF EXISTS v1;
1.66 + SELECT * FROM v1 ORDER BY a;
1.67 + }
1.68 +} {1 {no such table: v1}}
1.69 +do_test view-1.5 {
1.70 + execsql {
1.71 + CREATE VIEW v1 AS SELECT a,b FROM t1;
1.72 + SELECT * FROM v1 ORDER BY a;
1.73 + }
1.74 +} {1 2 4 5 7 8}
1.75 +do_test view-1.6 {
1.76 + catchsql {
1.77 + DROP TABLE t1;
1.78 + SELECT * FROM v1 ORDER BY a;
1.79 + }
1.80 +} {1 {no such table: main.t1}}
1.81 +do_test view-1.7 {
1.82 + execsql {
1.83 + CREATE TABLE t1(x,a,b,c);
1.84 + INSERT INTO t1 VALUES(1,2,3,4);
1.85 + INSERT INTO t1 VALUES(4,5,6,7);
1.86 + INSERT INTO t1 VALUES(7,8,9,10);
1.87 + SELECT * FROM v1 ORDER BY a;
1.88 + }
1.89 +} {2 3 5 6 8 9}
1.90 +do_test view-1.8 {
1.91 + db close
1.92 + sqlite3 db test.db
1.93 + execsql {
1.94 + SELECT * FROM v1 ORDER BY a;
1.95 + }
1.96 +} {2 3 5 6 8 9}
1.97 +
1.98 +do_test view-2.1 {
1.99 + execsql {
1.100 + CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5
1.101 + }; # No semicolon
1.102 + execsql2 {
1.103 + SELECT * FROM v2;
1.104 + }
1.105 +} {x 7 a 8 b 9 c 10}
1.106 +do_test view-2.2 {
1.107 + catchsql {
1.108 + INSERT INTO v2 VALUES(1,2,3,4);
1.109 + }
1.110 +} {1 {cannot modify v2 because it is a view}}
1.111 +do_test view-2.3 {
1.112 + catchsql {
1.113 + UPDATE v2 SET a=10 WHERE a=5;
1.114 + }
1.115 +} {1 {cannot modify v2 because it is a view}}
1.116 +do_test view-2.4 {
1.117 + catchsql {
1.118 + DELETE FROM v2;
1.119 + }
1.120 +} {1 {cannot modify v2 because it is a view}}
1.121 +do_test view-2.5 {
1.122 + execsql {
1.123 + INSERT INTO t1 VALUES(11,12,13,14);
1.124 + SELECT * FROM v2 ORDER BY x;
1.125 + }
1.126 +} {7 8 9 10 11 12 13 14}
1.127 +do_test view-2.6 {
1.128 + execsql {
1.129 + SELECT x FROM v2 WHERE a>10
1.130 + }
1.131 +} {11}
1.132 +
1.133 +# Test that column name of views are generated correctly.
1.134 +#
1.135 +do_test view-3.1 {
1.136 + execsql2 {
1.137 + SELECT * FROM v1 LIMIT 1
1.138 + }
1.139 +} {a 2 b 3}
1.140 +do_test view-3.2 {
1.141 + execsql2 {
1.142 + SELECT * FROM v2 LIMIT 1
1.143 + }
1.144 +} {x 7 a 8 b 9 c 10}
1.145 +do_test view-3.3.1 {
1.146 + execsql2 {
1.147 + DROP VIEW v1;
1.148 + CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1;
1.149 + SELECT * FROM v1 LIMIT 1
1.150 + }
1.151 +} {xyz 2 pqr 7 c-b 1}
1.152 +do_test view-3.3.2 {
1.153 + execsql2 {
1.154 + CREATE VIEW v1b AS SELECT t1.a, b+c, t1.c FROM t1;
1.155 + SELECT * FROM v1b LIMIT 1
1.156 + }
1.157 +} {a 2 b+c 7 c 4}
1.158 +
1.159 +ifcapable compound {
1.160 +do_test view-3.4 {
1.161 + execsql2 {
1.162 + CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b;
1.163 + SELECT * FROM v3 LIMIT 4;
1.164 + }
1.165 +} {a 2 a 3 a 5 a 6}
1.166 +do_test view-3.5 {
1.167 + execsql2 {
1.168 + CREATE VIEW v4 AS
1.169 + SELECT a, b FROM t1
1.170 + UNION
1.171 + SELECT b AS 'x', a AS 'y' FROM t1
1.172 + ORDER BY x, y;
1.173 + SELECT b FROM v4 ORDER BY b LIMIT 4;
1.174 + }
1.175 +} {b 2 b 3 b 5 b 6}
1.176 +} ;# ifcapable compound
1.177 +
1.178 +
1.179 +do_test view-4.1 {
1.180 + catchsql {
1.181 + DROP VIEW t1;
1.182 + }
1.183 +} {1 {use DROP TABLE to delete table t1}}
1.184 +do_test view-4.2 {
1.185 + execsql {
1.186 + SELECT 1 FROM t1 LIMIT 1;
1.187 + }
1.188 +} 1
1.189 +do_test view-4.3 {
1.190 + catchsql {
1.191 + DROP TABLE v1;
1.192 + }
1.193 +} {1 {use DROP VIEW to delete view v1}}
1.194 +do_test view-4.4 {
1.195 + execsql {
1.196 + SELECT 1 FROM v1 LIMIT 1;
1.197 + }
1.198 +} {1}
1.199 +do_test view-4.5 {
1.200 + catchsql {
1.201 + CREATE INDEX i1v1 ON v1(xyz);
1.202 + }
1.203 +} {1 {views may not be indexed}}
1.204 +
1.205 +do_test view-5.1 {
1.206 + execsql {
1.207 + CREATE TABLE t2(y,a);
1.208 + INSERT INTO t2 VALUES(22,2);
1.209 + INSERT INTO t2 VALUES(33,3);
1.210 + INSERT INTO t2 VALUES(44,4);
1.211 + INSERT INTO t2 VALUES(55,5);
1.212 + SELECT * FROM t2;
1.213 + }
1.214 +} {22 2 33 3 44 4 55 5}
1.215 +do_test view-5.2 {
1.216 + execsql {
1.217 + CREATE VIEW v5 AS
1.218 + SELECT t1.x AS v, t2.y AS w FROM t1 JOIN t2 USING(a);
1.219 + SELECT * FROM v5;
1.220 + }
1.221 +} {1 22 4 55}
1.222 +
1.223 +# Verify that the view v5 gets flattened. see sqliteFlattenSubquery().
1.224 +# This will only work if EXPLAIN is enabled.
1.225 +# Ticket #272
1.226 +#
1.227 +ifcapable {explain} {
1.228 +do_test view-5.3 {
1.229 + lsearch [execsql {
1.230 + EXPLAIN SELECT * FROM v5;
1.231 + }] OpenEphemeral
1.232 +} {-1}
1.233 +do_test view-5.4 {
1.234 + execsql {
1.235 + SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
1.236 + }
1.237 +} {1 22 22 2 4 55 55 5}
1.238 +do_test view-5.5 {
1.239 + lsearch [execsql {
1.240 + EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
1.241 + }] OpenEphemeral
1.242 +} {-1}
1.243 +do_test view-5.6 {
1.244 + execsql {
1.245 + SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
1.246 + }
1.247 +} {22 2 1 22 55 5 4 55}
1.248 +do_test view-5.7 {
1.249 + lsearch [execsql {
1.250 + EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
1.251 + }] OpenEphemeral
1.252 +} {-1}
1.253 +do_test view-5.8 {
1.254 + execsql {
1.255 + SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
1.256 + }
1.257 +} {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5}
1.258 +do_test view-5.9 {
1.259 + lsearch [execsql {
1.260 + EXPLAIN SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
1.261 + }] OpenEphemeral
1.262 +} {-1}
1.263 +} ;# endif explain
1.264 +
1.265 +do_test view-6.1 {
1.266 + execsql {
1.267 + SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2;
1.268 + }
1.269 +} {7 8 9 10 27}
1.270 +do_test view-6.2 {
1.271 + execsql {
1.272 + SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2;
1.273 + }
1.274 +} {11 12 13 14 39}
1.275 +
1.276 +do_test view-7.1 {
1.277 + execsql {
1.278 + CREATE TABLE test1(id integer primary key, a);
1.279 + CREATE TABLE test2(id integer, b);
1.280 + INSERT INTO test1 VALUES(1,2);
1.281 + INSERT INTO test2 VALUES(1,3);
1.282 + CREATE VIEW test AS
1.283 + SELECT test1.id, a, b
1.284 + FROM test1 JOIN test2 ON test2.id=test1.id;
1.285 + SELECT * FROM test;
1.286 + }
1.287 +} {1 2 3}
1.288 +do_test view-7.2 {
1.289 + db close
1.290 + sqlite3 db test.db
1.291 + execsql {
1.292 + SELECT * FROM test;
1.293 + }
1.294 +} {1 2 3}
1.295 +do_test view-7.3 {
1.296 + execsql {
1.297 + DROP VIEW test;
1.298 + CREATE VIEW test AS
1.299 + SELECT test1.id, a, b
1.300 + FROM test1 JOIN test2 USING(id);
1.301 + SELECT * FROM test;
1.302 + }
1.303 +} {1 2 3}
1.304 +do_test view-7.4 {
1.305 + db close
1.306 + sqlite3 db test.db
1.307 + execsql {
1.308 + SELECT * FROM test;
1.309 + }
1.310 +} {1 2 3}
1.311 +do_test view-7.5 {
1.312 + execsql {
1.313 + DROP VIEW test;
1.314 + CREATE VIEW test AS
1.315 + SELECT test1.id, a, b
1.316 + FROM test1 NATURAL JOIN test2;
1.317 + SELECT * FROM test;
1.318 + }
1.319 +} {1 2 3}
1.320 +do_test view-7.6 {
1.321 + db close
1.322 + sqlite3 db test.db
1.323 + execsql {
1.324 + SELECT * FROM test;
1.325 + }
1.326 +} {1 2 3}
1.327 +
1.328 +do_test view-8.1 {
1.329 + execsql {
1.330 + CREATE VIEW v6 AS SELECT pqr, xyz FROM v1;
1.331 + SELECT * FROM v6 ORDER BY xyz;
1.332 + }
1.333 +} {7 2 13 5 19 8 27 12}
1.334 +do_test view-8.2 {
1.335 + db close
1.336 + sqlite3 db test.db
1.337 + execsql {
1.338 + SELECT * FROM v6 ORDER BY xyz;
1.339 + }
1.340 +} {7 2 13 5 19 8 27 12}
1.341 +do_test view-8.3 {
1.342 + execsql {
1.343 + CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6;
1.344 + SELECT * FROM v7 ORDER BY a;
1.345 + }
1.346 +} {9 18 27 39}
1.347 +
1.348 +ifcapable subquery {
1.349 + do_test view-8.4 {
1.350 + execsql {
1.351 + CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM
1.352 + (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo);
1.353 + SELECT * FROM v8;
1.354 + }
1.355 + } 3
1.356 + do_test view-8.5 {
1.357 + execsql {
1.358 + SELECT mx+10, mx*2 FROM v8;
1.359 + }
1.360 + } {13 6}
1.361 + do_test view-8.6 {
1.362 + execsql {
1.363 + SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2;
1.364 + }
1.365 + } {13 7}
1.366 + do_test view-8.7 {
1.367 + execsql {
1.368 + SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2;
1.369 + }
1.370 + } {13 13 13 19 13 27}
1.371 +} ;# ifcapable subquery
1.372 +
1.373 +# Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW.
1.374 +#
1.375 +do_test view-9.1 {
1.376 + execsql {
1.377 + INSERT INTO t2 SELECT * FROM t2 WHERE a<5;
1.378 + INSERT INTO t2 SELECT * FROM t2 WHERE a<4;
1.379 + INSERT INTO t2 SELECT * FROM t2 WHERE a<3;
1.380 + SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1;
1.381 + }
1.382 +} {1 2 4 8}
1.383 +do_test view-9.2 {
1.384 + execsql {
1.385 + SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
1.386 + }
1.387 +} {1 2 4}
1.388 +do_test view-9.3 {
1.389 + execsql {
1.390 + CREATE VIEW v9 AS
1.391 + SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
1.392 + SELECT * FROM v9;
1.393 + }
1.394 +} {1 2 4}
1.395 +do_test view-9.4 {
1.396 + execsql {
1.397 + SELECT * FROM v9 ORDER BY 1 DESC;
1.398 + }
1.399 +} {4 2 1}
1.400 +do_test view-9.5 {
1.401 + execsql {
1.402 + CREATE VIEW v10 AS
1.403 + SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3;
1.404 + SELECT * FROM v10;
1.405 + }
1.406 +} {5 1 4 2 3 4}
1.407 +do_test view-9.6 {
1.408 + execsql {
1.409 + SELECT * FROM v10 ORDER BY 1;
1.410 + }
1.411 +} {3 4 4 2 5 1}
1.412 +
1.413 +# Tables with columns having peculiar quoted names used in views
1.414 +# Ticket #756.
1.415 +#
1.416 +do_test view-10.1 {
1.417 + execsql {
1.418 + CREATE TABLE t3("9" integer, [4] text);
1.419 + INSERT INTO t3 VALUES(1,2);
1.420 + CREATE VIEW v_t3_a AS SELECT a.[9] FROM t3 AS a;
1.421 + CREATE VIEW v_t3_b AS SELECT "4" FROM t3;
1.422 + SELECT * FROM v_t3_a;
1.423 + }
1.424 +} {1}
1.425 +do_test view-10.2 {
1.426 + execsql {
1.427 + SELECT * FROM v_t3_b;
1.428 + }
1.429 +} {2}
1.430 +
1.431 +do_test view-11.1 {
1.432 + execsql {
1.433 + CREATE TABLE t4(a COLLATE NOCASE);
1.434 + INSERT INTO t4 VALUES('This');
1.435 + INSERT INTO t4 VALUES('this');
1.436 + INSERT INTO t4 VALUES('THIS');
1.437 + SELECT * FROM t4 WHERE a = 'THIS';
1.438 + }
1.439 +} {This this THIS}
1.440 +ifcapable subquery {
1.441 + do_test view-11.2 {
1.442 + execsql {
1.443 + SELECT * FROM (SELECT * FROM t4) WHERE a = 'THIS';
1.444 + }
1.445 + } {This this THIS}
1.446 +}
1.447 +do_test view-11.3 {
1.448 + execsql {
1.449 + CREATE VIEW v11 AS SELECT * FROM t4;
1.450 + SELECT * FROM v11 WHERE a = 'THIS';
1.451 + }
1.452 +} {This this THIS}
1.453 +
1.454 +# Ticket #1270: Do not allow parameters in view definitions.
1.455 +#
1.456 +do_test view-12.1 {
1.457 + catchsql {
1.458 + CREATE VIEW v12 AS SELECT a FROM t1 WHERE b=?
1.459 + }
1.460 +} {1 {parameters are not allowed in views}}
1.461 +
1.462 +ifcapable attach {
1.463 + do_test view-13.1 {
1.464 + file delete -force test2.db
1.465 + catchsql {
1.466 + ATTACH 'test2.db' AS two;
1.467 + CREATE TABLE two.t2(x,y);
1.468 + CREATE VIEW v13 AS SELECT y FROM two.t2;
1.469 + }
1.470 + } {1 {view v13 cannot reference objects in database two}}
1.471 +}
1.472 +
1.473 +# Ticket #1658
1.474 +#
1.475 +do_test view-14.1 {
1.476 + catchsql {
1.477 + CREATE TEMP VIEW t1 AS SELECT a,b FROM t1;
1.478 + SELECT * FROM temp.t1;
1.479 + }
1.480 +} {1 {view t1 is circularly defined}}
1.481 +
1.482 +# Tickets #1688, #1709
1.483 +#
1.484 +do_test view-15.1 {
1.485 + execsql2 {
1.486 + CREATE VIEW v15 AS SELECT a AS x, b AS y FROM t1;
1.487 + SELECT * FROM v15 LIMIT 1;
1.488 + }
1.489 +} {x 2 y 3}
1.490 +do_test view-15.2 {
1.491 + execsql2 {
1.492 + SELECT x, y FROM v15 LIMIT 1
1.493 + }
1.494 +} {x 2 y 3}
1.495 +
1.496 +do_test view-16.1 {
1.497 + catchsql {
1.498 + CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1;
1.499 + }
1.500 +} {0 {}}
1.501 +do_test view-16.2 {
1.502 + execsql {
1.503 + SELECT sql FROM sqlite_master WHERE name='v1'
1.504 + }
1.505 +} {{CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1}}
1.506 +do_test view-16.3 {
1.507 + catchsql {
1.508 + DROP VIEW IF EXISTS nosuchview
1.509 + }
1.510 +} {0 {}}
1.511 +
1.512 +# correct error message when attempting to drop a view that does not
1.513 +# exist.
1.514 +#
1.515 +do_test view-17.1 {
1.516 + catchsql {
1.517 + DROP VIEW nosuchview
1.518 + }
1.519 +} {1 {no such view: nosuchview}}
1.520 +do_test view-17.2 {
1.521 + catchsql {
1.522 + DROP VIEW main.nosuchview
1.523 + }
1.524 +} {1 {no such view: main.nosuchview}}
1.525 +
1.526 +do_test view-18.1 {
1.527 + execsql {
1.528 + DROP VIEW t1;
1.529 + DROP TABLE t1;
1.530 + CREATE TABLE t1(a, b, c);
1.531 + INSERT INTO t1 VALUES(1, 2, 3);
1.532 + INSERT INTO t1 VALUES(4, 5, 6);
1.533 +
1.534 + CREATE VIEW vv1 AS SELECT * FROM t1;
1.535 + CREATE VIEW vv2 AS SELECT * FROM vv1;
1.536 + CREATE VIEW vv3 AS SELECT * FROM vv2;
1.537 + CREATE VIEW vv4 AS SELECT * FROM vv3;
1.538 + CREATE VIEW vv5 AS SELECT * FROM vv4;
1.539 +
1.540 + SELECT * FROM vv5;
1.541 + }
1.542 +} {1 2 3 4 5 6}
1.543 +
1.544 +# Ticket #3308
1.545 +# Make sure "rowid" columns in a view are named correctly.
1.546 +#
1.547 +do_test view-19.1 {
1.548 + execsql {
1.549 + CREATE VIEW v3308a AS SELECT rowid, * FROM t1;
1.550 + }
1.551 + execsql2 {
1.552 + SELECT * FROM v3308a
1.553 + }
1.554 +} {rowid 1 a 1 b 2 c 3 rowid 2 a 4 b 5 c 6}
1.555 +do_test view-19.2 {
1.556 + execsql {
1.557 + CREATE VIEW v3308b AS SELECT t1.rowid, t1.a, t1.b+t1.c FROM t1;
1.558 + }
1.559 + execsql2 {
1.560 + SELECT * FROM v3308b
1.561 + }
1.562 +} {rowid 1 a 1 t1.b+t1.c 5 rowid 2 a 4 t1.b+t1.c 11}
1.563 +do_test view-19.3 {
1.564 + execsql {
1.565 + CREATE VIEW v3308c AS SELECT t1.oid, A, t1.b+t1.c AS x FROM t1;
1.566 + }
1.567 + execsql2 {
1.568 + SELECT * FROM v3308c
1.569 + }
1.570 +} {rowid 1 a 1 x 5 rowid 2 a 4 x 11}
1.571 +
1.572 +finish_test