API Get action lookups for alphanumeric custom fields with option values with uppercase characters fails to find contacts
Replicable on https://dmaster.demo.civicrm.org
Create a custom field on a contact that is Alphanumeric and uses a Multi-Select widget (may be a condition for other custom field types / widgets as well)
Add a few options, but make at least one of the option values contain at least one uppercase character.
Update a contact, give the custom field value one of the options that has an uppercase character in the value.
Use the API anywhere, or use the API explorer, and do a Contact, get call, where custom_[id] = the option with the uppercase character.
No contacts will be returned.
Here where the custom field query is built, the param value for the custom field is made lowercase: https://github.com/civicrm/civicrm-core/blob/master/CRM/Core/BAO/CustomQuery.php#L338
In this file https://github.com/civicrm/civicrm-core/blob/master/CRM/Contact/BAO/Query.php#L5687
The operator RLIKE is updated to include the BINARY operator option Which makes the clause case sensitive...
Introduced in this commit: https://github.com/civicrm/civicrm-core/commit/a047568869892f9af65002c1709af1931f5f4b0d This PR: https://github.com/civicrm/civicrm-core/pull/12364
So it looks like a fix for the search builder regex search, has broken the API Contact Get for custom fields with option values with uppercase characters.
This could affect other things?
I'm not sure how to make these two things gel, but surely querying via the CiviCRM API should work, or take precedence.
I'm happy to make a PR, but I'm not sure what is the best thing to do...
Do we really need to make the option value lowercase when building the custom field query where clause?