1 files changed, 203 insertions, 0 deletions
diff --git a/lang/sql/sqlite/test/without_rowid1.test b/lang/sql/sqlite/test/without_rowid1.test
new file mode 100644
index 00000000..1a53d26e
--- /dev/null
+++ b/lang/sql/sqlite/test/without_rowid1.test
@@ -0,0 +1,203 @@
+# 2013-10-30
+#
+# 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.
+#
+#***********************************************************************
+#
+# This file implements regression tests for SQLite library.  The
+# focus of this file is testing WITHOUT ROWID tables.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix without_rowid1
+
+# Create and query a WITHOUT ROWID table.
+#
+do_execsql_test without_rowid1-1.0 {
+  CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID;
+  CREATE INDEX t1bd ON t1(b, d);
+  INSERT INTO t1 VALUES('journal','sherman','ammonia','helena');
+  INSERT INTO t1 VALUES('dynamic','juliet','flipper','command');
+  INSERT INTO t1 VALUES('journal','sherman','gamma','patriot');
+  INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena');
+  SELECT *, '|' FROM t1 ORDER BY c, a;
+} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
+
+integrity_check without_rowid1-1.0ic
+
+do_execsql_test without_rowid1-1.1 {
+  SELECT *, '|' FROM t1 ORDER BY +c, a;
+} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
+
+do_execsql_test without_rowid1-1.2 {
+  SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC;
+} {journal sherman gamma patriot | dynamic juliet flipper command | journal sherman ammonia helena | arctic sleep ammonia helena |}
+
+do_execsql_test without_rowid1-1.11 {
+  SELECT *, '|' FROM t1 ORDER BY b, d;
+} {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
+
+do_execsql_test without_rowid1-1.12 {
+  SELECT *, '|' FROM t1 ORDER BY +b, d;
+} {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
+
+# Trying to insert a duplicate PRIMARY KEY fails.
+#
+do_test without_rowid1-1.21 {
+  catchsql {
+    INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard');
+  }
+} {1 {UNIQUE constraint failed: t1.c, t1.a}}
+
+# REPLACE INTO works, however.
+#
+do_execsql_test without_rowid1-1.22 {
+  REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard');
+  SELECT *, '|' FROM t1 ORDER BY c, a;
+} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |}
+
+do_execsql_test without_rowid1-1.23 {
+  SELECT *, '|' FROM t1 ORDER BY b, d;
+} {dynamic phone flipper harvard | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
+
+# UPDATE statements.
+#
+do_execsql_test without_rowid1-1.31 {
+  UPDATE t1 SET d=3.1415926 WHERE a='journal';
+  SELECT *, '|' FROM t1 ORDER BY c, a;
+} {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | dynamic phone flipper harvard | journal sherman gamma 3.1415926 |}
+do_execsql_test without_rowid1-1.32 {
+  SELECT *, '|' FROM t1 ORDER BY b, d;
+} {dynamic phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
+
+do_execsql_test without_rowid1-1.35 {
+  UPDATE t1 SET a=1250 WHERE b='phone';
+  SELECT *, '|' FROM t1 ORDER BY c, a;
+} {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | 1250 phone flipper harvard | journal sherman gamma 3.1415926 |}
+integrity_check without_rowid1-1.36
+
+do_execsql_test without_rowid1-1.37 {
+  SELECT *, '|' FROM t1 ORDER BY b, d;
+} {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
+
+do_execsql_test without_rowid1-1.40 {
+  VACUUM;
+  SELECT *, '|' FROM t1 ORDER BY b, d;
+} {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
+integrity_check without_rowid1-1.41
+
+# Verify that ANALYZE works
+#
+do_execsql_test without_rowid1-1.50 {
+  ANALYZE;
+  SELECT * FROM sqlite_stat1 ORDER BY idx;
+} {t1 t1 {4 2 1} t1 t1bd {4 2 2}}
+ifcapable stat3 {
+  do_execsql_test without_rowid1-1.51 {
+    SELECT DISTINCT tbl, idx FROM sqlite_stat3 ORDER BY idx;
+  } {t1 t1 t1 t1bd}
+}
+ifcapable stat4 {
+  do_execsql_test without_rowid1-1.52 {
+    SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx;
+  } {t1 t1 t1 t1bd}
+}
+
+#----------
+
+do_execsql_test 2.1.1 {
+  CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
+  INSERT INTO t4 VALUES('abc', 'def');
+  SELECT * FROM t4;
+} {abc def}
+do_execsql_test 2.1.2 {
+  UPDATE t4 SET a = 'ABC';
+  SELECT * FROM t4;
+} {ABC def}
+
+do_execsql_test 2.2.1 {
+  DROP TABLE t4;
+  CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
+  INSERT INTO t4(a, b) VALUES('abc', 'def');
+  SELECT * FROM t4;
+} {def abc}
+
+do_execsql_test 2.2.2 {
+  UPDATE t4 SET a = 'ABC', b = 'xyz';
+  SELECT * FROM t4;
+} {xyz ABC}
+
+do_execsql_test 2.3.1 {
+  CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
+  INSERT INTO t5(a, b) VALUES('abc', 'def');
+  UPDATE t5 SET a='abc', b='def';
+} {}
+
+do_execsql_test 2.4.1 {
+  CREATE TABLE t6 (
+    a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
+  ) WITHOUT ROWID;
+
+  INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
+  UPDATE t6 SET a='ABC', c='ghi';
+} {}
+
+do_execsql_test 2.4.2 {
+  SELECT * FROM t6 ORDER BY b, a;
+  SELECT * FROM t6 ORDER BY c;
+} {ABC def ghi ABC def ghi}
+
+#-------------------------------------------------------------------------
+# Unless the destination table is completely empty, the xfer optimization 
+# is disabled for WITHOUT ROWID tables. The following tests check for
+# some problems that might occur if this were not the case.
+#
+reset_db
+do_execsql_test 3.1.1 {
+  CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
+  CREATE UNIQUE INDEX i1 ON t1(b);
+
+  CREATE TABLE t2(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
+  CREATE UNIQUE INDEX i2 ON t2(b);
+
+  INSERT INTO t1 VALUES('one', 'two');
+  INSERT INTO t2 VALUES('three', 'two');
+}
+
+do_execsql_test 3.1.2 {
+  INSERT OR REPLACE INTO t1 SELECT * FROM t2;
+  SELECT * FROM t1;
+} {three two}
+
+do_execsql_test 3.1.3 {
+  DELETE FROM t1;
+  INSERT INTO t1 SELECT * FROM t2;
+  SELECT * FROM t1;
+} {three two}
+
+do_catchsql_test 3.1.4 {
+  INSERT INTO t2 VALUES('four', 'four');
+  INSERT INTO t2 VALUES('six', 'two');
+  INSERT INTO t1 SELECT * FROM t2;
+} {1 {UNIQUE constraint failed: t2.b}}
+
+do_execsql_test 3.1.5 {
+  CREATE TABLE t3(a PRIMARY KEY);
+  CREATE TABLE t4(a PRIMARY KEY);
+
+  INSERT INTO t4 VALUES('i');
+  INSERT INTO t4 VALUES('ii');
+  INSERT INTO t4 VALUES('iii');
+
+  INSERT INTO t3 SELECT * FROM t4;
+  SELECT * FROM t3;
+} {i ii iii}
+  
+finish_test
+