Why does the LIKE SOQL operator implicitly translate picklist values?

Question

Assume, I've got a standard picklist field Rating on Account with values Hot, Cold. Using enabled Translation Workbench I've translated these values into German. When the language of a user is set to English, and the user performs a SOQL query

SELECT Id, Rating FROM Account WHERE Rating LIKE 'Cold'

it returns N records. But if we change the language of the user to German, the same SOQL query returns 0 records. However, when it changes to

SELECT Id, Rating FROM Account WHERE Rating LIKE 'Kalt'

it returns N records as well as for the very first scenario. At the same time, the returned values of the Rating field remain untranslated ('Cold'). This means that the LIKE operator implicitly translates the values of picklists before comparison. I've tested IN, NOT IN, = SOQL Comparison Operators and they don't behave like LIKE, they perform a comparison based on picklist value API Name not Label. There is no such information about it in Salesforce Docs. So finally my question is:

Why does the LIKE SOQL operator implicitly translate picklist values before comparison and how to perform a comparison based on picklist value API Names using the LIKE operator?

Answer 1

This is what I thought was the reason for the LIKE operator to automatically translate picklist values, and that has been confirmed on the documentation since Spring '21: The LIKE operator is used to match strings. The picklist value is translated, probably because of situations when the end-user searches for text in his own language.

Consider the following situation: a custom search component for a multinational company that supports many languages. The requirement is to enable search in picklist values that are being added/deactivated every month or so. If the user wants to search for a string, he won't type the API name of the field, but the translated version of the value he wants to get.

Answer 2

UPDATE

Since Spring 21 (API Version 51.0) the Translate Returned SOQL Results paragraph of Apex Docs was updated by adding the limitations:

• The LIKE operator can only query on the label of the picklist, not on its API name.

That means that the behavior described in the question is expected and now officially documented, and we should consider it.

Answer 3

I don't have a why for you, but if you have a scenario where LIKE needs to match the API name, you could create a formula field targeting the same picklist. FFs always return API name not label.