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

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