Why does this query exclude records if the field order is changed

Question

I have code that works on a scratch org but isn't working in production. I've tracked it down to a query that's searching a table. The Apex code looks like this:

SELECT Id
FROM Tag__c
WHERE Name IN :LoanController.fieldTagMap.values()
ORDER BY Name

Here's LoanController.fieldTagMap:

private static Map<String, String> fieldTagMap = new Map<String, String>{
    'AppraisalExpiration__c' => 'Aprsl Expire Soon',
    'RateExpiration__c' => 'Lock Expire Soon'
};

I log into production and go to the dev console and execute this query:

SELECT Id, Name
FROM Tag__c
WHERE Name IN ('Aprsl Expire Soon', 'Lock Expire Soon')
ORDER BY Name

Only the record with the name "Lock Expire Soon" is returned. So I try this query:

SELECT Id, Name
FROM Tag__c

I get all the records including "Aprsl Expire Soon". I copy the version in my query and paste it into the field and save the record, no change.

Two more clues:

• If I exclude the ORDER BY, I get both records back, but in the wrong order.
• If I retain the ORDER BY clause but reverse the order of fields of the IN clause, (('Lock Expire Soon', 'Aprsl Expire Soon')), I get both records back.

So by altering the query slightly, I can get both records back, but why should this matter? How can the order of my list of fields affect the returned records?

Answer 1

I'm unable to reproduce these results in Dev Console (Execute Anonymous)

private static Map<String, String> fieldTagMap = new Map<String, String>{
    'AppraisalExpiration__c' => 'Aprsl Expire Soon',
    'RateExpiration__c' => 'Lock Expire Soon'
};
    
Tag__c tag1 = new Tag__c();
tag1.Name = 'Aprsl Expire Soon';
insert tag1;

Tag__c tag2 = new Tag__c();
tag2.Name = 'Lock Expire Soon';
insert tag2;

List<Tag__c> idList = [SELECT Id, Name
FROM Tag__c
WHERE Name IN :fieldTagMap.values()
ORDER BY Name];

System.debug(idList);   

Results:

20:18:45:073 USER_DEBUG [19]|DEBUG|(Tag__c:{Id=a1S5900000015XeEAI, Name=Aprsl Expire Soon}, Tag__c:{Id=a1S5900000015XfEAI, Name=Lock Expire Soon})

Are you sure there isn't a typographic error in one of your Name strings?