Documentation misunderstanding or bug (FTS3 and "^" character)?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Documentation misunderstanding or bug (FTS3 and "^" character)?

mailing lists
Hi,

I could not find an example showing a result in the documentation, therefore I created one by myself:

CREATE VIRTUAL TABLE myData USING FTS3(content);
INSERT INTO myData 'alpha beta';

1)
SELECT * FROM myData WHERE myData MATCH 'beta';

Result:

content
alpha beta

This is what I expected.

2)
SELECT * FROM myData WHERE myData MATCH '^beta';

Result:

content
alpha beta

This is what I did not expect. According to the documentation I expected to get no rows because the column begins with alpha and not with beta.

What's the issue here? I tested these examples using version 3.24.0.

Regards,
Hardy

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Documentation misunderstanding or bug (FTS3 and "^" character)?

Dan Kennedy-4


The fts3/4 documentation says:

"If the FTS table is an FTS4 table (not FTS3), a token may also be
prefixed with a "^" character. In this case, in order to match the token
must appear as the very first token in any column of the matching row."

So change "FTS3" to "FTS4" and it will likely work.

Dan.


On 30/8/62 16:31, mailing lists wrote:

> Hi,
>
> I could not find an example showing a result in the documentation, therefore I created one by myself:
>
> CREATE VIRTUAL TABLE myData USING FTS3(content);
> INSERT INTO myData 'alpha beta';
>
> 1)
> SELECT * FROM myData WHERE myData MATCH 'beta';
>
> Result:
>
> content
> alpha beta
>
> This is what I expected.
>
> 2)
> SELECT * FROM myData WHERE myData MATCH '^beta';
>
> Result:
>
> content
> alpha beta
>
> This is what I did not expect. According to the documentation I expected to get no rows because the column begins with alpha and not with beta.
>
> What's the issue here? I tested these examples using version 3.24.0.
>
> Regards,
> Hardy
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Documentation misunderstanding or bug (FTS3 and "^" character)?

mailing lists
Hi,

but there is no token '^beta'! Even if in FTS3 '^' is not regarded as a special character. Is the '^' removed by the tokenizer? Because it is regarded as a diacritical character?

PS: I have to admit that I have overlooked the comment that '^' works only for FTS4 tables.

Regards,
Hardy

> Am 2019-08-30 um 12:24 schrieb Dan Kennedy <[hidden email]>:
>
>
>
> The fts3/4 documentation says:
>
> "If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed with a "^" character. In this case, in order to match the token must appear as the very first token in any column of the matching row."
>
> So change "FTS3" to "FTS4" and it will likely work.
>
> Dan.
>
>
> On 30/8/62 16:31, mailing lists wrote:
>> Hi,
>>
>> I could not find an example showing a result in the documentation, therefore I created one by myself:
>>
>> CREATE VIRTUAL TABLE myData USING FTS3(content);
>> INSERT INTO myData 'alpha beta';
>>
>> 1)
>> SELECT * FROM myData WHERE myData MATCH 'beta';
>>
>> Result:
>>
>> content
>> alpha beta
>>
>> This is what I expected.
>>
>> 2)
>> SELECT * FROM myData WHERE myData MATCH '^beta';
>>
>> Result:
>>
>> content
>> alpha beta
>>
>> This is what I did not expect. According to the documentation I expected to get no rows because the column begins with alpha and not with beta.
>>
>> What's the issue here? I tested these examples using version 3.24.0.
>>
>> Regards,
>> Hardy
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Documentation misunderstanding or bug (FTS3 and "^" character)?

Dan Kennedy-4

On 30/8/62 17:39, mailing lists wrote:
> Hi,
>
> but there is no token '^beta'! Even if in FTS3 '^' is not regarded as a special character. Is the '^' removed by the tokenizer?

Right. It's removed by the tokenizer.

FTS3/4 is focused on backwards compatibility. New work should really use
FTS5 - it's faster, has more features, is better tested and the query
syntax is properly defined.

