Import SQLite 3.8.7.4.

third_party/sqlite/src/test/fts3aux1.test

 # 2011 January 27
 #
 # 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 20 matching lines @@
 }
 
 do_execsql_test 1.2 {
   INSERT INTO t1 VALUES('one one one three three three');
   SELECT term, documents, occurrences FROM terms WHERE col = '*';
 } { 
   five  2 2     four  2 2     one   3 5     seven 1 1 
   six   1 1     three 4 6     two   1 1
 }
 
-do_execsql_test 1.3 {
-  DELETE FROM t1;
+do_execsql_test 1.3.1 { DELETE FROM t1; }
+do_execsql_test 1.3.2 {
   SELECT term, documents, occurrences FROM terms WHERE col = '*';
-} {}
+}
 
 do_execsql_test 1.4 {
   INSERT INTO t1 VALUES('a b a b a b a');
   INSERT INTO t1 SELECT * FROM t1;
   INSERT INTO t1 SELECT * FROM t1;
   INSERT INTO t1 SELECT * FROM t1;
   INSERT INTO t1 SELECT * FROM t1;
   INSERT INTO t1 SELECT * FROM t1;
   INSERT INTO t1 SELECT * FROM t1;
   INSERT INTO t1 SELECT * FROM t1;
@@ skipping 43 matching lines @@
   incr $varname
   return 1
 }
 db func rec rec
 
 # Use EQP to show that the WHERE expression "term='braid'" uses a different
 # index number (1) than "+term='braid'" (0).
 #
 do_execsql_test 2.1.1.1 {
   EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'
-} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} }
+} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} }
 do_execsql_test 2.1.1.2 {
   EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid'
-} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}}
+} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}}
 
 # Now show that using "term='braid'" means the virtual table returns
 # only 1 row to SQLite, but "+term='braid'" means all 19 are returned.
 #
 do_test 2.1.2.1 {
   set cnt 0
   execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' }
   set cnt
 } {2}
 do_test 2.1.2.2 {
@@ skipping 25 matching lines @@
 do_execsql_test 2.1.4.6 { SELECT * FROM terms_v WHERE +term='cba' } {}
 do_execsql_test 2.1.4.7 { SELECT * FROM terms_v WHERE term='abc'  } {}
 do_execsql_test 2.1.4.8 { SELECT * FROM terms_v WHERE +term='abc' } {}
 
 # Special case: term=NULL
 #
 do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}
 
 do_execsql_test 2.2.1.1 {
   EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
-} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~0 rows)} }
+} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2:} }
 do_execsql_test 2.2.1.2 {
   EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'
-} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
+} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} }
 
 do_execsql_test 2.2.1.3 {
   EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain'
-} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~0 rows)} }
+} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4:} }
 do_execsql_test 2.2.1.4 {
   EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain'
-} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
+} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} }
 
 do_execsql_test 2.2.1.5 {
   EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain'
-} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~0 rows)} }
+} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6:} }
 do_execsql_test 2.2.1.6 {
   EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
-} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
+} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} }
 
 do_test 2.2.2.1 {
   set cnt 0
   execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }
   set cnt
 } {18}
 do_test 2.2.2.2 {
   set cnt 0
   execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain' }
   set cnt
@@ skipping 143 matching lines @@
   2    0    "ORDER BY term"
   3    1    "ORDER BY term DESC"
   4    1    "ORDER BY documents ASC"
   5    1    "ORDER BY documents"
   6    1    "ORDER BY documents DESC"
   7    1    "ORDER BY occurrences ASC"
   8    1    "ORDER BY occurrences"
   9    1    "ORDER BY occurrences DESC"
 } {
 
-  set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}]
+  set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}]
   if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} }
 
   set sql "SELECT * FROM terms $orderby"
   do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res
 }
 
 #-------------------------------------------------------------------------
 # The next set of tests, fts3aux1-3.*, test error conditions in the 
 # fts4aux module. Except, fault injection testing (OOM, IO error etc.) is 
 # done in fts3fault2.test
 #
 
 do_execsql_test 3.1.1 {
   CREATE VIRTUAL TABLE t2 USING fts4;
 }
 
 do_catchsql_test 3.1.2 {
   CREATE VIRTUAL TABLE terms2 USING fts4aux;
-} {1 {wrong number of arguments to fts4aux constructor}}
+} {1 {invalid arguments to fts4aux constructor}}
 do_catchsql_test 3.1.3 {
   CREATE VIRTUAL TABLE terms2 USING fts4aux(t2, t2);
-} {1 {wrong number of arguments to fts4aux constructor}}
+} {1 {invalid arguments to fts4aux constructor}}
 
 do_execsql_test 3.2.1 {
   CREATE VIRTUAL TABLE terms3 USING fts4aux(does_not_exist)
 }
 do_catchsql_test 3.2.2 {
   SELECT * FROM terms3
 } {1 {SQL logic error or missing database}}
 do_catchsql_test 3.2.3 {
   SELECT * FROM terms3 WHERE term = 'abc'
 } {1 {SQL logic error or missing database}}
