Using Ids and strings in SOQL queries, a word of warning.

When querying your sObject data to retrieve particular objects, a common approach is to use Id or String fields as the criteria in a SOQL query.

Take a look at the following sample code that retrieves account objects by Id, and updates related contacts so that they are opted in to future fax and email communications. This type of function may be commonly used in batch operations or triggers.

// Opt all contacts related to any of a list of accounts
// in to email and fax communications. 
public static void optInContacts (List<Account> updatedAccounts)
{
    // Put the accounts Ids in a set
    Set<Id> accountIds = new Set<Id>{};
        
    for (Account acc: updatedAccounts)
    {
        accountIds.add(acc.Id);
    }
        
    // Use SOQL to retrieve the related contacts
    List<Contact> relatedContacts = 
        [SELECT HasOptedOutOfFax, HasOptedOutOfEmail
         FROM Contact
         WHERE AccountId IN :accountIds];
        
    // For each contact set opt out preferences
    for (Contact con : relatedContacts)
    {
        con.HasOptedOutOfFax   = false;
        con.HasOptedOutOfEmail = false;
    }
        
    // Update the contact records
    update relatedContacts;
}

Doesn't look too unusual right? However, upon running this method, you may affect more records than you intend to. The problem is that internal, non-API Salesforce code uses 15 character unique object Ids. These object Ids are case sensitive (externally 18 character insensitive Ids including check digits are available). However, database query tool SOQL is not case sensitive.

So in the above example imagine there were two account objects in your org with Ids 01rG0000000FBr6 and 01rG0000000FbR6 respectively. If you ran the code operation on a collection that contains the account with Id 01rG0000000FBr6, then both records would be picked up by the case-insensitive query and processed, potentially signing up contacts for communications when they have already refused.

There are a few ways to deal with this. As a general rule I would recommend using names as oppose to Ids. This however comes with the caveat that all your names are alphabetically unique. This will definitely work if the name is an auto-number field.

Alternatively you could use the set.contains method to remove the incorrect values before you process the results. The updated method looks something like this:

public class IdInsensitivityTest{

    // Opt all contacts related to any of a list of accounts
    // in to email and fax communications. 
    public static void optInContacts (List<Account> updatedAccounts)
    {
        // Put the accounts Ids in a set
        Set<Id> accountIds = new Set<Id>{};
        
        for (Account acc: updatedAccounts)
        {
            accountIds.add(acc.Id);
        }
        
        // Use SOQL to retrieve the related contacts
        List<Contact> relatedContacts = new List<Contact>{};
        
        for (Contact c : [SELECT AccountId, HasOptedOutOfFax, HasOptedOutOfEmail
                          FROM Contact
                          WHERE AccountId IN :accountIds])
        {
            // Use the set "contains" method to determine if 
            // the contacts related account is correct
            // Even though SOQL is not case sensitive, Apex is
            if (accountIDs.contains(c.AccountId))
            {
                relatedContacts.add(c);
            }
        }
        
        // For each contact set opt out preferences
        for (Contact con : relatedContacts)
        {
            con.HasOptedOutOfFax   = false;
            con.HasOptedOutOfEmail = false;
        }
        
        // Update the contact records
        update relatedContacts;
    }
}

You may think that this is overkill, and that the chances 15 length Ids will match alphabetically are pretty low, but if your functionality does something drastic like delete data, should you really be taking that chance? I urge you to consider this, as I will, in any further development involving id or string criteria in SOQL queries.

PS -> Thanks to Desynit's very own Gary McGeorge, who pointed out that old Sherlock Holmes saying :"when you have eliminated the impossible, whatever remains, however improbable, must be the truth?", Which certainly helped me work out why this was happening. Always something to bear in mind when tackling problems of this nature.