1 files changed, 281 insertions, 0 deletions
diff --git a/test/without_rowid1.test b/test/without_rowid1.test
new file mode 100644
index 0000000..9d7a643
--- /dev/null
+++ b/test/without_rowid1.test
@@ -0,0 +1,281 @@
+# 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}
+
+############################################################################
+# Ticket [c34d0557f740c450709d6e33df72d4f3f651a3cc]
+# Name resolution issue with WITHOUT ROWID
+#
+do_execsql_test 4.1 {
+  CREATE TABLE t41(a PRIMARY KEY) WITHOUT ROWID;
+  INSERT INTO t41 VALUES('abc');
+  CREATE TABLE t42(x);
+  INSERT INTO t42 VALUES('xyz');
+  SELECT t42.rowid FROM t41, t42;
+} {1}
+do_execsql_test 4.2 {
+  SELECT t42.rowid FROM t42, t41;
+} {1}
+
+
+#--------------------------------------------------------------------------
+# The following tests verify that the trailing PK fields added to each
+# entry in an index on a WITHOUT ROWID table are used correctly.
+#
+do_execsql_test 5.0 {
+  CREATE TABLE t45(a PRIMARY KEY, b, c) WITHOUT ROWID;
+  CREATE INDEX i45 ON t45(b);
+
+  INSERT INTO t45 VALUES(2, 'one', 'x');
+  INSERT INTO t45 VALUES(4, 'one', 'x');
+  INSERT INTO t45 VALUES(6, 'one', 'x');
+  INSERT INTO t45 VALUES(8, 'one', 'x');
+  INSERT INTO t45 VALUES(10, 'one', 'x');
+
+  INSERT INTO t45 VALUES(1, 'two', 'x');
+  INSERT INTO t45 VALUES(3, 'two', 'x');
+  INSERT INTO t45 VALUES(5, 'two', 'x');
+  INSERT INTO t45 VALUES(7, 'two', 'x');
+  INSERT INTO t45 VALUES(9, 'two', 'x');
+}
+
+do_eqp_test 5.1 {
+  SELECT * FROM t45 WHERE b=? AND a>?
+} {/*USING INDEX i45 (b=? AND a>?)*/}
+
+do_execsql_test 5.2 {
+  SELECT * FROM t45 WHERE b='two' AND a>4
+} {5 two x 7 two x 9 two x}
+
+do_execsql_test 5.3 {
+  SELECT * FROM t45 WHERE b='one' AND a<8
+} { 2 one x 4 one x 6 one x }
+
+do_execsql_test 5.4 {
+  CREATE TABLE t46(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID;
+  WITH r(x) AS (
+    SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<100
+  )
+  INSERT INTO t46 SELECT x / 20, x % 20, x % 10, x FROM r;
+}
+
+set queries {
+  1    2    "c = 5 AND a = 1"          {/*i46 (c=? AND a=?)*/}
+  2    6    "c = 4 AND a < 3"          {/*i46 (c=? AND a<?)*/}
+  3    4    "c = 2 AND a >= 3"         {/*i46 (c=? AND a>?)*/}
+  4    1    "c = 2 AND a = 1 AND b<10" {/*i46 (c=? AND a=? AND b<?)*/}
+  5    1    "c = 0 AND a = 0 AND b>5"  {/*i46 (c=? AND a=? AND b>?)*/}
+}
+
+foreach {tn cnt where eqp} $queries {
+  do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
+}
+
+do_execsql_test 5.6 {
+  CREATE INDEX i46 ON t46(c);
+}
+
+foreach {tn cnt where eqp} $queries {
+  do_execsql_test 5.7.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
+  do_eqp_test 5.7.$tn.2  "SELECT count(*) FROM t46 WHERE $where" $eqp
+}
+
+  
+finish_test