Exposed: Aggregate functions can return null (per the spec) but not in Exposed

[spand]

Aggregate SQL functions such as sum, avg, max and such return NULL if there was no rows. Has this been considered ? It seems null cannot be expressed currently and will show itself as a NPE with quite high surprise factor in the it[sumCol] call.

val sumCol = TestTable.intCol.sum()
TestTable
        .slice(sumCol)
        .selectAll()
        .map { it[sumCol] }
        .firstOrNull()

*http://www.h2database.com/html/functions.html#sum
*http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_sum

[spand]

That was quick. We are not quite there yet however. It only works because you .max() on a nullable column.

This still fails:

@Test fun testGroupBy06() {
    withCitiesAndUsers { cities, users, userData ->
        val maxNullableId: Max<Int> = cities.id.max()

        cities.slice(maxNullableId).selectAll()
                .map { it[maxNullableId] }.let { result ->
            assertTrue(result.size == 1)
            assertNotNull(result.single())
        }

        cities.slice(maxNullableId).select { cities.id.isNull() }
                .map { it[maxNullableId] }.let { result: List<Int?> ->
            assertTrue(result.size == 1)
            assertNull(result.single())
        }
    }
}

I would think all of the aggregate functions should return a nullable version of their types.

Also, about the Average function there seems to be a need for changing the type.
H2 docs says: "The returned value is of the same data type as the parameter."
PostgreSQL: "numeric for any integer-type argument, double precision for a floating-point argument, otherwise the same as the argument data type"
MySQL: "The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE)."

Im not sure if what the intersection with these statements is.. but at the very least is there a way of converting from an Int column to a Double column?

[Tapac]

You are absolutely right about nullability of aggregated functions, but I think that not all of them can return null value (count for example).
I've fixed it and add your test to ensure.
Also I have changed return value of avg function to BigDecimal and it's possible to provide scale to it (test also included).