1

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?

3

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

  • Thank you for the information, it is very valuable! I am not sure this is my problem but I have to double check and will mark the answer as accepted if that is the case. – Perennialista Feb 15 at 16:52
  • That was exactly it, specifically the combination between the fact that deterministic encryption makes the query case-sensitive and the fact that email is stored lowercase in the DB! – Perennialista Mar 12 at 22:03
  • 1
    @Perennialista - imagine how much fun I had (and you will have) to ensure all your Contact queries filtered by email have to lowercase the filter before executing – cropredy Mar 12 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.