https://sqlite.org/fts5.html

Dan.



> Because it is regarded as a diacritical character?
>
> PS: I have to admit that I have overlooked the comment that '^' works only for FTS4 tables.
>
> Regards,
> Hardy
>
>> Am 2019-08-30 um 12:24 schrieb Dan Kennedy <[hidden email]>:
>>
>>
>>
>> The fts3/4 documentation says:
>>
>> "If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed with a "^" character. In this case, in order to match the token must appear as the very first token in any column of the matching row."
>>
>> So change "FTS3" to "FTS4" and it will likely work.
>>
>> Dan.
>>
>>
>> On 30/8/62 16:31, mailing lists wrote:
>>> Hi,
>>>
>>> I could not find an example showing a result in the documentation, therefore I created one by myself:
>>>
>>> CREATE VIRTUAL TABLE myData USING FTS3(content);
>>> INSERT INTO myData 'alpha beta';
>>>
>>> 1)
>>> SELECT * FROM myData WHERE myData MATCH 'beta';
>>>
>>> Result:
>>>
>>> content
>>> alpha beta
>>>
>>> This is what I expected.
>>>
>>> 2)
>>> SELECT * FROM myData WHERE myData MATCH '^beta';
>>>
>>> Result:
>>>
>>> content
>>> alpha beta
>>>
>>> This is what I did not expect. According to the documentation I expected to get no rows because the column begins with alpha and not with beta.
>>>
>>> What's the issue here? I tested these examples using version 3.24.0.
>>>
>>> Regards,
>>> Hardy
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Documentation misunderstanding or bug (FTS3 and "^" character)?

mailing lists
Hi,

yes, but unfortunately I have still a project using FTS3 tables to which I have to add new features..

Regards,
Hardy

> Am 2019-08-30 um 13:00 schrieb Dan Kennedy <[hidden email]>:
>
>
> On 30/8/62 17:39, mailing lists wrote:
>> Hi,
>>
>> but there is no token '^beta'! Even if in FTS3 '^' is not regarded as a special character. Is the '^' removed by the tokenizer?
>
> Right. It's removed by the tokenizer.
>
> FTS3/4 is focused on backwards compatibility. New work should really use FTS5 - it's faster, has more features, is better tested and the query syntax is properly defined.
>
> https://sqlite.org/fts5.html
>
> Dan.
>
>
>
>> Because it is regarded as a diacritical character?
>>
>> PS: I have to admit that I have overlooked the comment that '^' works only for FTS4 tables.
>>
>> Regards,
>> Hardy
>>
>>> Am 2019-08-30 um 12:24 schrieb Dan Kennedy <[hidden email]>:
>>>
>>>
>>>
>>> The fts3/4 documentation says:
>>>
>>> "If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed with a "^" character. In this case, in order to match the token must appear as the very first token in any column of the matching row."
>>>
>>> So change "FTS3" to "FTS4" and it will likely work.
>>>
>>> Dan.
>>>
>>>
>>> On 30/8/62 16:31, mailing lists wrote:
>>>> Hi,
>>>>
>>>> I could not find an example showing a result in the documentation, therefore I created one by myself:
>>>>
>>>> CREATE VIRTUAL TABLE myData USING FTS3(content);
>>>> INSERT INTO myData 'alpha beta';
>>>>
>>>> 1)
>>>> SELECT * FROM myData WHERE myData MATCH 'beta';
>>>>
>>>> Result:
>>>>
>>>> content
>>>> alpha beta
>>>>
>>>> This is what I expected.
>>>>
>>>> 2)
>>>> SELECT * FROM myData WHERE myData MATCH '^beta';
>>>>
>>>> Result:
>>>>
>>>> content
>>>> alpha beta
>>>>
>>>> This is what I did not expect. According to the documentation I expected to get no rows because the column begins with alpha and not with beta.
>>>>
>>>> What's the issue here? I tested these examples using version 3.24.0.
>>>>
>>>> Regards,
>>>> Hardy
>>>>
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users