+ Start a Discussion
Brian.ax17Brian.ax17 

About SOQL includes

I've encountered some odd behaviour of the "includes" SOQL operator and would welcome opinions on whether there is a bug or this behaviour is by design.

I am executing a query with something like:
select id from object where afield includes('xyz') and bfield = 'something'

"afield" is a multi-select picklist, so includes is the only operation allowed. If the value 'xyz' is not in the picklist for afield, then all records in object are returned which match any other selection criteria. As soon as the value 'xyz' is added to the picklist fro afield, but not used in any record, then the query correctly returns no records.

In my real life situation, afield represents the user's role, and is deliberatly not included in the picklist, and the effect is that all records are process where in reality none should.

A possible work round would be to check the picklist values before executing the query, but unlike single select picklists, describe doesn't appear to return the picklist entries for multi-select picklists.

Brian

DevAngelDevAngel

Hi Brian,

Try doing this:

select id from object where (afield includes('xyz') and bfield = 'something')

I think this might be a bug, but the workaround is to include the entire criteria in parenthesis'.

Brian.ax17Brian.ax17

Thanks, but I'm afraid that didn't make any difference. An actual example is as follows:

Select id, Rule_Type__c, Rule__c, Contract_Type__c, Region__c, User_Role__c, Territory__c, Approver__c, Approver_Role__c, Name, Copy_to_Director__c, Copy_to_Manager__c , From_Value__c, To_Value__c, From_Discount__c, To_Discount__c from ApprovalRouting__c where (Rule_Type__c = 'Range' and Rule__c = 'Total Contract Value' and Contract_Type__c includes( 'Up-sell to Existing Customers') and Region__c = 'EMEA' and User_Role__c includes( 'Something Very Silly') and Territory__c includes( 'ERP Applications, EMEA') and From_Value__c <= 180000.0 and To_Value__c > 180000.0)

The above currently returns 10 records, if the value 'Something Very Silly' is added to the User_Role__C picklist, then correctly, no records are returned.

Brian

DevAngelDevAngel

Hi Brian,

This is a bug.  I don't have a work-around for you right now other than to look at the possible picklist values in the describe and make sure that the includes (" ") only uses a valid value before running the SOQL statement.

I've logged a bug so that the criteria is applied correctly.  I wonder though, is it an error to use an invalid picklist value in the includes statement?

docbilldocbill

I can't believe, here it is 7 years later, and this bug has never been fixed.  :(

 

BTW.  An alternative to using dynamic SOQL is to just to use a fixed size array.  e.g.

 

[ select Id from Bar__c where MyVar__c includes (:values[0],:values[1],:values[2],:values[3],:values[4]) ]

 

This solution works well when your picklist is small.   To handle the case where you have less than four values, then just append extra values to the list.  e.g.

 

while(value.size() < 4) values.add(value[0]);

List<Bar__c> bars = [

  select Id from Bar__c where MyVar__c includes (:values[0],:values[1],:values[2],:values[3],:values[4]) ];

 

The problem of course is what happens when you have too many.   Which is why this solution only works well for small lists.