5

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.

  • I don't think there is any issue with your query.It should work fine.I don't think Namespace prefix put any effect in queries until the query is in String format and you are using the Database.query() method to retrieve data. – Mr.Frodo Aug 1 '16 at 15:57
  • 4
    Have you treble checked that there are no fields or SObjects with the same names (ignoring the namespace prefix)? Suggest you play with the query in the developer console with/without the namespace prefix to see if you can reproduce the 100 vs 10 records result. (As I learned recently, IN and = are interchangeable.) – Keith C Aug 1 '16 at 16:59
  • Yep, checked all fields, none have the same API name as the packaged fields on the object in question. The best thing is - in dev console, the same query works without problems. – Ivan Vrtarić Aug 2 '16 at 9:54
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})
  • It turned out that the set indeed contained a null value in addition to a single regular SFDC ID, but it wasn't obvious in the logs even on the "Finest" setting until the set was serialized into JSON and explicitly logged. Thanks for setting me on the right path. – Ivan Vrtarić Nov 1 '16 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.