|
1 # 2002 February 26 |
|
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. The |
|
12 # focus of this file is testing VIEW statements. |
|
13 # |
|
14 # $Id: view.test,v 1.38 2008/08/21 14:54:29 drh Exp $ |
|
15 set testdir [file dirname $argv0] |
|
16 source $testdir/tester.tcl |
|
17 |
|
18 # Omit this entire file if the library is not configured with views enabled. |
|
19 ifcapable !view { |
|
20 finish_test |
|
21 return |
|
22 } |
|
23 |
|
24 do_test view-1.0 { |
|
25 execsql { |
|
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); |
|
30 SELECT * FROM t1; |
|
31 } |
|
32 } {1 2 3 4 5 6 7 8 9} |
|
33 |
|
34 do_test view-1.1 { |
|
35 execsql { |
|
36 BEGIN; |
|
37 CREATE VIEW IF NOT EXISTS v1 AS SELECT a,b FROM t1; |
|
38 SELECT * FROM v1 ORDER BY a; |
|
39 } |
|
40 } {1 2 4 5 7 8} |
|
41 do_test view-1.2 { |
|
42 catchsql { |
|
43 ROLLBACK; |
|
44 SELECT * FROM v1 ORDER BY a; |
|
45 } |
|
46 } {1 {no such table: v1}} |
|
47 do_test view-1.3 { |
|
48 execsql { |
|
49 CREATE VIEW v1 AS SELECT a,b FROM t1; |
|
50 SELECT * FROM v1 ORDER BY a; |
|
51 } |
|
52 } {1 2 4 5 7 8} |
|
53 do_test view-1.3.1 { |
|
54 db close |
|
55 sqlite3 db test.db |
|
56 execsql { |
|
57 SELECT * FROM v1 ORDER BY a; |
|
58 } |
|
59 } {1 2 4 5 7 8} |
|
60 do_test view-1.4 { |
|
61 catchsql { |
|
62 DROP VIEW IF EXISTS v1; |
|
63 SELECT * FROM v1 ORDER BY a; |
|
64 } |
|
65 } {1 {no such table: v1}} |
|
66 do_test view-1.5 { |
|
67 execsql { |
|
68 CREATE VIEW v1 AS SELECT a,b FROM t1; |
|
69 SELECT * FROM v1 ORDER BY a; |
|
70 } |
|
71 } {1 2 4 5 7 8} |
|
72 do_test view-1.6 { |
|
73 catchsql { |
|
74 DROP TABLE t1; |
|
75 SELECT * FROM v1 ORDER BY a; |
|
76 } |
|
77 } {1 {no such table: main.t1}} |
|
78 do_test view-1.7 { |
|
79 execsql { |
|
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; |
|
85 } |
|
86 } {2 3 5 6 8 9} |
|
87 do_test view-1.8 { |
|
88 db close |
|
89 sqlite3 db test.db |
|
90 execsql { |
|
91 SELECT * FROM v1 ORDER BY a; |
|
92 } |
|
93 } {2 3 5 6 8 9} |
|
94 |
|
95 do_test view-2.1 { |
|
96 execsql { |
|
97 CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5 |
|
98 }; # No semicolon |
|
99 execsql2 { |
|
100 SELECT * FROM v2; |
|
101 } |
|
102 } {x 7 a 8 b 9 c 10} |
|
103 do_test view-2.2 { |
|
104 catchsql { |
|
105 INSERT INTO v2 VALUES(1,2,3,4); |
|
106 } |
|
107 } {1 {cannot modify v2 because it is a view}} |
|
108 do_test view-2.3 { |
|
109 catchsql { |
|
110 UPDATE v2 SET a=10 WHERE a=5; |
|
111 } |
|
112 } {1 {cannot modify v2 because it is a view}} |
|
113 do_test view-2.4 { |
|
114 catchsql { |
|
115 DELETE FROM v2; |
|
116 } |
|
117 } {1 {cannot modify v2 because it is a view}} |
|
118 do_test view-2.5 { |
|
119 execsql { |
|
120 INSERT INTO t1 VALUES(11,12,13,14); |
|
121 SELECT * FROM v2 ORDER BY x; |
|
122 } |
|
123 } {7 8 9 10 11 12 13 14} |
|
124 do_test view-2.6 { |
|
125 execsql { |
|
126 SELECT x FROM v2 WHERE a>10 |
|
127 } |
|
128 } {11} |
|
129 |
|
130 # Test that column name of views are generated correctly. |
|
131 # |
|
132 do_test view-3.1 { |
|
133 execsql2 { |
|
134 SELECT * FROM v1 LIMIT 1 |
|
135 } |
|
136 } {a 2 b 3} |
|
137 do_test view-3.2 { |
|
138 execsql2 { |
|
139 SELECT * FROM v2 LIMIT 1 |
|
140 } |
|
141 } {x 7 a 8 b 9 c 10} |
|
142 do_test view-3.3.1 { |
|
143 execsql2 { |
|
144 DROP VIEW v1; |
|
145 CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1; |
|
146 SELECT * FROM v1 LIMIT 1 |
|
147 } |
|
148 } {xyz 2 pqr 7 c-b 1} |
|
149 do_test view-3.3.2 { |
|
150 execsql2 { |
|
151 CREATE VIEW v1b AS SELECT t1.a, b+c, t1.c FROM t1; |
|
152 SELECT * FROM v1b LIMIT 1 |
|
153 } |
|
154 } {a 2 b+c 7 c 4} |
|
155 |
|
156 ifcapable compound { |
|
157 do_test view-3.4 { |
|
158 execsql2 { |
|
159 CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b; |
|
160 SELECT * FROM v3 LIMIT 4; |
|
161 } |
|
162 } {a 2 a 3 a 5 a 6} |
|
163 do_test view-3.5 { |
|
164 execsql2 { |
|
165 CREATE VIEW v4 AS |
|
166 SELECT a, b FROM t1 |
|
167 UNION |
|
168 SELECT b AS 'x', a AS 'y' FROM t1 |
|
169 ORDER BY x, y; |
|
170 SELECT b FROM v4 ORDER BY b LIMIT 4; |
|
171 } |
|
172 } {b 2 b 3 b 5 b 6} |
|
173 } ;# ifcapable compound |
|
174 |
|
175 |
|
176 do_test view-4.1 { |
|
177 catchsql { |
|
178 DROP VIEW t1; |
|
179 } |
|
180 } {1 {use DROP TABLE to delete table t1}} |
|
181 do_test view-4.2 { |
|
182 execsql { |
|
183 SELECT 1 FROM t1 LIMIT 1; |
|
184 } |
|
185 } 1 |
|
186 do_test view-4.3 { |
|
187 catchsql { |
|
188 DROP TABLE v1; |
|
189 } |
|
190 } {1 {use DROP VIEW to delete view v1}} |
|
191 do_test view-4.4 { |
|
192 execsql { |
|
193 SELECT 1 FROM v1 LIMIT 1; |
|
194 } |
|
195 } {1} |
|
196 do_test view-4.5 { |
|
197 catchsql { |
|
198 CREATE INDEX i1v1 ON v1(xyz); |
|
199 } |
|
200 } {1 {views may not be indexed}} |
|
201 |
|
202 do_test view-5.1 { |
|
203 execsql { |
|
204 CREATE TABLE t2(y,a); |
|
205 INSERT INTO t2 VALUES(22,2); |
|
206 INSERT INTO t2 VALUES(33,3); |
|
207 INSERT INTO t2 VALUES(44,4); |
|
208 INSERT INTO t2 VALUES(55,5); |
|
209 SELECT * FROM t2; |
|
210 } |
|
211 } {22 2 33 3 44 4 55 5} |
|
212 do_test view-5.2 { |
|
213 execsql { |
|
214 CREATE VIEW v5 AS |
|
215 SELECT t1.x AS v, t2.y AS w FROM t1 JOIN t2 USING(a); |
|
216 SELECT * FROM v5; |
|
217 } |
|
218 } {1 22 4 55} |
|
219 |
|
220 # Verify that the view v5 gets flattened. see sqliteFlattenSubquery(). |
|
221 # This will only work if EXPLAIN is enabled. |
|
222 # Ticket #272 |
|
223 # |
|
224 ifcapable {explain} { |
|
225 do_test view-5.3 { |
|
226 lsearch [execsql { |
|
227 EXPLAIN SELECT * FROM v5; |
|
228 }] OpenEphemeral |
|
229 } {-1} |
|
230 do_test view-5.4 { |
|
231 execsql { |
|
232 SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y; |
|
233 } |
|
234 } {1 22 22 2 4 55 55 5} |
|
235 do_test view-5.5 { |
|
236 lsearch [execsql { |
|
237 EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y; |
|
238 }] OpenEphemeral |
|
239 } {-1} |
|
240 do_test view-5.6 { |
|
241 execsql { |
|
242 SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y; |
|
243 } |
|
244 } {22 2 1 22 55 5 4 55} |
|
245 do_test view-5.7 { |
|
246 lsearch [execsql { |
|
247 EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y; |
|
248 }] OpenEphemeral |
|
249 } {-1} |
|
250 do_test view-5.8 { |
|
251 execsql { |
|
252 SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y; |
|
253 } |
|
254 } {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5} |
|
255 do_test view-5.9 { |
|
256 lsearch [execsql { |
|
257 EXPLAIN SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y; |
|
258 }] OpenEphemeral |
|
259 } {-1} |
|
260 } ;# endif explain |
|
261 |
|
262 do_test view-6.1 { |
|
263 execsql { |
|
264 SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2; |
|
265 } |
|
266 } {7 8 9 10 27} |
|
267 do_test view-6.2 { |
|
268 execsql { |
|
269 SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2; |
|
270 } |
|
271 } {11 12 13 14 39} |
|
272 |
|
273 do_test view-7.1 { |
|
274 execsql { |
|
275 CREATE TABLE test1(id integer primary key, a); |
|
276 CREATE TABLE test2(id integer, b); |
|
277 INSERT INTO test1 VALUES(1,2); |
|
278 INSERT INTO test2 VALUES(1,3); |
|
279 CREATE VIEW test AS |
|
280 SELECT test1.id, a, b |
|
281 FROM test1 JOIN test2 ON test2.id=test1.id; |
|
282 SELECT * FROM test; |
|
283 } |
|
284 } {1 2 3} |
|
285 do_test view-7.2 { |
|
286 db close |
|
287 sqlite3 db test.db |
|
288 execsql { |
|
289 SELECT * FROM test; |
|
290 } |
|
291 } {1 2 3} |
|
292 do_test view-7.3 { |
|
293 execsql { |
|
294 DROP VIEW test; |
|
295 CREATE VIEW test AS |
|
296 SELECT test1.id, a, b |
|
297 FROM test1 JOIN test2 USING(id); |
|
298 SELECT * FROM test; |
|
299 } |
|
300 } {1 2 3} |
|
301 do_test view-7.4 { |
|
302 db close |
|
303 sqlite3 db test.db |
|
304 execsql { |
|
305 SELECT * FROM test; |
|
306 } |
|
307 } {1 2 3} |
|
308 do_test view-7.5 { |
|
309 execsql { |
|
310 DROP VIEW test; |
|
311 CREATE VIEW test AS |
|
312 SELECT test1.id, a, b |
|
313 FROM test1 NATURAL JOIN test2; |
|
314 SELECT * FROM test; |
|
315 } |
|
316 } {1 2 3} |
|
317 do_test view-7.6 { |
|
318 db close |
|
319 sqlite3 db test.db |
|
320 execsql { |
|
321 SELECT * FROM test; |
|
322 } |
|
323 } {1 2 3} |
|
324 |
|
325 do_test view-8.1 { |
|
326 execsql { |
|
327 CREATE VIEW v6 AS SELECT pqr, xyz FROM v1; |
|
328 SELECT * FROM v6 ORDER BY xyz; |
|
329 } |
|
330 } {7 2 13 5 19 8 27 12} |
|
331 do_test view-8.2 { |
|
332 db close |
|
333 sqlite3 db test.db |
|
334 execsql { |
|
335 SELECT * FROM v6 ORDER BY xyz; |
|
336 } |
|
337 } {7 2 13 5 19 8 27 12} |
|
338 do_test view-8.3 { |
|
339 execsql { |
|
340 CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6; |
|
341 SELECT * FROM v7 ORDER BY a; |
|
342 } |
|
343 } {9 18 27 39} |
|
344 |
|
345 ifcapable subquery { |
|
346 do_test view-8.4 { |
|
347 execsql { |
|
348 CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM |
|
349 (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo); |
|
350 SELECT * FROM v8; |
|
351 } |
|
352 } 3 |
|
353 do_test view-8.5 { |
|
354 execsql { |
|
355 SELECT mx+10, mx*2 FROM v8; |
|
356 } |
|
357 } {13 6} |
|
358 do_test view-8.6 { |
|
359 execsql { |
|
360 SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2; |
|
361 } |
|
362 } {13 7} |
|
363 do_test view-8.7 { |
|
364 execsql { |
|
365 SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2; |
|
366 } |
|
367 } {13 13 13 19 13 27} |
|
368 } ;# ifcapable subquery |
|
369 |
|
370 # Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW. |
|
371 # |
|
372 do_test view-9.1 { |
|
373 execsql { |
|
374 INSERT INTO t2 SELECT * FROM t2 WHERE a<5; |
|
375 INSERT INTO t2 SELECT * FROM t2 WHERE a<4; |
|
376 INSERT INTO t2 SELECT * FROM t2 WHERE a<3; |
|
377 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1; |
|
378 } |
|
379 } {1 2 4 8} |
|
380 do_test view-9.2 { |
|
381 execsql { |
|
382 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3; |
|
383 } |
|
384 } {1 2 4} |
|
385 do_test view-9.3 { |
|
386 execsql { |
|
387 CREATE VIEW v9 AS |
|
388 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3; |
|
389 SELECT * FROM v9; |
|
390 } |
|
391 } {1 2 4} |
|
392 do_test view-9.4 { |
|
393 execsql { |
|
394 SELECT * FROM v9 ORDER BY 1 DESC; |
|
395 } |
|
396 } {4 2 1} |
|
397 do_test view-9.5 { |
|
398 execsql { |
|
399 CREATE VIEW v10 AS |
|
400 SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3; |
|
401 SELECT * FROM v10; |
|
402 } |
|
403 } {5 1 4 2 3 4} |
|
404 do_test view-9.6 { |
|
405 execsql { |
|
406 SELECT * FROM v10 ORDER BY 1; |
|
407 } |
|
408 } {3 4 4 2 5 1} |
|
409 |
|
410 # Tables with columns having peculiar quoted names used in views |
|
411 # Ticket #756. |
|
412 # |
|
413 do_test view-10.1 { |
|
414 execsql { |
|
415 CREATE TABLE t3("9" integer, [4] text); |
|
416 INSERT INTO t3 VALUES(1,2); |
|
417 CREATE VIEW v_t3_a AS SELECT a.[9] FROM t3 AS a; |
|
418 CREATE VIEW v_t3_b AS SELECT "4" FROM t3; |
|
419 SELECT * FROM v_t3_a; |
|
420 } |
|
421 } {1} |
|
422 do_test view-10.2 { |
|
423 execsql { |
|
424 SELECT * FROM v_t3_b; |
|
425 } |
|
426 } {2} |
|
427 |
|
428 do_test view-11.1 { |
|
429 execsql { |
|
430 CREATE TABLE t4(a COLLATE NOCASE); |
|
431 INSERT INTO t4 VALUES('This'); |
|
432 INSERT INTO t4 VALUES('this'); |
|
433 INSERT INTO t4 VALUES('THIS'); |
|
434 SELECT * FROM t4 WHERE a = 'THIS'; |
|
435 } |
|
436 } {This this THIS} |
|
437 ifcapable subquery { |
|
438 do_test view-11.2 { |
|
439 execsql { |
|
440 SELECT * FROM (SELECT * FROM t4) WHERE a = 'THIS'; |
|
441 } |
|
442 } {This this THIS} |
|
443 } |
|
444 do_test view-11.3 { |
|
445 execsql { |
|
446 CREATE VIEW v11 AS SELECT * FROM t4; |
|
447 SELECT * FROM v11 WHERE a = 'THIS'; |
|
448 } |
|
449 } {This this THIS} |
|
450 |
|
451 # Ticket #1270: Do not allow parameters in view definitions. |
|
452 # |
|
453 do_test view-12.1 { |
|
454 catchsql { |
|
455 CREATE VIEW v12 AS SELECT a FROM t1 WHERE b=? |
|
456 } |
|
457 } {1 {parameters are not allowed in views}} |
|
458 |
|
459 ifcapable attach { |
|
460 do_test view-13.1 { |
|
461 file delete -force test2.db |
|
462 catchsql { |
|
463 ATTACH 'test2.db' AS two; |
|
464 CREATE TABLE two.t2(x,y); |
|
465 CREATE VIEW v13 AS SELECT y FROM two.t2; |
|
466 } |
|
467 } {1 {view v13 cannot reference objects in database two}} |
|
468 } |
|
469 |
|
470 # Ticket #1658 |
|
471 # |
|
472 do_test view-14.1 { |
|
473 catchsql { |
|
474 CREATE TEMP VIEW t1 AS SELECT a,b FROM t1; |
|
475 SELECT * FROM temp.t1; |
|
476 } |
|
477 } {1 {view t1 is circularly defined}} |
|
478 |
|
479 # Tickets #1688, #1709 |
|
480 # |
|
481 do_test view-15.1 { |
|
482 execsql2 { |
|
483 CREATE VIEW v15 AS SELECT a AS x, b AS y FROM t1; |
|
484 SELECT * FROM v15 LIMIT 1; |
|
485 } |
|
486 } {x 2 y 3} |
|
487 do_test view-15.2 { |
|
488 execsql2 { |
|
489 SELECT x, y FROM v15 LIMIT 1 |
|
490 } |
|
491 } {x 2 y 3} |
|
492 |
|
493 do_test view-16.1 { |
|
494 catchsql { |
|
495 CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1; |
|
496 } |
|
497 } {0 {}} |
|
498 do_test view-16.2 { |
|
499 execsql { |
|
500 SELECT sql FROM sqlite_master WHERE name='v1' |
|
501 } |
|
502 } {{CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1}} |
|
503 do_test view-16.3 { |
|
504 catchsql { |
|
505 DROP VIEW IF EXISTS nosuchview |
|
506 } |
|
507 } {0 {}} |
|
508 |
|
509 # correct error message when attempting to drop a view that does not |
|
510 # exist. |
|
511 # |
|
512 do_test view-17.1 { |
|
513 catchsql { |
|
514 DROP VIEW nosuchview |
|
515 } |
|
516 } {1 {no such view: nosuchview}} |
|
517 do_test view-17.2 { |
|
518 catchsql { |
|
519 DROP VIEW main.nosuchview |
|
520 } |
|
521 } {1 {no such view: main.nosuchview}} |
|
522 |
|
523 do_test view-18.1 { |
|
524 execsql { |
|
525 DROP VIEW t1; |
|
526 DROP TABLE t1; |
|
527 CREATE TABLE t1(a, b, c); |
|
528 INSERT INTO t1 VALUES(1, 2, 3); |
|
529 INSERT INTO t1 VALUES(4, 5, 6); |
|
530 |
|
531 CREATE VIEW vv1 AS SELECT * FROM t1; |
|
532 CREATE VIEW vv2 AS SELECT * FROM vv1; |
|
533 CREATE VIEW vv3 AS SELECT * FROM vv2; |
|
534 CREATE VIEW vv4 AS SELECT * FROM vv3; |
|
535 CREATE VIEW vv5 AS SELECT * FROM vv4; |
|
536 |
|
537 SELECT * FROM vv5; |
|
538 } |
|
539 } {1 2 3 4 5 6} |
|
540 |
|
541 # Ticket #3308 |
|
542 # Make sure "rowid" columns in a view are named correctly. |
|
543 # |
|
544 do_test view-19.1 { |
|
545 execsql { |
|
546 CREATE VIEW v3308a AS SELECT rowid, * FROM t1; |
|
547 } |
|
548 execsql2 { |
|
549 SELECT * FROM v3308a |
|
550 } |
|
551 } {rowid 1 a 1 b 2 c 3 rowid 2 a 4 b 5 c 6} |
|
552 do_test view-19.2 { |
|
553 execsql { |
|
554 CREATE VIEW v3308b AS SELECT t1.rowid, t1.a, t1.b+t1.c FROM t1; |
|
555 } |
|
556 execsql2 { |
|
557 SELECT * FROM v3308b |
|
558 } |
|
559 } {rowid 1 a 1 t1.b+t1.c 5 rowid 2 a 4 t1.b+t1.c 11} |
|
560 do_test view-19.3 { |
|
561 execsql { |
|
562 CREATE VIEW v3308c AS SELECT t1.oid, A, t1.b+t1.c AS x FROM t1; |
|
563 } |
|
564 execsql2 { |
|
565 SELECT * FROM v3308c |
|
566 } |
|
567 } {rowid 1 a 1 x 5 rowid 2 a 4 x 11} |
|
568 |
|
569 finish_test |