11

When an ID value is upper-case the expected rows are returned in a SOQL query. However, when using a lower-case ID in the where clause, zero rows are returned.

It also doesn't matter if it's a String or an Id type.

Is this a change to SQOL where clause behavior? I would expect it to work no matter the case of the string... after all, what's the point of case-insensitivity if you've got to make sure it's in the proper case before you execute the query?

Update further investigation has revealed that the behavior is different per object. In the two examples, Account and User queries behave differently. Accounts don't work at all but Users do when the Id is upper-case.

User

Exec Anon - User

String upperId = '005L0000001JVZUIA4';
List<User> users = [SELECT Id FROM User WHERE Id =: upperId];
system.debug(users);

String lowerId = '005L0000001JVZUIA4'.toLowerCase();
List<User> users2 = [SELECT Id FROM User WHERE Id =: lowerId];
system.debug(users2);

Id idFromUpper = (Id)upperId;
List<User> users3 = [SELECT Id FROM User WHERE Id =: idFromUpper];
system.debug(users3);

Id idFromLower = (Id)lowerId;
List<User> users4 = [SELECT Id FROM User WHERE Id =: idFromLower];
system.debug(users4);

Result - User

EXECUTION_STARTED
CODE_UNIT_STARTED|[EXTERNAL]|execute_anonymous_apex
SOQL_EXECUTE_BEGIN|[2]|Aggregations:0|select Id from User where Id = :tmpVar1
SOQL_EXECUTE_END|[2]|Rows:1
SYSTEM_METHOD_ENTRY|[3]|System.debug(ANY)
USER_DEBUG|[3]|DEBUG|(User:{Id=005L0000001JVZUIA4})
SYSTEM_METHOD_EXIT|[3]|System.debug(ANY)

SYSTEM_METHOD_ENTRY|[5]|String.toLowerCase()
SYSTEM_METHOD_EXIT|[5]|String.toLowerCase()
SOQL_EXECUTE_BEGIN|[6]|Aggregations:0|select Id from User where Id = :tmpVar1
SOQL_EXECUTE_END|[6]|Rows:0
SYSTEM_METHOD_ENTRY|[7]|System.debug(ANY)
USER_DEBUG|[7]|DEBUG|()
SYSTEM_METHOD_EXIT|[7]|System.debug(ANY)

SOQL_EXECUTE_BEGIN|[10]|Aggregations:0|select Id from User where Id = :tmpVar1
SOQL_EXECUTE_END|[10]|Rows:1
SYSTEM_METHOD_ENTRY|[11]|System.debug(ANY)
USER_DEBUG|[11]|DEBUG|(User:{Id=005L0000001JVZUIA4})
SYSTEM_METHOD_EXIT|[11]|System.debug(ANY)

SOQL_EXECUTE_BEGIN|[14]|Aggregations:0|select Id from User where Id = :tmpVar1
SOQL_EXECUTE_END|[14]|Rows:0
SYSTEM_METHOD_ENTRY|[15]|System.debug(ANY)
USER_DEBUG|[15]|DEBUG|()
SYSTEM_METHOD_EXIT|[15]|System.debug(ANY)

CUMULATIVE_LIMIT_USAGE
LIMIT_USAGE_FOR_NS|(default)|
  Number of SOQL queries: 4 out of 100
  Number of query rows: 2 out of 50000

Account

Exec Anon - Account

List<Account> a = [SELECT Id FROM Account LIMIT 1];
system.debug(a);

String upperId = '001E000000WTEqoIAH'.toUpperCase();
system.debug('Upper: ' + upperId);
List<Account> accounts = [SELECT Id FROM Account WHERE Id =: upperId];
system.debug('Accounts: ' + accounts);

String lowerId = '001E000000WTEqoIAH'.toLowerCase();
system.debug('Lower: ' + lowerId);
List<Account> accounts2 = [SELECT Id FROM Account WHERE Id =: lowerId];
system.debug('Accounts 2:' + accounts2);

Result - Account

EXECUTION_STARTED
CODE_UNIT_STARTED|[EXTERNAL]|execute_anonymous_apex
SOQL_EXECUTE_BEGIN|[1]|Aggregations:0|select Id from Account limit 1
SOQL_EXECUTE_END|[1]|Rows:1
SYSTEM_METHOD_ENTRY|[2]|System.debug(ANY)
USER_DEBUG|[2]|DEBUG|(Account:{Id=001E000000WTEqoIAH}) // curiously, it's an 18-character in mixed case
SYSTEM_METHOD_EXIT|[2]|System.debug(ANY)

SYSTEM_METHOD_ENTRY|[4]|String.toUpperCase()
SYSTEM_METHOD_EXIT|[4]|String.toUpperCase()
SYSTEM_METHOD_ENTRY|[5]|System.debug(ANY)
USER_DEBUG|[5]|DEBUG|Upper: 001E000000WTEQOIAH
SYSTEM_METHOD_EXIT|[5]|System.debug(ANY)

