[3.x]: Unexpected results searching with :: syntax

What happened?

Description

We have some products we're trying to search by model number stored in a field productNumber. The model numbers follow a format like

ABC-1
ABC-1 DE-2
ABC-1 DE-3

Using the query {% set product = craft.products.search('productNumber::' ~ query).one() %} successfully returns a product when searching "ABC-1", but not when searching "ABC-1 DE-2" or "ABC-1 DE-3". Based on search documentation it seems like this is expected due to the space in the query.

However, using the query {% set product = craft.products.search('productNumber::"' ~ query ~ '"').one() %} returns no product for any of the queries.

Seems like this may be due to how the query is written:

SELECT "elementId"
FROM "searchindex"
WHERE ((("fieldId" = '7')) AND "keywords_vector" @@ 'abc & 1 & de & 2'::tsquery AND "keywords" LIKE 'abc 1 de 2') AND ("siteId"=1)

The LIKE statement string includes just the query segments, but the value in the keywords column has spaces prepended and appended to the string of segments.

Craft CMS version

Pro 3.7.44

PHP version

7.4.28

Operating system and version

Linux 4.15.0-65-generic

Database type and version

PostgreSQL 14.2

Image driver and version

Imagick 3.4.4 (ImageMagick 6.9.7-4)

Installed plugins and versions

Asset Usage 2.2.1 AsyncQueue 2.3.0 Back In Stock 1.4.0 Braintree for Craft Commerce 2.4.3 Craft Commerce 3.4.15 Feed Me 4.5.3 Formie 1.6.0 Gift Voucher 2.7.1 Purchase Patterns 1.1.5 Redactor 2.10.8 Retour 3.1.72 SEOmatic 3.4.31 ShipStation Connect 1.3.7 Sprout Encode Email 2.0.7 SuperSort 3.1.0 TaxJar 1.0.5.3 Videos 2.1.0 Wordsmith 3.3.2

craftcms / cms