Can anyone check and let me know if the same thing happens for you?
A bug in force.com explorer
Adding a subquery in the SELECT portion before FROM reduces the number of records returned.
Also if I add a where clause in subquery it returns some 250 records if I remove that it returns 19 records
if I remove that subquery itself it returns 267 records...
I tried the same query in workbench and it worked without any problems.
I checked the same with two of my friends in my organization and the same thing is happening for them as well.
Here is the email that I have sent to the Forcedotcom team :-
Adding a subquery with a where clause in the SELECT portion before FROM reduces the number of records returned. Ex:- This returns 267 records.
Select Id, Name, URL__c, Country__c, (select DocumentID__c,Name from DocumentBelongsTo__r limit 1) FROM Asset__c where isdeleted=false and ((Id IN (Select Asset__c from Exchange_Customer_Asset__c where isDeleted =false and Company__c ='001Z000000ABCPx' and (Asset__r.Title__c like '%message%' or Asset__r.Name like '%message%'))) ) LIMIT 10000
where as this returns 250 records :-
Select Id, Name, URL__c, Country__c, (select DocumentID__c,Name from DocumentBelongsTo__r limit 1), (Select Id,GroupLicense__c,Company__c From CustomerAsset__r Where Company__c = '001Z000000ABCPx') //This line is added
FROM Asset__c where isdeleted=false and ((Id IN (Select Asset__c from Exchange_Customer_Asset__c where isDeleted =false and Company__c ='001Z000000ABCPx' and (Asset__r.Title__c like '%message%' or Asset__r.Name like '%message%'))) ) LIMIT 10000
where as if I remove the where clause in the highlighted line this returns 19 records :-
Select Id, Name, URL__c, Country__c, (select DocumentID__c,Name from DocumentBelongsTo__r)
, (Select Id,GroupLicense__c,Company__c From CustomerAsset__r) //This line is added
FROM Asset__c where isdeleted=false and ((Id IN (Select Asset__c from Exchange_Customer_Asset__c where isDeleted =false and Company__c ='001Z000000ABCPx' and (Asset__r.Title__c like '%message%' or Asset__r.Name like '%message%'))) ) LIMIT 10000
Also when I remove the where clause in highlighted line or add a limit inside the highlighted line the no. of rows changes. Everything works perfectly fine in workbench.com
How can the data returned change based on subqueries inside select clause?
*Note :- I have hardcoded ids here just for querying
Attribution to: Sathya
Possible Suggestion/Solution #1
This is expected server side behavour, the server controls the size of the response to keep it within reasonable bounds (as most clients will parse the response in memory, you don't want to give the client a gigantic response). The server uses many things to decide what to return including the number & type of fields, and the total number of records returned, i.e. both the top level main records, and the child records, and the batch size hint set by the client.
In your queries you are adding additional child select queries which will increase the total number of records in the query result, and this will then affect the number of top level records in the first response. Remember that query
has its counterpart queryMore
to allow you to chunk through the entire resultset of your query. Remember to check the done flag on the returned queryResult.
Perhaps the force.com explorer doesn't show the queryMore option as needed, if so that's a bug. (although i don't think anyone is maintaining it these days).
Attribution to: superfell
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/1782