Non-selective query error Email-to-case Contact.Email Deterministically Encrypted when dup emails

Question

Given:

• Over 200,000 Contacts with emails
• Contact.Email is deterministically encrypted - case insensitive
• All Contacts encrypted
• Two contacts have the same email address [email protected]
• E2C executes triggers and eventually a selector class (V47) that does this SOQL: Select Id, ... FROM Contact WHERE Email IN :emails

When

• Email sent from [email protected] to E2C routing address

Then

• SFDC returns non-selective query error indicating field is not indexed or data skew (and Contact.Email is an OOTB indexed field)

BUT

• If there is only one Contact with Contact.Email = [email protected]
• An email sent to E2C from [email protected] works fine and Case is created

And here it gets weird ... executing the same SOQL query in execute anonymous using the E2C context user from Setup | Support Settings when there are duplicate Contacts for [email protected] works fine - either straight SOQL or execute anonymous of the selector class method.

The error occurs only when running E2C. This makes no sense as E2C should just be a client to the trigger execution path once the Case is created. Support Case filed

Related (fixed) Known Issue

Answer 1

To reproduce the error (V47), you need

• at least 200,000 Contacts with Contact.Email populated and encryption sync'd (I used 225,000 with 10,000 having Contact.Email=null)
• 2 Contacts with same email address
• Email-to-case enabled
• Send email from email address represented on 2 Contacts

RESULTS:

Contact.Email encrypted        Contacts w/ same Email   Email-to-Case Result
-----------------------        ----------------------   --------------------
Deterministic case-insensitive          2               SOQL non-selective exception
Deterministic case-sensitive            2               Success, no error; case created

So, since SFDC stores email addresses for Contact.Email always in lowercase, a viable workaround is the following:

• Use Deterministic (Case Sensitive) encryption on Contact.Email

• Adjust your SOQL queries to always lowercase the filtering emailAddress(es). For example (assumes someVbl is non-Null):

    [SELECT Id, Name FROM Contact WHERE Email = :someVbl.toLowerCase()];
  

UPDATE From Support/R&D 2019-12-03

In short, they (R&D) indicated the documentation on this needs to be clarified. The relevant section is at the bottom of the considerations for Deterministic:

https://help.salesforce.com/articleView?id=security_pe_deterministic_considerations.htm&type=5

Case-insensitive deterministic encryption supports standard indexes on email fields on the Contact and Lead objects, and the Email Message—Relation field. However, you might see slower performance on these fields when they’re encrypted with the case-insensitive deterministic scheme.

Because we say indexes are 'supported' but what we really mean is 'we allow such indexes to exist and do not block encryption, but the indexes do not function as expected.' If this seems like a poor distinction, that's because it is, at least now. Originally we could not support case-insensitive encryption on these fields at all because of the presence of an index that we could not modify. Subsequently, we figured out how to let the index continue to exist and not prevent case-insensitive encryption, but we have not yet been able to make the index actually useful.

A proper restatement would be something like: EmailMessageRelation fields, Contact.Email, and Lead.Email may all be encrypted with case-insensitive deterministic encryption, but doing so will prevent the use of Salesforce standard indexes, which could impact performance on your queries.

... They recommend switching to case sensitive encryption for Contact.Email though, so it fully supports the index that is needed due to the record count.