Import SQLite 3.10.2.

third_party/sqlite/src/test/view.test

 # 2002 February 26
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #***********************************************************************
@@ skipping 134 matching lines @@
     CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1;
     SELECT * FROM v1 LIMIT 1
   }
 } {xyz 2 pqr 7 c-b 1}
 do_test view-3.3.2 {
   execsql2 {
     CREATE VIEW v1b AS SELECT t1.a, b+c, t1.c FROM t1;
     SELECT * FROM v1b LIMIT 1
   }
 } {a 2 b+c 7 c 4}
+do_test view-3.3.3 {
+  execsql2 {
+    CREATE VIEW v1c(x,y,z) AS SELECT a, b+c, c-b FROM t1;
+    SELECT * FROM v1c LIMIT 1;
+  }
+} {x 2 y 7 z 1}
+do_catchsql_test view-3.3.4 {
+  CREATE VIEW v1err(x,y DESC,z) AS SELECT a, b+c, c-b FROM t1;
+} {1 {syntax error after column name "y"}}
+do_catchsql_test view-3.3.5 {
+  DROP VIEW IF EXISTS v1err;
+  CREATE VIEW v1err(x,y) AS SELECT a, b+c, c-b FROM t1;
+  SELECT * FROM v1err;
+} {1 {expected 2 columns for 'v1err' but got 3}}
+do_catchsql_test view-3.3.6 {
+  DROP VIEW IF EXISTS v1err;
+  CREATE VIEW v1err(w,x,y,z) AS SELECT a, b+c, c-b FROM t1;
+  SELECT * FROM v1err;
+} {1 {expected 4 columns for 'v1err' but got 3}}
 
 ifcapable compound {
 do_test  view-3.4 {
   execsql2 {
     CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b;
     SELECT * FROM v3 LIMIT 4;
   }
 } {a 2 a 3 a 5 a 6}
 do_test view-3.5 {
   execsql2 {
@@ skipping 165 matching lines @@
 } {7 2 13 5 19 8 27 12}
 do_test view-8.2 {
   db close
   sqlite3 db test.db
   execsql {
     SELECT * FROM v6 ORDER BY xyz;
   }
 } {7 2 13 5 19 8 27 12}
 do_test view-8.3 {
   execsql {
-    CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6;
+    CREATE VIEW v7(a) AS SELECT pqr+xyz FROM v6;
     SELECT * FROM v7 ORDER BY a;
   }
 } {9 18 27 39}
 
 ifcapable subquery {
   do_test view-8.4 {
     execsql {
       CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM
         (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo);
       SELECT * FROM v8;
@@ skipping 97 matching lines @@
   }
 } {This this THIS}
 
 # Ticket #1270:  Do not allow parameters in view definitions.
 #
 do_test view-12.1 {
   catchsql {
     CREATE VIEW v12 AS SELECT a FROM t1 WHERE b=?
   }
 } {1 {parameters are not allowed in views}}
+do_test view-12.2 {
+  catchsql {
+    CREATE VIEW v12(x) AS SELECT a FROM t1 WHERE b=?
+  }
+} {1 {parameters are not allowed in views}}
 
 ifcapable attach {
   do_test view-13.1 {
     forcedelete test2.db
     catchsql {
       ATTACH 'test2.db' AS two;
       CREATE TABLE two.t2(x,y);
       CREATE VIEW v13 AS SELECT y FROM two.t2;
     }
   } {1 {view v13 cannot reference objects in database two}}
 }
 
 # Ticket #1658
 #
 do_test view-14.1 {
   catchsql {
     CREATE TEMP VIEW t1 AS SELECT a,b FROM t1;
     SELECT * FROM temp.t1;
   }
 } {1 {view t1 is circularly defined}}
+do_test view-14.2 {
+  catchsql {
+    DROP VIEW IF EXISTS temp.t1;
+    CREATE TEMP VIEW t1(a,b) AS SELECT a,b FROM t1;
+    SELECT * FROM temp.t1;
+  }
+} {1 {view t1 is circularly defined}}
 
 # Tickets #1688, #1709
 #
 do_test view-15.1 {
   execsql2 {
     CREATE VIEW v15 AS SELECT a AS x, b AS y FROM t1;
     SELECT * FROM v15 LIMIT 1;
   }
 } {x 2 y 3}
 do_test view-15.2 {
@@ skipping 104 matching lines @@
     CREATE VIEW v64 AS SELECT * FROM v32 UNION SELECT * FROM v32;
     CREATE VIEW v128 AS SELECT * FROM v64 UNION SELECT * FROM v64;
     CREATE VIEW v256 AS SELECT * FROM v128 UNION SELECT * FROM v128;
     CREATE VIEW v512 AS SELECT * FROM v256 UNION SELECT * FROM v256;
     CREATE VIEW v1024 AS SELECT * FROM v512 UNION SELECT * FROM v512;
     CREATE VIEW v2048 AS SELECT * FROM v1024 UNION SELECT * FROM v1024;
     CREATE VIEW v4096 AS SELECT * FROM v2048 UNION SELECT * FROM v2048;
     CREATE VIEW v8192 AS SELECT * FROM v4096 UNION SELECT * FROM v4096;
     CREATE VIEW v16384 AS SELECT * FROM v8192 UNION SELECT * FROM v8192;
     CREATE VIEW v32768 AS SELECT * FROM v16384 UNION SELECT * FROM v16384;
-    CREATE VIEW vx AS SELECT * FROM v32768 UNION SELECT * FROM v32768;
+    SELECT * FROM v32768 UNION SELECT * FROM v32768;
   }
 } {1 {too many references to "v1": max 65535}}
 ifcapable progress {
   do_test view-21.2 {
     db progress 1000 {expr 1}
     catchsql {
       SELECT * FROM v32768;
     }
   } {1 interrupted}
 }
 
 db close
 sqlite3 db :memory:
 do_execsql_test view-22.1 {
   CREATE VIEW x1 AS SELECT 123 AS '', 234 AS '', 345 AS '';
   SELECT * FROM x1;
 } {123 234 345}
 do_test view-22.2 {
   unset -nocomplain x
   db eval {SELECT * FROM x1} x break
   lsort [array names x]
 } {{} * :1 :2}
 
 
 finish_test