SOQL query in managed package doesn't filter objects

Question

I have a SOQL query in my Apex code similar to this:

List<My_Packaged_Object__c> myMethod(Set<Id> targetIds) {
     return [
        SELECT id, name, lookup_field_a__c, lookup_field_b__c,
            lookup_field_a__r.name, lookup_field_b__r.name
        FROM My_Packaged_Object__c
        WHERE lookup_field_a__c IN :targetIds
           OR lookup_field_b__c IN :targetIds
        ORDER BY name
    ];
}

This method is within a class in a managed package.

Let's say that targetIds set contains only 1 ID, there are 100 records of My_Packaged_Object__c type, and only 10 of those fulfill the query condition; when the method is called, it returns all 100 records. It's as if the where-clause is ignored.

While investigating logs on the affected sandbox org, I saw that this query was transformed into a query similar to this:

SELECT id, name, prefix__lookup_field_a__c, prefix__lookup_field_b__c,
    lookup_field_a__r.name, prefix__lookup_field_b__r.name
FROM My_Packaged_Object__c
WHERE lookup_field_a__c = :tmpVar1
   OR lookup_field_b__c = :tmpVar2
ORDER BY name

(formatted for easier reading)

Note that some fields have package prefix added, and some don't. Also note that IN keyword was changed into equality sign.

Does anyone know what could be the reason for this behaviour? There are some custom, user-added fields on My_Packaged_Object__c, but they all have their own prefix, none of them have the same API nameas the packaged fields, and they're not referenced in the query.

Answer 1

I think the main question you want to ask here is why the result of this query can return more than expected rows.

The possible reason for this is the null value in either of set.

If you have Null value in either of tmpVar1 or tmpVar2 it will return more than expected records.

Without this There is No practical way to return different result(console VS apex).

There were very few cases where indexes were not updated and sue to that it returned incorrect result. But that result was also consistent.

I think consistency is very basic and essential property of database and I dont believe that this could be possible without any logical error in our Apex code(for ex. null value). Otherwise there would be many threads about this issue on internet.

About the In and =: As Keith have already mentioned that = and IN are interchange in SOQL. For ex:

Set<Id> ids  = new Set<id> () ;
ids.add('0019000000xHXSAAA4');
ids.add('0019000000xHXSBAA4');
list<Account> acc1 = [SELECT id, name FROM Account where id = :ids];    
System.debug(acc1);
list<Account> acc2 = [SELECT id, name FROM Account where id in :ids];   
System.debug(acc2);

As you will notice both the SOQL statements will produce same result.

03:05:56.22 (32642737)|USER_DEBUG|[5]|DEBUG|(Account:{Id=0019000000xHXSAAA4, Name=GenePoint}, Account:{Id=0019000000xHXSBAA4, Name=United Oil & Gas, UK})
03:05:56.22 (39886606)|USER_DEBUG|[7]|DEBUG|(Account:{Id=0019000000xHXSAAA4, Name=GenePoint}, Account:{Id=0019000000xHXSBAA4, Name=United Oil & Gas, UK})