I have to admit I'm an FTS neophyte so I'm trying to understand what's going on
here.
Here's the SQL:
create virtual table data using fts4(content text);
insert into data values('we got bug report API_Version:374 right here');
insert into data values('we got bug report API_Version:375 right here');
insert into data values('we got API_Version:UAR right here');
insert into data values('we got API_Version:UAS right here');
With or without quotes this works...you can see that each term matches if you
treat _ and : as whitespace
sqlite> select offsets(data) from data where data match '"bug report
API_Version:374"';
0 0 7 3 0 1 11 6 0 2 18 3 0 3 22 7 0 4 30 3
sqlite> select offsets(data) from data where data match 'bug report
API_Version:374';
0 0 7 3 0 1 11 6 0 2 18 3 0 3 22 7 0 4 30 3
Now...purportedly FTS ignores all non-alphanumeric characters in the index.
So now we try
sqlite> select offsets(data) from data where data match 'bug report API_Version
374';
0 0 7 3 0 1 11 6 0 2 18 3 0 3 22 7 0 4 30 3
And indeed...nothing changes...
Now for the slightly more complex queries
sqlite> select offsets(data) from data where data match 'bug report API
Version:374 OR API_Version:UAR';
0 0 7 3 0 1 11 6 0 2 18 3 0 5 18 3 0 3 22 7 0 6 22 7 0 4 30 3
0 2 7 3 0 5 7 3 0 3 11 7 0 6 11 7 0 7 19 3
sqlite> select offsets(data) from data where data match '(bug report API
Version:374) OR "API_Version:UAR"';
0 0 7 3 0 1 11 6 0 2 18 3 0 3 22 7 0 4 30 3
0 2 7 3 0 5 7 3 0 3 11 7 0 6 11 7 0 7 19 3
sqlite> select offsets(data) from data where data match '(bug report API
Version:374) OR (API_Version:UAR)';
0 0 7 3 0 1 11 6 0 2 18 3 0 5 18 3 0 3 22 7 0 6 22 7 0 4 30 3
0 2 7 3 0 5 7 3 0 3 11 7 0 6 11 7 0 7 19 3
sqlite> select offsets(data) from data where data match 'bug API report';
0 0 7 3 0 2 11 6 0 1 18 3
0 0 7 3 0 2 11 6 0 1 18 3
sqlite> select offsets(data) from data where data match "bug API report";
0 0 7 3 0 2 11 6 0 1 18 3
0 0 7 3 0 2 11 6 0 1 18 3
sqlite>
sqlite> select offsets(data) from data where data match '"bug API report"';
sqlite> select offsets(data) from data where data match '"bug report API"';
0 0 7 3 0 1 11 6 0 2 18 3
0 0 7 3 0 1 11 6 0 2 18 3
But how do we match so that we only get one offsets, for the phrase, instead
of one for each word? If quotes are a "phrase" shouldn't there be only one
match?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.org on behalf of Tod Wulff
Sent: Wed 12/29/2010 2:53 AM
To: 'General Discussion of SQLite Database'
Subject: EXTERNAL:[sqlite] FTS4 snippets contains unintended content?
Good day.
Anyone seeing additional content returned with the snippet text when using
the OR (or AND?) MATCH Syntax - i.e. the field contents of the field
immediately prior to the snippet(blah) clause?
SELECT Target_Name, Target_Content, Target_Description, Target_Link,
Parent_Short, Parent_Name, Parent_Link, API_Version, rowid,
snippet(RIM_API_FTS_DB, ' ', ' ', '...', -3, -10) FROM RIM_API_FTS_DB WHERE
RIM_API_FTS_DB MATCH 'bug report API_Version:374 OR API_Version:UAR' ORDER
BY rank(matchinfo(RIM_API_FTS_DB), 50, 10, 25, 15, 0, 0, 0, 0, 0 ) DESC
LIMIT 10 OFFSET 0;
In the snippet col in each result row, I get the ...[snippet text]... XXX ß
Where XXX is the content of the field called out in the MATCH clause.
It only shows up when I use the OR MATCH Syntax. I haven't tested with AND
yet.
[03:41] <Schema> 1: [3.7.4] Release History Of SQLite ( http://j.mp/fHI3HG )
...generator bug introduced in version 3.6.14 . This bug ...status()
interface, in order to report out the amount of... 374
[03:41] <Schema> 2: [3.7.4] SQLite Older News ( http://j.mp/fX8ErX )
...single notable bug was fixed (ticket #3929). This bug cause...still be
considered alpha. Please report any problems. The file... 374
[03:41] <Schema> 3: [3.7.4] How SQLite Is Tested ( http://j.mp/gdqXN1 )
...Whenever a bug is reported against SQLite, that bug is...0 (all
statistics in the report are against that release... 374
[03:41] <Schema> 4: [3.7.4] About SQLite ( http://j.mp/gw9S60 ) ...Interface
Spec Development Timeline Report a Bug Wiki SQLite is... 374
[03:41] <Schema> 5: [UAR] SQLite Changes From Version 3.5.9 To 3.6.0 (
http://j.mp/f6M2jc ) ...allows the xAccess() method to report failures. In
association with...IN operators is technically a bug fix, not a design...
UAR
Please chime in if you have seen this. Thanks, in advance.
-t
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
