websearch_to_tsquery() returns queries that don't match to_tsvector()
Hi,
I'm surprised that the following expression is false:
select to_tsvector('english', 'aaa: bbb') @@
websearch_to_tsquery('english', '"aaa: bbb"');
?column?
----------
f
(1 row)
My expectation is that to_tsvector('english', text) @@
websearch_to_tsquery('english', '" || text || "') would be true for
all texts, or pretty close to all texts. Otherwise it makes search
rather unpredictable. The actual example that started this
investigation was searching for '"/path/to/some/exe: no such file or
directory"' (which was failing to find the exact matches that I knew
existed).
Looking at the tsvector and tsquery, we can see that the problem is
that the ":" counts as one position for the ts_query but not the
ts_vector:
select to_tsvector('english', 'aaa: bbb'), websearch_to_tsquery('english',
'"aaa: bbb"');
to_tsvector | websearch_to_tsquery
-----------------+----------------------
'aaa':1 'bbb':2 | 'aaa' <2> 'bbb'
(1 row)
So I wondered: are there more such cases? Looking at all texts of the
form 'aaa' || maybe-space || one-byte || maybe-space || 'bbb', it
happens quite a bit:
select text, ts_vector, ts_query, matches from unnest(array['', ' ']) as
prefix, unnest(array['', ' ']) as suffix, (select chr(a) as char from
generate_series(1,192) as s(a)) as zz1, lateral (select 'aaa' || prefix ||
char || suffix || 'bbb' as text) as zz2, lateral (select
to_tsvector('english', text) as ts_vector) as zz3, lateral (select
websearch_to_tsquery('english', '"' || text || '"') as ts_query) as zz4,
lateral (select ts_vector @@ ts_query as matches) as zz5 where not matches;
text | ts_vector | ts_query | matches
----------------+-----------------+------------------+---------
aaa \x01 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x02 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x03 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x04 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x05 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x06 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x07 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x08 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x0E bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x0F bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x10 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x11 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x12 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x13 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x14 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x15 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x16 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x17 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x18 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x19 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x1A bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x1B bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x1C bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x1D bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x1E bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x1F bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa # bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa $ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa % bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ' bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa * bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa + bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa , bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa . bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa / bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa: bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa : bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ; bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa = bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa > bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ? bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa @ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa [ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ] bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ^ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa _ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ` bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa { bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa } bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ~bbb | 'aaa':1 'bbb':2 | 'aaa' <-> '~bbb' | f
aaa ~ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \x7F bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0080 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0081 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0082 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0083 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0084 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0085 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0086 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0087 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0088 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0089 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u008A bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u008B bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u008C bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u008D bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u008E bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u008F bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0090 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0091 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0092 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0093 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0094 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0095 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0096 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0097 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0098 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u0099 bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u009A bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u009B bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u009C bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u009D bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u009E bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa \u009F bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¡ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¢ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa £ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¤ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¥ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¦ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa § bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¨ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa © bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa « bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¬ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ® bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¯ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ° bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ± bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ² bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ³ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ´ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¶ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa · bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¸ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¹ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa » bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¼ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ½ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¾ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
aaa ¿ bbb | 'aaa':1 'bbb':2 | 'aaa' <2> 'bbb' | f
(114 rows)
There is no obvious workaround either:
- there's no function that converts a tsvector like 'aaa':1 'bbb':2
into a tsquery like 'aaa' <-> 'bbb', that one might be able to use to
build a query with exactly the same normalization as tsvector.
- replacing all problematic characters above by spaces seems to work
for most characters but not others, as for instance it fixes 'aaa
. bbb' but breaks 'aaa.bbb'.
select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)
Hi!
On Mon, Apr 19, 2021 at 9:57 AM Valentin Gatien-Baron
<valentin.gatienbaron@gmail.com> wrote:
Looking at the tsvector and tsquery, we can see that the problem is
that the ":" counts as one position for the ts_query but not the
ts_vector:select to_tsvector('english', 'aaa: bbb'), websearch_to_tsquery('english', '"aaa: bbb"');
to_tsvector | websearch_to_tsquery
-----------------+----------------------
'aaa':1 'bbb':2 | 'aaa' <2> 'bbb'
(1 row)
It seems there is another bug with phrase search and query parsing.
It seems to me that since 0c4f355c6a websearch_to_tsquery() should
just parse text in quotes as a single token. Besides fixing this bug,
it simplifies the code.
Trying to fix this bug before 0c4f355c6a doesn't seem to worth the efforts.
I propose to push the attached patch to v14. Objections?
------
Regards,
Alexander Korotkov
Attachments:
0001-Make-websearch_to_tsquery-parse-text-in-quotes-as-a-.patchapplication/octet-stream; name=0001-Make-websearch_to_tsquery-parse-text-in-quotes-as-a-.patchDownload+53-74
Alexander Korotkov <aekorotkov@gmail.com> writes:
It seems there is another bug with phrase search and query parsing.
It seems to me that since 0c4f355c6a websearch_to_tsquery() should
just parse text in quotes as a single token. Besides fixing this bug,
it simplifies the code.
OK ...
Trying to fix this bug before 0c4f355c6a doesn't seem to worth the efforts.
Agreed, plus it doesn't sound like the sort of behavior change that
we want to push out in minor releases.
I propose to push the attached patch to v14. Objections?
This patch seems to include some unrelated fooling around in GiST?
regards, tom lane
On Sun, May 2, 2021 at 8:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alexander Korotkov <aekorotkov@gmail.com> writes:
It seems there is another bug with phrase search and query parsing.
It seems to me that since 0c4f355c6a websearch_to_tsquery() should
just parse text in quotes as a single token. Besides fixing this bug,
it simplifies the code.OK ...
Trying to fix this bug before 0c4f355c6a doesn't seem to worth the efforts.
Agreed, plus it doesn't sound like the sort of behavior change that
we want to push out in minor releases.
+1
I propose to push the attached patch to v14. Objections?
This patch seems to include some unrelated fooling around in GiST?
Ooops, I've included this by oversight. The next revision is attached.
Anything besides that?
------
Regards,
Alexander Korotkov
Attachments:
0001-Make-websearch_to_tsquery-parse-text-in-quotes-as-v2.patchapplication/octet-stream; name=0001-Make-websearch_to_tsquery-parse-text-in-quotes-as-v2.patchDownload+40-66
Alexander Korotkov <aekorotkov@gmail.com> writes:
Ooops, I've included this by oversight. The next revision is attached.
Anything besides that?
Some quick eyeball review:
+ /* Everything is quotes is processed as a single token */
Should read "Everything in quotes ..."
- /* or else gettoken_tsvector() will raise an error */
+ /* or else ƒtsvector() will raise an error */Looks like an unintentional change?
@@ -846,7 +812,6 @@ parse_tsquery(char *buf,
state.buffer = buf;
state.buf = buf;
state.count = 0;
- state.in_quotes = false;
state.state = WAITFIRSTOPERAND;
state.polstr = NIL;
This change seems wrong/unsafe too.
regards, tom lane
On Sun, May 2, 2021 at 10:57 AM Alexander Korotkov <aekorotkov@gmail.com>
wrote:
On Sun, May 2, 2021 at 8:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alexander Korotkov <aekorotkov@gmail.com> writes:
It seems there is another bug with phrase search and query parsing.
It seems to me that since 0c4f355c6a websearch_to_tsquery() should
just parse text in quotes as a single token. Besides fixing this bug,
it simplifies the code.OK ...
Trying to fix this bug before 0c4f355c6a doesn't seem to worth the
efforts.
Agreed, plus it doesn't sound like the sort of behavior change that
we want to push out in minor releases.+1
I propose to push the attached patch to v14. Objections?
This patch seems to include some unrelated fooling around in GiST?
Ooops, I've included this by oversight. The next revision is attached.
Anything besides that?
------
Regards,
Alexander Korotkov
Hi,
+ /* Everything is quotes is processed as a single token
*/
is quotes -> in quotes
+ /* iterate to the closing quotes or end of the string*/
closing quotes -> closing quote
+ /* or else ƒtsvector() will raise an error */
The character before tsvector() seems to be special.
Cheers
On Sun, May 2, 2021 at 9:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alexander Korotkov <aekorotkov@gmail.com> writes:
Ooops, I've included this by oversight. The next revision is attached.
Anything besides that?Some quick eyeball review:
+ /* Everything is quotes is processed as a single token */
Should read "Everything in quotes ..."
- /* or else gettoken_tsvector() will raise an error */ + /* or else ƒtsvector() will raise an error */Looks like an unintentional change?
Thank you for catching this!
@@ -846,7 +812,6 @@ parse_tsquery(char *buf,
state.buffer = buf;
state.buf = buf;
state.count = 0;
- state.in_quotes = false;
state.state = WAITFIRSTOPERAND;
state.polstr = NIL;This change seems wrong/unsafe too.
It seems OK, because this patch removes in_quotes field altogether.
We don't have to know whether we in quotes in the state, since we
process everything in quotes as a single token.
------
Regards,
Alexander Korotkov
Attachments:
0001-Make-websearch_to_tsquery-parse-text-in-quotes-as-v3.patchapplication/octet-stream; name=0001-Make-websearch_to_tsquery-parse-text-in-quotes-as-v3.patchDownload+39-65
On Sun, May 2, 2021 at 9:06 PM Zhihong Yu <zyu@yugabyte.com> wrote:
+ /* Everything is quotes is processed as a single token */
is quotes -> in quotes
+ /* iterate to the closing quotes or end of the string*/
closing quotes -> closing quote
+ /* or else ƒtsvector() will raise an error */
The character before tsvector() seems to be special.
Thank you for catching. Fixed in v3.
------
Regards,
Alexander Korotkov
On Sun, May 2, 2021 at 9:17 PM Zhihong Yu <zyu@yugabyte.com> wrote:
One minor comment:
+ /* iterate to the closing quotes or end of the string*/closing quotes -> closing quote
Yep, I've missed the third place to change from plural to single form :)
------
Regards,
Alexander Korotkov
Attachments:
0001-Make-websearch_to_tsquery-parse-text-in-quotes-as-v4.patchapplication/octet-stream; name=0001-Make-websearch_to_tsquery-parse-text-in-quotes-as-v4.patchDownload+39-65
Import Notes
Reply to msg id not found: CALNJ-vSvfzmcjgqwOxK7K-YqFnfC5-om7QRF7B4tDNqvgeg@mail.gmail.com
On Sun, May 2, 2021 at 11:12 AM Alexander Korotkov <aekorotkov@gmail.com>
wrote:
On Sun, May 2, 2021 at 9:06 PM Zhihong Yu <zyu@yugabyte.com> wrote:
+ /* Everything is quotes is processed as a single
token */
is quotes -> in quotes
+ /* iterate to the closing quotes or end of the
string*/
closing quotes -> closing quote
+ /* or else ƒtsvector() will raise an error */
The character before tsvector() seems to be special.
Thank you for catching. Fixed in v3.
------
Regards,
Alexander Korotkov
Hi,
One minor comment:
+ /* iterate to the closing quotes or end of the string*/
closing quotes -> closing quote
Cheers
Alexander Korotkov <aekorotkov@gmail.com> writes:
On Sun, May 2, 2021 at 9:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
- state.in_quotes = false;
This change seems wrong/unsafe too.
It seems OK, because this patch removes in_quotes field altogether.
Oh, sorry, I misread the patch --- I thought that earlier hunk
was removing a local variable. Agreed, if you can do without this
state field altogether, that's fine.
regards, tom lane
On Sun, May 2, 2021 at 9:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alexander Korotkov <aekorotkov@gmail.com> writes:
On Sun, May 2, 2021 at 9:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
- state.in_quotes = false;
This change seems wrong/unsafe too.
It seems OK, because this patch removes in_quotes field altogether.
Oh, sorry, I misread the patch --- I thought that earlier hunk
was removing a local variable. Agreed, if you can do without this
state field altogether, that's fine.
OK, thank you for review!
------
Regards,
Alexander Korotkov