.sum() get the error if all values of a group is zero

[ditansu]

TL;DR

.sum() get error if all values of at a group is zero
[ ERROR ] PostgreSQLError.decode: Could not decode Int: 0x0000000000000000 (NUMERIC). (ErrorMiddleware.swift:26)

Explain:

1. Models:

final class TestTable: PostgreSQLModel {
  var id: Int?
  var name: String
  var score: Int
 …
struct TestRecord: Content {
  let name: String
  let score: Int
}

2. Query

return  conn.select()
        .column(\TestTable.name)
        .column(.sum(\TestTable.score), as: "score")
        .from(TestTable.self)
        .groupBy(\TestTable.name)
        .orderBy(\TestTable.name)
        .all(decoding: TestRecord.self)

3. Good result

…
    _ = try TestTable(name: "test1", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test2", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test1", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test2", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test3", score: 2).save(on: conn).wait()
…
    _ = try TestTable(name: "test1", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test2", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test1", score: 0).save(on: conn).wait()
    _ = try TestTable(name: "test2", score: 0).save(on: conn).wait()
    _ = try TestTable(name: "test3", score: 1).save(on: conn).wait()
…

4. Error for any variant below:

    _ = try TestTable(name: "test1", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test2", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test1", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test2", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test3", score: 0).save(on: conn).wait()
….
    _ = try TestTable(name: "test1", score: 0).save(on: conn).wait()
    _ = try TestTable(name: "test2", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test1", score: 0).save(on: conn).wait()
    _ = try TestTable(name: "test2", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test3", score: 1).save(on: conn).wait()
….
    _ = try TestTable(name: "test1", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test2", score: 0).save(on: conn).wait()
    _ = try TestTable(name: "test1", score: 1).save(on: conn).wait()
    _ = try TestTable(name: "test2", score: 0).save(on: conn).wait()
    _ = try TestTable(name: "test3", score: 1).save(on: conn).wait()
….

5. Error message:

[psql] [2018-10-07 20:37:19 +0000] SELECT "TestTable"."name", SUM("TestTable"."score") AS "score" FROM "TestTable" GROUP BY "TestTable"."name" ORDER BY "TestTable"."name" ASC []
[ ERROR ] PostgreSQLError.decode: Could not decode Int: 0x0000000000000000 (NUMERIC). (ErrorMiddleware.swift:26)
[WARNING] [Async] [QueueHandler] Read triggered when input queue was empty, ignoring: close(PostgreSQL.PostgreSQLMessage.CloseResponse.statement(name: "ELECT 3")).

[ditansu]

Update:

It seems the Fluent have a problem to work with PostgreSQL's numeric type when value is zero
The .sum() function upper type from int8 to numeric - because int8 + int8 not adjusted to int8 size. So, workaround is change type from Int8 to lower, for example to PostgreSQLDataType.int4 or .int2. In this case result will be int8 or int4, which no have problem with zero. Sure, if that is applicable to your situation.

workaround example

extension TestTable: Migration {
  public static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
    return PostgreSQLDatabase.create(self, on: connection) { builder in
      builder.field(for: \.id, isIdentifier: true)
      builder.field(for: \.name)
      builder.field(for: \.score, type: PostgreSQLDataType.int4)
    }
  }
}