xref: /sqlite-3.40.0/test/fts3query.test (revision f2fcd075) 
1# 2009 December 20
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#
12# This file contains tests of fts3 queries that have been useful during
13# the development process as well as some that have been useful in tracking
14# down bugs. They are not focused on any particular functionality.
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# If this build does not include FTS3, skip the tests in this file.
21#
22ifcapable !fts3 { finish_test ; return }
23source $testdir/malloc_common.tcl
24source $testdir/fts3_common.tcl
25set DO_MALLOC_TEST 0
26
27do_test fts3query-1.1 {
28  execsql {
29    CREATE VIRTUAL TABLE t1 USING fts3(x);
30    BEGIN;
31      INSERT INTO t1 VALUES('The source code for SQLite is in the public');
32  }
33} {}
34
35do_select_test fts3query-1.2 {
36  SELECT * FROM t1;
37} {{The source code for SQLite is in the public}}
38do_select_test fts3query-1.3 {
39  SELECT * FROM t1 WHERE t1 MATCH 'sqlite'
40} {{The source code for SQLite is in the public}}
41
42do_test fts3query-1.4 { execsql {COMMIT} } {}
43
44do_select_test fts3query-1.5 {
45  SELECT * FROM t1;
46} {{The source code for SQLite is in the public}}
47do_select_test fts3query-1.6 {
48  SELECT * FROM t1 WHERE t1 MATCH 'sqlite'
49} {{The source code for SQLite is in the public}}
50
51
52set sqlite_fts3_enable_parentheses 1
53do_test fts3query-2.1 {
54  execsql {
55    CREATE VIRTUAL TABLE zoink USING fts3;
56    INSERT INTO zoink VALUES('The apple falls far from the tree');
57  }
58} {}
59do_test fts3query-2.2 {
60  execsql {
61    SELECT docid FROM zoink WHERE zoink MATCH '(apple oranges) AND apple'
62  }
63} {}
64do_test fts3query-2.3 {
65  execsql {
66    SELECT docid FROM zoink WHERE zoink MATCH 'apple AND (oranges apple)'
67  }
68} {}
69set sqlite_fts3_enable_parentheses 0
70
71do_test fts3query-3.1 {
72  execsql {
73    CREATE VIRTUAL TABLE foobar using FTS3(description, tokenize porter);
74    INSERT INTO foobar (description) values ('
75      Filed under: Emerging Technologies, EV/Plug-in, Hybrid, Chevrolet, GM,
76      ZENN 2011 Chevy Volt - Click above for high-res image gallery There are
77      16 days left in the month of December. Besides being time for most
78      Americans to kick their Christmas shopping sessions into high gear and
79      start planning their resolutions for 2010, it also means that there''s
80      precious little time for EEStor to "deliver functional technology" to
81      Zenn Motors as promised. Still, the promises held out by the secretive
82      company are too great for us to forget about entirely. We''d love for
83      EEStor''s claims to be independently verified and proven accurate, as
84      would just about anyone else looking to break free of petroleum in fav
85    ');
86  }
87} {}
88
89do_test fts3query-3.2 {
90  execsql { SELECT docid FROM foobar WHERE description MATCH '"high sp d"' }
91} {}
92
93proc mit {blob} {
94  set scan(littleEndian) i*
95  set scan(bigEndian) I*
96  binary scan $blob $scan($::tcl_platform(byteOrder)) r
97  return $r
98}
99db func mit mit
100
101do_test fts3query-3.3 {
102  execsql { SELECT mit(matchinfo(foobar)) FROM foobar WHERE foobar MATCH 'the' }
103} {{1 1 3 3 1}}
104
105# The following tests check that ticket 775b39dd3c has been fixed.
106#
107proc eqp {sql} {
108  uplevel [list execsql "EXPLAIN QUERY PLAN $sql"]
109}
110do_test fts3query-4.1 {
111  execsql {
112    DROP TABLE IF EXISTS t1;
113    CREATE TABLE t1(number INTEGER PRIMARY KEY, date);
114    CREATE INDEX i1 ON t1(date);
115    CREATE VIRTUAL TABLE ft USING fts3(title);
116    CREATE TABLE bt(title);
117  }
118} {}
119do_test fts3query-4.2 {
120  eqp "SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date"
121} {0 0 {TABLE t1 WITH INDEX i1 ORDER BY} 1 1 {TABLE ft VIRTUAL TABLE INDEX 1:}}
122do_test fts3query-4.3 {
123  eqp "SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date"
124} {0 1 {TABLE t1 WITH INDEX i1 ORDER BY} 1 0 {TABLE ft VIRTUAL TABLE INDEX 1:}}
125do_test fts3query-4.4 {
126  eqp "SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date"
127} {0 0 {TABLE t1 WITH INDEX i1 ORDER BY} 1 1 {TABLE bt USING PRIMARY KEY}}
128do_test fts3query-4.5 {
129  eqp "SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date"
130} {0 1 {TABLE t1 WITH INDEX i1 ORDER BY} 1 0 {TABLE bt USING PRIMARY KEY}}
131
132
133finish_test
134
135