Salesforce API response to /listViews/id/describe returns an incorrect query when both lead and campaign member filters on status field are used. Anyone else seen this behavior?

Here's our workflow

1. Our software makes a call to /services/data/v36.0/sobjects/Lead/listviews/00BU0000003VsoeMAC/describe, where  00BU0000003VsoeMAC is a leads list ID
2. The response from Salesforce includes a "query" like SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1') AND Status != 'Invited' AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null AND Status != 'Attended' ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST
3. Our software url-encodes this query and makes a GET to Salesforce to this url - /services/data/v36.0/query/?q=<encoded query>
4. Salesforce responds, and our software parses the results into a list of contact records

This workflow has been failing for lead lists with multiple filters on status. For instance, we have one lead list that has filters on both lead status and campaign status.

 Here's the query returned to us by Salesforce in step #2 above -
SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1') AND Status != 'Invited' AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null AND Status != 'Attended' ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST


There are three filters on "Status" here, all of which get applied to the lead object:

1. Status != 'Invited'
2. Status != 'Open'
3. Status != 'Attended'

The actual list definition has an Open filter on Lead Status and the other two (Invited/Attended) on Campaign Member Status. But in the query, the Status filter on Campaign Member doesn't seem to filtering in the Campaign Member part of the query. As a result, the query execution (step #4 above) returns us leads that are not part of the selected list since all of the status filtering is being done on leads, and not campaign member as it was meant to be. The list view in the UI shows the correct/expected data. It is the API that is not returning the expected data. 

We see a bug in the query and this is how it should have been - 

SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1' AND Status != 'Invited' AND Status != 'Attended')  AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST

If you notice, in our query above, Invited/Attended status clause has been made part of the CampaignMember select sub query whereas Open filter check is grouped in the where clause on the Leads table select statement.

Hoping someone here proposes a solution/workaround for this, or if it indeed is a bug, have it fixed for us :)

Thanks!
Manjusha
 

Salesforce API response to /listViews/id/describe returns an incorrect query when both lead and campaign member filters on status field are used. Anyone else seen this behavior?

Here's our workflow

1. Our software makes a call to /services/data/v36.0/sobjects/Lead/listviews/00BU0000003VsoeMAC/describe, where  00BU0000003VsoeMAC is a leads list ID
2. The response from Salesforce includes a "query" like SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1') AND Status != 'Invited' AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null AND Status != 'Attended' ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST
3. Our software url-encodes this query and makes a GET to Salesforce to this url - /services/data/v36.0/query/?q=<encoded query>
4. Salesforce responds, and our software parses the results into a list of contact records

This workflow has been failing for lead lists with multiple filters on status. For instance, we have one lead list that has filters on both lead status and campaign status.

 Here's the query returned to us by Salesforce in step #2 above -
SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1') AND Status != 'Invited' AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null AND Status != 'Attended' ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST


There are three filters on "Status" here, all of which get applied to the lead object:

1. Status != 'Invited'
2. Status != 'Open'
3. Status != 'Attended'

The actual list definition has an Open filter on Lead Status and the other two (Invited/Attended) on Campaign Member Status. But in the query, the Status filter on Campaign Member doesn't seem to filtering in the Campaign Member part of the query. As a result, the query execution (step #4 above) returns us leads that are not part of the selected list since all of the status filtering is being done on leads, and not campaign member as it was meant to be. The list view in the UI shows the correct/expected data. It is the API that is not returning the expected data. 

We see a bug in the query and this is how it should have been - 

SELECT FirstName, LastName, Company, Email, Phone, Title, toLabel(Status), CreatedDate, Owner.Alias, Id, LastModifiedDate, SystemModstamp FROM Lead WHERE IsConverted = false AND Id IN (SELECT LeadId FROM CampaignMember WHERE CampaignId = '701U0000001XvK1' AND Status != 'Invited' AND Status != 'Attended')  AND Owner.FirstName = 'KC' AND Status != 'Open' AND Email != null ORDER BY FirstName ASC NULLS FIRST, Id ASC NULLS FIRST

If you notice, in our query above, Invited/Attended status clause has been made part of the CampaignMember select sub query whereas Open filter check is grouped in the where clause on the Leads table select statement.

Hoping someone here proposes a solution/workaround for this, or if it indeed is a bug, have it fixed for us :)

Thanks!
Manjusha