AryaWu/sqlite

1# 2010 February 022#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#*************************************************************************11# This file implements regression tests for SQLite library.  The12# focus of this script is testing the FTS4 module.13#14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# If SQLITE_ENABLE_FTS3 is defined, omit this file.20ifcapable !fts3 {21  finish_test22  return23}24 25# Create the fts_kjv_genesis procedure which fills and FTS3/4 table with26# the complete text of the Book of Genesis.27#28source $testdir/genesis.tcl29 30# The following is a list of queries to perform against the above31# FTS3/FTS4 database.  We will be trying these queries in various32# configurations to ensure that they always return the same answers.33#34set fts4aa_queries {35  {abraham}36  {the king}37  {"the king"}38  {abraham OR joseph}39  {ab* OR jos*}40  {lived t*}41  {spake hebrew}42  {melchizedek}43  {t* melchizedek}44  {melchizedek t*}45}46unset -nocomplain fts4aa_res47 48# Set up the baseline results49#50do_test fts4aa-1.0 {51  db eval {52    CREATE VIRTUAL TABLE t1 USING fts4(words, tokenize porter);53  }54  fts_kjv_genesis55  foreach q $::fts4aa_queries {56    set r [db eval {SELECT docid FROM t1 WHERE words MATCH $q ORDER BY docid}]57    set ::fts4aa_res($q) $r58  }59} {}60 61# Legacy test cases62#63do_test fts4aa-1.1 {64  db eval {65    SELECT docid FROM t1 EXCEPT SELECT docid FROM t1_docsize66  }67} {}68do_test fts4aa-1.2 {69  db eval {70    SELECT docid FROM t1_docsize EXCEPT SELECT docid FROM t171  }72} {}73 74proc mit {blob} {75  set scan(littleEndian) i*76  set scan(bigEndian) I*77  binary scan $blob $scan($::tcl_platform(byteOrder)) r78  return $r79}80db func mit mit81 82do_test fts4aa-1.3 {83  db eval {84    SELECT docid, mit(matchinfo(t1, 'pcxnal')) FROM t1 WHERE t1 MATCH 'melchizedek';85  }86} {1014018 {1 1 1 1 1 1533 25 20}}87do_test fts4aa-1.4 {88  db eval {89    SELECT docid, mit(matchinfo(t1, 'pcxnal')) FROM t190     WHERE t1 MATCH 'spake hebrew'91     ORDER BY docid;92  }93} {1039014 {2 1 1 40 40 1 6 6 1533 25 42} 1039017 {2 1 1 40 40 1 6 6 1533 25 26}}94do_test fts4aa-1.5 {95  db eval {96    SELECT docid, mit(matchinfo(t1, 'pcxnal')) FROM t197     WHERE t1 MATCH 'laban overtook jacob'98     ORDER BY docid;99  }100} {1031025 {3 1 2 54 46 1 3 3 2 181 160 1533 25 24}}101 102do_test fts4aa-1.6 {103  db eval {104    DELETE FROM t1 WHERE docid!=1050026;105    SELECT hex(size) FROM t1_docsize;106    SELECT hex(value) FROM t1_stat;107  }108} {17 01176F}109 110do_test fts4aa-1.7 {111  db eval {112    SELECT docid FROM t1 EXCEPT SELECT docid FROM t1_docsize113  }114} {}115do_test fts4aa-1.8 {116  db eval {117    SELECT docid FROM t1_docsize EXCEPT SELECT docid FROM t1118  }119} {}120ifcapable fts4_deferred {121  do_test fts4aa-1.9 {122    # Note: Token 'in' is being deferred in the following query. 123    db eval {124      SELECT docid, mit(matchinfo(t1, 'pcxnal')) FROM t1125       WHERE t1 MATCH 'joseph died in egypt'126       ORDER BY docid;127    }128  } {1050026 {4 1 1 1 1 1 1 1 2 1 1 1 1 1 1 23 23}}129}130 131# Should get the same search results from FTS3132#133do_test fts4aa-2.0 {134  db eval {135    DROP TABLE t1;136    CREATE VIRTUAL TABLE t1 USING fts3(words, tokenize porter);137  }138  fts_kjv_genesis139} {}140unset -nocomplain ii141set ii 0142foreach {q r} [array get fts4aa_res] {143  incr ii144  do_test fts4aa-2.$ii {145    db eval {SELECT docid FROM t1 WHERE words MATCH $::q ORDER BY docid}146  } $r147}148 149# Should get the same search results when the page size is very large150#151do_test fts4aa-3.0 {152  db close153  forcedelete test.db154  sqlite3 db test.db155  db eval {156    PRAGMA page_size=65536;157    CREATE VIRTUAL TABLE t1 USING fts4(words, tokenize porter);158  }159  fts_kjv_genesis160} {}161unset -nocomplain ii162set ii 0163foreach {q r} [array get fts4aa_res] {164  incr ii165  do_test fts4aa-3.$ii {166    db eval {SELECT docid FROM t1 WHERE words MATCH $::q ORDER BY docid}167  } $r168}169 170# Should get the same search results when an authorizer prevents171# all PRAGMA statements.172#173proc no_pragma_auth {code arg1 arg2 arg3 arg4 args} {174  if {$code=="SQLITE_PRAGMA"} {return SQLITE_DENY}175  return SQLITE_OK;176}177do_test fts4aa-4.0 {178  db auth ::no_pragma_auth179  db eval {180    DROP TABLE t1;181    CREATE VIRTUAL TABLE t1 USING fts4(words, tokenize porter);182  }183  fts_kjv_genesis184} {}185unset -nocomplain ii186set ii 0187foreach {q r} [array get fts4aa_res] {188  incr ii189  do_test fts4aa-4.$ii {190    db eval {SELECT docid FROM t1 WHERE words MATCH $::q ORDER BY docid}191  } $r192}193 194# 2019-11-16 https://bugs.chromium.org/p/chromium/issues/detail?id=1025472195#196db close197sqlite3 db :memory:198do_execsql_test fts4aa-5.10 {199  CREATE VIRTUAL TABLE t1 USING fts4(a, b, c, d, e,f,g,h,i,j,k,l,m,n,o,p,q,r);200  INSERT INTO t1 VALUES('X Y', '2', '3', '4', '5', '6', '7', '8', '9', '0',201                        'a','b','c','d','e','f','g','h');202  UPDATE t1_docsize SET size=x'88' WHERE docid=1;203} {}204do_catchsql_test fts4aa-5.20 {205  SELECT quote(matchinfo(t1, 'l')) FROM t1 WHERE t1 MATCH 'X Y';206} {1 {database disk image is malformed}}207do_execsql_test fts4aa-5.30 {208  DROP TABLE t1;209  CREATE VIRTUAL TABLE t1 USING fts4(a,b,c,d);210  INSERT INTO t1 VALUES('one two','three four','five six','seven eight');211} {}212do_catchsql_test fts4aa-5.40 {213  UPDATE t1_stat SET value=x'01010101' WHERE id=0;214  SELECT quote(matchinfo(t1,'a')) FROM t1 WHERE t1 MATCH 'one two';215} {1 {database disk image is malformed}}216do_catchsql_test fts4aa-5.50 {217  UPDATE t1_stat SET value=x'010101' WHERE id=0;218  SELECT quote(matchinfo(t1,'a')) FROM t1 WHERE t1 MATCH 'one two';219} {1 {database disk image is malformed}}220do_catchsql_test fts4aa-5.60 {221  UPDATE t1_stat SET value=x'01' WHERE id=0;222  SELECT quote(matchinfo(t1,'a')) FROM t1 WHERE t1 MATCH 'one two';223} {1 {database disk image is malformed}}224do_catchsql_test fts4aa-5.70 {225  UPDATE t1_stat SET value=x'' WHERE id=0;226  SELECT quote(matchinfo(t1,'a')) FROM t1 WHERE t1 MATCH 'one two';227} {1 {database disk image is malformed}}228 229# 2019-11-18 https://bugs.chromium.org/p/chromium/issues/detail?id=1025467230db close231sqlite3 db :memory:232if {$tcl_platform(byteOrder)=="littleEndian"} {233  set res {X'0200000000000000000000000E0000000E00000001000000010000000100000001000000'}234} else {235  set res {X'0000000200000000000000000000000E0000000E00000001000000010000000100000001'}236}237do_catchsql_test fts4aa-6.10 {238  CREATE VIRTUAL TABLE f USING fts4();239  INSERT INTO f_segdir VALUES (77,91,0,0,'255 77',x'0001308000004d5c4ddddddd4d4d7b4d4d4d614d8019ff4d05000001204d4d2e4d6e4d4d4d4b4d6c4d004d4d4d4d4d4d3d000000004d5d4d4d645d4d004d4d4d4d4d4d4d4d4d454d6910004d05ffff054d646c4d004d5d4d4d4d4d3d000000004d4d4d4d4d4d4d4d4d4d4d69624d4d4d04004d4d4d4d4d604d4ce1404d554d45');240  INSERT INTO f_segdir VALUES (77,108,0,0,'255 77',x'0001310000fa64004d4d4d3c5d4d654d4d4d614d8000ff4d05000001204d4d2e4d6e4d4d4dff4d4d4d4d4d4d00104d4d4d4d000000004d4d4d0400311d4d4d4d4d4d4d4d4d4d684d6910004d05ffff054d4d6c4d004d4d4d4d4d4d3d000000004d4d4d4d644d4d4d4d4d4d69624d4d4d03ed4d4d4d4d4d604d4ce1404d550080');241  INSERT INTO f_stat VALUES (0,x'80808080100000000064004d4d4d3c4d4d654d4d4d614d8000ff4df6ff1a00204d4d2e4d6e4d4d4d104d4d4d4d4d4d00104d4d4d4d4d4d69574d4d4d000031044d4d4d3e4d4d4c4d05004d6910');242  SELECT quote(matchinfo(f,'pnax')) from f where f match '0 1';243} {1 {database disk image is malformed}}244 245# 2019-11-18 Detect infinite loop in fts3SelectLeaf()246db close247sqlite3 db :memory:248do_catchsql_test fts4aa-7.10 {249  CREATE VIRTUAL TABLE f USING fts4();250  INSERT INTO f_segdir VALUES (63,60,60,60,'60 60',x'3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c483c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c20003c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c283c3c3c3c3c3c3c3c3c3c3c223c3c3c3c3c3c3c3c3c');251  INSERT INTO f_segments VALUES (60,x'3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c5a3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c2a3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c5e3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c803c3c3c3c3c3c233c3c3c3c1c3c3c3c3c3c3c3c3c3c3c3c1b3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c273c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c1a3c3c3c3c3c3c000200003c3c3c3c3c3c3c3c3c3c3c3c3c383c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d898d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d3c3c3c3c3c3c3c3c3c3cba3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c1c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c00023c3c3c3c3c3c383c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3cbc3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c2c3c3c3c403c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c16161616161616163c3c3c3c3c3c3c3c3c3c3c3c3c583c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c2b3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c1c013c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c20003c3c3c3c3c3c3c3c3c3c3c800000003c3c3c3c3c3c3c2c3c3c3c3c3c3c353c08080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808f4080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808083c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c323c3c3c3c3c3c3c3c3c3c3c4f3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3cfcfcfcfcfcfcfcfcfcfcfc10fcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfd02fcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfcfc03e8fcfcfcfc3c3c3c3c3c3c8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d8d3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c553c3c3c3c3c3c3c3c3c3c3c3c3c573c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c000000803c3c4dd5d5a6d52cf3d5d5d5d5d5d5d5d5d5d5d5d5d5d53c3c3c3c3f3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c2d3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c013c3c3c3c00643c3c3c3ce93c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c263c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c363c3c3c3c3c3c3c3c3c3c3c3c3c3c543c3c3c3c3c3c3c3c3c3c273c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c330000003c3c3c3c3c3c3c3c3c3c3c3c3c3c4d3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c000010003c3c3c3c3c3c413c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c1c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c403c3c3c3c3c3c3c3c3c3c3c3cec0000fa3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c2d3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c4c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c5e3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c1b3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c593c3c3c3c3c3c243c3c373c3c3c3c3cff3c3c3c3c3c3c3c3c3c3c3c3c3c000080003c3c3c3c3c3c3c3c3c3c353c3c3c3c3c3d3c3c3c3c3c3c3c3c3c3c3c3c4d3c3c3c3c3c3c3c3c3c3c3c3c3c40003c3c3c3c3c293c3c3c3c3c3c3c3c3c3d3c3c3c3c3c3c3c3c353c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c4f3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3f3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3cff7f3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c2d3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3ca43c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3cbf3c3c3c3c3c3c3c3c3c008000003c3c3c3c3c3c3c3c343c3c373c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c593c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c3c');252  SELECT * from f where f match '0';253} {1 {database disk image is malformed}}254 255 256finish_test257