Two-column index records are missing during Apex test run
Last updated 2017-09-08 ·Reference W-3845856 ·Reported By 3 users
Summary
In Apex test, there is a SOQL query and it uses 2-column index table based on query plan.
- Actual Result.
Records meet the filter conditions are not returned.
- Expected Result.
The results should return records meeting the filter conditions.
Repro
1. Fresh EE Org.
2. As an example:
Create two column index on Opportunity object, field1 is StageName and field2 is CreatedDate.
3. Create an Apex test: please change the AccountId to one accountId in your local.
===================================
@isTest
public class SOQL2ColIndexTest {
static testMethod void verifySOQL2ColIndex(){
List<Opportunity> opps = new List<Opportunity>();
//Insert data first
for(Integer i=0; i<10; i++){
Opportunity o = new Opportunity(CloseDate=Date.valueOf('2017-01-30'),AccountId='001xx000003DGfC',Name='test',StageName='Prospecting');
opps.add(o);
}
insert opps;
// set created date, this is system test method.
for(integer i = 0; i < 10; i++){
Test.setCreatedDate(opps[i].Id, date.today().addDays(-120));
}
Test.startTest();
// Do SOQL Queries
// Query on created date, no 2-col will be used and verify 10 records returned.
Map<Id, Opportunity> queryOpps1 = new Map<Id, Opportunity>([select id, name from opportunity where createdDate < 2017-03-10T00:00:00Z]);
System.assertEquals(10, queryOpps1.size());
// Query on stageName, no 2-col will be used and verify 10 records returned.
Map<Id, Opportunity> queryOpps2 = new Map<Id, Opportunity>([select id, name from opportunity where StageName='Prospecting']);
System.assertEquals(10, queryOpps2.size());
// Query on both stageName and created date, 2-col will be used and verify 0 records returned, but it should be 10.
Map<Id, Opportunity> queryOpps3 = new Map<Id, Opportunity>([select id, name from opportunity where createdDate < 2017-03-10T00:00:00Z and StageName='Prospecting']);
System.assertEquals(0, queryOpps3.size());
System.assertEquals(10, queryOpps3.size());
Test.stopTest();
}
}
===================================
4. Run the test, it will fail at "System.assertEquals(10, queryOpps3.size());"
Workaround
Remove the related two-column index.
Reported By (3)
Is it Fixed?
Any unreleased services, features, statuses, or dates referenced in this or other public statements are not currently available and may not be delivered on time or at all. Customers who purchase our services should make their purchase decisions based upon features that are currently available.
