[5.x]: Sorting on a custom plain text field is case and accent sensitive

What happened?

Description

Using orderBy to order an element query resultset by a custom field appears to be case and accent sensitive, despite all tables in the database being collated as utf8mb4_0900_ai_ci.

Steps to reproduce

1. Create a section people with an entry type standardProfile having firstName and lastName text fields.
2. Format the title field in the entry type as {firstName} {lastName}
3. Create entries with the following names, populating the firstName and lastName fields

• Melanie Cr
• Michael Hu
• Mélanie Bl

4. Create a template to list the entries:

{% set people = craft.entries.section('people').orderBy('firstName ASC, lastName ASC').all() %}

<ol>
{% for person in people %}
    <li>{{ person.title }}</li>
{% endfor %}
</ol>

Expected behavior

1. Mélanie Bl
2. Melanie Cr
3. Michael Hu

Actual behavior

1. Melanie Cr
2. Michael Hu
3. Mélanie Bl

Notes

1. Explicitly passing the collation to ORDER BY yields the expected sort order:

{% set firstNameFieldSql = fieldValueSql(entryType('standardProfile'), 'firstName') %}
{% set lastNameFieldSql = fieldValueSql(entryType('standardProfile'), 'lastName') %}
{% set people = craft.entries.section('people').orderBy(firstNameFieldSql~' COLLATE \'utf8mb4_0900_ai_ci\' ASC, '~lastNameFieldSql~' COLLATE \'utf8mb4_0900_ai_ci\' ASC').all() %}

Sorting by title also yields the expected sort order.

2. I tried recreating the issue in a fresh Craft install and the order was correct. This may be an accidental result (perhaps dependent on the order that the entries were stored in the database), or it may indicate something specific about the original site that I've missed (configuration was otherwise identical).

3. I tried resaving all entries and re-running craft db/convert-charset, but it made no difference.

4. Possibly relevant:
   https://stackoverflow.com/questions/50509186/case-and-accent-insensitive-sorting-of-mysql-json-string

Craft CMS version

5.8.6

PHP version

8.2

Operating system and version

Mac OSX

Database type and version

MySQL 8.0.40

Image driver and version

No response

Installed plugins and versions

"carlcs/craft-redactorcustomstyles": "^5.0.0",
"craftcms/cms": "5.8.6",
"craftcms/feed-me": "6.8.0",
"craftcms/google-cloud": "^2.2.0",
"craftcms/postmark": "^3.1.0",
"craftcms/redactor": "4.2.0",
"croxton/imgixer": "^3.0.1",
"doublesecretagency/craft-googlemaps": "5.1.6",
"doublesecretagency/craft-siteswitcher": "^3.0.0",
"nilsenpaul/complete-cookie-consent": "dev-fix/vue-bundle as 2.0.0",
"nystudio107/craft-seomatic": "5.1.16",
"nystudio107/craft-similar": "5.0.2",
"putyourlightson/craft-sherlock": "5.1.2",
"putyourlightson/craft-sprig": "3.6.1",
"servd/craft-asset-storage": "4.0.17",
"solspace/craft-freeform": "5.11.5",
"studioespresso/craft-date-range": "^5.0.0-beta.4",
"studioespresso/craft-splashingimages": "5.0.4",
"verbb/calendar-links": "^4.0.0",
"verbb/image-resizer": "4.0.3",
"verbb/navigation": "3.0.9",
"vlucas/phpdotenv": "^5.4.0"

Start with the element query’s orderBy path and custom plain-text field value SQL, using the supplied people/firstName/lastName reproduction on MySQL 8.0.40; compare it with title sorting and explicit COLLATE behavior. Done means case- and accent-insensitive ordering produces Mélanie Bl, Melanie Cr, then Michael Hu without requiring callers to add COLLATE.