Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)
On 2014-10-09, 11:09 AM, Dan Kennedy wrote: On 10/09/2014 07:23 PM, Sohail Somani wrote: On 2014-10-09, 7:32 AM, Dan Kennedy wrote: Got it, thanks for the explanation. Just to make sure that I understand you correctly, is the clause MATCH '*l0l* *h4x*' getting translated to MATCH 'l0l* h4x*'? Yes, that's right. Dan. In that case, shouldn't the test in the original post have returned the same results for both cases? Fair point. Fixed here: http://www.sqlite.org/src/info/49dfee7cd1c9 Dan. Thank you kind sir (or madam, as the case may be). Sohail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)
On 10/09/2014 07:23 PM, Sohail Somani wrote: On 2014-10-09, 7:32 AM, Dan Kennedy wrote: Got it, thanks for the explanation. Just to make sure that I understand you correctly, is the clause MATCH '*l0l* *h4x*' getting translated to MATCH 'l0l* h4x*'? Yes, that's right. Dan. In that case, shouldn't the test in the original post have returned the same results for both cases? Fair point. Fixed here: http://www.sqlite.org/src/info/49dfee7cd1c9 Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)
On 2014-10-09, 7:32 AM, Dan Kennedy wrote: Got it, thanks for the explanation. Just to make sure that I understand you correctly, is the clause MATCH '*l0l* *h4x*' getting translated to MATCH 'l0l* h4x*'? Yes, that's right. Dan. In that case, shouldn't the test in the original post have returned the same results for both cases? Maybe I'm misunderstanding something. Sohail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)
On 10/09/2014 01:13 AM, Sohail Somani wrote:
On 2014-10-07, 4:04 PM, Dan Kennedy wrote:
On 10/08/2014 01:52 AM, Sohail Somani wrote:
Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*",
though it did work as expected with the older version. I'd suggest
keeping the old behaviour unless there is a performance-based reason
not to.
On 2014-10-07, 2:49 PM, Sohail Somani wrote:
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';
COMMIT;
BEGIN TRANSACTION;
INSERT INTO t(key,value0,value1) VALUES('$key','l0l','h4x');
COMMIT;
SELECT '--- 2 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';
Unfortunately, this was a break with backwards compatibility in response
to this issue:
https://www.mail-archive.com/sqlite-users@sqlite.org/msg83345.html
Before:
http://www.sqlite.org/src/info/e21bf7a2ade6373e
(version 3.8.6), it was up to the specific tokenizer being used whether
or not the special characters *, ", ( and ) were available to the query
parser. After that commit, they are stripped out first. So with the new
version, your query is now equivalent to "MATCH '101* h4x*'".
This should only affect FTS tables that use custom tokenizers (not the
default simple or porter tokenizers).
Got it, thanks for the explanation. Just to make sure that I
understand you correctly, is the clause MATCH '*l0l* *h4x*' getting
translated to MATCH 'l0l* h4x*'?
Yes, that's right.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)
On 2014-10-07, 4:04 PM, Dan Kennedy wrote:
On 10/08/2014 01:52 AM, Sohail Somani wrote:
Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*",
though it did work as expected with the older version. I'd suggest
keeping the old behaviour unless there is a performance-based reason
not to.
On 2014-10-07, 2:49 PM, Sohail Somani wrote:
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';
COMMIT;
BEGIN TRANSACTION;
INSERT INTO t(key,value0,value1) VALUES('$key','l0l','h4x');
COMMIT;
SELECT '--- 2 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';
Unfortunately, this was a break with backwards compatibility in response
to this issue:
https://www.mail-archive.com/sqlite-users@sqlite.org/msg83345.html
Before:
http://www.sqlite.org/src/info/e21bf7a2ade6373e
(version 3.8.6), it was up to the specific tokenizer being used whether
or not the special characters *, ", ( and ) were available to the query
parser. After that commit, they are stripped out first. So with the new
version, your query is now equivalent to "MATCH '101* h4x*'".
This should only affect FTS tables that use custom tokenizers (not the
default simple or porter tokenizers).
Got it, thanks for the explanation. Just to make sure that I understand
you correctly, is the clause MATCH '*l0l* *h4x*' getting translated to
MATCH 'l0l* h4x*'?
Sohail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)
On 10/08/2014 01:52 AM, Sohail Somani wrote:
Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*",
though it did work as expected with the older version. I'd suggest
keeping the old behaviour unless there is a performance-based reason
not to.
On 2014-10-07, 2:49 PM, Sohail Somani wrote:
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';
COMMIT;
BEGIN TRANSACTION;
INSERT INTO t(key,value0,value1) VALUES('$key','l0l','h4x');
COMMIT;
SELECT '--- 2 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Unfortunately, this was a break with backwards compatibility in response
to this issue:
https://www.mail-archive.com/sqlite-users@sqlite.org/msg83345.html
Before:
http://www.sqlite.org/src/info/e21bf7a2ade6373e
(version 3.8.6), it was up to the specific tokenizer being used whether
or not the special characters *, ", ( and ) were available to the query
parser. After that commit, they are stripped out first. So with the new
version, your query is now equivalent to "MATCH '101* h4x*'".
This should only affect FTS tables that use custom tokenizers (not the
default simple or porter tokenizers).
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)
Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*",
though it did work as expected with the older version. I'd suggest
keeping the old behaviour unless there is a performance-based reason not to.
On 2014-10-07, 2:49 PM, Sohail Somani wrote:
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';
COMMIT;
BEGIN TRANSACTION;
INSERT INTO t(key,value0,value1) VALUES('$key','l0l','h4x');
COMMIT;
SELECT '--- 2 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)
$ ./bin/sqlite3 --version
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668
$ ./bin/sqlite3 -batch < /tmp/test.sql
--- 1 ---
0
--- 2 ---
1
$ ./bin/sqlite3 --version
3.8.7 2014-09-30 19:04:41 5ce05757aac80b99c3b2141cd301809f8e28e661
/bin/sqlite3 -batch < /tmp/test.sql
--- 1 ---
0
--- 2 ---
0
The SQL is below. Perhaps someone can see if I'm doing something wrong?
BEGIN TRANSACTION;
CREATE TABLE t(
id INTEGER PRIMARY KEY AUTOINCREMENT
,key TEXT NOT NULL
,value0 DEFAULT ''
,value1 DEFAULT ''
);
CREATE VIRTUAL TABLE t_fts USING FTS4 (
content="t",tokenize=unicode61 "tokenchars=-_"
,key
,value0
,value1
);
CREATE TRIGGER t_bu BEFORE UPDATE ON t BEGIN
DELETE FROM t_fts WHERE docid = old.id;
END;
CREATE TRIGGER t_bd BEFORE DELETE ON t BEGIN
DELETE FROM t_fts WHERE docid = old.id;
END;
CREATE TRIGGER t_au AFTER UPDATE ON t BEGIN
INSERT INTO t_fts(
docid
,key
,value0
,value1
)
VALUES (
new.rowid
,new.key
,new.value0
,new.value1
);
END;
CREATE TRIGGER t_ai AFTER INSERT ON t BEGIN
INSERT INTO t_fts(
docid
,key
,value0
,value1
)
VALUES (
new.rowid
,new.key
,new.value0
,new.value1
);
END;
COMMIT;
BEGIN TRANSACTION;
SELECT '--- 1 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';
COMMIT;
BEGIN TRANSACTION;
INSERT INTO t(key,value0,value1) VALUES('$key','l0l','h4x');
COMMIT;
SELECT '--- 2 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
