[sql] Import reference version of SQLite 3.8.7.4.

third_party/sqlite/sqlite-src-3080704/test/tableopts.test

+# 2013-10-19
+#
+# 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.
+#
+#***********************************************************************
+#
+# Test the operation of table-options in the WITH clause of the
+# CREATE TABLE statement.
+#
+
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+do_test tableopt-1.1 {
+  catchsql {
+    CREATE TABLE t1(a,b) WITHOUT rowid;
+  }
+} {1 {PRIMARY KEY missing on table t1}}
+do_test tableopt-1.1b {
+  catchsql {
+    CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT,b) WITHOUT rowid;
+  }
+} {1 {AUTOINCREMENT not allowed on WITHOUT ROWID tables}}
+do_test tableopt-1.2 {
+  catchsql {
+    CREATE TABLE t1(a,b) WITHOUT unknown2;
+  }
+} {1 {unknown table option: unknown2}}
+
+do_execsql_test tableopt-2.1 {
+  CREATE TABLE t1(a, b, c, PRIMARY KEY(a,b)) WITHOUT rowid;
+  INSERT INTO t1 VALUES(1,2,3),(2,3,4);
+  SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;
+} {3 4}
+do_test tableopt-2.1.1 {
+  catchsql {
+    SELECT rowid, * FROM t1;
+  }
+} {1 {no such column: rowid}}
+do_test tableopt-2.1.2 {
+  catchsql {
+    SELECT _rowid_, * FROM t1;
+  }
+} {1 {no such column: _rowid_}}
+do_test tableopt-2.1.3 {
+  catchsql {
+    SELECT oid, * FROM t1;
+  }
+} {1 {no such column: oid}}
+do_execsql_test tableopt-2.2 {
+  VACUUM;
+  SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;
+} {3 4}
+do_test tableopt-2.3 {
+  sqlite3 db2 test.db
+  db2 eval {SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;}
+} {3 4}
+db2 close
+
+# Make sure the "without" keyword is still usable as a table or
+# column name.
+#
+do_execsql_test tableopt-3.1 {
+  CREATE TABLE without(x INTEGER PRIMARY KEY, without TEXT);
+  INSERT INTO without VALUES(1, 'xyzzy'), (2, 'fizzle');
+  SELECT * FROM without WHERE without='xyzzy';
+} {1 xyzzy}
+
+  
+finish_test