AryaWu/sqlite

1# 2014 January 42#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 FTS3 module.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set ::testprefix fts3join18 19# If SQLITE_ENABLE_FTS3 is defined, omit this file.20ifcapable !fts3 {21  finish_test22  return23}24 25do_execsql_test 1.0 {26  CREATE VIRTUAL TABLE ft1 USING fts4(x);27  INSERT INTO ft1 VALUES('aaa aaa');28  INSERT INTO ft1 VALUES('aaa bbb');29  INSERT INTO ft1 VALUES('bbb aaa');30  INSERT INTO ft1 VALUES('bbb bbb');31 32  CREATE TABLE t1(id, y);33  INSERT INTO t1 VALUES(1, 'aaa');34  INSERT INTO t1 VALUES(2, 'bbb');35}36 37do_execsql_test 1.1 {38  SELECT docid FROM ft1, t1 WHERE ft1 MATCH y AND id=1;39} {1 2 3}40 41do_execsql_test 1.2 {42  SELECT docid FROM ft1, t1 WHERE ft1 MATCH y AND id=1 ORDER BY docid;43} {1 2 3}44 45do_execsql_test 2.0 {46  CREATE VIRTUAL TABLE ft2 USING fts4(x);47  CREATE VIRTUAL TABLE ft3 USING fts4(y);48 49  INSERT INTO ft2 VALUES('abc');50  INSERT INTO ft2 VALUES('def');51  INSERT INTO ft3 VALUES('ghi');52  INSERT INTO ft3 VALUES('abc');53}54 55do_execsql_test 2.1 { SELECT * FROM ft2, ft3 WHERE x MATCH y; } {abc abc}56do_execsql_test 2.2 { SELECT * FROM ft2, ft3 WHERE y MATCH x; } {abc abc}57do_execsql_test 2.3 { SELECT * FROM ft3, ft2 WHERE x MATCH y; } {abc abc}58do_execsql_test 2.4 { SELECT * FROM ft3, ft2 WHERE y MATCH x; } {abc abc}59 60do_catchsql_test 2.5 { 61  SELECT * FROM ft3, ft2 WHERE y MATCH x AND x MATCH y; 62} {1 {unable to use function MATCH in the requested context}}63 64do_execsql_test 3.0 {65  CREATE VIRTUAL TABLE vt USING fts3(x);66  INSERT INTO vt VALUES('abc');67  INSERT INTO vt VALUES('xyz');68 69  CREATE TABLE tt(a INTEGER PRIMARY KEY);70  INSERT INTO tt VALUES(1), (2);71}72 73do_execsql_test 3.1 {74  SELECT * FROM tt LEFT JOIN (75    SELECT rowid AS rrr, * FROM vt WHERE vt MATCH 'abc'76  ) ON tt.a = rrr77} {1 1 abc 2 {} {}}78 79do_execsql_test 3.2 {80  SELECT * FROM tt LEFT JOIN vt ON (vt MATCH 'abc')81} {1 abc 2 abc}82 83#-------------------------------------------------------------------------84# Test that queries of the form found in test case 4.2 use an automatic85# index to avoid running multiple fts queries.86#87do_execsql_test 4.1 {88  CREATE VIRTUAL TABLE ft4 USING fts3(x);89  CREATE TABLE t4(y, z);90  CREATE INDEX t4y ON t1(y);91}92 93do_eqp_test 4.2 {94  SELECT * FROM t4 LEFT JOIN (95      SELECT docid, * FROM ft4 WHERE ft4 MATCH ?96  ) AS rr ON t4.rowid=rr.docid 97  WHERE t4.y = ?;98} {99  QUERY PLAN100  |--SCAN t4101  `--SCAN ft4 VIRTUAL TABLE INDEX 3: LEFT-JOIN102}103 104finish_test105