Why does filtering by a Owner Id and Owner Type fail in unit tests?

I wrote a class that needs to query for “cases owned by the current user or in a queue”, and so I wrote this seemingly normal query:

Case[] cases = [SELECT Id 
                FROM   Case 
                WHERE  IsClosed = false AND 
                       Id = :controller.getSelected() AND
                      (OwnerId = :UserInfo.getUserId() OR Owner.Type = 'Queue')];

This worked when I tested it in the UI, but when I went to write a unit test, I ended up retrieving zero rows, which prompted an assertion:

System.assertEquals(1, cmcc.getCaseCloseCount());

To fail with “Expected: 1, Actual: 0.”

Mildly perplexed, I took out OR Owner.Type = 'Queue' and it worked as expected. Thinking this might just be a one-off situation, I wrote a separate unit test to verify the behavior:

@isTest class queueFailTest {
    @isTest static void test() {
        Group g = new Group(Name='TestQueue',Type='Queue');
        System.runas(new User(Id=UserInfo.getUserId())) {
            insert g;
            insert new QueueSobject(QueueId=g.id, SObjectType='Lead');
        }
        insert new lead(lastname='test',company='test');
        insert new lead(lastname='testqueue',company='testqueue',ownerid=g.Id);
        lead[] record1 = [select id from lead where ownerid = :userinfo.getuserid()];
        lead[] record2 = [select id from lead where owner.type = 'queue'];
        lead[] record3 = [select id from lead where (ownerid = :userinfo.getuserid() or owner.type='queue')];
        System.assertEquals(1, record1.size());
        System.assertEquals(1, record2.size());
        System.assertEquals(2, record3.size()); // Expected: 2, Actual: 1
    }
}

This fails on line 15 as noted above (Expected: 2, Actual: 1), leading me to believe that Owner.Type = ‘Queue’ is somehow causing the OwnerId filter to be ignored despite being an OR filter.

Can anyone else confirm this behavior? Is this a Known Issue? Is there an alternative way of doing this query with just one query, and not two, just for the sake of unit tests?

Note: All three queries work normally in Execute Anonymous and normal SOQL (e.g. Data Loader) queries. It fails in unit tests regardless of SeeAllData being true or false (my org has no leads in it, as it’s just an empty Developer Edition).

Answer

Is there an alternative way of doing this query with just one query, and not two, just for the sake of unit tests?

As a trusty and time tested pattern for getting around SOQL Limitations, I would use a formula here:

OR(OwnerId = $User.Id, Owner.Type = "Queue")

And then you can just change your filter to:

WHERE IsOwnedByMeOrQueue__c = true

Attribution
Source : Link , Question Author : sfdcfox , Answer Author : Adrian Larson

Leave a Comment