From: "Campbell, Lance" <lance@illinois.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: RE: SQL issue after migrating from version 13 to 15
Thread-Topic: SQL issue after migrating from version 13 to 15
Thread-Index: AdoFxC032wrPz2jlSaGS3OgEbHTFRwAAcmyAAADsoDA=
Date: Mon, 23 Oct 2023 15:58:13 +0000
Message-ID: 
 <SJ0PR11MB5629FF3A0251014B14EBEB79DED8A@SJ0PR11MB5629.namprd11.prod.outlook.com>
References: 
 <SJ0PR11MB562987A3390C885A8916E95ADED8A@SJ0PR11MB5629.namprd11.prod.outlook.com>
 <381371.1698075077@sss.pgh.pa.us>
In-Reply-To: <381371.1698075077@sss.pgh.pa.us>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?cH3RI20hydD1XiaNGUi9FRCfvZziLFxikhBzLTaJnciVOWPYe4mRmAoFAh1z?=
 =?us-ascii?Q?UX5QDJqWV/88LpoUwnC4i8n0HsUX9H25+JqqsvBFhHy3FwW7x5wmBdd18Du8?=
 =?us-ascii?Q?T3ckU4OD2YrSBbnnz5I48jDa0JloTwOIHnDEh7CTf0BJLky2gXvV6hqd+3HZ?=
 =?us-ascii?Q?838XEFmd92wTXYBDqIje7jU4XXX8AjSQEhAL/zwbUhloofja4inqgmqFU7Ii?=
 =?us-ascii?Q?oBJsqoAR9nDcNLV26Nh7efJM9S20K1t7rl6VhrejAGUGEzus2WUB+WX2LO3/?=
 =?us-ascii?Q?d9SKgVmbWgEFWFJWZ2a+8YgtWZB66puUyRRM8S2e2Ca9iEEdky+oiVFt26rR?=
 =?us-ascii?Q?V3K54AE7DQ1+oQtDoulauWg9xdRWlDAW2fjpzrNwilBAOZBXzETmmobGNuef?=
 =?us-ascii?Q?g6x1GU3/ExxXPsYWBlahYgJPshB6Rj05jElskoe+rZiONi+wAf+Lvo7hnpOe?=
 =?us-ascii?Q?NvW7VBkvRSmmiw73AzxWs66eQIt6Z/i1+Dl5JXwwAClxWlF0l6uKA9kJDolo?=
 =?us-ascii?Q?dNHNqs7I15MijvySMjAED94HMS6bcB3De9UmZyk70bpTLmRuYSRWdHncdDLB?=
 =?us-ascii?Q?UaH6QBI6aDPRIfOCkkphUzFeBOfx3R53M0dj5RAORhukVZcOe9kz6zj9J9hY?=
 =?us-ascii?Q?ag0rf5VHfgsOZ7nCEaRePHYxhpuWk1vx6oI9s70e1LcK7r8aiaugltsfkD9k?=
 =?us-ascii?Q?GBPPA26GPfCQjxqN4Cd7IHc2m1AbR7AruiDSMWSdyoUeb8of/nXI6e0LO22c?=
 =?us-ascii?Q?QQO0oaU6IW+5mjSNxxdN6s58dh2kKNknL+8Jm3KaEKZ6b6/SxtfpsQ/tp/zC?=
 =?us-ascii?Q?NFPdiv4Lq/ICjXf3qd7WXgf4x/QdqdXAK2D9rXyXHEXzhtgm1nZfDxxvhX9J?=
 =?us-ascii?Q?uU5DWn+k1A259/gSKN0kxk6qNLul2kkJZUSBUUdyb5TFnHcv+Sojjt3iVB0v?=
 =?us-ascii?Q?m6Gd0DiQhTrxsRUGX9wlvoO5EFfDQacoBMq1bV0vyzVDNfbvAKOIsV84mj+a?=
 =?us-ascii?Q?u6k453/ClI4cwESHtRWJQpTA39Hd8jKVd6t6Xx9ZVE5u/g3kg3P8We8MgzmS?=
 =?us-ascii?Q?XwrUDLtejTZiZvYUZU9AnKP9zLfo+qmtapgkRCf1h5hin3qjqh0l26bZBh9B?=
 =?us-ascii?Q?TtibxkOSNTNJy0MrNGv+IZz/7TsrWXynEKrSAFk1v554hPscDEFF4km5kxGt?=
 =?us-ascii?Q?AJ4A9K+AR/lkyaSLtkZi7DvUtS7E5PjaBspYkwxl3NS/UFGmun/WG+V5lMPA?=
 =?us-ascii?Q?8YY2GrzK5DkF339FM55eftZIXBYoYa8F5/bMp3gJveMjR8wc8J7oBXEbgOOe?=
 =?us-ascii?Q?Cerj1PL0ozPE2KDXWHFR8FgiZcz5cwzMDt9AGI1IUSfucTor/MlQ3Ow2/rtX?=
 =?us-ascii?Q?IoAc3Xqhwbas5Enfv0mvVX9iI6G28NziQr7SJaWLvHjdkeVd/5mezmDmYGSn?=
 =?us-ascii?Q?gMPDucS+rNFMWUHmAWo2zrCqFDdPfGEjqCupczCG6qWpcHd/+pHLq6Krk8TW?=
 =?us-ascii?Q?OAP7vHhfDowA1+jzZG+oq1N7cYUjgl2FjLXSlSsSmXepV/M5ELv7S7Cr9RoI?=
 =?us-ascii?Q?U8dTQkhkLllu7G0k7gA=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
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: 
 8758e581-e72b-463c-1c6d-08dbd3e0dcf9
X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Oct 2023 15:58:13.6288
 (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: 
 25LnAZCbfaVgK1qOPSsrXx2LdPcPKB/sZ41KKkuBSbpLbyd/lQO+W0KTgeaXrh5eR36CcXJF4Hc/7rwx+PgrZA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: DS7PR11MB6038
X-Proofpoint-GUID: ow4WntcEs1SNzFO5AJd8AmGGGT3USfNI
X-Proofpoint-ORIG-GUID: ow4WntcEs1SNzFO5AJd8AmGGGT3USfNI
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=999 phishscore=0 spamscore=0 classifier=spam adjust=0
 reason=mlx scancount=1 engine=8.12.0-2310170001
 definitions=main-2310230139
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/SJ0PR11MB5629FF3A0251014B14EBEB79DED8A%40SJ0PR11MB5629.namprd11.prod.outlook.com>
Precedence: bulk

Thanks so much for the assistance. That resolved my issue. I hope you have =
a great week.

Lance

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>=20
Sent: Monday, October 23, 2023 10:31 AM
To: Campbell, Lance <lance@illinois.edu>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: SQL issue after migrating from version 13 to 15

"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)) @@=20
> to_tsquery('simple', 'real')

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

> AND to_tsvector('simple', CAST (table_column as text)) @@=20
> to_tsquery('simple', '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 t=
hat to_tsquery input has changed.  In v13:

regression=3D# select to_tsquery('simple', 'real,impact');
    to_tsquery    =20
-------------------
 'real' & 'impact'
(1 row)

In v14 and later:

regression=3D# select to_tsquery('simple', 'real,impact');
     to_tsquery     =20
---------------------
 '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 beha=
vior you want is & then I'd suggest writing &, rather than assuming that so=
me other punctuation will behave the same.  Or you could switch to plainto_=
tsquery(), which disregards the punctuation altogether.

			regards, tom lane