[sql] Import SQLite 3.17.0.

third_party/sqlite/src/ext/fts5/test/fts5vocab.test

 # 2015 Apr 24
 #
 # 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.
 #
 #***********************************************************************
 #
 # The tests in this file focus on testing the fts5vocab module.
 #
 
 source [file join [file dirname [info script]] fts5_common.tcl]
 set testprefix fts5vocab
 
 # If SQLITE_ENABLE_FTS5 is defined, omit this file.
 ifcapable !fts5 {
   finish_test
   return
 }
 
+foreach_detail_mode $testprefix {
+
+proc null_list_entries {iFirst nInterval L} {
+  for {set i $iFirst} {$i < [llength $L]} {incr i $nInterval} {
+    lset L $i {}
+  }
+  return $L
+}
+
+proc star_from_row {L} {
+  if {[detail_is_full]==0} {
+    set L [null_list_entries 2 3 $L]
+  }
+  return $L
+}
+
+proc star_from_col {L} {
+  if {[detail_is_col]} {
+    set L [null_list_entries 3 4 $L]
+  }
+  if {[detail_is_none]} {
+    set L [null_list_entries 1 4 $L]
+    set L [null_list_entries 3 4 $L]
+  }
+  return $L
+}
+
+proc row_to_col {L} {
+  if {[detail_is_none]==0} { error "this is for detail=none mode" }
+  set ret [list]
+  foreach {a b c} $L {
+    lappend ret $a {} $b {}
+  }
+  set ret
+}
+
+if 1 {
 
 do_execsql_test 1.1.1 {
-  CREATE VIRTUAL TABLE t1 USING fts5(one, prefix=1);
+  CREATE VIRTUAL TABLE t1 USING fts5(one, prefix=1, detail=%DETAIL%);
   CREATE VIRTUAL TABLE v1 USING fts5vocab(t1, 'row');
   PRAGMA table_info = v1;
 } {
   0 term {} 0 {} 0
   1 doc {} 0 {} 0
   2 cnt {} 0 {} 0
 }
 
 do_execsql_test 1.1.2 {
   CREATE VIRTUAL TABLE v2 USING fts5vocab(t1, 'col');
   PRAGMA table_info = v2;
 } {
   0 term {} 0 {} 0
   1 col {} 0 {} 0
   2 doc {} 0 {} 0
   3 cnt {} 0 {} 0
 }
 
 do_execsql_test 1.2.1 { SELECT * FROM v1 } { }
 do_execsql_test 1.2.2 { SELECT * FROM v2 } { }
 
 do_execsql_test 1.3 {
   INSERT INTO t1 VALUES('x y z');
   INSERT INTO t1 VALUES('x x x');
 }
 
 do_execsql_test 1.4.1 {
   SELECT * FROM v1;
-} {x 2 4  y 1 1  z 1 1}
+} [star_from_row {x 2 4  y 1 1  z 1 1}]
 
 do_execsql_test 1.4.2 {
   SELECT * FROM v2;
-} {x one 2 4  y one 1 1  z one 1 1}
+} [star_from_col {x one 2 4  y one 1 1  z one 1 1}]
 
 do_execsql_test 1.5.1 {
   BEGIN;
     INSERT INTO t1 VALUES('a b c');
     SELECT * FROM v1 WHERE term<'d';
-} {a 1 1   b 1 1   c 1 1}
+} [star_from_row {a 1 1   b 1 1   c 1 1}]
 
 do_execsql_test 1.5.2 {
     SELECT * FROM v2 WHERE term<'d';
   COMMIT;
-} {a one 1 1  b one 1 1  c one 1 1}
+} [star_from_col {a one 1 1  b one 1 1  c one 1 1}]
 
 do_execsql_test 1.6 {
   DELETE FROM t1 WHERE one = 'a b c';
   SELECT * FROM v1;
-} {x 2 4  y 1 1  z 1 1}
+} [star_from_row {x 2 4  y 1 1  z 1 1}]
 
 #-------------------------------------------------------------------------
 #
 do_execsql_test 2.0 {
-  CREATE VIRTUAL TABLE tt USING fts5(a, b);
+  CREATE VIRTUAL TABLE tt USING fts5(a, b, detail=%DETAIL%);
   INSERT INTO tt VALUES('d g b f d f', 'f c e c d a');
   INSERT INTO tt VALUES('f a e a a b', 'e d c f d d');
   INSERT INTO tt VALUES('b c a a a b', 'f f c c b c');
   INSERT INTO tt VALUES('f d c a c e', 'd g d e g d');
   INSERT INTO tt VALUES('g d e f a g x', 'f f d a a b');
   INSERT INTO tt VALUES('g c f b c g', 'a g f d c b');
   INSERT INTO tt VALUES('c e c f g b', 'f e d b g a');
   INSERT INTO tt VALUES('g d e f d e', 'a c d b a g');
   INSERT INTO tt VALUES('e f a c c b', 'b f e a f d y');
   INSERT INTO tt VALUES('c c a a c f', 'd g a e b g');
 }
 
