[3.x]: Entry query title "does not contain" with underscore in string

[benjaminkohl]

What happened?

Description

I feel like this used to work but there is an entry query where we are trying to omit entries where the title contains _CLEAN. We set up the title parameter like so:

.title('not *_CLEAN*')

The query result is omitting all entries that have "CLEAN" so it is like the underscore doesn't matter. I even tried adding a whole bunch more underscores to that parameter string and it still omits all entries where "CLEAN" is in the title. To sanity check myself, I changed the string to "CLLEAN" and all the entries showed up again.

Expected behavior

The underscore character should not be ignored.

Actual behavior

It appears that the underscore is irrelevant and so are any other underscores added to that string.

Craft CMS version

3.7.30.1, 3.7.53.1

PHP version

7.3

Operating system and version

Darwin 20.6.0

Database type and version

MySQL 5.7.34

Image driver and version

Imagick 3.4.4 (ImageMagick 6.9.6-2)

Installed plugins and versions

Amazon S3 1.3.0
Cookies 1.1.15
CP Field Inspect 1.2.5
Craft Commerce 3.4.13
Imager X v3.5.5
Inventory 2.1.1
Lab Reports 1.0.0
Linkit 1.1.12.1
Link Vault 3.2.0
Redactor 2.9.0
Relabel 2.0.2
Typogrify 1.1.18

[angrybrad]

Craft has always normalized search indexes and search query terms:

cms/src/services/Search.php

Line 338 in 2ee1ed6

$keywords = SearchHelper::normalizeKeywords($keywords, [], true, $site->language);

Part of that normalization process is removing punctuation:

cms/src/helpers/Search.php

Lines 54 to 56 in 2ee1ed6

	// Remove punctuation and diacritics
	$punctuation = self::_getPunctuation();
	$str = str_replace(array_keys($punctuation), $punctuation, $str);

To which the underscore belongs:

cms/src/helpers/Search.php

Line 147 in 2ee1ed6

'_' => ' ',

Similar issues have been brought up before:

https://github.com/craftcms/cms/issues?q=is%3Aissue+search+normalize+keywords+is%3Aclosed

We have a long-standing FR to have the option to replace the core search provider from the native database-backed one to something like Algolia that I think is the ultimate fix for these:

#8111

[benjaminkohl]

Okay, thanks for the info. I didn't even know using that syntax relied on the search index. I always assumed those expressions got converted into SQL "LIKE" expressions. I'll make appropriate updates to my queries.

I was worried there would be existing issues already but wasn't sure what to search for to find them. Sorry for the dupe!

[brandonkelly]

You’re right @benjaminkohl, the title param just produces a condition directly on the element query; it doesn’t use the Search service.

Turns out that underscores are wildcard characters within like expressions in MySQL and PostgreSQL, so that explains the behavior you’re seeing.

I’ve just resolved this for the next Craft 3 and 4 releases by escaping underscores, so they are treated as literal underscores.

To get the fix early, change your craftcms/cms requirement in composer.json to "v3.x-dev as 3.7.53.1" and run composer update.

[angrybrad]

@benjaminkohl completely missed it was .title() and assumed it was .search() 🤦🏼

my bad

[benjaminkohl]

Ah, okay. I already deployed an alternative "andWhere" condition to the queries involved but that's cool that the underscores won't be an issue if it comes up again. Thanks!

[brandonkelly]

That works too!

Craft 3.7.54 and 4.2.4 are now tagged with the fix.