Fluent Postgres driver crashes getting sum and average aggregates

[hsharghi]

Describe the bug

In Postgres database, getting aggregate values of sum and average will throw PostgresDecodingError
(MySQL and SQLite databases both works as expected, and the bug is only occurred when using Postgres.)

PostgresDecodingError(code: typeMismatch, columnName: "aggregate", columnIndex: 0, targetType: Swift.Optional<Swift.Int>, postgresType: NUMERIC, postgresFormat: binary, postgresData: ByteBuffer { readerIndex: 0, writerIndex: 10, readableBytes: 10, capacity: 10, storageCapacity: 2048, slice: _ByteBufferSlice { 71..<81 }, storage: 0x0000000101827600 (2048 bytes) }

To Reproduce

Steps to reproduce the behavior:

1. Create model and migration for a simple model containing an Integer column.

final class User: Model {       
    static let schema = "users"
    
    @ID(key: .id)
    var id: UUID?

    @Field(key: "balance")
    var balance: Int
    
    init() { }
    
    init(id: UUID? = nil, balance: Int) {
        self.id = id
        self.balance = balance
    }
}

2. Create migrations for User model

struct CreateUsers: AsyncMigration {
    func prepare(on database: Database) async throws {
        try await database.schema(User.schema)
            .id()
            .field("balance", .integer)
            .create()
    }
    
    func revert(on database: Database) async throws {
        try await database.schema(User.schema).delete()
    }
}

3. Add a method to get sum and average of balance for all users

extension User {
    func getSum(on db: Database) async throws -> Int {
       try await User.query(on: db)
            .sum(\.$balance)
    }

    func getAverage(on db: Database) async throws -> Int {
       try await User.query(on: db)
            .average(\.$balance)
    }
}

4. Call any of getSum or getAverage methods will cause the app to crash.

Expected behavior

It is expected to get the sum or average values of the given column.

Environment

macOS Ventura 13.4 (22F66)
Swift 5.8
Vapor framework: 4.77.0
toolbox: 18.7.1

Additional context

As @gwynne mentioned in Discord:
The problem is caused by two factors:

1. Postgres is - correctly - concerned that taking the sum of a given series of values of a given integer type could overflow that type (imagine, for example, trying to add any positive number to an integer with the value 2_147_483_647, the maximum possible value for that type). Postgres returns the result as the next-larger integer type, which in the case of bigint is numeric (because there are no larger purely-integer types). In the case of avg(), it's the rather more straightforward issue that the average of any given series of integers of any size is not itself necessarily an integer. Notably, MySQL and SQLite don't do this, or at least not in a way that trips over the second factor:

2. Fluent does not take into account the fact that many aggregate functions do not return a value of the same type as their inputs. This is what really makes this ugly to fix; the fix would require breaking public API.

[gwynne]

This is a duplicate of vapor/fluent-postgres-driver#166