Debian Codegraph @

test/view.test

raw
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.39 2008/12/14 14:45:21 danielk1977 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.1.100 {
42 db config enable_view off
43 catchsql {
44 SELECT * FROM v1 ORDER BY a;
45 }
46 } {1 {access to view "v1" prohibited}}
47 do_execsql_test view-1.1.101 {
48 CREATE TEMP VIEW v1temp AS SELECT a, b FROM t1;
49 SELECT * FROM v1temp ORDER BY a;
50 } {1 2 4 5 7 8}
51 do_test view-1.1.110 {
52 db config enable_view on
53 catchsql {
54 SELECT * FROM v1 ORDER BY a;
55 SELECT * FROM v1temp ORDER BY a;
56 }
57 } {0 {1 2 4 5 7 8 1 2 4 5 7 8}}
58 ifcapable vtab {
59 do_execsql_test view-1.1.120 {
60 SELECT name, type FROM pragma_table_list('v1');
61 } {v1 view}
62 }
63 do_test view-1.2 {
64 catchsql {
65 ROLLBACK;
66 SELECT * FROM v1 ORDER BY a;
67 }
68 } {1 {no such table: v1}}
69 do_test view-1.3 {
70 execsql {
71 CREATE VIEW v1 AS SELECT a,b FROM t1;
72 SELECT * FROM v1 ORDER BY a;
73 }
74 } {1 2 4 5 7 8}
75 do_test view-1.3.1 {
76 db close
77 sqlite3 db test.db
78 execsql {
79 SELECT * FROM v1 ORDER BY a;
80 }
81 } {1 2 4 5 7 8}
82 do_test view-1.4 {
83 catchsql {
84 DROP VIEW IF EXISTS v1;
85 SELECT * FROM v1 ORDER BY a;
86 }
87 } {1 {no such table: v1}}
88 do_test view-1.5 {
89 execsql {
90 CREATE VIEW v1 AS SELECT a,b FROM t1;
91 SELECT * FROM v1 ORDER BY a;
92 }
93 } {1 2 4 5 7 8}
94 do_test view-1.6 {
95 catchsql {
96 DROP TABLE t1;
97 SELECT * FROM v1 ORDER BY a;
98 }
99 } {1 {no such table: main.t1}}
100 do_test view-1.7 {
101 execsql {
102 CREATE TABLE t1(x,a,b,c);
103 INSERT INTO t1 VALUES(1,2,3,4);
104 INSERT INTO t1 VALUES(4,5,6,7);
105 INSERT INTO t1 VALUES(7,8,9,10);
106 SELECT * FROM v1 ORDER BY a;
107 }
108 } {2 3 5 6 8 9}
109 do_test view-1.8 {
110 db close
111 sqlite3 db test.db
112 execsql {
113 SELECT * FROM v1 ORDER BY a;
114 }
115 } {2 3 5 6 8 9}
116
117 do_execsql_test view-1.10 {
118 CREATE TABLE t9(x INTEGER);
119 CREATE VIEW v9a AS SELECT x FROM t9;
120 CREATE VIEW v9b AS SELECT * FROM t9;
121 CREATE VIEW v9c(x) AS SELECT x FROM t9;
122 CREATE VIEW v9d(x) AS SELECT * FROM t9;
123 } {}
124 do_execsql_test view-1.11 {
125 PRAGMA table_info(v9a);
126 } {0 x INTEGER 0 {} 0}
127 do_execsql_test view-1.12 {
128 PRAGMA table_info(v9b);
129 } {0 x INTEGER 0 {} 0}
130 do_execsql_test view-1.13 {
131 PRAGMA table_info(v9c);
132 } {0 x INTEGER 0 {} 0}
133 do_execsql_test view-1.14 {
134 PRAGMA table_info(v9d);
135 } {0 x INTEGER 0 {} 0}
136
137 do_test view-2.1 {
138 execsql {
139 CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5
140 }; # No semicolon
141 execsql2 {
142 SELECT * FROM v2;
143 }
144 } {x 7 a 8 b 9 c 10}
145 do_test view-2.2 {
146 catchsql {
147 INSERT INTO v2 VALUES(1,2,3,4);
148 }
149 } {1 {cannot modify v2 because it is a view}}
150 do_test view-2.3 {
151 catchsql {
152 UPDATE v2 SET a=10 WHERE a=5;
153 }
154 } {1 {cannot modify v2 because it is a view}}
155 do_test view-2.4 {
156 catchsql {
157 DELETE FROM v2;
158 }
159 } {1 {cannot modify v2 because it is a view}}
160 do_test view-2.5 {
161 execsql {
162 INSERT INTO t1 VALUES(11,12,13,14);
163 SELECT * FROM v2 ORDER BY x;
164 }
165 } {7 8 9 10 11 12 13 14}
166 do_test view-2.6 {
167 execsql {
168 SELECT x FROM v2 WHERE a>10
169 }
170 } {11}
171
172 # Test that column name of views are generated correctly.
173 #
174 do_test view-3.1 {
175 execsql2 {
176 SELECT * FROM v1 LIMIT 1
177 }
178 } {a 2 b 3}
179 do_test view-3.2 {
180 execsql2 {
181 SELECT * FROM v2 LIMIT 1
182 }
183 } {x 7 a 8 b 9 c 10}
184 do_test view-3.3.1 {
185 execsql2 {
186 DROP VIEW v1;
187 CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1;
188 SELECT * FROM v1 LIMIT 1
189 }
190 } {xyz 2 pqr 7 c-b 1}
191 do_test view-3.3.2 {
192 execsql2 {
193 CREATE VIEW v1b AS SELECT t1.a, b+c, t1.c FROM t1;
194 SELECT * FROM v1b LIMIT 1
195 }
196 } {a 2 b+c 7 c 4}
197 do_test view-3.3.3 {
198 execsql2 {
199 CREATE VIEW v1c(x,y,z) AS SELECT a, b+c, c-b FROM t1;
200 SELECT * FROM v1c LIMIT 1;
201 }
202 } {x 2 y 7 z 1}
203 do_catchsql_test view-3.3.4 {
204 CREATE VIEW v1err(x,y DESC,z) AS SELECT a, b+c, c-b FROM t1;
205 } {1 {syntax error after column name "y"}}
206 do_catchsql_test view-3.3.5 {
207 DROP VIEW IF EXISTS v1err;
208 CREATE VIEW v1err(x,y) AS SELECT a, b+c, c-b FROM t1;
209 SELECT * FROM v1err;
210 } {1 {expected 2 columns for 'v1err' but got 3}}
211 do_catchsql_test view-3.3.6 {
212 DROP VIEW IF EXISTS v1err;
213 CREATE VIEW v1err(w,x,y,z) AS SELECT a, b+c, c-b FROM t1;
214 SELECT * FROM v1err;
215 } {1 {expected 4 columns for 'v1err' but got 3}}
216
217 ifcapable compound {
218 do_test view-3.4 {
219 execsql2 {
220 CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b;
221 SELECT * FROM v3 LIMIT 4;
222 }
223 } {a 2 a 3 a 5 a 6}
224 do_test view-3.5 {
225 execsql2 {
226 CREATE VIEW v4 AS
227 SELECT a, b FROM t1
228 UNION
229 SELECT b AS 'x', a AS 'y' FROM t1
230 ORDER BY x, y;
231 SELECT b FROM v4 ORDER BY b LIMIT 4;
232 }
233 } {b 2 b 3 b 5 b 6}
234 } ;# ifcapable compound
235
236
237 do_test view-4.1 {
238 catchsql {
239 DROP VIEW t1;
240 }
241 } {1 {use DROP TABLE to delete table t1}}
242 do_test view-4.2 {
243 execsql {
244 SELECT 1 FROM t1 LIMIT 1;
245 }
246 } 1
247 do_test view-4.3 {
248 catchsql {
249 DROP TABLE v1;
250 }
251 } {1 {use DROP VIEW to delete view v1}}
252 do_test view-4.4 {
253 execsql {
254 SELECT 1 FROM v1 LIMIT 1;
255 }
256 } {1}
257 do_test view-4.5 {
258 catchsql {
259 CREATE INDEX i1v1 ON v1(xyz);
260 }
261 } {1 {views may not be indexed}}
262
263 do_test view-5.1 {
264 execsql {
265 CREATE TABLE t2(y,a);
266 INSERT INTO t2 VALUES(22,2);
267 INSERT INTO t2 VALUES(33,3);
268 INSERT INTO t2 VALUES(44,4);
269 INSERT INTO t2 VALUES(55,5);
270 SELECT * FROM t2;
271 }
272 } {22 2 33 3 44 4 55 5}
273 do_test view-5.2 {
274 execsql {
275 CREATE VIEW v5 AS
276 SELECT t1.x AS v, t2.y AS w FROM t1 JOIN t2 USING(a);
277 SELECT * FROM v5;
278 }
279 } {1 22 4 55}
280
281 # Verify that the view v5 gets flattened. see sqliteFlattenSubquery().
282 # This will only work if EXPLAIN is enabled.
283 # Ticket #272
284 #
285 ifcapable {explain} {
286 do_test view-5.3 {
287 lsearch [execsql {
288 EXPLAIN SELECT * FROM v5;
289 }] OpenEphemeral
290 } {-1}
291 do_test view-5.4 {
292 execsql {
293 SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
294 }
295 } {1 22 22 2 4 55 55 5}
296 do_test view-5.5 {
297 lsearch [execsql {
298 EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y;
299 }] OpenEphemeral
300 } {-1}
301 do_test view-5.6 {
302 execsql {
303 SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
304 }
305 } {22 2 1 22 55 5 4 55}
306 do_test view-5.7 {
307 lsearch [execsql {
308 EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y;
309 }] OpenEphemeral
310 } {-1}
311 do_test view-5.8 {
312 execsql {
313 SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
314 }
315 } {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5}
316 do_test view-5.9 {
317 lsearch [execsql {
318 EXPLAIN SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y;
319 }] OpenEphemeral
320 } {-1}
321 } ;# endif explain
322
323 do_test view-6.1 {
324 execsql {
325 SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2;
326 }
327 } {7 8 9 10 27}
328 do_test view-6.2 {
329 execsql {
330 SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2;
331 }
332 } {11 12 13 14 39}
333
334 do_test view-7.1 {
335 execsql {
336 CREATE TABLE test1(id integer primary key, a);
337 CREATE TABLE test2(id integer, b);
338 INSERT INTO test1 VALUES(1,2);
339 INSERT INTO test2 VALUES(1,3);
340 CREATE VIEW test AS
341 SELECT test1.id, a, b
342 FROM test1 JOIN test2 ON test2.id=test1.id;
343 SELECT * FROM test;
344 }
345 } {1 2 3}
346 do_test view-7.2 {
347 db close
348 sqlite3 db test.db
349 execsql {
350 SELECT * FROM test;
351 }
352 } {1 2 3}
353 do_test view-7.3 {
354 execsql {
355 DROP VIEW test;
356 CREATE VIEW test AS
357 SELECT test1.id, a, b
358 FROM test1 JOIN test2 USING(id);
359 SELECT * FROM test;
360 }
361 } {1 2 3}
362 do_test view-7.4 {
363 db close
364 sqlite3 db test.db
365 execsql {
366 SELECT * FROM test;
367 }
368 } {1 2 3}
369 do_test view-7.5 {
370 execsql {
371 DROP VIEW test;
372 CREATE VIEW test AS
373 SELECT test1.id, a, b
374 FROM test1 NATURAL JOIN test2;
375 SELECT * FROM test;
376 }
377 } {1 2 3}
378 do_test view-7.6 {
379 db close
380 sqlite3 db test.db
381 execsql {
382 SELECT * FROM test;
383 }
384 } {1 2 3}
385
386 do_test view-8.1 {
387 execsql {
388 CREATE VIEW v6 AS SELECT pqr, xyz FROM v1;
389 SELECT * FROM v6 ORDER BY xyz;
390 }
391 } {7 2 13 5 19 8 27 12}
392 do_test view-8.2 {
393 db close
394 sqlite3 db test.db
395 execsql {
396 SELECT * FROM v6 ORDER BY xyz;
397 }
398 } {7 2 13 5 19 8 27 12}
399 do_test view-8.3 {
400 execsql {
401 CREATE VIEW v7(a) AS SELECT pqr+xyz FROM v6;
402 SELECT * FROM v7 ORDER BY a;
403 }
404 } {9 18 27 39}
405
406 ifcapable subquery {
407 do_test view-8.4 {
408 execsql {
409 CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM
410 (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo);
411 SELECT * FROM v8;
412 }
413 } 3
414 do_test view-8.5 {
415 execsql {
416 SELECT mx+10, mx*2 FROM v8;
417 }
418 } {13 6}
419 do_test view-8.6 {
420 execsql {
421 SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2;
422 }
423 } {13 7}
424 do_test view-8.7 {
425 execsql {
426 SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2;
427 }
428 } {13 13 13 19 13 27}
429 } ;# ifcapable subquery
430
431 # Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW.
432 #
433 do_test view-9.1 {
434 execsql {
435 INSERT INTO t2 SELECT * FROM t2 WHERE a<5;
436 INSERT INTO t2 SELECT * FROM t2 WHERE a<4;
437 INSERT INTO t2 SELECT * FROM t2 WHERE a<3;
438 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1;
439 }
440 } {1 2 4 8}
441 do_test view-9.2 {
442 execsql {
443 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
444 }
445 } {1 2 4}
446 do_test view-9.3 {
447 execsql {
448 CREATE VIEW v9 AS
449 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3;
450 SELECT * FROM v9;
451 }
452 } {1 2 4}
453 do_test view-9.4 {
454 execsql {
455 SELECT * FROM v9 ORDER BY 1 DESC;
456 }
457 } {4 2 1}
458 do_test view-9.5 {
459 execsql {
460 CREATE VIEW v10 AS
461 SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3;
462 SELECT * FROM v10;
463 }
464 } {5 1 4 2 3 4}
465 do_test view-9.6 {
466 execsql {
467 SELECT * FROM v10 ORDER BY 1;
468 }
469 } {3 4 4 2 5 1}
470
471 # Tables with columns having peculiar quoted names used in views
472 # Ticket #756.
473 #
474 do_test view-10.1 {
475 execsql {
476 CREATE TABLE t3("9" integer, [4] text);
477 INSERT INTO t3 VALUES(1,2);
478 CREATE VIEW v_t3_a AS SELECT a.[9] FROM t3 AS a;
479 CREATE VIEW v_t3_b AS SELECT "4" FROM t3;
480 SELECT * FROM v_t3_a;
481 }
482 } {1}
483 do_test view-10.2 {
484 execsql {
485 SELECT * FROM v_t3_b;
486 }
487 } {2}
488
489 do_test view-11.1 {
490 execsql {
491 CREATE TABLE t4(a COLLATE NOCASE);
492 INSERT INTO t4 VALUES('This');
493 INSERT INTO t4 VALUES('this');
494 INSERT INTO t4 VALUES('THIS');
495 SELECT * FROM t4 WHERE a = 'THIS';
496 }
497 } {This this THIS}
498 ifcapable subquery {
499 do_test view-11.2 {
500 execsql {
501 SELECT * FROM (SELECT * FROM t4) WHERE a = 'THIS';
502 }
503 } {This this THIS}
504 }
505 do_test view-11.3 {
506 execsql {
507 CREATE VIEW v11 AS SELECT * FROM t4;
508 SELECT * FROM v11 WHERE a = 'THIS';
509 }
510 } {This this THIS}
511
512 # Ticket #1270: Do not allow parameters in view definitions.
513 #
514 do_test view-12.1 {
515 catchsql {
516 CREATE VIEW v12 AS SELECT a FROM t1 WHERE b=?
517 }
518 } {1 {parameters are not allowed in views}}
519 do_test view-12.2 {
520 catchsql {
521 CREATE VIEW v12(x) AS SELECT a FROM t1 WHERE b=?
522 }
523 } {1 {parameters are not allowed in views}}
524
525 ifcapable attach {
526 do_test view-13.1 {
527 forcedelete test2.db
528 catchsql {
529 ATTACH 'test2.db' AS two;
530 CREATE TABLE two.t2(x,y);
531 CREATE VIEW v13 AS SELECT y FROM two.t2;
532 }
533 } {1 {view v13 cannot reference objects in database two}}
534 }
535
536 # Ticket #1658
537 #
538 do_test view-14.1 {
539 catchsql {
540 CREATE TEMP VIEW t1 AS SELECT a,b FROM t1;
541 SELECT * FROM temp.t1;
542 }
543 } {1 {view t1 is circularly defined}}
544 do_test view-14.2 {
545 catchsql {
546 DROP VIEW IF EXISTS temp.t1;
547 CREATE TEMP VIEW t1(a,b) AS SELECT a,b FROM t1;
548 SELECT * FROM temp.t1;
549 }
550 } {1 {view t1 is circularly defined}}
551
552 # Tickets #1688, #1709
553 #
554 do_test view-15.1 {
555 execsql2 {
556 CREATE VIEW v15 AS SELECT a AS x, b AS y FROM t1;
557 SELECT * FROM v15 LIMIT 1;
558 }
559 } {x 2 y 3}
560 do_test view-15.2 {
561 execsql2 {
562 SELECT x, y FROM v15 LIMIT 1
563 }
564 } {x 2 y 3}
565
566 do_test view-16.1 {
567 catchsql {
568 CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1;
569 }
570 } {0 {}}
571 do_test view-16.2 {
572 execsql {
573 SELECT sql FROM sqlite_master WHERE name='v1'
574 }
575 } {{CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1}}
576 do_test view-16.3 {
577 catchsql {
578 DROP VIEW IF EXISTS nosuchview
579 }
580 } {0 {}}
581
582 # correct error message when attempting to drop a view that does not
583 # exist.
584 #
585 do_test view-17.1 {
586 catchsql {
587 DROP VIEW nosuchview
588 }
589 } {1 {no such view: nosuchview}}
590 do_test view-17.2 {
591 catchsql {
592 DROP VIEW main.nosuchview
593 }
594 } {1 {no such view: main.nosuchview}}
595
596 do_test view-18.1 {
597 execsql {
598 DROP VIEW t1;
599 DROP TABLE t1;
600 CREATE TABLE t1(a, b, c);
601 INSERT INTO t1 VALUES(1, 2, 3);
602 INSERT INTO t1 VALUES(4, 5, 6);
603
604 CREATE VIEW vv1 AS SELECT * FROM t1;
605 CREATE VIEW vv2 AS SELECT * FROM vv1;
606 CREATE VIEW vv3 AS SELECT * FROM vv2;
607 CREATE VIEW vv4 AS SELECT * FROM vv3;
608 CREATE VIEW vv5 AS SELECT * FROM vv4;
609
610 SELECT * FROM vv5;
611 }
612 } {1 2 3 4 5 6}
613
614 # Ticket #3308
615 # Make sure "rowid" columns in a view are named correctly.
616 #
617 do_test view-19.1 {
618 execsql {
619 CREATE VIEW v3308a AS SELECT rowid, * FROM t1;
620 }
621 execsql2 {
622 SELECT * FROM v3308a
623 }
624 } {rowid 1 a 1 b 2 c 3 rowid 2 a 4 b 5 c 6}
625 do_test view-19.2 {
626 execsql {
627 CREATE VIEW v3308b AS SELECT t1.rowid, t1.a, t1.b+t1.c FROM t1;
628 }
629 execsql2 {
630 SELECT * FROM v3308b
631 }
632 } {rowid 1 a 1 t1.b+t1.c 5 rowid 2 a 4 t1.b+t1.c 11}
633 do_test view-19.3 {
634 execsql {
635 CREATE VIEW v3308c AS SELECT t1.oid, A, t1.b+t1.c AS x FROM t1;
636 }
637 execsql2 {
638 SELECT * FROM v3308c
639 }
640 } {rowid 1 a 1 x 5 rowid 2 a 4 x 11}
641
642 # Ticket #3539 had this crashing (see commit [5940]).
643 do_test view-20.1 {
644 execsql {
645 DROP TABLE IF EXISTS t1;
646 DROP VIEW IF EXISTS v1;
647 CREATE TABLE t1(c1);
648 CREATE VIEW v1 AS SELECT c1 FROM (SELECT t1.c1 FROM t1);
649 }
650 } {}
651
652 db close
653 sqlite3 db :memory:
654 do_execsql_test view-22.1 {
655 CREATE VIEW x1 AS SELECT 123 AS '', 234 AS '', 345 AS '';
656 SELECT * FROM x1;
657 } {123 234 345}
658 do_test view-22.2 {
659 unset -nocomplain x
660 db eval {SELECT * FROM x1} x break
661 lsort [array names x]
662 } {{} * :1 :2}
663
664 do_test view-25.1 {
665 db eval {
666 CREATE TABLE t25 (x);
667 INSERT INTO t25 (x) VALUES (1);
668 ANALYZE;
669 }
670 proc authLogDelete {code arg1 arg2 arg3 arg4 args} {
671 if {$code=="SQLITE_DELETE" && [string match sqlite_stat* $arg1]} {
672 # lappend ::log [list $code $arg1 $arg2 $arg3 $arg4 $args]
673 lappend ::log [list $code $arg1 $arg2 $arg3 $arg4]
674 }
675 return SQLITE_OK
676 }
677 set log ""
678 db authorizer ::authLogDelete
679 db eval {DROP VIEW x1;}
680 set log
681 } {}
682
683 set res [list {SQLITE_DELETE sqlite_stat1 {} main {}}]
684 ifcapable stat4 { lappend res {SQLITE_DELETE sqlite_stat4 {} main {}} }
685 do_test view-25.2 {
686 set log ""
687 db eval {DROP TABLE t25;}
688 set log
689 } $res
690
691 #-------------------------------------------------------------------------
692 do_execsql_test view-26.0 {
693 CREATE TABLE t16(a, b, c UNIQUE);
694 INSERT INTO t16 VALUES(1, 1, 1);
695 INSERT INTO t16 VALUES(2, 2, 2);
696 INSERT INTO t16 VALUES(3, 3, 3);
697 CREATE VIEW v16 AS SELECT max(a) AS mx, min(b) AS mn FROM t16 GROUP BY c;
698
699 SELECT * FROM v16 AS one, v16 AS two WHERE one.mx=1;
700 } {
701 1 1 1 1
702 1 1 2 2
703 1 1 3 3
704 }
705 do_execsql_test view-26.1 {
706 WITH v17(x,y) AS (SELECT max(a), min(b) FROM t16 GROUP BY c)
707 SELECT * FROM v17 AS one, v17 AS two WHERE one.x=1;
708 } {
709 1 1 1 1
710 1 1 2 2
711 1 1 3 3
712 }
713
714 #-------------------------------------------------------------------------
715 reset_db
716 do_execsql_test view-27.0 {
717 CREATE TABLE t0(c0 TEXT, c1);
718 INSERT INTO t0(c0, c1) VALUES (-1, 0);
719 CREATE VIEW v0(c0, c1) AS SELECT t0.c0, AVG(t0.c1) FROM t0;
720 }
721
722 do_execsql_test view-27.1 {
723 SELECT c0, typeof(c0), c1, typeof(c1) FROM v0;
724 } {
725 -1 text
726 0.0 real
727 }
728
729 do_execsql_test view-27.2 { SELECT c0<c1 FROM v0 } 1
730 do_execsql_test view-27.3 { SELECT c1<c0 FROM v0 } 0
731 do_execsql_test view-27.4 {
732 SELECT 1 FROM v0 WHERE c1<c0
733 } {}
734 do_execsql_test view-27.5 {
735 SELECT 1 FROM v0 WHERE c0<c1
736 } {1}
737
738 do_execsql_test view-27.6 {
739 SELECT c0<c1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0)
740 } 1
741 do_execsql_test view-27.7 {
742 SELECT c1<c0 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0)
743 } 0
744 do_execsql_test view-27.8 {
745 SELECT 1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) WHERE c1<c0
746 } {}
747 do_execsql_test view-27.9 {
748 SELECT 1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) WHERE c0<c1
749 } {1}
750
751 #-------------------------------------------------------------------------
752 reset_db
753 do_execsql_test view-28.0 {
754 CREATE TABLE t0(c0 TEXT);
755 CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t0;
756 INSERT INTO t0(c0) VALUES ('0');
757 }
758 do_execsql_test view-28.1 {
759 SELECT 0 IN (c0) FROM t0;
760 } {0}
761 do_execsql_test view-28.2 {
762 SELECT 0 IN (c0) FROM (SELECT c0 FROM t0);
763 } {0}
764
765 #-------------------------------------------------------------------------
766 # 2020-10-26. https://sqlite.org/forum/forumpost/daa2c728cc
767 #
768 reset_db
769 do_catchsql_test view-29.0 {
770 CREATE TABLE t1(a,b,c);
771 CREATE VIEW IF NOT EXISTS IF AS SELECT null;
772 } {1 {malformed database schema (IF) - near "AS": syntax error}}
773 do_catchsql_test view-29.1 {
774 CREATE TABLE t2(c,d,e);
775 SELECT name FROM sqlite_schema ORDER BY name;
776 } {0 {t1 t2}}
777
778 #-------------------------------------------------------------------------
779 # 2022-12-11. https://sqlite.org/src/info/679ed6a2
780 #
781 # 2022-12-14 change: If the AS SELECT of a VIEW is a compound where
782 # the datatypes on each arm of the compound are different, then the
783 # datatype of the overall column is BLOB (ANY).
784 #
785 reset_db
786 do_execsql_test view-30.0 {
787 CREATE TABLE t0(a INT, b TEXT);
788
789 INSERT INTO t0 VALUES(1,'one');
790
791 CREATE VIEW t1 AS SELECT a, b FROM t0 UNION ALL SELECT 2, 2;
792 CREATE VIEW t2(a,b) AS SELECT a, b FROM t0 UNION ALL SELECT 2, 2;
793 }
794
795 ifcapable schema_pragmas {
796 do_execsql_test view-30.1 {
797 PRAGMA table_info = t1;
798 } { 0 a INT 0 {} 0 1 b BLOB 0 {} 0 }
799 do_execsql_test view-30.2 {
800 PRAGMA table_info = t2;
801 } { 0 a INT 0 {} 0 1 b BLOB 0 {} 0 }
802 }
803
804 #-----------------------------------------------------------------------
805 # 2024-04-25 Trying to make type information on compound subqueries
806 # more predictable and rational.
807 #
808 reset_db
809 do_execsql_test view-31.1 {
810 CREATE TABLE x2(b TEXT);
811 CREATE TABLE x1(a TEXT);
812 INSERT INTO x1 VALUES('123');
813 -- Two queries get the same result even though the order of terms
814 -- in the CTE is reversed
815 WITH c(x) AS ( SELECT b FROM x2 UNION SELECT 123 )
816 SELECT count(*) FROM x1 WHERE a IN c;
817 WITH c(x) AS ( SELECT 123 UNION SELECT b FROM x2 )
818 SELECT count(*) FROM x1 WHERE a IN c;
819 } {0 0}
820 do_execsql_test view-31.2 {
821 CREATE TABLE t3(a INTEGER, b TEXT);
822 INSERT INTO t3 VALUES(123, 123);
823 WITH s AS ( VALUES(123), (456) ) SELECT * FROM t3 WHERE b IN s;
824 } {123 123}
825
826
827 finish_test