-set res_col {
+set res_row [star_from_row {
+  a 10 20   b 9 14   c 9 20   d 9 19   
+  e 8 13   f 10 20   g 7 14   x 1 1   
+  y 1 1
+}]
+set res_col [star_from_col {
   a a 6 11    a b 7 9
   b a 6 7     b b 7 7 
   c a 6 12    c b 5 8 
   d a 4 6     d b 9 13 
   e a 6 7     e b 6 6 
   f a 9 10    f b 7 10 
   g a 5 7     g b 5 7
   x a 1 1     y b 1 1
-}
-set res_row {
-  a 10 20   b 9 14   c 9 20   d 9 19   
-  e 8 13   f 10 20   g 7 14   x 1 1   
-  y 1 1
+}]
+if {[detail_is_none]} {
+  set res_col [row_to_col $res_row]
 }
 
 foreach {tn tbl resname} {
   1 "fts5vocab(tt, 'col')" res_col
   2 "fts5vocab(tt, 'row')" res_row
   3 "fts5vocab(tt, \"row\")" res_row
   4 "fts5vocab(tt, [row])" res_row
   5 "fts5vocab(tt, `row`)" res_row
 
   6 "fts5vocab('tt', 'row')" res_row
@@ skipping 29 matching lines @@
   CREATE VIRTUAL TABLE aux.cc USING fts5vocab(main, tbl, row);
 } {1 {wrong number of vtable arguments}}
 
 #-------------------------------------------------------------------------
 # Test fts5vocab tables created in the temp schema. 
 #
 reset_db
 forcedelete test.db2
 do_execsql_test 5.0 {
   ATTACH 'test.db2' AS aux;
-  CREATE VIRTUAL TABLE t1 USING fts5(x);
-  CREATE VIRTUAL TABLE temp.t1 USING fts5(x);
-  CREATE VIRTUAL TABLE aux.t1 USING fts5(x);
+  CREATE VIRTUAL TABLE t1 USING fts5(x, detail=%DETAIL%);
+  CREATE VIRTUAL TABLE temp.t1 USING fts5(x, detail=%DETAIL%);
+  CREATE VIRTUAL TABLE aux.t1 USING fts5(x, detail=%DETAIL%);
 
   INSERT INTO main.t1 VALUES('a b c');
   INSERT INTO main.t1 VALUES('d e f');
   INSERT INTO main.t1 VALUES('a e c');
 
   INSERT INTO temp.t1 VALUES('1 2 3');
   INSERT INTO temp.t1 VALUES('4 5 6');
   INSERT INTO temp.t1 VALUES('1 5 3');
 
   INSERT INTO aux.t1 VALUES('x y z');
   INSERT INTO aux.t1 VALUES('m n o');
   INSERT INTO aux.t1 VALUES('x n z');
 }
 
 breakpoint
 do_execsql_test 5.1 {
   CREATE VIRTUAL TABLE temp.vm  USING fts5vocab(main, t1, row);
   CREATE VIRTUAL TABLE temp.vt1 USING fts5vocab(t1, row);
   CREATE VIRTUAL TABLE temp.vt2 USING fts5vocab(temp, t1, row);
   CREATE VIRTUAL TABLE temp.va  USING fts5vocab(aux, t1, row);
 }
 
-do_execsql_test 5.2 { SELECT * FROM vm } {
+do_execsql_test 5.2 { SELECT * FROM vm } [star_from_row {
   a 2 2 b 1 1 c 2 2 d 1 1 e 2 2 f 1 1
-}
-do_execsql_test 5.3 { SELECT * FROM vt1 } {
+}]
+do_execsql_test 5.3 { SELECT * FROM vt1 } [star_from_row {
   1 2 2 2 1 1 3 2 2 4 1 1 5 2 2 6 1 1
-}
-do_execsql_test 5.4 { SELECT * FROM vt2 } {
+}]
+do_execsql_test 5.4 { SELECT * FROM vt2 } [star_from_row {
   1 2 2 2 1 1 3 2 2 4 1 1 5 2 2 6 1 1
-}
-do_execsql_test 5.5 { SELECT * FROM va } {
+}]
+do_execsql_test 5.5 { SELECT * FROM va } [star_from_row {
   m 1 1 n 2 2 o 1 1 x 2 2 y 1 1 z 2 2
-}
+}]
 
 #-------------------------------------------------------------------------
 #
 do_execsql_test 6.0 {
   CREATE TABLE iii(iii);
   CREATE TABLE jjj(x);
 }
 
 do_catchsql_test 6.1 {
   CREATE VIRTUAL TABLE vocab1 USING fts5vocab(iii, row);
   SELECT * FROM vocab1;
 } {1 {no such fts5 table: main.iii}}
 
 do_catchsql_test 6.2 {
   CREATE VIRTUAL TABLE vocab2 USING fts5vocab(jjj, row);
   SELECT * FROM vocab2;
 } {1 {no such fts5 table: main.jjj}}
 
 do_catchsql_test 6.2 {
   CREATE VIRTUAL TABLE vocab3 USING fts5vocab(lll, row);
   SELECT * FROM vocab3;
 } {1 {no such fts5 table: main.lll}}
 
 #-------------------------------------------------------------------------
 # Test single term queries on fts5vocab tables (i.e. those with term=?
 # constraints in the WHERE clause).
 #
 do_execsql_test 7.0 {
-  CREATE VIRTUAL TABLE tx USING fts5(one, two);
+  CREATE VIRTUAL TABLE tx USING fts5(one, two, detail=%DETAIL%);
   INSERT INTO tx VALUES('g a ggg g a b eee',      'cc d aa ff g ee');
   INSERT INTO tx VALUES('dd fff i a i jjj',       'f fff hh jj e f');
   INSERT INTO tx VALUES('ggg a f f fff dd aa',    'd ggg f f j gg ddd');
   INSERT INTO tx VALUES('e bb h jjj ii gg',       'e aa e f c fff');
   INSERT INTO tx VALUES('j ff aa a h',            'h a j bbb bb');
   INSERT INTO tx VALUES('cc i ff c d f',          'dd ii fff f c cc d');
   INSERT INTO tx VALUES('jjj g i bb cc eee',      'hhh iii aaa b bbb aaa');
   INSERT INTO tx VALUES('hhh hhh hhh bb fff f',   'fff gg aa ii h a');
   INSERT INTO tx VALUES('b c cc aaa iii ggg f',   'iii ff ee a ff c cc');
   INSERT INTO tx VALUES('hhh b hhh aaa j i i',    'dd ee ee aa bbb iii');
@@ skipping 37 matching lines @@
     SELECT $term, 'one', sum(cont(one, $term)>0), sum(cont(one, $term)) FROM tx
   }]
   if {[lindex $r1 2]==0} {set r1 [list]}
 
   set r2 [db eval {
     SELECT $term, 'two', sum(cont(two, $term)>0), sum(cont(two, $term)) FROM tx
   }]
   if {[lindex $r2 2]==0} {set r2 [list]}
 
   set resc [concat $r1 $r2]
