What happened?

Description

I just encountered one surprising change when updating from Craft 4 to Craft 5 that I haven't seen a mention of in the docs or in the changelog (though of course it's possible that I missed it). All custom field conditions are case-sensitive now, even when using a case-insensitive collation. At least in MySQL, can't speak to PostgreSQL.

I think that's because the content is now stored in a JSON column. Apparently, extracted JSON columns are always treated as utf8mb4_bin by default, so all comparisons are binary, i.e. case-sensitive. We're always using utf8mb4_0900_ai_ci, so this was a bit surprising.

In Craft 4 this always worked, regardless of whether the first_name was Dwight or dwight:

{% set = craft.entries()
    .first_name('dwight')
    .one()
%}

In Craft 5, this will not work if the first name is Dwight with a capital D.

This does not affect the search index (.search()) since the search terms are always lowercased. But it does affect all custom field conditions against fields that store their content in the content column, as far as I can tell.

Steps to reproduce

1. Use a lowercase value in a custom field condition when the field has capital letters in it.

Expected behavior

If possible, the built-in fields should handle this edge-case automatically. Maybe modifyElementsQuery for the built-in fields can be adjusted to do a case-insensitive comparison? Though this might be bad for performance, not sure.

In any case, the current behaviour was pretty surprising since we're already using a case-insensitive collation. If this behaviour can't be changed, it should be mentioned in a couple of places in the docs. At the very least:

• In the Craft 4 -> Craft 5 upgrade guide.
• In the documentation on element queries.

Actual behavior

The breaking change from Craft 4 to 5 is mostly undocumented and unexpected.

Craft CMS version

5.2.6

PHP version

8.3

Operating system and version

No response

Database type and version

MySQL 8

Image driver and version

No response

Installed plugins and versions

No response