Обсуждение: Searching email, Full Text Search prefix, not expected results

Поиск
Список
Период
Сортировка

Searching email, Full Text Search prefix, not expected results

От
Daniel Vázquez
Дата:
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      
----------------------

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!

Re: Searching email, Full Text Search prefix, not expected results

От
Tom Lane
Дата:
=?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

Re: Searching email, Full Text Search prefix, not expected results

От
Daniel Vázquez
Дата:
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.

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      
----------------------

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!



--
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