SOQL_EXECUTE_BEGIN|[6]|Aggregations:0|select Id from Account where Id = :tmpVar1
SOQL_EXECUTE_END|[6]|Rows:0
SYSTEM_METHOD_ENTRY|[7]|String.valueOf(Object)
SYSTEM_METHOD_EXIT|[7]|String.valueOf(Object)
SYSTEM_METHOD_ENTRY|[7]|System.debug(ANY)
USER_DEBUG|[7]|DEBUG|Accounts: ()
SYSTEM_METHOD_EXIT|[7]|System.debug(ANY)

SYSTEM_METHOD_ENTRY|[9]|String.toLowerCase()
SYSTEM_METHOD_EXIT|[9]|String.toLowerCase()
SYSTEM_METHOD_ENTRY|[10]|System.debug(ANY)
USER_DEBUG|[10]|DEBUG|Lower: 001e000000wteqoiah
SYSTEM_METHOD_EXIT|[10]|System.debug(ANY)

SOQL_EXECUTE_BEGIN|[11]|Aggregations:0|select Id from Account where Id = :tmpVar1
SOQL_EXECUTE_END|[11]|Rows:0
SYSTEM_METHOD_ENTRY|[12]|String.valueOf(Object)
SYSTEM_METHOD_EXIT|[12]|String.valueOf(Object)
SYSTEM_METHOD_ENTRY|[12]|System.debug(ANY)
USER_DEBUG|[12]|DEBUG|Accounts 2:()
SYSTEM_METHOD_EXIT|[12]|System.debug(ANY)

CUMULATIVE_LIMIT_USAGE
LIMIT_USAGE_FOR_NS|(default)|
  Number of SOQL queries: 3 out of 100
  Number of query rows: 1 out of 50000
8

Posting this here just so that anyone else who comes along and missed the spectacular shining gem dropped by Peter Chittum in another question.

https://salesforce.stackexchange.com/a/9570/660

The "case-insensitive" Id is not case-insensitive in and of itself. It is unique case-insensitive.

Could SFDC convert the id using the reverse of the algorithm derived from the last 3 digits of the 18? Yes. But we don't. Not being the person who designed or built them, but just another developer in the field like you, near as I can tell we just drop those characters, meaning if you have code that manipulates any of the alpha characters and changes their case, you will have broken the Id.

I'm so happy to have come across this. Thanks Peter!

1

Reason behind this is the ID checksum. ID is combination of Record Unique Identifier + Checksum calculated of Record Unique Identifier.

Ids are case sensitive. And their checksum must not be changed during transaction.

In you case you have Id in upper case. I do have 00590000001LjyRAAS.

So Query against your logic only third will work only.

// It doesn't work
String uppercaseId = '00590000001LjyRAAS'.toUpperCase();
List<User> users = [SELECT Id FROM User WHERE Id =: uppercaseId];
system.debug(users);

// It doesn't work
String lowerId = '00590000001LjyRAAS'.toLowerCase();
List<User> users2 = [SELECT Id FROM User WHERE Id =: lowerId];
system.debug(users2);

// It works
String actualId = '00590000001LjyRAAS';
List<User> users3 = [SELECT Id FROM User WHERE Id =: actualId];
system.debug(users3);

In browser:

// It does not work (character Y is capital)
https://huddle-idp-dev-ed.my.salesforce.com/00590000001LjYR?noredirect=1

// It works
https://huddle-idp-dev-ed.my.salesforce.com/00590000001LjyR?noredirect=1

All character cases (including check sum if SOQL) must match to get expected result.

  • This answer doesn't make logical sense. The whole reason for the 3 checksum characters is so that the ID can be represented in a case-insensitive manner where the checksum characters can be used to determine the case sensitive representation. If the casing must match on all 18 characters, that completely defeats the purpose. – Mark Pond Sep 22 '14 at 18:19
  • @MarkPond But it doesn't work. Few people face this problem as most of the logic is automated and no one manipulate Ids during execution. – Ashwani Sep 22 '14 at 18:24
  • So, given an 18 character ID that is in lower-case (for any reason) what's your suggested approach to being able to query for that record? – Mark Pond Sep 22 '14 at 18:28
  • @MarkPond Earlier case insensitive IDs were introduced to make VLookup work and make it easy to export data into excel. As well in formulas functions case sensitive IDs were causing various issues. So Salesforce made Ids case insensitive for those scenarios and changes logic to return 18 character Id in all scenarios. But it doesn't work in Apex mode. – Ashwani Sep 22 '14 at 18:36
  • Your examples demonstrate that only the first 15 characters of the Id string are considered by by the SOQL query and because of that, it's case-sensitive. That's interesting. – Mark Pond Sep 22 '14 at 22:32

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.