From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Campbell, Lance" <lance@illinois.edu>
cc: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Re: SQL issue after migrating from version 13 to 15
In-reply-to: 
 <SJ0PR11MB562987A3390C885A8916E95ADED8A@SJ0PR11MB5629.namprd11.prod.outlook.com>
References: 
 <SJ0PR11MB562987A3390C885A8916E95ADED8A@SJ0PR11MB5629.namprd11.prod.outlook.com>
Comments: In-reply-to "Campbell, Lance" <lance@illinois.edu>
	message dated "Mon, 23 Oct 2023 15:19:35 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <381370.1698075077.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 23 Oct 2023 11:31:17 -0400
Message-ID: <381371.1698075077@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/381371.1698075077%40sss.pgh.pa.us>
Precedence: bulk

"Campbell, Lance" <lance@illinois.edu> writes:
> The below segment of the where clause works fine if the value passed is =
a single value like "real":

> AND to_tsvector('simple', CAST (table_column as text)) @@ to_tsquery('si=
mple', 'real')

> However, this no longer works when there are two values "real,impact". T=
he only change was migrating from PostgreSQL 13 to 15:

> AND to_tsvector('simple', CAST (table_column as text)) @@ to_tsquery('si=
mple', 'real,impact')

You really should define what you mean by "works" in a question
like this.

However, I think what you are unhappy about is that the interpretation
of that to_tsquery input has changed.  In v13:

regression=3D# select to_tsquery('simple', 'real,impact');
    to_tsquery     =

-------------------
 'real' & 'impact'
(1 row)

In v14 and later:

regression=3D# select to_tsquery('simple', 'real,impact');
     to_tsquery      =

---------------------
 'real' <-> 'impact'
(1 row)

The v14 release notes mention that there were incompatible changes in
this area, although they don't cite this specific case.  But anyway,
if the behavior you want is & then I'd suggest writing &, rather than
assuming that some other punctuation will behave the same.  Or you
could switch to plainto_tsquery(), which disregards the punctuation
altogether.

			regards, tom lane