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

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

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!

Attribution
Source : Link , Question Author : Mark Pond , Answer Author : Community

Leave a Comment