From: "Campbell, Lance" <lance@illinois.edu>
To: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: SQL issue after migrating from version 13 to 15
Thread-Topic: SQL issue after migrating from version 13 to 15
Thread-Index: AdoFxC032wrPz2jlSaGS3OgEbHTFRw==
Date: Mon, 23 Oct 2023 15:19:35 +0000
Message-ID: 
 <SJ0PR11MB562987A3390C885A8916E95ADED8A@SJ0PR11MB5629.namprd11.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?FuUkbdlo97VVOeSq6t65WjKTONcpZdkk+VDbgsY6VxiVY49WQaC+ldvIYAmU?=
 =?us-ascii?Q?DRdZu1uKpCwQQqZj0EqMBDBmXgsPRIgEWmV78HJbtNHUcDU8HmHcbkV42Khf?=
 =?us-ascii?Q?m1mDDSNousgONp6u707VPCEHM9hTL/O/maf9lXwvbg08r3+QKklhdOhQ3SHG?=
 =?us-ascii?Q?91hMA7J5OFQej7wFpfe5VkNZBqahD0Obasb6h0yhcLjWQlvQR3OOxpyxpqiy?=
 =?us-ascii?Q?sd+HJ5pW5hXiMMzHIQvrLxqE+VoYoi5cLtIK8+K2mJ50P+zVwZVy9HMkE+Bc?=
 =?us-ascii?Q?GAs/PFht4y4WzS5WZUvZFZURypNdV6Gr6hWzXywXxC1yM0MTxYZM4bp9LM0J?=
 =?us-ascii?Q?JtfxcGSit+fkYvtObmdeSlo8AXKG83nFLrYUXL6eoyRDCZql8+cWrlmCY6gH?=
 =?us-ascii?Q?bflPaQk+WQjKzDiLsTO4OfsBECnrvzPVtOnEcBz6dqXtIBNNgZhTdJe4Jb0I?=
 =?us-ascii?Q?1NyGrXloG8IkAQsHl7NV5bk6vhquM6iP6zMsUyi62vwYao1OoStwQGRnfHUF?=
 =?us-ascii?Q?+cEOwsula6Ti2h1nFvlmonMJo6cnk6iSBSzOx2Ma85SKTdwjWxK1/QwYpqFy?=
 =?us-ascii?Q?FB+NA8PgaFdug+YIno0kVDBVfO5WzwbeRQU/r1K2R9zuivmAR5l/U7aghZ3N?=
 =?us-ascii?Q?4jRvBYN4J5qMkMmw6/UQ1uaEfZcmPsCPZu4A99iRWRXikEhXXN73ue0BfAK+?=
 =?us-ascii?Q?8JwLC7PdOGzMBvhqJ0Yd3j0Vc6unS3N+WHZLDgRr3R4qj6Qx9w/FyP62OKwR?=
 =?us-ascii?Q?yFBEKR3+3tsY0nobOlydGCt37l8lY6I53r1ud1UTRkEge3C2AFIavx0e1QwR?=
 =?us-ascii?Q?Fb2yGr+RSrqKxZ7t54PdcXKJhsvK9810Q8Tt0+ULeoTtpO+Z7mHPePuQQ4ff?=
 =?us-ascii?Q?fyhPA/fA5ix8ufF6cbcV8RFZuD/5vfjIteUgXmkT47msWMIzyvUmvDCT4/7W?=
 =?us-ascii?Q?7I6ZCdQPosZCKt73lZVSQNC//GHX6FZ2P69/aMLLUC2YCU7Fks+B2beQUAPa?=
 =?us-ascii?Q?rR3KuT8qp9hP6IpUVhRQsAUyjoHvri7mK9/nhWzFLgIL39VzTQWdURlzy9jy?=
 =?us-ascii?Q?avbeb3kBm9NxVmBcnPFWLA0oAPH1s1n9foh1mSNlIk0HG2rW1Ynxm+6JpKYK?=
 =?us-ascii?Q?yrY5NC12iwQ0VC+5IFYOIrYvrXajVHMi75pynzbxubbnU8FYPx9K8q0ohRHG?=
 =?us-ascii?Q?DZSzxgSdrThkiLYbpggNiTD2Qk4IZmmnoPV5U/h23JO/xSa6wmsW3NEgrtaj?=
 =?us-ascii?Q?2teA8o756VDqXnKYp9qxpWAqtd06QvheXWq4suU8cLM2Y7re9nkP8Vsktl2K?=
 =?us-ascii?Q?ABOmjxGV1SlLRaRilxP+oZo2VcREiP0O8o3maIIsFpf8FAgLRB2nrJORrZBE?=
 =?us-ascii?Q?tEH3IpdLAmhPn/Fabo8+0B9xssfsNbpdK2/KW/n9JRP3N5qNvqXLWlZVnTX3?=
 =?us-ascii?Q?Z+6Ei5IpDZFTWuoQ5MBvZJQJ7Wlobhe610+YQPOY2erytZRYpicmeFwaivy4?=
 =?us-ascii?Q?eYX34STAhD4Q1YDNZHjxcPabA1pQeU6j3UNWW1qtDybLQqnAMQ6DhDvENrvy?=
 =?us-ascii?Q?yd20qx/6AtkvAtP3wJE=3D?=
