Why does $0.date.max() produce an optional Date? rather than a Date if the Table doesn't have optional fields

[ajaxharg]

I am trying to understand the logic for why max() and all the other similar operators produce an optional value. In my case for some reason, $0.date.max() is producing an optional. I can coerce it to a date with $0.date.max().ifnull(Date.distantPast), but that doesn't seem right in this simple scenario.

Error: Initializer 'init(queenID:date:count:)' requires the types 'Optional' and 'Date' be equivalent

In the definition included below, there is no non-optional overload of max.

https://www.pointfree.co/episodes/ep317-sql-builders-advanced-selects In this episode, the functions are not returning an optionally wrapped type.

/// A maximum aggregate of this expression.
///
/// ```swift
/// Reminder.select { $0.date.max() }
/// // SELECT max("reminders"."date") FROM "reminders"
/// ```
///
/// - Parameters filter: A `FILTER` clause to apply to the aggregation.
/// - Returns: A maximum aggregate of this expression.
public func max(
  filter: (some QueryExpression<Bool>)? = Bool?.none
) -> some QueryExpression<QueryValue._Optionalized.Wrapped?> {
  AggregateFunction("max", [queryFragment], filter: filter?.queryFragment)
}

@Table
struct QueenLocation: Identifiable {
    let id: UUID
    var date : Date
    var queenID: Queen.ID
    var locationID: QueenHousing.ID        
    var componentID: HiveComponent.ID? 
}


@Selection
struct LastLocationDate {
    let queenID:Queen.ID
    let date:Date
}

@ObservationIgnored
    @FetchAll(QueenLocation
        .group(by: \.queenID)
        .select {
            LastLocationDate.Columns(
                queenID: $0.queenID,
                date: $0.date.max() **<-Error: Initializer 'init(queenID:date:count:)' requires the types 'Optional' and 'Date' be equivalent**
            )
        })
    
    var lastLocationDates

If I do the minimal example of 'FetchAll(QueenLocation.select{$0.date.max()}) var data' I get the following type

var data: [Optional <Date>.QueryOutput ]{ get }

I've done this for now, date: #sql("max(\($0.date))")

[mbrandonw]

max (and other aggregate functions) return optionals because that is how SQLite works. You can always run sqlite3 in a terminal window to explore what SQLite is doing under the hood:

% sqlite3
SQLite version 3.51.0 2025-06-12 13:14:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table foos (id integer primary key);
sqlite> select max(id) from foos;

sqlite> 

The above query shows that max(id) is null when there are no rows in the table.

https://www.pointfree.co/episodes/ep317-sql-builders-advanced-selects In this episode, the functions are not returning an optionally wrapped type.

The query builder created in our episodes are a simplified version of the final tool that was built.

I've done this for now, date: #sql("max(\($0.date))")

If you do that then you will just get a SQL error if the table is empty. And that may be just fine for your use case, but in certain kinds of queries it can definitely be a problem.

You can also use the ifnull function to coalesce to a value:

$0.date.max().ifnull(Date.distantPast)

[ajaxharg]

Ah thanks. I shall test with empty tables. I'm using preopulated ones at the moment.