Why are the results returned using a subquery different than when querying Tasks directly?

Question

I am running the code below in a Task trigger. The subquery on Account returns 0 rows, but the direct Task query returns 1 row; there should be 1 row. Can someone explain why this could be happening. Thanks!

            List<Task> debugTaskList = [select activityDate from Task
                       where ownerId <> :excludedId and isClosed = true
                      and activityDate != null and AccountId = :acctId
                        order by activityDate desc nulls last limit 1];

            List<Account> debugAcctList = [select Id,
                              (select activityDate 
                              from Tasks
                              where ownerId <> :excludedId and isClosed = true
                             and activityDate != null
                              order by activityDate desc nulls last limit 1)
                           from Account where id = :acctId];

            system.assert(false, 'acctList = ' + debugAcctList[0].Tasks.size() 
                              + ', taskList = ' + debugTaskList.size());

Answer 1

Background

Very interesting question, it seems like what you're doing should work just fine. After some further investigation it appears that the Tasks child relationship subquery only returns tasks directly related to accounts by Task.WhatId where as your direct query will return all tasks related to the account by Task.AccountId (which is a formula field that gets set for tasks related to accounts, contacts, cases, and opportunities for a particular account). In your example, my guess is one of the tasks returned in your direct query is related to a contact, case, or opportunity).

Fortunately there are a couple other virtual child relationship subqueries that should work for you depending on your use case (I got these details for the workbench). Given you're querying closed activities you'll want to use ActivityHistories.

Tasks

• Relationship Name: Tasks
• Child sObject: Task
• Field: WhatId

Open Activities

• Relationship Name: OpenActivities
• Child sObject: OpenActivity (really just activity, a polymorphic event and task)
• Field: AccountId

Activity History

• Relationship Name: ActivityHistories
• Child sObject: ActivityHistory (again, just an Activity)
• Field: AccountId

Answer 2

(select activityDate from Tasks

I believe the problem is that you're selecting activityDate from Tasks instead of Activity History. I don't show activityDate as being a valid related field in Tasks from Account. Instead, it only appears in ActivityHistory.

EDIT

The above was clearly a mistake from when I didn't click the API level names box in Force.com Explorer when checking my Dev Org. Please accept my apologies.

Taking another look...

from Tasks where ownerId <> :excludedId and isClosed = true and activityDate != null

You may want to break this part of your query up into something that looks more like

from Tasks where ownerId != (:excludedId AND isClosed = true AND activityDate != null)

There may be something about it picking up the value of excludedId and applying the value of operator to isClosed and/or the activityDate differently in one query than it does the other. I also have a preference for using != logical operator over the <> operator. I'd think that would at least be worth trying.