Content-Type: multipart/alternative;
	boundary="_000_SJ0PR11MB562987A3390C885A8916E95ADED8ASJ0PR11MB5629namp_"
MIME-Version: 1.0
X-OriginatorOrg: illinois.edu
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: SJ0PR11MB5629.namprd11.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 96603ef0-dca0-400d-a509-08dbd3db7729
X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Oct 2023 15:19:35.3128
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 44467e6f-462c-4ea2-823f-7800de5434e3
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 UiCmycrvExmJskSlxFpdlClxIux+c3VtYOADKS18s0G5IGZ4+HAgzfHFkzkSsPrwtUt1wBMVwboRNrlijZAfBA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SA0PR11MB4718
X-Proofpoint-GUID: u6JVFhVfI4kj4-1NAcAp0yj64trb37Ou
X-Proofpoint-ORIG-GUID: u6JVFhVfI4kj4-1NAcAp0yj64trb37Ou
X-Spam-Details: rule=cautious_plus_nq_notspam policy=cautious_plus_nq score=0
 priorityscore=1501 impostorscore=0 malwarescore=0 adultscore=0
 clxscore=1011 suspectscore=0 bulkscore=0 lowpriorityscore=0 mlxscore=0
 mlxlogscore=782 phishscore=0 spamscore=0 classifier=spam adjust=0
 reason=mlx scancount=1 engine=8.12.0-2310170001
 definitions=main-2310230133
X-Spam-Score: 0
X-Spam-OrigSender: lance@illinois.edu
X-Spam-Bar: 
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/SJ0PR11MB562987A3390C885A8916E95ADED8A%40SJ0PR11MB5629.namprd11.prod.outlook.com>
Precedence: bulk

--_000_SJ0PR11MB562987A3390C885A8916E95ADED8ASJ0PR11MB5629namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

We migrated our PostgreSQL database from version 13 to 15. Absolutely no co=
ding changes were made to our software.
table_column is a column in a table of type tsvector

The below segment of the where clause works fine if the value passed is a s=
ingle value like "real":

AND to_tsvector('simple', CAST (table_column as text)) @@ to_tsquery('simpl=
e', 'real')

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

AND to_tsvector('simple', CAST (table_column as text)) @@ to_tsquery('simpl=
e', 'real,impact')

No exception is being thrown.

Thanks for your help with this.

Lance Campbell
University of Illinois

--_000_SJ0PR11MB562987A3390C885A8916E95ADED8ASJ0PR11MB5629namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">We migrated our PostgreSQL database from version 13 =
to 15. Absolutely no coding changes were made to our software.<o:p></o:p></=
p>
<p class=3D"MsoNormal">table_column is a column in a table of type tsvector=
<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">The below segment of the where clause works fine if =
the value passed is a single value like &#8220;real&#8221;:<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">AND to_tsvector('simple', CAST (table_column as text=
)) @@ to_tsquery('simple', 'real')<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">However, this no longer works when there are two val=
ues &#8220;real,impact&#8221;. The only change was migrating from PostgreSQ=
L 13 to 15:<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">AND to_tsvector('simple', CAST (table_column as text=
)) @@ to_tsquery('simple', 'real,impact')<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">No exception is being thrown.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Thanks for your help with this.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Lance Campbell<o:p></o:p></p>
<p class=3D"MsoNormal">University of Illinois <o:p></o:p></p>
</div>
</body>
</html>

--_000_SJ0PR11MB562987A3390C885A8916E95ADED8ASJ0PR11MB5629namp_--