+
+  set resc [star_from_col $resc]
+  set resr [star_from_row $resr]
+  if {[detail_is_none]} { set resc [row_to_col $resr] }
   do_execsql_test 7.$term.1 {SELECT * FROM txc WHERE term=$term} $resc
   do_execsql_test 7.$term.2 {SELECT * FROM txr WHERE term=$term} $resr
 }
 
 do_execsql_test 7.1 {
   CREATE TABLE txr_c AS SELECT * FROM txr;
   CREATE TABLE txc_c AS SELECT * FROM txc;
 }
 
 # Test range queries on the fts5vocab tables created above.
@@ skipping 44 matching lines @@
   } [db eval {SELECT * FROM txc_c WHERE term<$b}]
   do_execsql_test 7.2.$tn.12 {
     SELECT * FROM txc WHERE term>$a AND term<$b
   } [db eval {SELECT * FROM txc_c WHERE term>$a AND term <$b}]
 }
 
 do_execsql_test 7.3.1 {
   SELECT count(*) FROM txr, txr_c WHERE txr.term = txr_c.term;
 } {30}
 
-do_execsql_test 7.3.2 {
-  SELECT count(*) FROM txc, txc_c 
-  WHERE txc.term = txc_c.term AND txc.col=txc_c.col;
-} {57}
+if {![detail_is_none]} {
+  do_execsql_test 7.3.2 {
+    SELECT count(*) FROM txc, txc_c
+      WHERE txc.term = txc_c.term AND txc.col=txc_c.col;
+  } {57}
+}
+
+}
+
+#-------------------------------------------------------------------------
+# Test the fts5vocab tables response to a specific types of corruption:
+# where the fts5 index contains hits for columns that do not exist.
+#
+do_execsql_test 8.0 {
+  CREATE VIRTUAL TABLE x1 USING fts5(a, b, c, detail=%DETAIL%);
+  INSERT INTO x1 VALUES('a b c', 'd e f', 'g h i');
+  INSERT INTO x1 VALUES('g h i', 'a b c', 'd e f');
+  INSERT INTO x1 VALUES('d e f', 'g h i', 'a b c');
+  CREATE VIRTUAL TABLE x1_r USING fts5vocab(x1, row);
+  CREATE VIRTUAL TABLE x1_c USING fts5vocab(x1, col);
+}
+
+set resr [star_from_row {a 3 3 b 3 3 c 3 3 d 3 3 e 3 3 f 3 3 g 3 3 h 3 3 i 3 3}]
+set resc [star_from_col {
+  a a 1 1 a b 1 1 a c 1 1 b a 1 1 
+  b b 1 1 b c 1 1 c a 1 1 c b 1 1 
+  c c 1 1 d a 1 1 d b 1 1 d c 1 1
+  e a 1 1 e b 1 1 e c 1 1 f a 1 1 
+  f b 1 1 f c 1 1 g a 1 1 g b 1 1 
+  g c 1 1 h a 1 1 h b 1 1 h c 1 1 
+  i a 1 1 i b 1 1 i c 1 1
+}]
+if {[detail_is_none]} { set resc [row_to_col $resr] }
+
+do_execsql_test 8.1.1 { SELECT * FROM x1_r; } $resr
+do_execsql_test 8.1.2 { SELECT * FROM x1_c } $resc
+
+do_execsql_test 8.2 {
+  PRAGMA writable_schema = 1;
+  UPDATE sqlite_master 
+  SET sql = 'CREATE VIRTUAL TABLE x1 USING fts5(a, detail=%DETAIL%)'
+  WHERE name = 'x1';
+}
+db close
+sqlite3 db test.db
+sqlite3_fts5_may_be_corrupt 1
+
+do_execsql_test 8.2.1 { SELECT * FROM x1_r } $resr
+
+if {[detail_is_none]} {
+  do_execsql_test 8.2.2 { SELECT * FROM x1_c } $resc
+} else {
+  do_catchsql_test 8.2.2 { 
+    SELECT * FROM x1_c 
+  } {1 {database disk image is malformed}}
+}
+
+sqlite3_fts5_may_be_corrupt 0
+}
+
+#-------------------------------------------------------------------------
+# Test that both "ORDER BY term" and "ORDER BY term DESC" work.
+#
+reset_db
+do_execsql_test 9.1 {
+  CREATE VIRTUAL TABLE x1 USING fts5(x);
+  INSERT INTO x1 VALUES('def ABC ghi');
+  INSERT INTO x1 VALUES('DEF abc GHI');
+}
+
+do_execsql_test 9.2 {
+  CREATE VIRTUAL TABLE rrr USING fts5vocab(x1, row);
+  SELECT * FROM rrr
+} {
+  abc 2 2 def 2 2 ghi 2 2
+}
+do_execsql_test 9.3 {
+  SELECT * FROM rrr ORDER BY term ASC
+} {
+  abc 2 2 def 2 2 ghi 2 2
+}
+do_execsql_test 9.4 {
+  SELECT * FROM rrr ORDER BY term DESC
+} {
+  ghi 2 2 def 2 2 abc 2 2 
+}
+do_test 9.5 {
+  set e2 [db eval { EXPLAIN SELECT * FROM rrr ORDER BY term ASC }]
+  expr [lsearch $e2 SorterSort]<0
+} 1
+do_test 9.6 {
+  set e2 [db eval { EXPLAIN SELECT * FROM rrr ORDER BY term DESC }]
+  expr [lsearch $e2 SorterSort]<0
+} 0
+
+
 
 finish_test