Feedback-ID: i0c21471d:Fastmail
MIME-Version: 1.0
Date: Thu, 15 Aug 2024 12:13:06 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: bosamia.karan@gmail.com, pgsql-bugs@lists.postgresql.org
Message-Id: <f90f08b5-ba6d-4b39-9653-ede0f70a1be9@app.fastmail.com>
In-Reply-To: <18580-11fe381a9adde140@postgresql.org>
References: <18580-11fe381a9adde140@postgresql.org>
Subject: Re: BUG #18580: The pg_similarity appears to be wrong
Content-Type: multipart/alternative;
 boundary=1f6e19882c7446dea02b348f73fd9a7d
Archived-At: 
 <https://www.postgresql.org/message-id/f90f08b5-ba6d-4b39-9653-ede0f70a1be9%40app.fastmail.com>
Precedence: bulk

--1f6e19882c7446dea02b348f73fd9a7d
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Mon, Aug 12, 2024, at 6:58 AM, PG Bug reporting form wrote:
> SELECT *
> FROM (
>   SELECT 
>     *, 
>     similarity(provision_clean_description, 'Policies The General Partner
> shall promptly notify the Investor of any proposed changes in the Funds
> leverage policies including adjustments to leverage ratios') AS sim_tim
>   FROM provision_database
> ) pd
>   WHERE sim_tim <= 1 and sim_tim > 0.7 and firm_id=18;
> 
> This both sentences giving similarity score as 1 despite the fact that the
> sentence 1. has Policies as the starting word(do not include the starting
> hyphen in the sentences):
> - Policies The General Partner shall promptly notify the Investor of any
> proposed changes in the Funds leverage policies including adjustments to
> leverage ratios
> - The General Partner shall promptly notify the Investor of any proposed
> changes in the Funds leverage policies including adjustments to leverage
> ratios
> 

This is not a bug.

That's how trigram works. The documentation [1] explains that the words don't
need to be in the same order because it counts the number of common trigrams.
Trigrams are extracted ignoring non-alphanumeric characters. Trigrams are
case-insensitive. You can check the trigrams extracted using the show_trgm()
function.

--
-- return the non-common trigrams
--
WITH a AS (
SELECT x FROM unnest(show_trgm('Policies The General Partner shall promptly
notify the Investor of any proposed changes in the Funds leverage policies
including adjustments to leverage ratios')) x),
b AS (
SELECT x FROM unnest(show_trgm('The General Partner shall promptly notify the
Investor of any proposed changes in the Funds leverage policies including
adjustments to leverage ratios')) x)
SELECT * FROM a FULL JOIN b ON (a.x = b.x) WHERE a.x IS NULL OR b.x IS NULL;

[1] https://www.postgresql.org/docs/current/pgtrgm.html#PGTRGM-CONCEPTS


--
Euler Taveira
EDB   https://www.enterprisedb.com/

--1f6e19882c7446dea02b348f73fd9a7d
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>On Mon, Aug 12,=
 2024, at 6:58 AM, PG Bug reporting form wrote:<br></div><blockquote typ=
e=3D"cite" id=3D"qt" style=3D""><div>SELECT *<br></div><div>FROM (<br></=
div><div>&nbsp; SELECT&nbsp;<br></div><div>&nbsp;&nbsp;&nbsp; *,&nbsp;<b=
r></div><div>&nbsp;&nbsp;&nbsp; similarity(provision_clean_description, =
'Policies The General Partner<br></div><div>shall promptly notify the In=
vestor of any proposed changes in the Funds<br></div><div>leverage polic=
ies including adjustments to leverage ratios') AS sim_tim<br></div><div>=
&nbsp; FROM provision_database<br></div><div>) pd<br></div><div>&nbsp; W=
HERE sim_tim &lt;=3D 1 and sim_tim &gt; 0.7 and firm_id=3D18;<br></div><=
div><br></div><div>This both sentences giving similarity score as 1 desp=
ite the fact that the<br></div><div>sentence 1. has Policies as the star=
ting word(do not include the starting<br></div><div>hyphen in the senten=
ces):<br></div><div>- Policies The General Partner shall promptly notify=
 the Investor of any<br></div><div>proposed changes in the Funds leverag=
e policies including adjustments to<br></div><div>leverage ratios<br></d=
iv><div>- The General Partner shall promptly notify the Investor of any =
proposed<br></div><div>changes in the Funds leverage policies including =
adjustments to leverage<br></div><div>ratios<br></div><div><br></div></b=
lockquote><div><br></div><div>This is not a bug.</div><div><br></div><di=
v>That's how trigram works. The documentation [1] explains that the word=
s don't<br></div><div>need to be in the same order because it counts the=
 number of common trigrams.<br></div><div>Trigrams are extracted ignorin=
g non-alphanumeric characters. Trigrams are<br></div><div>case-insensiti=
ve. You can check the trigrams extracted using the show_trgm()<br></div>=
<div>function.<br></div><div><br></div><div>--<br></div><div>-- return t=
he non-common trigrams<br></div><div>--<br></div><div>WITH a AS (<br></d=
iv><div>SELECT x FROM unnest(show_trgm('Policies The General Partner sha=
ll promptly<br></div><div>notify the Investor of any proposed changes in=
 the Funds leverage policies<br></div><div>including adjustments to leve=
rage ratios')) x),<br></div><div>b AS (<br></div><div>SELECT x FROM unne=
st(show_trgm('The General Partner shall promptly notify the<br></div><di=
v>Investor of any proposed changes in the Funds leverage policies includ=
ing<br></div><div>adjustments to leverage ratios')) x)<br></div><div>SEL=
ECT * FROM a FULL JOIN b ON (a.x =3D b.x) WHERE a.x IS NULL OR b.x IS NU=
LL;<br></div><div><br></div><div>[1] <a href=3D"https://www.postgresql.o=
rg/docs/current/pgtrgm.html#PGTRGM-CONCEPTS">https://www.postgresql.org/=
docs/current/pgtrgm.html#PGTRGM-CONCEPTS</a><br></div><div><br></div><di=
v><br></div><div id=3D"sig112409080"><div class=3D"signature">--<br></di=
v><div class=3D"signature">Euler Taveira<br></div><div class=3D"signatur=
e">EDB&nbsp;&nbsp; <a href=3D"https://www.enterprisedb.com/">https://www=
.enterprisedb.com/</a><br></div></div><div><br></div></body></html>
--1f6e19882c7446dea02b348f73fd9a7d--