Soql in apex trigger returns different result depending on user who causes the trigger.

Question

In Communities we have an ideas component where users can post/comment/vote on different ideas.

We are trying to create a trigger which will send an email to the idea creator whenever someone comments on their idea.

In doing so we have set up an ideaComment trigger.

In creating the email we are querying the parent idea to get some fields which help fill out the email body:

return [SELECT Id, Title, Body, CreatedById, CreatedDate, CreatorName, CreatorSmallPhotoUrl FROM Idea WHERE Id in :ideaIds];

One of the fields we use is the CreatorSmallPhotoUrl. In a development sandbox, I noticed that CreatorSmallPhotoUrl returns as a relative URL. ie:

/*CompanyName*/profilephoto/7290t000000CbGo/T

However, running the same query in the dev console returned absolute URLs which is my first point of confusion.

Despite this confusion, we made it work with relative URLs, however, I have now found this does not work in our full sandbox... sometimes.

While logged in to our customer community as an admin user the query returns a relative URL. However, when logged in as company customer community user it returns an absolute URL.

My understanding is that triggers should all run in system context, so I do not understand why users of different profiles would cause different query results.

One thing I tried which I was hoping would shed some light on this confusion was to set up a very simple test class and run the query as different profiles.

The test class is here:

@isTest(SeeAllData=true)
public class MyTestClass {
    static testMethod void test1(){
        Set<Id> ideaIds = new Set<Id>();
        ideaIds.add('0870t000000027cAAA');
        User communityUser = [SELECT ID FROM User WHERE Email='removed_for_stackExchange'][0];
        System.runAs(communityUser){
            System.debug([SELECT Id, Title, Body, CreatedById, CreatedDate, CreatorName, CreatorSmallPhotoUrl FROM Idea WHERE Id in :ideaIds]);
        }
        System.debug([SELECT Id, Title, Body, CreatedById, CreatedDate, CreatorName, CreatorSmallPhotoUrl FROM Idea WHERE Id in :ideaIds]);

        User adminUser = [SELECT ID FROM User WHERE Email='removed_for_stackExchange'][0];
        System.runAs(adminUser){
            System.debug([SELECT Id, Title, Body, CreatedById, CreatedDate, CreatorName, CreatorSmallPhotoUrl FROM Idea WHERE Id in :ideaIds]);
        }
    }
}

I found that all three debug statements returned absolute URLs.

So in summary:

In developer sandbox:

1. query in trigger always returns relative URLs
2. query in dev console always returns absolute URLs

In full sandbox:

1. query in trigger, when fired by an admin, returns relative URLs
2. query in trigger, when fired by a customer community user returns absolute URLs
3. query in dev console returns absolute URLs
4. query in test returns absolute URLs even when running as customer community user profile.

Can anyone explain this to me?