NSPredicate SUBQUERY aggregates

Question

In all of the examples I've seen of SUBQUERY, @count is always used, e.g.,

SUBQUERY(employees, $e, $e.lastName == "Smith").@count > 0

So I have three very closely related questions, which work best as a single StackOverflow question:

1. Is there any use for SUBQUERY without @count? If so, I haven't found it.
2. Can any other aggregates be used with SUBQUERY? If so, I haven't been able to get them to work. (See below.)
3. What exactly does SUBQUERY return? The logical thing seems to be a filtered collection of the type of the first parameter. (I'm speaking conceptually here. Obviously the SQL will be something different, as SQL debugging shows pretty plainly.)

This gives an exception, as does every other aggregate I've tried other than @count, which seems to show that no other aggregates can be used:

SUBQUERY(employees, $e, $e.lastName == "Smith")[email protected] > 75000

(Let's leave aside for the moment whether this is the best way to express such a thing. The question is about SUBQUERY, not about how best to formulate a query.)

Mundi helpfully pointed out that another use for SUBQUERY is nested subqueries. Yes, I'm aware of them and have used them, but this question is really about the result of SUBQUERY. If we think of SUBQUERY as a function, what is its result and in what ways can it be used, other than with @count?

UPDATE

Thanks to Mundi's research, it appears that aggregates like @avg do in fact work with SUBQUERY, particularly with an in-memory filter such as filteredArrayUsingPredicate:, but not with Core Data when the underlying data store is NSSQLiteStoreType.

Answer 1

1. Yes, think of nested subqueries. See Dave DeLong's answer that explains subquery in very simple terms.
2. The reason your @avg does not work is unknown because it should actually work on any collection that has the appropriate attributes required by the aggregate function.
3. See 1.: SUBQUERY returns a collection.

Here is the transcript of an experiment that proves that the subquery works as expected.

import UIKit
import CoreData

class Department: NSManagedObject {
    var name = "Department"
    var employees = Set<Person>()

    convenience init(name: String) {
        self.init()
        self.name = name
    }
}

class Person: NSManagedObject {
    var name: String = "Smith"
    var salary: NSNumber = 0

    convenience init(name: String, salary: NSNumber) {
        self.init()
        self.name = name
        self.salary = salary
    }
}

let department = Department()
department.employees = Set ([
 Person(name: "Smith", salary: NSNumber(double: 30000)),
 Person(name: "Smith", salary: NSNumber(double: 60000)) ])


let predicate = NSPredicate(format: "SUBQUERY(employees, $e, $e.name = %@)[email protected] > 44000", "Smith")

let depts = [department, Department()]
let filtered = (depts as NSArray).filteredArrayUsingPredicate(predicate)

The above returns exactly one department with the two employees. If I substitute 45000 in the predicate, the result will return nothing.