[sql] Remove SQLite 3.10.2 reference directory.

third_party/sqlite/sqlite-src-3100200/test/without_rowid1.test

-# 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
-}
-
-#-------------------------------------------------------------------------
-# Check that redundant UNIQUE constraints do not cause a problem.
-#
-do_execsql_test 6.0 {
-  CREATE TABLE t47(a, b UNIQUE PRIMARY KEY) WITHOUT ROWID;
-  CREATE INDEX i47 ON t47(a);
-  INSERT INTO t47 VALUES(1, 2);
-  INSERT INTO t47 VALUES(2, 4);
-  INSERT INTO t47 VALUES(3, 6);
-  INSERT INTO t47 VALUES(4, 8);
-
-  VACUUM;
-  PRAGMA integrity_check;
-  SELECT name FROM sqlite_master WHERE tbl_name = 't47';
-} {ok t47 i47}
-
-do_execsql_test 6.1 {
-  CREATE TABLE t48(
-    a UNIQUE UNIQUE, 
-    b UNIQUE, 
-    PRIMARY KEY(a), 
-    UNIQUE(a)
-  ) WITHOUT ROWID;
-  INSERT INTO t48 VALUES('a', 'b'), ('c', 'd'), ('e', 'f');
-  VACUUM;
-  PRAGMA integrity_check;
-  SELECT name FROM sqlite_master WHERE tbl_name = 't48';
-} {
-  ok  t48   sqlite_autoindex_t48_2
-}
-
-# 2015-05-28: CHECK constraints can refer to the rowid in a
-# rowid table, but not in a WITHOUT ROWID table.
-#
-do_execsql_test 7.1 {
-  CREATE TABLE t70a(
-     a INT CHECK( rowid!=33 ),
-     b TEXT PRIMARY KEY
-  );
-  INSERT INTO t70a(a,b) VALUES(99,'hello');
-} {}
-do_catchsql_test 7.2 {
-  INSERT INTO t70a(rowid,a,b) VALUES(33,99,'xyzzy');
-} {1 {CHECK constraint failed: t70a}}
-do_catchsql_test 7.3 {
-  CREATE TABLE t70b(
-     a INT CHECK( rowid!=33 ),
-     b TEXT PRIMARY KEY
-  ) WITHOUT ROWID;
-} {1 {no such column: rowid}}
-
-  
-finish_test