Обсуждение: Searching email, Full Text Search prefix, not expected results
Hi guys, I'm going to crazy about FTS with prefix agains email values on tsvector. Follow how to reproduce:
For the next tsvector:
=# select to_tsvector('user@company.com')
to_tsvector
----------------------
'user@company.com':1
I expects TRUE for all next tsqueryes:
select to_tsvector('user@company.com') @@ to_tsquery('u:*');
select to_tsvector('user@company.com') @@ to_tsquery('us:*');
select to_tsvector('user@company.com') @@ to_tsquery('use:*');
select to_tsvector('user@company.com') @@ to_tsquery('user:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@c:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@co:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@com:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@comp:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@compa:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@compan:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@company:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@company.:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@company.c:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@company.co:*');
select to_tsvector('user@company.com') @@ to_tsquery('user@company.com:*');
But NOT, there are some NOT expected and confusing me results:
=# select to_tsvector('user@company.com') @@ to_tsquery('us:*');
?column?
----------
t
(1 row)
=# select to_tsvector('user@company.com') @@ to_tsquery('user:*');
?column?
----------
t
=# select to_tsvector('user@company.com') @@ to_tsquery('user@:*');
?column?
----------
t
select to_tsvector('user@company.com') @@ to_tsquery('user@comp:*');
?column?
----------
f <---- FALSE (I expects TRUE)
=# select to_tsvector('user@company.com') @@ to_tsquery('user@company:*');
?column?
----------
f <---- FALSE (I expects TRUE)
=# select to_tsvector('user@company.com') @@ to_tsquery('user@company.:*');
?column?
----------
f <---- FALSE (I expects TRUE)
=# select to_tsvector('user@company.com') @@ to_tsquery('user@company.c:*');
?column?
----------
f <---- FALSE (I expects TRUE)
=# select to_tsvector('user@company.com') @@ to_tsquery('user@company.co:*');
?column?
----------
t <---- TRUE .... OOhhhhhHHH I'm going crazy!!!
=# select to_tsvector('user@company.com') @@ to_tsquery('user@company.com:*');
?column?
----------
t <---- TRUE ... Yes I'm crazy.
Please some ligths about it.
(I follow the official docs in http://www.postgresql.org/docs/9.1/interactive/textsearch.html for my knowledge)
Thx!
=?ISO-8859-1?Q?Daniel_V=E1zquez?= <daniel2d2art@gmail.com> writes: > Hi guys, I'm going to crazy about FTS with prefix agains email values on > tsvector. tsvector is meant for searching for natural-language words. It is not a good idea to imagine that it works exactly like a substring match, especially on strings that aren't simple words. (Frankly, I think the prefix feature is a piece of junk, precisely because it encourages people to think cases like this will work.) Possibly you'd be happier with trigrams ... regards, tom lane
But tsvector recognices email format as natural. And I'm not looking for a substring. Please see the queries are incremental with the search string, and see last four results ... I think some think it's no working properly.
--
Daniel Vázquez
SICONET (A Bull Group Company)
Torre Agbar. Avda. Diagonal, 211 - planta 23
08018 - Barcelona
telf: + 34 93 2272727 (Ext. 2952)
fax: + 34 93 2272728
www.bull.es - www.siconet.es
daniel.vazquez@bull.es
El 14 de marzo de 2012 19:05, Daniel Vázquez <daniel2d2art@gmail.com> escribió:
Hi guys, I'm going to crazy about FTS with prefix agains email values on tsvector. Follow how to reproduce:For the next tsvector:=# select to_tsvector('user@company.com')to_tsvector----------------------'user@company.com':1I expects TRUE for all next tsqueryes:select to_tsvector('user@company.com') @@ to_tsquery('u:*');select to_tsvector('user@company.com') @@ to_tsquery('us:*');select to_tsvector('user@company.com') @@ to_tsquery('use:*');select to_tsvector('user@company.com') @@ to_tsquery('user:*');select to_tsvector('user@company.com') @@ to_tsquery('user@:*');select to_tsvector('user@company.com') @@ to_tsquery('user@c:*');select to_tsvector('user@company.com') @@ to_tsquery('user@co:*');select to_tsvector('user@company.com') @@ to_tsquery('user@com:*');select to_tsvector('user@company.com') @@ to_tsquery('user@comp:*');select to_tsvector('user@company.com') @@ to_tsquery('user@compa:*');select to_tsvector('user@company.com') @@ to_tsquery('user@compan:*');select to_tsvector('user@company.com') @@ to_tsquery('user@company:*');select to_tsvector('user@company.com') @@ to_tsquery('user@company.:*');select to_tsvector('user@company.com') @@ to_tsquery('user@company.c:*');select to_tsvector('user@company.com') @@ to_tsquery('user@company.co:*');select to_tsvector('user@company.com') @@ to_tsquery('user@company.com:*');But NOT, there are some NOT expected and confusing me results:=# select to_tsvector('user@company.com') @@ to_tsquery('us:*');?column?----------t(1 row)=# select to_tsvector('user@company.com') @@ to_tsquery('user:*');?column?----------t=# select to_tsvector('user@company.com') @@ to_tsquery('user@:*');?column?----------tselect to_tsvector('user@company.com') @@ to_tsquery('user@comp:*');?column?----------f <---- FALSE (I expects TRUE)=# select to_tsvector('user@company.com') @@ to_tsquery('user@company:*');?column?----------f <---- FALSE (I expects TRUE)=# select to_tsvector('user@company.com') @@ to_tsquery('user@company.:*');?column?----------f <---- FALSE (I expects TRUE)=# select to_tsvector('user@company.com') @@ to_tsquery('user@company.c:*');?column?----------f <---- FALSE (I expects TRUE)=# select to_tsvector('user@company.com') @@ to_tsquery('user@company.co:*');?column?----------t <---- TRUE .... OOhhhhhHHH I'm going crazy!!!=# select to_tsvector('user@company.com') @@ to_tsquery('user@company.com:*');?column?----------t <---- TRUE ... Yes I'm crazy.Please some ligths about it.(I follow the official docs in http://www.postgresql.org/docs/9.1/interactive/textsearch.html for my knowledge)Thx!
Daniel Vázquez
SICONET (A Bull Group Company)
Torre Agbar. Avda. Diagonal, 211 - planta 23
08018 - Barcelona
telf: + 34 93 2272727 (Ext. 2952)
fax: + 34 93 2272728
www.bull.es - www.siconet.es
daniel.vazquez@bull.es