Debian Codegraph @

test/fts3join.test

raw
1 # 2014 January 4
2 #
3 # The author disclaims copyright to this source code. In place of
4 # 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. The
12 # focus of this script is testing the FTS3 module.
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set ::testprefix fts3join
18
19 # If SQLITE_ENABLE_FTS3 is defined, omit this file.
20 ifcapable !fts3 {
21 finish_test
22 return
23 }
24
25 do_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
37 do_execsql_test 1.1 {
38 SELECT docid FROM ft1, t1 WHERE ft1 MATCH y AND id=1;
39 } {1 2 3}
40
41 do_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
45 do_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
55 do_execsql_test 2.1 { SELECT * FROM ft2, ft3 WHERE x MATCH y; } {abc abc}
56 do_execsql_test 2.2 { SELECT * FROM ft2, ft3 WHERE y MATCH x; } {abc abc}
57 do_execsql_test 2.3 { SELECT * FROM ft3, ft2 WHERE x MATCH y; } {abc abc}
58 do_execsql_test 2.4 { SELECT * FROM ft3, ft2 WHERE y MATCH x; } {abc abc}
59
60 do_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
64 do_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
73 do_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 = rrr
77 } {1 1 abc 2 {} {}}
78
79 do_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 automatic
85 # index to avoid running multiple fts queries.
86 #
87 do_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
93 do_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 PLAN
100 |--SCAN t4
101 `--SCAN ft4 VIRTUAL TABLE INDEX 3: LEFT-JOIN
102 }
103
104 finish_test