Shield Encryption(Deterministic vs Probabilistic) and Where Clause

Question

I was going through salesforce documentation for shield encryption and got to know below.

Deterministic encryption comes in two types: case-sensitive and case-insensitive. With case-sensitive encryption, a SOQL query against the Contact object, where LastName = Jones, returns only Jones, not jones or JONES. Similarly, when the case-sensitive deterministic scheme tests for unicity (uniqueness), each version of “Jones” is unique. For case-insensitive, a SOQL query against the Lead object, where Company = Acme, returns Acme, acme, or ACME. When the case-insensitive scheme tests for unicity (uniqueness), each version of Acme is considered identical.

In the trailhead playground I ran following 2 cases.

Case 1: Enabled default shield encryption (Probablistic), and marked Lead 'Name' field as encrypted. Created a lead with Name = 'John Doe'

Tried to run below query on dev console [Select Id, Name FROM Lead where Name = 'John Doe'] As expected it gave me an error saying I cannot use 'Name' field in Where clause.

Case2: Enabled 'Deterministic' encryption, and changed Leads 'Name' field encryption to 'Deterministic-Case Sensitive'. Did the synch of data and tried to run the same query again.

[Select Id, Name FROM Lead WHERE Name = 'John Doe']

It still gives me same error as that of Case 1. I am not sure what is causing an error in second case? Am I reading the documentation in wrong way or am I missing something.

[Note: I did saw below warning message on encryption policy page, not sure if I need to reach out to Salesforce to do anything with my current data to run 2nd case]

Answer 1

I ran into this myself and raised a case with Salesforce with this description:

Doc says SOQL works on Contact.Name when deterministic encryption (case insensitive) is enabled but in fact, it does not (V46) Description Documentation Deterministic Encryption Considerations https://help.salesforce.com/articleView?id=security_pe_deterministic_considerations.htm&type=5 explicitly says:

"Case-insensitive deterministic encryption supports compound fields."

Yet when I deterministically encrypt case insensitive Contact.Name and do this SOQL query

SELECT Id, Name FROM Contact WHERE Name = 'William Jones'

I get error

[object Object]: select id, name from contact where name = 'William Jones' ^ ERROR at Row:1:Column:36 field 'name' can not be filtered in a query call

because Contact.name in metadata is marked as filterable = false.

Either doc is wrong or there's a bug/KI

The response was (after many interchanges)

Thank you for the confirmation. As per my research, I see this is working as designed. As mentioned in the article the "Concatenated values, such as compound names, aren’t the same as the separate values."

As mentioned in the document It is advised to use the "First Name and Last Name fields" in the query for getting the results.

I also cross-checked with my senior team and they have confirmed that " both forms of deterministic encryption (case-sensitive and case-insensitive) both support compound fields” but when querying these fields they have to be individual column queries."

and after I few more interchanges, I stated ...

OK - so you are acknowledging that this is a documentation bug. I look forward to a revised set of doc

you can close the case