Aggregate SUM function fails

[Craz1k0ek]

The exact issue is described here. The sum function of Postgres can return several data types, which is not taken into account when executing the aggregate function.

The exact issue lies in the PostgresRow+Database.swift at line 42, where the sum is always decoded to an Int? even though a Double may be returned from the aggregate function.

Postgress Aggregate Functions

[Craz1k0ek]

I've done some more investigation on the matter and the issue can be solved multiple ways.

One would be to cast the type in the database already. For the query that means there must come some kind of cast parameter in the SQLExpression. This in turn can then be read and inserted in SQLSerializer which is called on line 13-14 of SQLDatabase in the sql-kit package. For postgres, the cast can be simply added in the query:

# SELECT SUM("scheme"."column"::<cast type>) FROM "table";
# For integer type, cast to integer is required.
SELECT SUM("scheme"."column"::integer) FROM "table";

For other databases, the implementation may vary, but this currently is only noticed on postgres, however, it may potentially be an issue for MySQL too.

Another option is to add the cast Type to the .sum function (and maybe any aggregate function) to enforce a certain cast on the later DatabaseOutput decode step as shown below:

struct Start: Model, Codable {
    ...
    @Field(key: "nr_of_planets")
    var nrOfPlanets: Int
    ...
}
// For a specific double, this ignores the fact that the field is Int.
// This is a requirement for stored bigints in postgres.
Star.query(on: req.db).sum(\.$nrOfPlanets, as: Double.self).flatMap { doubleVal -> Int in
    // Cast it manually
    if let d = doubleVal { return Int(d) }
    return -1
}

This issue is closely related to #92.