+ Start a Discussion
Paul Dyson.ax812Paul Dyson.ax812 

SOQL in clause matching null to all Ids

We've found a very odd bug in our system which we've managed to track down to some Salesforce behaviour that is at the least counter-intuitive and may be a bug.

We have a query like

select Contact__c from Custom_Object__c where Contact__c in :listOfIds

Contact__c is a lookup relationship to contact. This returns all the Custom_Object__c records where the related Contact is in a list of Contact Ids

This works as expected unless we add null into listOfIds. If we do then every record is matched and it appears that null in an array of Ids queryed using and  'in' condition effectively breaks that condition as it is interpreted as any value.

Is this known behaviour? Is it intentional? Will it get fixed at some point? 

Yes for nul value error should be thrown. you can add a check for null values and then paas listOfIds to the query.

So how are you getting listOfIds? Are you querying for this?
Paul Dyson.ax812Paul Dyson.ax812
That's the odd thing. There is no error which we would have trapped and realised straight away what the issue is. Instead all records are returned.

The listOfIds is being generated by a query. Custom_Object__c has a lookup field Contact__c which is optional. We add all Ids to a set and then run the second query.

Clearly we can filter out the records with a null relationship but didn't think we'd have to. If listOfIds is (123, 456, null, 789) we'd expect only Contacts with ids 123, 456 and 789 to be returned, or a NullPointerException, not for it to return every Contact in the system!