In previous versions of SQLite (3.5.4.2), the following worked as expected:
CREATE VIRTUAL TABLE cities USING fts3(name, state, nickname);
INSERT into cities (name,state,nickname) VALUES ("new york", "ny", "big
apple");
INSERT into cities (name,state,nickname) VALUES ("apple town", "xx",
"big time");
SELECT * FROM cities WHERE cities MATCH 'nickname:"big apple"'
Upon upgrading to SQLite 3.6.16, this fails and sqlite3_step() returns
SQLITE_ERROR.
From the code:
/* If we reach this point, it means that execution is finished with
** an error of some kind.
*/
vdbe_error_halt:
assert( rc );
p->rc = rc;
sqlite3VdbeHalt(p);
if( rc==SQLITE_IOERR_NOMEM ) db->mallocFailed = 1;
rc = SQLITE_ERROR;
From
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/README.syntax
<col> MATCH 'col1:hello col2:world'
It is not possible to associate a specific table column with a quoted
phrase or a term preceded by a '-' operator. A '*' character may be
appended to a term associated with a specific column for prefix
matching.
I assume this no longer works because it "is not possible to associate a
specific table column with a quoted phrase".
Any idea when this changed or which ticket is associated with it? Any
suggestions on how I can change my query to achieve the desired result?
Note that:
SELECT * FROM cities WHERE cities MATCH 'nickname:big apple'
will not work as that will return both cities.
Thanks in advance,
-Seth
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
