Xjyuk

I have an SOQL query between two custom objects, let's call them A__c and B__c, where A__c has a Lookup field called B__c that references a B__c instance and B__c has two fields X__c and Y__c like:

SELECT Id, B__r.Id, B__r.X__c, B__r.Y__c FROM A__c

When I have instances of B__c "shared" between the queried instances of A__c, i.e. where multiple A__c instances refer to the same B__c instance(s), does each A__c have its own copy of the related B__c or is SOQL smart enough to optimize the B__c instances so two or more returned A__c's actually use the same in-memory B__c as needed?

Empirically, it appears that the common B__c instances are actually shared (I can update the B__c instances obtained via A__c.B__r in different ways, based on other A__c values, and get the expected results). However, I can't find any official documentation about this. Can anyone point me to some documentation that states this as expected behaviour?

Can anyone point me to some documentation that states this as expected behaviour?

I've never seen such documentation.

Empirically, it appears that the common B__c instances are actually shared (I can update the B__c instances obtained via A__c.B__r in different ways, based on other A__c values, and get the expected results).

This is definitely true; I was able to verify this in my developer edition using ===, which compares memory addresses rather than contents:

Contact[] records = [select account.name from contact where account.name = 'demo'];
system.assert(records[0].account === records[1].account);

However, without documentation, we cannot rely on this behavior. At this point, someone should probably open a case with support. Either the documentation should be updated (thus ensuring we have a guarantee), or the behavior should actually be changed, because this could have unintended consequences for developers that try to do "clever" things with those records.

While I'm glad that Apex is saving us some memory (heap is really easy to fill up), undocumented behavior could be dangerous. I've never seen this behavior before, but I can envision some sort of algorithm that uses the account records for several different fields and ends up having invalid data because of this shared object behavior.

In Understanding Query Results, it says:

Query results are returned as nested objects. The primary or “driving” object of the main SELECT statement in a SOQL query contains query results of subqueries.

I didnt find much of other documentation on this, I have been using this kind of behaviour of SOQL from a long time.

For example, consider below SOQL:

System.debug(JSON.serialize([SELECT Name, Account.Name, Account.Phone, Account.AccountNumber 
 FROM Contact
 WHERE Id='XXXXXXXXXSJQA0']));

The debug you get will be as below:

[
 
 "attributes": 
 "type": "Contact",
 "url": "/services/data/v46.0/sobjects/Contact/0030K00001yZ4SJQA0"
 ,
 "Name": "Some mrs with POC",
 "AccountId": "XXXXXXXXX45qAAA",
 "Id": "XXXXXXXXXZ4SJQA0",
 "RecordTypeId": "XXXXXXXXX1I4LeQAK",
 "Account": 
 "attributes": 
 "type": "Account",
 "url": "/services/data/v46.0/sobjects/Account/XXXXXXX45qAAA"
 ,
 "Id": "XXXXXXXXX5qAAA",
 "Name": "United Oil & Gas Corp.123",
 "Phone": "(212) 842-5500",
 "AccountNumber": "CD355118"
 
 
]

This clearly shows that SOQL intelligently gets single instance of parent object with all fields. This has some advantages where you can update the parent object as below:

Contact cnt = [SELECT Name, Account.Name, Account.Phone, Account.AccountNumber 
 FROM Contact
 WHERE Id='0030K00001yZ4SJQA0'];

cnt.Account.Name = cnt.Account.Name + 'Changed';
cnt.Account.Phone = '1111111111';
update cnt.Account;

This way of processing saves additional SQOL queries and code.