SOQL query with IN operator on deterministically encrypted field returns different data

Question

I have a SOQL query that uses the IN operator to find some Contacts by Email:

Set<String> emailSet = new Set<String>{.......};
Map<Id, Contact> contactMap = new Map<Id, Contact>([SELECT id, name, email
FROM Contact 
WHERE email IN :emailSet
ORDER BY LastModifiedDate DESC LIMIT 150]);

In my test:

if the Email field on the Contact object is not encrypted then I get 7 results but

if the field is encrypted with deterministic encryption then I get just 1 result

Literally the only thing that changes between executions is me marking the field as encrypted or not in Setup. I thought the IN operator can be used on deterministically encrypted fields since it is basically like an = operator on steroids and Salesforce does not throw any error, what is going on here?

Answer 1

One issue you may be having is that deterministic encryption queries are case sensitive. SFDC will save Contact.Email values as lowercase but if your search keys are mixed case, then the query will produce the results you see.

Normal SOQL (unencrypted) is case insensitive.

From the V45 Help

You can apply case-sensitive deterministic encryption or exact-match case-insensitive deterministic encryption (beta) to data on a field-by-field basis.

The exact match (beta) was added in V45; if your apex class is at V44 or lower, you don't even have the option