Searching email, Full Text Search prefix, not expected results

=?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.

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':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!

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