@@ skipping 32 matching lines @@
   INSERT INTO x3 SELECT term FROM terms WHERE col = '*';
 }
 
 proc do_plansql_test {tn sql r} {
   uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r]
 }
 
 do_plansql_test 4.2 {
   SELECT y FROM x2, terms WHERE y = term AND col = '*'
 } {
-  0 0 0 {SCAN TABLE x2 (~1000000 rows)} 
-  0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} 
+  0 0 0 {SCAN TABLE x2} 
+  0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} 
   a b c d e f g h i j k l
 }
 
 do_plansql_test 4.3 {
   SELECT y FROM terms, x2 WHERE y = term AND col = '*'
 } {
-  0 0 1 {SCAN TABLE x2 (~1000000 rows)} 
-  0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} 
+  0 0 1 {SCAN TABLE x2} 
+  0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} 
   a b c d e f g h i j k l
 }
 
 do_plansql_test 4.4 {
   SELECT y FROM x3, terms WHERE y = term AND col = '*'
 } {
-  0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
-  0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
+  0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 
+  0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)}
   a b c d e f g h i j k l
 }
 
 do_plansql_test 4.5 {
   SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'
 } {
-  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
-  0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
+  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 
+  0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)}
   a k l
 }
 
 #-------------------------------------------------------------------------
 # The following tests check that fts4aux can handle an fts table with an
 # odd name (one that requires quoting for use in SQL statements). And that
 # the argument to the fts4aux constructor is properly dequoted before use.
 #
-#
 do_execsql_test 5.1 {
   CREATE VIRTUAL TABLE "abc '!' def" USING fts4(x, y);
   INSERT INTO "abc '!' def" VALUES('XX', 'YY');
 
   CREATE VIRTUAL TABLE terms3 USING fts4aux("abc '!' def");
   SELECT * FROM terms3;
 } {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1}
 
 do_execsql_test 5.2 {
   CREATE VIRTUAL TABLE "%%^^%%" USING fts4aux('abc ''!'' def');
   SELECT * FROM "%%^^%%";
 } {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1}
 
+#-------------------------------------------------------------------------
+# Test that we can create an fts4aux table in the temp database.
+#
+forcedelete test.db2
+do_execsql_test 6.1 {
+  CREATE VIRTUAL TABLE ft1 USING fts4(x, y);
+  INSERT INTO ft1 VALUES('a b', 'c d');
+  INSERT INTO ft1 VALUES('e e', 'c d');
+  INSERT INTO ft1 VALUES('a a', 'b b');
+  CREATE VIRTUAL TABLE temp.aux1 USING fts4aux(main, ft1);
+  SELECT * FROM aux1;
+} {
+    a * 2 3 a 0 2 3 
+    b * 2 3 b 0 1 1 b 1 1 2 
+    c * 2 2 c 1 2 2 
+    d * 2 2 d 1 2 2 
+    e * 1 2 e 0 1 2
+}
+
+do_execsql_test 6.2 {
+  ATTACH 'test.db2' AS att;
+  CREATE VIRTUAL TABLE att.ft1 USING fts4(x, y);
+  INSERT INTO att.ft1 VALUES('v w', 'x y');
+  INSERT INTO att.ft1 VALUES('z z', 'x y');
+  INSERT INTO att.ft1 VALUES('v v', 'w w');
+  CREATE VIRTUAL TABLE temp.aux2 USING fts4aux(att, ft1);
+  SELECT * FROM aux2;
+} {
+    v * 2 3 v 0 2 3 
+    w * 2 3 w 0 1 1 w 1 1 2 
+    x * 2 2 x 1 2 2 
+    y * 2 2 y 1 2 2 
+    z * 1 2 z 0 1 2
+}
+
+foreach {tn q res1 res2} {
+  1  { SELECT * FROM %%% WHERE term = 'a' } {a * 2 3 a 0 2 3} {}
+  2  { SELECT * FROM %%% WHERE term = 'x' } {} {x * 2 2 x 1 2 2} 
+
+  3  { SELECT * FROM %%% WHERE term >= 'y' } 
+     {} {y * 2 2 y 1 2 2 z * 1 2 z 0 1 2}
+
+  4  { SELECT * FROM %%% WHERE term <= 'c' } 
+     {a * 2 3 a 0 2 3 b * 2 3 b 0 1 1 b 1 1 2 c * 2 2 c 1 2 2} {}
+} {
+  set sql1 [string map {%%% aux1} $q]
+  set sql2 [string map {%%% aux2} $q]
+
+  do_execsql_test 7.$tn.1 $sql1 $res1
+  do_execsql_test 7.$tn.2 $sql2 $res2
+}
+
+do_test 8.1 {
+  catchsql { CREATE VIRTUAL TABLE att.aux3 USING fts4aux(main, ft1) }
+} {1 {invalid arguments to fts4aux constructor}}
+
+do_test 8.2 {
+  execsql {DETACH att}
+  catchsql { SELECT * FROM aux2 }
+} {1 {SQL logic error or missing database}}
 
 finish_test