Why does 18-character ID casing matter in a SOQL WHERE clause?

Question

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

Answer 1

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!

Answer 2

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.